SELECT to get the last 5 business days- tricky

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 2005 Forums

Transact-SQL (2005)

SELECT to get the last 5 business days- tricky

Author

Topic

adiel
Starting Member

16 Posts

Posted - 2008-04-07 : 13:04:51

I have stumbled upon a sql question I cannot answer. I am looking for the following SELECT sql statement:

Basically I need a way to get "5 days ago from today". BUT, the trick is that there is a table called tblnoworkday with contains weekends and holidays and those dates cannot count. So basically what I am really trying to get is "5 Business days ago from today".

So it would basically do this for an query input date of '4/9/08'. If the table is called TblNoWorkday and contains the following records:

...
2008-04-06 00:00:00.000
2008-04-05 00:00:00.000
...

This is the last 5 business days then: (not in the table)

4/9/08
4/8/08
4/7/08
****weekend****
4/4/08
4/3/08

The query should return just 4/3/08. The problem is 4/3/08 doesn't exist in the database since the database only contains the no work days.

Any ideas on how to do this in a simple query without having to create another table with the valid working dates?

Thanks Before Hand,
Adiel

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-07 : 13:15:52

select top 5 d = dateadd(dd,i.i*-1,dte.d)
from (select i = 0 union all select 1 union select 2 ... union select 10) i
cross join (select d = dateadd(dd,0,getdate())) dte
left join tblnoworkday n
on dateadd(dd,i.i*-1,dte.d) = n.dte
where n.dte is null
order by dte.d desc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

adiel
Starting Member

16 Posts

Posted - 2008-04-07 : 13:45:04

Thanks! I ran into two issues with your query. I changed it as follows:

select top 5 d = dateadd(dd,i.i*-1,dte.d)
from (select i = 0 union all select 1 union select 2 union select 3 union select 4 union select 5
union select 6 union select 7 union select 8 union select 9 union select 10) i
cross join (select d = dateadd(dd,0,getdate())) dte
left join tblnoworkday n
on dateadd(dd,i.i*-1,dte.d) = n.noworkday
where n.noworkday is null
order by dte.d desc

1. It still returns all dates for some reason. Here is the output. (I changed the column name to match my column name. The column noworkday is datetime)

2008-04-07 13:40:50.023
2008-04-06 13:40:50.023
2008-04-05 13:40:50.023
2008-04-04 13:40:50.023
2008-04-03 13:40:50.023

2. How can it be modified to just return the 5th date. (It would be 4/3/08 if you see my example above.)

Thanks Again,
Adiel

bfoster
Starting Member

30 Posts

Posted - 2008-04-07 : 14:21:31

Here's a slightly different solution using a common table expression. You may be able to apply the top(1) idea that I used below to the other query if you want to stick with the other query.

WITH dte AS
(
SELECT CAST(CONVERT(varchar, getdate(), 101) AS DATETIME) AS d
UNION ALL
SELECT DATEADD(day, -1, d )
FROM dte
WHERE d >= DATEADD(day, -15, getdate())
)
SELECT top(1) a.d
FROM
(
SELECT top(5) dte.d
FROM dte
left join tblnoworkday n on dte.d = n.noworkday
where n.noworkday is null
order by dte.d desc
) a
order by a.d asc;

adiel
Starting Member

16 Posts

Posted - 2008-04-07 : 14:30:40

Thanks bfoster, I received the following error when I ran your code:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '('.

I am testing the code on sql server 2000. Thanks Again,
Adiel

adiel
Starting Member

16 Posts

Posted - 2008-04-07 : 14:59:13

It looks like you are expecting an object called dte that does not exist:

Invalid object name 'dte'.

bfoster
Starting Member

30 Posts

Posted - 2008-04-07 : 15:27:44

Common table expression are new to SQL Server 2005 so you won't be able to use that syntax in SQL Server 2000. That's the problem there.

In that case, use the original suggestion.

select top(1) d
from
(
[first suggestion given]
) a
order by d asc

My only other thought is you may need to look into the time part of the dates that you are trying to compare and make sure they are all 00:00:00.000 if that's what is in your tblnoworkday.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-07 : 16:49:32

Sorry - got the date bit wrong

select top 1 *
from
(
select top 5 d = dateadd(dd,i.i*-1,dte.d)
from (select i = 0 union all select 1 union select 2 union select 3 union select 4 union select 5
union select 6 union select 7 union select 8 union select 9 union select 10) i
cross join (select d = convert(datetime,datediff(dd,0,getdate()))) dte
left join tblnoworkday n
on dateadd(dd,i.i*-1,dte.d) = n.noworkday
where n.noworkday is null
order by dte.d desc
) a
order by d

might be better as

select top 1 *
from
(
select top 5 d = dte.d
from
(
select d = dte.d - i
from (select i = 0 union all select 1 union select 2 union select 3 union select 4 union select 5
union select 6 union select 7 union select 8 union select 9 union select 10) i
cross join (select d = convert(datetime,datediff(dd,0,getdate()))) dte
) dte
left join tblnoworkday n
on dte.d = n.noworkday
where n.noworkday is null
order by dte.d desc
) a
order by d

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 00:54:53

Cross post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100464

E 12°55'05.25"
N 56°04'39.16"

adiel
Starting Member

16 Posts

Posted - 2008-04-08 : 08:59:08

Thanks Everyone!! Peso, it was not a cross-post, it was a move-post. I posted in 2005 but it was supposed to be sql 2000.

>> am testing the code on sql server 2000. Thanks Again,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 09:02:05

[code]SELECT TOP 5 DATEADD(DAY, d.theDay, 0) AS theDay
FROM (
SELECT DATEDIFF(DAY, 0, GETDATE()) AS theDay UNION ALL
SELECT DATEDIFF(DAY, 1, GETDATE()) UNION ALL
SELECT DATEDIFF(DAY, 2, GETDATE()) UNION ALL
SELECT DATEDIFF(DAY, 3, GETDATE()) UNION ALL
SELECT DATEDIFF(DAY, 4, GETDATE()) UNION ALL
SELECT DATEDIFF(DAY, 5, GETDATE()) UNION ALL
SELECT DATEDIFF(DAY, 6, GETDATE()) UNION ALL
SELECT DATEDIFF(DAY, 7, GETDATE()) UNION ALL
SELECT DATEDIFF(DAY, 8, GETDATE()) UNION ALL
SELECT DATEDIFF(DAY, 9, GETDATE()) UNION ALL
SELECT DATEDIFF(DAY, 10, GETDATE()) UNION ALL
SELECT DATEDIFF(DAY, 11, GETDATE()) UNION ALL
SELECT DATEDIFF(DAY, 12, GETDATE()) UNION ALL
SELECT DATEDIFF(DAY, 13, GETDATE())
) AS d
LEFT JOIN tblNoWorkDay AS x ON x.SomeDay = d.theDay
WHERE x.SomeDay IS NULL
ORDER BY d.theDay DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources