Click here to Skip to main content
14,427,367 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi,
i need to create a query where i will pass year as int and will get all the records in all the months of that year.

so for example if i enter 2013 the output will be like below
month  count 
jan     10 
feb     0
mar     25
apr     15
may     15
jun     14
july    12
aug     0
sep     22
oct    15 
nov    26 
dec     0

i tried below query which gives me output like below
jan     10 

mar     25
apr     15
may     15
jun     14
july    12

sep     22
oct    15 
nov    26 

here is my query.
select month(DepotArrivalDate), count(*)
from tbl_CMS_Container
WHERE     year(DepotArrivalDate) = '2013' 
group by month(DepotArrivalDate) 

the issue is i dont have any record for feb / dec month so this query is not giving me any output for that row but i want that if that do not have it should show 0 like first output.
Posted
Updated 6-Jan-20 3:49am
v3
Rate this:
Please Sign up or sign in to vote.

Solution 1

Create a table variable with the full set of months, and populate with the twelve options.

Then use left join to get what you want.

Something like:

declare @Months table ( Month varchar(3))

insert into @Months values ('Jan'), ('Feb'), ('Mar'), .... 

select M.Month, count(*)
from @Months M
left join ....
   
v2
Comments
phil.o 6-Jan-20 8:57am
   
I changed the code tag to a pre tag, which is more suitable for multiline code-blocks. Hope you don't mind :)
Rate this:
Please Sign up or sign in to vote.

Solution 2

You may wish to create a table with all of the month/year/count combos you expect - then update that with your results. You may wish to initialize the count field of the table w/zeros.

Another option, more generalized, would employ a single list of Jan - Dec (1-12) and you may then use a LEFT JOIN to your query, matching only the month's numeric values: good for only one year at a time, but always good.

When creating the return set from the LEFT-JOIN option, you may use ISNULL(your-count-column, 0) to replace the nulls from missing months with 0's

   
Rate this:
Please Sign up or sign in to vote.

Solution 4

select [month],[count] from (SELECT
SUM (CASE WHEN DATEPART (MONTH,RR.checkin) = 1 THEN 1 ELSE 0 END) 'Jan',
SUM (CASE WHEN DATEPART (MONTH,RR.checkin) = 2 THEN 1 ELSE 0 END) 'Feb',
SUM (CASE WHEN DATEPART (MONTH,RR.checkin) = 3 THEN 1 ELSE 0 END) 'Mar',
SUM (CASE WHEN DATEPART (MONTH,RR.checkin) = 4 THEN 1 ELSE 0 END) 'Apr',
SUM (CASE WHEN DATEPART (MONTH,RR.checkin) = 5 THEN 1 ELSE 0 END) 'May',
SUM (CASE WHEN DATEPART (MONTH,RR.checkin) = 6 THEN 1 ELSE 0 END) 'Jun',
SUM (CASE WHEN DATEPART (MONTH,RR.checkin) = 7 THEN 1 ELSE 0 END) 'Jul',
SUM (CASE WHEN DATEPART (MONTH,RR.checkin) = 8 THEN 1 ELSE 0 END) 'Aug',
SUM (CASE WHEN DATEPART (MONTH,RR.checkin) = 9 THEN 1 ELSE 0 END) 'Sep',
SUM (CASE WHEN DATEPART (MONTH,RR.checkin) = 10 THEN 1 ELSE 0 END) 'Oct',
SUM (CASE WHEN DATEPART (MONTH,RR.checkin) = 11 THEN 1 ELSE 0 END) 'Nov',
SUM (CASE WHEN DATEPART (MONTH,RR.checkin) = 12 THEN 1 ELSE 0 END) 'Dec'

FROM Orders RR WITH(NOLOCK)
WHERE YEAR(RR.checkin)= 2020) tb1
UNPIVOT (
count for [month] in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)

) unpvt
   
Comments
CHill60 6-Jan-20 10:40am
   
I suspect you were downvoted because the question is so old and you did not format or explain your solution. I have partially countered the downvote because this is a reasonable elegant solution and produces exactly what the OP wanted (5 years ago though)… it could do with some explanation though. Personally I would have used the more succinct
select MM.[Month], MM.[text], ISNULL(COUNT(MONTH(RR.Checkin)), 0) as [count]
FROM (VALUES (1,'Jan'),(2,'Feb'),(3,'Mar'),(4,'Apr'),(5,'May'),(6,'Jun'),(7,'Jul'),(8,'Aug'),(9,'Sep'),(10,'Oct'),(11,'Nov'),(12,'Dec')) MM([Month], [text])
LEFT OUTER JOIN @Orders RR ON MM.[Month] = MONTH(RR.checkin) AND YEAR(RR.checkin)= 2019 
GROUP BY MM.[Month], MM.[text]
which is what Solution 2 suggested.
Might be best to sticking to answering more recent posts

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100