Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

Sql help

Hi,
I am trying to write sql to find the info based on a particular field, grouped by date (quarter)
The sql works fine but I have two more points,
1. Be able to add the DatePart(year, date) to the date columns - so that we know which year quarter it is
As you can see in below code it would be adding 2010 q1 + 2011 q1
Need to get over that
First is most important
2. Is there any way,
That I could change the above sql to have count(MAN) for multiple models?
Displayed as this model had count = this - in particular quarter
select DatePart(quarter, date) as date1,  count(MAN)
from table
where DatePart(year, date) >= '2010'
and model = 'Particular'
group by DatePart(quarter, date)

Open in new window

0
SameerMirza
Asked:
SameerMirza
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:
select model, DatePart(year, date), DatePart(quarter, date) as date1,  count(MAN)
from table
where DatePart(year, date) >= 2010
group by model, DatePart(year, date), DatePart(quarter, date) 

Open in new window

0
 
SameerMirzaAuthor Commented:
thanks angel. But issue is to have done somthing like
DatePart(quarter, date) + '/' + DatePart(year, date)
Problem rises when it comes to the data conversion related issue
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you mean:
select model
, cast(DatePart(year, date) as varchar(10)) + '/' + cast( DatePart(quarter, date) as varchar(10)) as date1,  count(MAN)
from table
where DatePart(year, date) >= 2010
group by model, cast(DatePart(year, date) as varchar(10)) + '/' + cast( DatePart(quarter, date) as varchar(10))

Open in new window

0
 
SameerMirzaAuthor Commented:
yes. Its perfact
0
 
SameerMirzaAuthor Commented:
thx
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now