Convert date range into week days

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

General SQL Server Forums

New to SQL Server Programming

Convert date range into week days

Author

Topic

egghi
Starting Member

17 Posts

Posted - 2006-11-03 : 14:46:22

Hi,

I don't know if this can be done, but hopefully one of the experts in this forum cna help me out.

I have a TimeOff table which contains 5 fields: timeoffID, employeename, startdate, enddate, and timofftype. I need to create a report which shows how many people take sick day (one of the timeofftypes) on each week day (i.e. Monday, Tuesday, Wednesday, and etc.) The problem is that I don't know how to convert the date range (from startdate to enddate) into indvidual week day for each timeoff record.

For example, on the table,

TimeoffID employeename startdate enddate timeofftype
1 Andy 11/02/2006 11/03/2006 Sick Day
2 Bill 11/03/2006 11/03/2006 Sick Day

The report needs to show:

Monday Tuesday Wednesday Thursday Friday Saturday Sunday
0 0 0 1 2 0 0

Thank you in advance!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-03 : 15:36:15

Search for function F_TABLE_DATE made by Michael Valentine Jones here at SQLTeam.

Peter Larsson
Helsingborg, Sweden

egghi
Starting Member

17 Posts

Posted - 2006-11-03 : 17:12:28

Hi Peter,

Thank you for the advice. I checked Michael's script and I honestly have no idea how and where (the table is in SQL, and I was wondering if I can create a SQL view and bind the Access report to the view) to apply it to my question... Could you give me more tips?

Thank youuu!!!

egghi
Starting Member

17 Posts

Posted - 2006-11-03 : 17:26:06

Hi,

I came across the DATENAME() when researching on the internet. So, I created a query in SQL like this:

SELECT DATENAME(dw,StartDate),COUNT(DATENAME(dw,StartDate))
FROM TimeOff
GROUP BY DATENAME(dw,StartDate)

And it returns PART of the results that I wanted:

Thursday 1
Friday 1

The problem is that the stats does not include the weekdays after the time-off start date and until the time-off end date.

Any advice or suggestion will be greatly appreciated! Thank you!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-03 : 20:04:46

Function F_TABLE_DATE is a table valued function, so it can be used like a table in a query.

The query below shows how you can use it to do the crosstab you want.


declare @start_date	datetime
declare @end_date	datetime

set @start_date = '20061101'
set @end_date   = '20061130'

declare @t table (
TimeoffID	int		not null,
employeename	varchar(10)	not null,
startdate	datetime	not null,
enddate		datetime	not null,
timeofftype	varchar(10)	not null
)

insert into @t
select 1, 'Andy', '11/02/2006', '11/03/2006', 'Sick Day'  union all
select 2, 'Bill', '11/03/2006', '11/03/2006', 'Sick Day'

select
	[Monday]	=
		sum(case when b.DAY_OF_WEEK = 2 then 1 else 0 end) ,
	[Tuesday]	=
		sum(case when b.DAY_OF_WEEK = 3 then 1 else 0 end) ,
	[Wednesday]	=
		sum(case when b.DAY_OF_WEEK = 4 then 1 else 0 end) ,
	[Thursday]	=
		sum(case when b.DAY_OF_WEEK = 5 then 1 else 0 end) ,
	[Friday]	=
		sum(case when b.DAY_OF_WEEK = 6 then 1 else 0 end) ,
	[Saturday]	=
		sum(case when b.DAY_OF_WEEK = 7 then 1 else 0 end) ,
	[Sunday]	=
		sum(case when b.DAY_OF_WEEK = 1 then 1 else 0 end)
from
	@t a
	join
	F_TABLE_DATE(@start_date,@end_date ) b
	on b.DATE between a.startdate and a.enddate

Results:



(2 row(s) affected)

Monday      Tuesday     Wednesday   Thursday    Friday      Saturday    Sunday      
----------- ----------- ----------- ----------- ----------- ----------- ----------- 
0           0           0           1           2           0           0

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources