CASE in SELECT - SQL Server Forums

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)

CASE in SELECT

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2005-04-06 : 05:03:08

Hi All,
I have the statements already to return the corresponding record or recordsets.

select t1.fileid as 'file ID',
t1.Descriptions,
t1.DDate as 'Done Date',
t2.ddate as 'Start Date',
Datediff(dd, t2.ddate, t1.ddate) as 'Actual Day(s)'

from #temp t1 join #temp t2 on (t1.fileid = t2.fileid)
where t2.Benchid = t1.triggeredby
order by t1.fileid, t1.stageID

But I discovered that in the <b>'Actual Days'</b> d date difference will not take weekend days into consideration.

Some one gave me this snippet, to remove weekend days

select case datename(dw, t2.ddate) = 'Saturday' then
dateadd(d, -1, t2.ddate)
case datename(dw, t2.ddate) = 'Sunday' then
dateadd(d, -2, t2.ddate)
else t2.ddate
Datediff(dd, t2.ddate, t1.ddate) as 'Actual Day(s)'

end

I have the problem of injecting this block into the main select statement.

My main problem is i want the datediff in the select statement to consider weekend days.

Please Help.
Thanks

I sign for fame not for shame but all the same, I sign my name.

satishdg
Starting Member

10 Posts

Posted - 2005-04-06 : 07:38:21

Hi,

use
convert(t2.dddate,6)-convert(t1.dddate,6) as Actual Days

There is convert format which will return only DD.Use that format.So that you will get Actual diffrence of days.This is for SQL SERVER.I am not sure abt last parameter 6.The values are between 1 to 14.Check it.

Satish

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2005-04-06 : 10:22:00

Hi,
I thing you dont quite understand me. I want to remove saturday and sunday out of the days calculation bcos they are not working days. My result will be based on working days, and that will be from monday to friday.So that if t1.ddate is friday and t2.ddate is the next monday, the acyual days will be 2.
This is my plight.
Thanks

I sign for fame not for shame but all the same, I sign my name.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-06 : 10:54:59

if the starting date and the ending date are both always weekdays, and you don't need to take into account holidays, then you can take total # of days difference, and subtract total weeks difference times 2.

use this to work with and test:

declare @StartDate datetime;
declare @EndDate datetime;

set @StartDate = '4/4/2005'
set @EndDate = '4/18/2005'

select datediff(dd,@StartDate, @EndDate) - datediff(ww,@StartDate, @EndDate) * 2

- Jeff

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2005-04-06 : 12:09:47

Hi All,
Thanks it works like a wizard wand. You made my day, I tried to cheat by cross posting, and i dont like to be seen as a cheat. Am sorry.

Jeff, you and spirit are my heroes.

:)

Thanks once again.

I sign for fame not for shame but all the same, I sign my name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources