April 26, 2012 at 3:51 am
How do I write a SQL which outputs all the days of the given month even when the Count of the Registrations in the Group by is Zero?
This will skip zero reg. days from the output.
select convert(integer,DATENAME(day,sourcedate)) as Day, count(*) as RegTotal
from tblName
where year(sourcedate) = 2012
and month(sourcedate) = 4
group by DATENAME(day,sourcedate)
order by 1
Many Thanks
BK
April 26, 2012 at 4:02 am
The best option is to create and use CalendarTable, but you can do it by:
select convert(integer,DATENAME(day,m.MonthDay)) as Day, count(*) as RegTotal
from (SELECT DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,'20120401') as MonthDay
FROM sys.objects) AS m -- on fly calendar, you can use any table which have more than 31 records
LEFT JOIN tblName AS t
ON t.sourcedate = m.MonthDay -- will only work if sourcedate doesn't contain time part
where year(m.MonthDay) = 2012
and month(m.MonthDay) = 4
group by DATENAME(day,m.MonthDay)
order by 1
April 26, 2012 at 5:36 am
Thank you Eugene.
Both my SQL and yours (very interesting SQL), gives the same result.
Day RegTotal
116
4121
532
636
737
836
933
1046
1145
I thought of a calendar table but then it needs to be pre-populated for past and future years and all days correct in month (30 and 31) and 28/29 for Feb. Leap Years.
That makes it very complicated.
Any other suggestions ?
Tx
Bij
April 26, 2012 at 6:13 am
kotharibij (4/26/2012)
Thank you Eugene.Both my SQL and yours (very interesting SQL), gives the same result.
Day RegTotal
116
4121
532
636
737
836
933
1046
1145
I thought of a calendar table but then it needs to be pre-populated for past and future years and all days correct in month (30 and 31) and 28/29 for Feb. Leap Years.
That makes it very complicated.
Any other suggestions ?
Tx
Bij
If you could read this article[/url] about the best way to post DDL and sample data then post back with it, you'll receive working, testing solutions. Otherwise, we have to guess what your table structure is like which causes issues.
Eugene's code essentially uses a "tally" table to create an OTF calendar table so should work. If it doesn't, then there is something about your table or data structure that we're not aware of.
April 26, 2012 at 6:18 am
kotharibij (4/26/2012)
Thank you Eugene.Both my SQL and yours (very interesting SQL), gives the same result.
Day RegTotal
116
4121
532
636
737
836
933
1046
1145
I thought of a calendar table but then it needs to be pre-populated for past and future years and all days correct in month (30 and 31) and 28/29 for Feb. Leap Years.
That makes it very complicated.
Any other suggestions ?
Tx
Bij
1. I cannot believe that the my and yours queries give the same result, as my one should retunr row for every day in April, regardless if this day found in your table "tblName".
Actually, my query should be modified slightly to return 0 as count for days which not found in tblName. Just change COUNT(*) to COUNT(t.sourcedate)
2. Calendar table is not complicated at all! It will make all such queries much simpler and most likely faster. To populate for 5 years starting from 2009 you can do simply:
SELECT DayDate
FROM
(
SELECT DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,'20090101') as DayDate
FROM sys.objects s1 CROSS JOIN sys.objects s2
) q
WHERE DayDate < = '20131231'
April 26, 2012 at 7:23 am
The table is so simple. It's just got a date column and a regID column (integer). The rest of the columns not applicable for this query.
I can see how your calender table from sys objects is simple and efficient. On it's own it gives me all the dates I need for the month.
It's only when joining (left join) with the tblName of Registration, it ignores the dates not existing in my table.
I used the isNull() function and checked with having clause to see if it filters off the zero reg. days. but didn't work.
I changed your query table to CTE type table and then did left outer join, still the same.
I will see if I can use #hash tables (temp. table) to store and then join...
April 26, 2012 at 7:36 am
As I don't have a crystal ball or magic mirror, I cannot see what you are doing without you posting complete query and tables DDL. It doesn't matter how good and detailed you are trying to describe it in a plain text, only code gives the right picture. Please follow the link at the bottom of my signature and you will find the information about how to post on this forum to help your helpers...
April 26, 2012 at 9:04 pm
Eugene,
Didn't I see you post a CrystalBall CLR last month?
I gotta locate it because I planned to run a performance test against my new MagicMirror SP using Jeff Moden's million row test harness to generate random thoughts.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 27, 2012 at 2:38 am
April 27, 2012 at 11:26 am
As already indicated-- you should include your code.
However...using my calendar table-- which I leave for you to derive-- I got correct results. Note the ISNULL ain't needed (I was surprised to see this). Also note the (better for performance) WHERE clause.
--calendarbase has 1 row per date. For 4/1/2012, datege = 4/1/2012 and datelt = 4/2/2012
--mytable has times in the datetime so I must use the more verbose ON clause. It still works if all your times are 00:00:00
select cb.datege,datepart(day,cb.datege) as dayofmonth,count(fm.mypk) qty
from calendarbase cb
-- you can use a simpler ON clause if yourtable.date has all times = 00:00:00 (on fm.createddt = cb.datege)
left join mytable fm on fm.createddt >= cb.datege and fm.createddt < cb.datelt
where cb.datege >= '4/1/2012' and cb.datege < '5/1/2012'
group by cb.datege
order by cb.datege
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply