October 14, 2013 at 11:34 pm
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.
October 15, 2013 at 1:08 am
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
October 15, 2013 at 1:55 am
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.
October 15, 2013 at 2:26 am
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.
October 15, 2013 at 2:49 am
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
October 15, 2013 at 4:45 am
Thank you so much Lynn.
Your suggested solution is perfect.
Thanks
Abhas.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy