April 21, 2009 at 6:40 am
If the compdte (completed date) = '1900-01-01 00:00:00.000'
then count the days from the entered date (entdte) till now (getdate()
else
count the days from the entered date till the completed date (compdte)
This is what I have, but it's not working:
Select callnbr,entdte,compdte,
Case workdays
when compdte <> '1900-01-01 00:00:00.000'
then
CASE WHEN DATEDIFF(day, entdte, compdte) - (2 * DATEDIFF(week, entdte, compdte)) -
CASE WHEN DATEPART(weekday,
entdte + 1) = 1 THEN 1 ELSE 0 END - CASE WHEN DATEPART(weekday, compdte + 1) = 1
THEN 1 ELSE 0 END < 0 THEN 0
ELSE DATEDIFF(day, entdte, compdte)
- (2 * DATEDIFF(week, entdte, compdte)) - CASE WHEN DATEPART(weekday, entdte + 1) = 1
THEN 1 ELSE 0 END - CASE WHEN DATEPART(weekday, compdte + 1)
= 1 THEN 1
ELSE
Case when compdte = '1900-01-01 00:00:00.000'
CASE WHEN DATEDIFF(day, entdte, getdate()) - (2 * DATEDIFF(week, entdte, getdate())) -
CASE WHEN DATEPART(weekday,
entdte + 1) = 1 THEN 1 ELSE 0 END - CASE WHEN DATEPART(weekday, getdate() + 1) = 1
THEN 1 ELSE 0 END < 0 THEN 0
ELSE DATEDIFF(day, entdte, getdate())
- (2 * DATEDIFF(week, entdte, getdate())) - CASE WHEN DATEPART(weekday, entdte + 1) = 1
THEN 1 ELSE 0 END - CASE WHEN DATEPART(weekday, getdate() + 1)
= 1 THEN 1 ELSE 0 END
End
END
AS Workdays
from svc00200
[/code]
Thanks in advance
April 21, 2009 at 7:26 am
Is it not simple as below. Please state your requirement otherwise
SELECT callnbr,entdte,compdte,
CASE compdte
WHEN '1900-01-01 00:00:00.000' THEN DATEDIFF(day, entdte, getdate())
ELSE DATEDIFF(day, entdte, compdte)
End AS Workdays
FROM svc00200
April 21, 2009 at 7:30 am
Or you can try something like this:
SELECT datediff(dd
,entdte
,CASE compdte
WHEN '1900-01-01 00:00:00.000'
THEN getdate()
ELSE compdte
END
)AS WORKDAYS
FROM svc00200
-Vikas Bindra
April 21, 2009 at 9:25 am
Arun Sathianathan (4/21/2009)
Is it not simple as below. Please state your requirement otherwise
It has to factor in weekends...
April 21, 2009 at 9:42 am
Check the below article that answers your requirement
http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/
April 21, 2009 at 10:48 am
Arun Sathianathan (4/21/2009)
Check the below article that answers your requirement
No, it doesn't.
I have code that calculates workdays already:
Select callnbr,entdte,compdte,
CASE WHEN DATEDIFF(day, entdte, compdte) - (2 * DATEDIFF(week, entdte, compdte)) -
CASE WHEN DATEPART(weekday,
entdte + 1) = 1 THEN 1 ELSE 0 END - CASE WHEN DATEPART(weekday, compdte + 1) = 1
THEN 1 ELSE 0 END < 0 THEN 0
ELSE DATEDIFF(day, entdte, compdte)
- (2 * DATEDIFF(week, entdte, compdte)) - CASE WHEN DATEPART(weekday, entdte + 1) = 1
THEN 1 ELSE 0 END - CASE WHEN DATEPART(weekday, compdte + 1)
= 1 THEN 1 ELSE 0 END
END
AS Workdays from svc00200
and gives me the correct results
CallNbr EntDte CompDte Workdays
0000106301 2009-01-02 00:00:00.0002009-01-07 00:00:00.0003
0000106737 2009-01-08 00:00:00.0002009-01-16 00:00:00.0006
0000106995 2009-01-12 00:00:00.0001900-01-01 00:00:00.0000
The problem I'm trying to solve lies in row 3....
If CompDte=1900-01-01 00:00:00.000, then count the workdays from the entdte until now (getdate())
This statement works:
Select callnbr,entdte,compdte,Case When compdte='1900-01-01 00:00:00.000' then getdate() Else compdte End as Dta1 from svc00200
Trying to put it in the above statement:
Select callnbr,entdte,compdte,
CASE WHEN DATEDIFF(day, entdte,
Case When compdte='1900-01-01 00:00:00.000' then getdate() else compdte end )
- (2 * DATEDIFF(week, entdte, compdte)) -
CASE WHEN DATEPART(weekday,
entdte + 1) = 1 THEN 1 ELSE 0 END - CASE WHEN DATEPART(weekday,
(Case When compdte='1900-01-01 00:00:00.000' then getdate() else compdte end ) + 1) = 1
THEN 1 ELSE 0 END < 0 THEN 0
ELSE DATEDIFF(day, entdte, compdte)
- (2 * DATEDIFF(week, entdte,
(Case When compdte='1900-01-01 00:00:00.000' then getdate() else compdte end ))) - CASE WHEN DATEPART(weekday, entdte + 1) = 1
THEN 1 ELSE 0 END - CASE WHEN DATEPART(weekday,
(Case When compdte='1900-01-01 00:00:00.000' then getdate() else compdte end ) + 1)
= 1 THEN 1 ELSE 0 END
END
AS Workdays from svc00200
results in any call with a compdte of '1900-01-01 00:00:00.000' having a workdays as a negative number:
0000103831 2008-11-20 00:00:00.0002009-01-12 00:00:00.00037
0000103914 2008-11-21 00:00:00.0002008-11-22 00:00:00.0000
0000105320 2008-12-12 00:00:00.0002008-12-16 00:00:00.0002
0000105982 2008-12-26 00:00:00.0002008-12-27 00:00:00.0000
0000105996 2008-12-26 00:00:00.0002008-12-26 00:00:00.0000
0000106301 2009-01-02 00:00:00.0002009-01-07 00:00:00.0003
0000106737 2009-01-08 00:00:00.0002009-01-16 00:00:00.0006
0000106995 2009-01-12 00:00:00.0001900-01-01 00:00:00.000-39851
April 21, 2009 at 11:13 am
You could just replace every occurrence of compdte in your query with
ISNULL(NULLIF(compdte, 0), GETDATE())
A datetime value of '1900-01-01 00:00:00.000' is 0 (zero) when the datetime is converted to an integer.
April 21, 2009 at 12:23 pm
andrewd.smith (4/21/2009)
You could just replace every occurrence of compdte in your query with
ISNULL(NULLIF(compdte, 0), GETDATE())A datetime value of '1900-01-01 00:00:00.000' is 0 (zero) when the datetime is converted to an integer.
Most awesome...I think that's got it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply