April 28, 2008 at 7:58 am
How would I go about creating a date column that would give me the sequential dates for a given date range?
Declare @StartDate DateTime
Declare @EndDate DateTime
SET @StartDate = '09/01/2007'
SET @EndDate ='09/15/2007'
Select NULL AS 'DateOfRange',
0 AS 'NacDateCounts',
0 AS '72hourResignationsCounts',
0 AS 'NetSignedCounts',
0 AS 'RunningTotal',
0 AS 'NoInactivesCounts',
0 AS 'NoReactivationsCounts',
0 AS 'TotalActiveCounts',
0 AS 'TotalInactiveCounts'
FROM Consultant
WHERE nacdate >= @StartDate AND nacdate <= @EndDate
So in this case the DateOfRange column would contain a row for each day in the date range:
DateOfRange NacDateCounts 72hourResignationsCounts NetSignedCounts RunningTotal NoInactivesCounts NoReactivationsCounts TotalActiveCounts TotalInactiveCounts
----------- ------------- ------------------------ --------------- ------------ ----------------- --------------------- ----------------- -------------------
09/01/2007 0 0 0 0 0 0 0 0
09/02/2007 0 0 0 0 0 0 0 0
And so on...
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 28, 2008 at 8:05 am
Art
The best way is to have a separate "numbers table" or "tally table", and to join to that. They have a multitude of uses and usually come out on top for speed. Search this site - there's lots of information on how to create them.
John
April 28, 2008 at 8:25 am
I'd agree with John. Disk space is cheap and having a table that stores dates would give you quicker results than some complicated SQL to do the same thing.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 28, 2008 at 8:28 am
Numbers table is great for that kind of thing, but I also keep a Dates table. Good for things where you need special data in date ranges, like holidays and weekends and such.
Either one will work for this.
- 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 28, 2008 at 8:29 am
I was thinking along the lines of creating a temp table to hold the dates generated by the date range and then join to it as I need it. But I need to be able to generate the dates. I can get the next date with:
SELECT DATEADD(dd,1,@StartDate) AS DateRange
But I wuld need all the dates in the range though.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 28, 2008 at 8:34 am
Art
That's why I said to search this site - there are some ingenious ways of generating the numbers/dates if you look for them!
John
April 28, 2008 at 8:38 am
One such solution would be to create an User-Defined Function that takes in the Start and End date and returns a table variable with all the dates
Then use a while loop to load each date from the start to the end into the table variable.
Dave Novak
April 28, 2008 at 9:13 am
create table dbo.Numbers (
Number int identity (0, 1) primary key,
Junk bit)
go
insert into dbo.Numbers (junk)
select top 10000 0
from sys.all_objects s1
cross join sys.all_objects s2
go
alter table dbo.Numbers
drop column junk
go
select dateadd(day, number, '1/1/1900')
from dbo.Numbers
That will create a "Numbers" table for you, with 10,000 rows in it.
I keep two versions: Numbers (0-10,000), and BigNumbers (0-100,000,000). Numbers is good for most uses, and BigNumbers handles the ones that Numbers won't. (A cross-join of Numbers to Numbers with Row_Number() would do the same thing, but less efficiently.)
The last select will give you every day from 1/1/1900 to 19 May 1927 (that's 10-thousand days). If you go up to 100-thousand days, it goes from 1/1/1900 to 16 Oct 2173. 1-million goes to 28 Nov 4637, which will certainly be enough for any normal use.
- 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 28, 2008 at 10:49 am
I find this interesting:
When I run this based on my date table:
SELECT d.DateValue AS DateofRange , Count(c.NacDate) AS SignedCount
FROM #DateList d LEFT JOIN Consultant c ON d.DateValue=c.NACDate
WHERE d.DateValue between @StartDate and @ENdDate
GROUP BY d.DateValue
ORDER BY d.DateValue
I get what I expect a count for every date in the table including 0 for days that do not have data but when I run this base on the same date table:
SELECT d.DateValue AS DateofRange , Count(c.DeactivationDate) AS '72hourResignationsCount'
FROM #DateList d LEFT JOIN Consultant c ON d.DateValue=c.DeactivationDate
WHERE d.DateValue between @StartDate and @ENdDate
AND c.StatusID = '72HOURRESIGNATION' AND c.Active = 0
GROUP BY d.DateValue
ORDER BY d.DateValue
I only get 2 records back with data and not the expected 15 record (given the date range). I need to get back 15 records including the 0 for days with no data.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 28, 2008 at 10:53 am
Having StatusID and Active in your Where clause is getting rid of any rows that would have 0 in them. You need to move those to the Join clause, out of the Where clause.
- 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 28, 2008 at 10:57 am
GSquared (4/28/2008)
Having StatusID and Active in your Where clause is getting rid of any rows that would have 0 in them. You need to move those to the Join clause, out of the Where clause.
or... allow for them to be null in your WHERE clause (it will result in the same query benig executed).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 28, 2008 at 10:59 am
Matt Miller (4/28/2008)
GSquared (4/28/2008)
Having StatusID and Active in your Where clause is getting rid of any rows that would have 0 in them. You need to move those to the Join clause, out of the Where clause.or... allow for them to be null in your WHERE clause (it will result in the same query benig executed).
6 of 1, half a dozen of the other. I don't like having Or statements in Joins/Where, and splitting them avoids that. Not sure it works any better, but it's my preference.
- 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 28, 2008 at 11:00 am
Cool that worked perfectly.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 28, 2008 at 11:43 am
GSquared (4/28/2008)
Matt Miller (4/28/2008)
GSquared (4/28/2008)
Having StatusID and Active in your Where clause is getting rid of any rows that would have 0 in them. You need to move those to the Join clause, out of the Where clause.or... allow for them to be null in your WHERE clause (it will result in the same query benig executed).
6 of 1, half a dozen of the other. I don't like having Or statements in Joins/Where, and splitting them avoids that. Not sure it works any better, but it's my preference.
I don't know why I didn't see this earlier - but keeping the results in the outer WHERE versus moving it into the JOIN clause (or putting it in the WHERE of a new derived table statement) are NOT equivalent statements. Meaning - you will get different rows, some things may not qualify, etc....
In other words - these 2 are the "same" query (return the same thing, etc...)
SELECT
d.DateValue AS DateofRange ,
Count(c.DeactivationDate) AS '72hourResignationsCount'
FROM #DateList d
LEFT JOIN Consultant c
ON d.DateValue=c.DeactivationDate
AND c.StatusID = '72HOURRESIGNATION'
AND c.Active = 0
WHERE d.DateValue between @StartDate and @ENdDate
GROUP BY d.DateValue
ORDER BY d.DateValue
and
SELECT
d.DateValue AS DateofRange ,
Count(c.DeactivationDate) AS '72hourResignationsCount'
FROM #DateList d
LEFT JOIN
(select *
from Consultant
WHERE c.StatusID = '72HOURRESIGNATION'
AND c.Active = 0) c
ON d.DateValue=c.DeactivationDate
WHERE d.DateValue between @StartDate and @ENdDate
GROUP BY d.DateValue
ORDER BY d.DateValue
but the outer where gives you something a little different:
SELECT
d.DateValue AS DateofRange ,
Count(c.DeactivationDate) AS '72hourResignationsCount'
FROM #DateList d
LEFT JOIN
Consultant c
ON d.DateValue=c.DeactivationDate
WHERE d.DateValue between @StartDate and @ENdDate
and (c.StatusID = '72HOURRESIGNATION' or c.statusID is null)
AND (c.Active = 0 or c.active is null)
GROUP BY d.DateValue
ORDER BY d.DateValue
The rowcount of the "left" side would be different....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 28, 2008 at 12:17 pm
You're right. With aggregates, it matters. Some other things, too.
I think for Art's query, the criteria in the Join statement will work best.
- 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 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply