display 12 month list through query

Question

display 12 month list through query

abhas

SSCarpal Tunnel

Points: 4681
More actions
October 14, 2013 at 11:34 pm

#283890

Hi All,
I am havinfg a column in a table which store a date. say for eg birthDate. I want to display count monthwise.But in case if there is no birthday falls in March Month then i need to display column header as 3 and under that 0 (zero) count. Can you please suggest any idea.
Thanks
Abhas.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

Create a (temporary) table that hold all the month values. Outer-join this table with your table that holds the birthdays on the extracted month-value of that birthday. Group and total on the month value. The code would be something like this:

create table #months (month_nr tinyint identity(1,1), month_value char(3))

insert into #months (month_value)

values ('Jan'), ('Feb'), ('Mar'), ('Apr'), ('May'), ('Jun'), ('Jul'), ('Aug'), ('Sep'), ('Oct'), ('Nov'), ('Dec')

select month_value, count(birthday) as nr_of_birthdays

from your_table

right outer join #months

on datepart(month, your_table.birthday) = #months.month_nr

group by month_value, month_nr

order by month_nr

drop table #months

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 2

Thanks HB,

But i need to display data for prev 5 months, current month and next 6 months. along with year.for example for example, current month is Oct, then I need to display as 05-2013,06-2013........04-2014

Thanks

Abhas.

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

abhas (10/15/2013)
Thanks HB,
But i need to display data for prev 5 months, current month and next 6 months. along with year.for example for example, current month is Oct, then I need to display as 05-2013,06-2013........04-2014
Thanks
Abhas.

This is always easier when the poster supplies DDL, readily consumable sample data and expected results based on that sample data along with a description of what they want to do.

Here's my guess at your sample data: -

--TestEnvironment of 1,000,000 rows of random-ish data

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN;

DROP TABLE #testEnvironment;

END;

--1,000,000 Random rows of data

SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,

CAST(RAND(CHECKSUM(NEWID())) * 365 /*(Number of days in range)*/ + CAST('2013-06-25' AS DATETIME) /*(Start date, e.g. '1945-01-01 00:00:00*/ AS DATE) AS birthDate

INTO #testEnvironment

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

CREATE CLUSTERED INDEX cl_ID_testEnvironment ON #testEnvironment(ID);

CREATE NONCLUSTERED INDEX nc_birthDate_testEnvironment ON #testEnvironment(birthDate);

Here are two options that produce what I think is your expected results, based on my guess of your sample data: -

--Option 1

SELECT COUNT([SourceData].birthDate) AS [Number of Births], [YearOfMonths].[Month]

FROM (SELECT b.[Month]

FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))a(N)

CROSS APPLY (VALUES(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + a.N, 0)))b([Month])

) [YearOfMonths]

OUTER APPLY (SELECT birthDate

FROM #testEnvironment

WHERE [YearOfMonths].[Month] = DATEADD(MONTH, DATEDIFF(MONTH, 0, birthDate), 0)

) [SourceData]

GROUP BY [YearOfMonths].[Month]

ORDER BY [YearOfMonths].[Month];

--Option 2

SELECT COUNT([SourceData].birthDate) AS [Number of Births], [YearOfMonths].[Month]

FROM #testEnvironment [SourceData]

RIGHT OUTER JOIN (SELECT b.[Month]

FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))a(N)

CROSS APPLY (VALUES(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + a.N, 0)))b([Month])

) [YearOfMonths] ON [YearOfMonths].[Month] = DATEADD(MONTH, DATEDIFF(MONTH, 0, [SourceData].birthDate), 0)

GROUP BY [YearOfMonths].[Month]

ORDER BY [YearOfMonths].[Month];

If that isn't what you want, can you have a read through this link (http://www.sqlservercentral.com/articles/Best+Practices/61537/)[/url] and set up some DDL, consumable sample data and expected results based on your sample data ?

Thanks.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Hope this helps get you started. Not enough in your original post to really provide you with all the code you need.

with Tally12 as (select n from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12))dt(n)),

RunningCalendar as (select dateadd(month, n - 6, dateadd(month, datediff(month, 0, getdate()), 0)) as Dates from Tally12)

select cast(year(Dates) as char(4)) + '-' + right('0' + cast(month(Dates) as varchar(2)), 2)

from RunningCalendar

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 5

Thank you so much Lynn.

Your suggested solution is perfect. 🙂

Thanks

Abhas.