April 14, 2008 at 1:15 pm
I have the need to generate a table variable that has two fields: MonthNum and DayNum. What I need to be in it is a total of 372 records. For each month numer I need a corresponding day number from 1 to 31. So in the end for each of the 12 months I end up with 31 records. What is an easy way to get that?
April 14, 2008 at 1:18 pm
You want to have 31 days even for the months that have fewer than 31 days?
April 14, 2008 at 1:27 pm
As strange as this sounds, yes, I think. I am working on a report to compare revenue from month to month and the users want to know where they are at on any given day an how that compares to previous months. So, since some months have 31 days then I am going to extend the ones with fewer to that many days. It is a running total so it will just be flat in those months for the extra days.
April 14, 2008 at 1:29 pm
If you have a numbers table, it's easy enough:
;with Months (Month) as
(select number
from dbo.Numbers
where number between 1 and 12),
Days (Day) as
(select number
from dbo.Numbers
where number between 1 and 31)
select Month, Day
from months
cross join days
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 14, 2008 at 3:35 pm
Thanks GSquared! I do have a numbers table and this solved the issue I was having. I do want to get clarrification though so I understand what is going on rather than just blindly using it. Trying to learn from this for the future. It appears that you are using a cte but almost looks like you're using two. I didn't think you could have multiple cte definitions. Now in looking in Books Online I see that you can but only if they are combined with one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT which we aren't using here.
Just trying to be better educated. Thanks for your help and explainations.
April 14, 2008 at 3:43 pm
You can use multiple CTEs if they are separated with commas. The stuff about Union, et al, is for recursive CTEs or multiple queries in one definition. Slightly different subject than using two or more CTEs. Books Online doesn't have much about the comma thing. I discovered that on my own. I've seen others use it, but I'm not sure where they found it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 14, 2008 at 4:05 pm
OK. Thanks again for your help.
April 14, 2008 at 8:23 pm
Just my 2 cents... you don't need CTE's for this... don't even need derived tables... just the Cross Join...
SELECT t1.N AS Month, t2.N AS Day
FROM dbo.Tally t1 --or, change to your numbers table name
CROSS JOIN
dbo.Tally t2 --or, change to your numbers table name
WHERE t1.N <= 12
AND t2.N <= 31
--Jeff Moden
April 15, 2008 at 9:11 am
Yeah, the CTEs were just a sample. There are a number of ways to accomplish this. The best choice will depend on the rest of the code it's being used with.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply