Determining days within a daterange

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

Determining days within a daterange

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-12-22 : 22:33:52

Folks, I have the following query that works fine if I just want to know how many days someone
is going to be absent. In this query, the Absence table has a fromdate and a todate. However,
It does NOT show each day in between.

Select Employee,

DATEDIFF(DD,
Cast(
substring(convert(varchar(10),A.Fromdate),5,2)+'-'+
substring(convert(varchar(10),A.Fromdate),7,2)+'-'+
substring(convert(varchar(10),A.Fromdate),1,4)
as Datetime),

Cast(
substring(convert(varchar(10),A.Todate),5,2)+'-'+
substring(convert(varchar(10),A.Todate),7,2)+'-'+
substring(convert(varchar(10),A.Todate),1,4)
as Datetime) )

FROM Absences A
WHERE A.FromDate>20081201
AND (A.FromDate>0 OR A.TODate>0)

If I have the following dataset:

Employee________FromDate________ToDate
John D._________1-1-08__________1-1-08
Jane C._________1-3-08__________1-4-08
Karl G._________1-2-08__________1-6-08

My desired output would look like this:

Employee_Mon________Tue____Wed____Thu____Fri____Sat____Sun
John D.__1-1-08_________________________________________
Jane C.____________________1-3-08_1-4-08________________
Karl G._____________1-2-08_1-3-08_1-4-08_1-5-08_1-6-08

I have another query that I wrote that has fixed dates (no date ranges)
and used a case expression for each day of the week, but I'm not sure
where to begin when you have to compare dates in order to group each
date that falls between the 2 dates to show on a specific day.

Anyone have any ideas?

Thanks!
Craig

(EDIT: By the way, I had a similar question a few weeks ago; still stumped with this. Wasn't able to make it work because I can not insert data into the database; only Select queries are permitted. Also, the data to be compared will change on a regular basis, so I need to be able to evaluate new dates all the time)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-22 : 22:51:07

[code]SELECT *
FROM
(
SELECT t.Employee AS Employee_mon,
DATEADD(dd,v.number,t.FromDate) AS Date,
LEFT(DATENAME(dw,DATEADD(dd,v.number,t.FromDate)),3) AS DayVal
FROM YourTable t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(dd,v.number,t.FromDate)<=t.ToDate
)t
PIVOT (MAX(Date) FOR DayVal IN ([Mon],[Tue],[Wed],[Thu],[Fri]))p
[/code]

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-12-22 : 23:40:05

Hi Vis,

THanks for the reply...we're running SQL Server 2005, but I don't think the query writer is updated to be able to accept Pivot. It comes up with the following error:

SQLExecDIrect returned SQL_ERROR (FromHSTMT), SQLState=37000, Native Error=325, SQL_Error=0
Message=Microsoft[ODBC SQL Server Driver][SQL Server] Incorrect Syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

Is there a different way to do this than Pivot?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-23 : 00:38:53

Why can't you change your compatibility level to 90 like the error suggested? Do you require 80?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-25 : 01:58:41

just change compatibility level to 90 using below statement