Returning non-existing dates in table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Returning non-existing dates in table

Author

Topic

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2003-03-26 : 16:54:22

I have a tabel like
stuff_id worked_date -- in mm/dd/yyyy format
-------- -----------
1 03/03/2003
1 03/04/2003
1 03/05/2003
2 03/03/2003
1 03/07/2003
2 03/05/2003
1 03/10/2003
2 03/10/2003
this table holds all stuff info about their worked dates. And this table holds about 150 stuff and over 2 years data.

What I want is returning non worked dates (not existing date) for each stuff. And another issue is weekends. weekends are off days in this company, so we must ignore weekends.

for example, for the table above, this query must return

1 03/06/2003
2 03/04/2003
2 03/06/2003

any ideas?

thanks in advance.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-26 : 17:08:11

First, you need a table of all dates covering all date ranges you wish to check for. generate this table however you like and save it as "Dates". If you need help making this table, let me know, but it must exist.

Next, you need a table of all "stuff_ids". i assume you have this table somewhere (primary key of stuff_id), let's call it Stuff.

Finally, I will assume you want a date range to check of @StartDate and @EndDate.

Then, your answer is:

SELECT Dates.Date, Stuff.StuffID
from
Dates
CROSS JOIN
Stuff
LEFT OUTER JOIN
YourTable
ON Dates.date = YourTable.Worked_Date and
Stuff.stuff_id = YourTable.Stuff_id
where
Dates.Date BETWEEN (@StartDate and @EndDate) AND
YourTable.Worked_Date is NULL

- Jeff

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2003-03-26 : 18:16:10

Thanks Jeff,

I have almost same idea as yours but looking for any alternative ideas.

What about temp "Dates" table. Is there any simple method for filling that table excluding weekends, lets say for one year?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-26 : 18:39:21

You have to loop through a bunch of days and insert. just exclude dates where datepart(dw) = a weekend. I think it's 1 or 7 for sunday and saturday.

i.e.,

declare @d datetime;
set @d = '1/1/2000' -- start date
while @d < '12/31/2020' -- end date
begin
if datepart(dw,@d) not in (1,7) insert into Dates (Date) values (@d)
set @d = @d + 1
end

... something like that. But having a table of all dates and doing the cross join is the only way I know of to do it ... it is the most logically sound, anyway. If you find another way, let me know.

Remember, you can't report off of data that doesn't exist ANYWHERE ... the "empty dates" must be stored somewhere in sql, or generated somehow.

- Jeff

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2003-03-27 : 16:35:42

Thanks alot..
I found another way that doesnot use dates table. but have some limitations. may be can be used in some cases. I am working on it, when finished, I ll write the code here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources