• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • 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] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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