Calendar datediff of workdays missing last piece!

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)

Calendar datediff of workdays missing last piece!

Author

Topic

SLM09
Starting Member

31 Posts

Posted - 2010-06-10 : 20:42:31

Hello,

I am trying to create a quick table that will give the last 35 days as well as their age from today. Trick being, they will ignore weekends and round sat/sun into mondays age (ie, if monday was 3 days ago, sat/sun would have been 3 "workdays" ago as well).

Anyway, wrote this on the fly. I'm sure there are a dozen ways to tackle this, but the headache im hitting is how to union the results. Gets the ages fine, but can't find where to put the union in for it to display nicely.

Thanks in advance

declare @date as datetime
declare @offset as int
set @date = dateadd(dd,datediff(dd,0,getdate()),0) - 1
set @offset = 0

while datediff(dd, @date, getdate()) < 35
begin
select
@date as date
,case
when datepart(dw, @date) in (1,7) then @offset
else @offset + 1
end as age

if datepart(dw, @date) not in (1, 7)
begin
set @offset = @offset + 1
end

set @date = @date - 1
end

SLM09
Starting Member

31 Posts

Posted - 2010-06-10 : 21:01:00

Figured something out... again, I know it's not the prettiest code... but maybe it will serve as a quick fix for someone else out there. :)

declare @date as datetime
declare @offset as int
set @date = dateadd(dd,datediff(dd,0,getdate()),0) - 1
set @offset = 0

select dateadd(dd,datediff(dd,0,getdate()),0) as date, 0 as age
into #t1

while datediff(dd, @date, getdate()) < 35
begin

insert into #t1
select
@date as date
,case
when datepart(dw, @date) in (1,7) then @offset
else @offset + 1
end as age

if datepart(dw, @date) not in (1, 7)
begin
set @offset = @offset + 1
end

set @date = @date - 1
end

select * from #t1 order by age asc

drop table #t1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-11 : 01:59:32

Here is a recursive CTE approach

;WITH cteYak([date], age, [index])
AS (
	SELECT	DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS [date],
		0 AS age,
		1 AS [index]

	UNION ALL

	SELECT	DATEADD(DAY, -1, [date]) AS [date],
		CASE DATEDIFF(DAY, 1, [date]) % 7
			WHEN 5 THEN age
			WHEN 6 THEN age
			ELSE age + 1
		END AS age,
		[index] + 1
	FROM	cteYak
	WHERE	[index] < 35
)
SELECT	[date],
	age
FROM	cteYak

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources