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

Transact-SQL (2000)

SELECT to get the last 5 business days- tricky

Author

Topic

adiel
Starting Member

16 Posts

Posted - 2008-04-07 : 15:35:29

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?

Environment: Sql Server 2000

Thanks Before Hand,
Adiel

bbasir
Yak Posting Veteran

76 Posts

Posted - 2008-04-07 : 17:17:44

you can avoid using an additional table by just using a cursor....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 00:50:31

SELECT TOP 5 d.theDay
FROM (
SELECT GETDATE() AS theDay UNION ALL
SELECT DATEADD(DAY, -1, GETDATE()) UNION ALL
SELECT DATEADD(DAY, -2, GETDATE()) UNION ALL
SELECT DATEADD(DAY, -3, GETDATE()) UNION ALL
SELECT DATEADD(DAY, -4, GETDATE()) UNION ALL
SELECT DATEADD(DAY, -5, GETDATE()) UNION ALL
SELECT DATEADD(DAY, -6, GETDATE()) UNION ALL
SELECT DATEADD(DAY, -7, GETDATE()) UNION ALL
SELECT DATEADD(DAY, -8, GETDATE()) UNION ALL
SELECT DATEADD(DAY, -9, GETDATE()) UNION ALL
SELECT DATEADD(DAY, -10, GETDATE()) UNION ALL
SELECT DATEADD(DAY, -11, GETDATE()) UNION ALL
SELECT DATEADD(DAY, -12, GETDATE()) UNION ALL
SELECT DATEADD(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

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

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 04:58:49

That would include the time in the date and so the join won't work.

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 : 05:07:10

You are correct.

SELECT TOP 5	d.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

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

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 05:48:38

Unfortunately that will return the number of days from 19000101 rather than the date.
Have a look at the way I did it
You can either
SELECT TOP 5 theDay = convert(datetime,d.theDay)

or convert to datetime in the query. That's why I separated the date from the number of days to make it simpler.
I missed out the subtract though which I've now added.

==========================================
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 : 05:52:50

Here I do not agree with you (due to implicit conversion).
See this first part

SELECT TOP 5	d.theDay
FROM		(
			SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) 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

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

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 05:56:17

If you add the dateadd it will work but you didn't have it in the previous query.
The dateadd does the same as a convert datetime. I would say it's less readable but that's just me.

==========================================
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 : 06:00:21

Can also be demonstrated by this

DECLARE	@TableA TABLE (i INT)

INSERT	@TableA
SELECT	39544 UNION ALL
SELECT	39543 UNION ALL
SELECT	39542

DECLARE	@TableB TABLE (dt DATETIME)

INSERT	@TableB
SELECT	'20080408' UNION ALL
SELECT	'20080407' UNION ALL
SELECT	'20080406'

SELECT		*
FROM		@TableA AS a
INNER JOIN	@TableB AS b ON b.dt = a.i

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 06:04:05

Ah! Now I see your point.
You are referring to the final resultset and it's presentation. There you are correct.

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

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 06:08:41

Don't think you understand - oops - yes you do now.

your query was
SELECT TOP 5 d.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

SELECT DATEDIFF(DAY, 0, GETDATE())
Will give an integer. There is nothing in this query to convert it to datetime.
The join will work as it will implicitly convert (assuming the value in tblNoWorkDay is a datetime but the value returned in the select will be an integer.
Your second query will return a datetime because the dateadd returns a datetime and all the other values in the union all will take the datatype from the first entry.

If the output is used somewhere that converts to s datetime in sql server then it won't make a difference but if it is returned to a client or is not converted then it would be incorrect.

==========================================
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-08 : 09:20:02

Thanks nr. Thanks Peso.

I ended up using nr's solution. Peso also had another possible solution. You guys made something complex into a simple solution. :)

Thanks Again,
Adiel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources