Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
All_mememe
Starting Member
2 Posts |
 Posted - 2011-02-03 : 06:41:15
|
| I need a query that selects all Expiry dates from a table and counts the number of expiry dates per month. I want it to also display the month name. If there are no dates in one month I still want it to display eg Jan 0. At the moment I have: select month(ExpiryDate) as E, count (*) as Ofrom Taxgroup by month(ExpiryDate) Result:E O1 33 45 97 18 510 1If there are no expiry dates for the other months I need to these to display and 0 occurrences for them. Is this possible? Any help would be appreciated! Thanks! |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-03 : 06:58:25
|
| check if this idea may help you!!Blue is yours query .. red is just an addition to get missing required info declare @tab table (ymonth int)insert into @tab values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)select month(ExpiryDate) as E, count (*) as Ofrom Taxgroup by month(ExpiryDate)Union AllSelect ymonth,0 from @tab where ymonth not in (select distinct month(ExpiryDate) from Tax)CheersMIK |
 |
|
|
All_mememe
Starting Member
2 Posts |
Posted - 2011-02-03 : 15:45:47
|
| Thanks MIK but unfortunately this didn't seem to work for me! |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-03 : 16:58:18
|
The above would work with UNION instead of UNION ALL. UNION will remove duplicates, UNION ALL will notHow about this?select Convert(varchar(3),ExpiryDate) as Mon,count (*) as Cntfrom Taxgroup by Convert(varchar(3),ExpiryDate)Union Select Convert(varchar(3),dateadd(m,number,'19000101')),0FROM master.dbo.spt_valuesWhere type='p' and number <=11
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-04 : 01:10:52
|
| @all mememe, No problem at all but do explain in your response that what do you mean by Not Work :). Might be I understood your requirements incorrectly. But if its the problem in explanation then every one else will come to you with same solution :D@DataGuru, I think there would be no duplicates .. as i am filtering them out by the condition where ymonth not in (select distinct month(ExpiryDate) from Tax) 2,4,6,9,11,12 with a 0 in the second column would be the result set of the second statement.. isn't it? However i think there could be duplicates in your query e.g. what if the upper part of your query returns (Jan, 50) and the lower one returns (Jan,0), and applying UNION over these two data sets. Cheers!MIK |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-04 : 07:35:23
|
quote:
Originally posted by MIK_2008
@all mememe, No problem at all but do explain in your response that what do you mean by Not Work :). Might be I understood your requirements incorrectly. But if its the problem in explanation then every one else will come to you with same solution :D@DataGuru, I think there would be no duplicates .. as i am filtering them out by the condition where ymonth not in (select distinct month(ExpiryDate) from Tax) 2,4,6,9,11,12 with a 0 in the second column would be the result set of the second statement.. isn't it? However i think there could be duplicates in your query e.g. what if the upper part of your query returns (Jan, 50) and the lower one returns (Jan,0), and applying UNION over these two data sets. Cheers!MIK
True...the IN filter should remove them, and there would be potential duplicates in the UNION I showed as well for the reason you describe. Brain dead last night for the most part..thanks for reviewing! LOL.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
|
|
|
|
Sql query - Select all months and count?