calcualte Days between 2 dates but exclude weekend - Code not working need help please urgent

Question

Post reply

calcualte Days between 2 dates but exclude weekend - Code not working need help please urgent

dhananjay.nagarkar

Default port

Points: 1496
More actions
May 29, 2013 at 5:50 am

#278250

Dear friends,
I have requirement to Calculate Days between 2 Dates but the code is not working-
My current code is-
--
CAST ((datediff(dd,convert(int,Job Posting Create Date_JP
]),convert(int,Report Run Date_BYR
)+1))
- (datediff(WK,convert(int,Job Posting Create Date_JP
convert(int,Report Run Date_BYR))*2)
- (CASE when DATENAME(DW,convert(int,Report Run Date_BYR
)) = 'Sunday' then 1 else 0 END)
- (CASE when DATENAME(DW,convert(int,Job Posting Create Date_JP
)) = 'Saturday' then 1 else 0 END) AS VARCHAR(255))-- AS Age (Working Days)
---
Note- I have to do varachar since where the avlue comes blank I have to display as 'N/A'
the date formats values we have are -
,[WorkForce_JobPosting].[Job Posting Create Date_JP]) =
WorkForce_JobPosting].[Report Run Date_BYR]) =
Report Run Date_BYR
2013-05-28 09:04:51.227
2013-05-28 09:04:51.227
2013-05-28 09:04:51.227
2013-05-28 09:04:51.227
Job Posting Create Date_JP
2013-05-17 14:49:25.000
Job Posting Create Date_JP
2013-05-19 22:38:49.000
Job Posting Create Date_JP
2013-05-20 02:44:49.000
Job Posting Create Date_JP
2013-05-21 03:28:28.000
Expected values are-
I'm getting output as Age (Working Days) -
6,7,7,6
Where as the User says it should be-
7,6,6,5
Would be much obliged if any help to fix this ASAP critical issue.
thanks
Dhnanajay

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Bhaskar.Shetty Hall of Fame Points: 3054 More actions · Answer 1

Try this...

SELECT CASE WHEN StartDate IS NULL THEN 'N/A' ELSE CAST(DATEDIFF(dd,StartDate,EndDate) AS varchar) END Days

geoff5 Hall of Fame Points: 3560 More actions · Answer 2

The solution I propose is based on Jeff Moden's "tally table" concept[/url].

This may not be the most efficient way to use the tally table, but it does calculate the number of weekdays between two dates. Hopefully it will run efficiently enough to be adequate for your reporting needs. On the up side, you should be able to lift the calculated column formula directly from this example and put it into your query context with only the minor change of the column name for the posting create date in two places.

The solution uses a tally table subquery to create the set of dates between the posting_create_date and the current date. That query is itself wrapped in a subquery that pulls the dates and uses a SUM function and a CASE function to count only the dates that are not Sunday or Saturday (assuming the US standard day-of-week value of 1 for Sunday and 7 for Saturday).

WITH

raw_data AS

(SELECT posting_create_date = '5/17/2013' UNION ALL

SELECT '5/19/2013' UNION ALL

SELECT '5/20/2013'UNION ALL

SELECT '5/21/2013')

SELECT

posting_create_date,

number_of_weekdays =

(SELECT SUM(CASE WHEN DATEPART(dw, elapsed_dates.elapsed_date) BETWEEN 2 AND 6 THEN 1 ELSE 0 END)

FROM

(SELECT TOP (DATEDIFF(DAY, posting_create_date, GETDATE()))

elapsed_date = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, posting_create_date)

FROM master.sys.all_columns)

elapsed_dates)

FROM

raw_data

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Bhaskar.Shetty (5/29/2013)
Try this...
SELECT CASE WHEN StartDate IS NULL THEN 'N/A' ELSE CAST(DATEDIFF(dd,StartDate,EndDate) AS varchar) END Days

Using your code, adapted slightly:

declare @StartDate date = '20130501'

--Wednesday

declare @EndDate date = '20130507'

--Tuesday

select case when @StartDate is null then 'N/A'

else cast(datediff(dd, @StartDate, @EndDate) as varchar)

end days

This returns 6, which clearly does not exclude weekends.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

First, let's create some sample data from the mess that you posted: -

IF object_id('tempdb..#test') IS NOT NULL

BEGIN;

DROP TABLE #test;

END;

SELECT [Job Posting Create Date_JP], [Report Run Date_BYR]

INTO #test

FROM (SELECT '2013-05-17 14:49:25.000' AS [Job Posting Create Date_JP], '2013-05-28 09:04:51.227' AS [Report Run Date_BYR] UNION ALL

SELECT '2013-05-19 22:38:49.000', '2013-05-28 09:04:51.227' UNION ALL

SELECT '2013-05-20 02:44:49.000', '2013-05-28 09:04:51.227' UNION ALL

SELECT '2013-05-21 03:28:28.000', '2013-05-28 09:04:51.227'

) A;

Now, anyone that wants to help you with your "urgent" task can easily have a copy of your sample data and therefore will be able to quickly test any solution that they suggest.

Secondly, let's solve your issue.

SELECT (DATEDIFF(dd, [Job Posting Create Date_JP], [Report Run Date_BYR]))-

(DATEDIFF(wk, [Job Posting Create Date_JP], [Report Run Date_BYR]) * 2)-

(CASE WHEN DATENAME(dw, [Job Posting Create Date_JP]) = 'Sunday' THEN 1 ELSE 0 END)-

(CASE WHEN DATENAME(dw, [Report Run Date_BYR]) = 'Saturday' THEN 1 ELSE 0 END) AS weekDays,

[Job Posting Create Date_JP], [Report Run Date_BYR]

FROM #test;

The above results in: -

weekDays Job Posting Create Date_JP Report Run Date_BYR

----------- -------------------------- -----------------------

7 2013-05-17 14:49:25.000 2013-05-28 09:04:51.227

6 2013-05-19 22:38:49.000 2013-05-28 09:04:51.227

6 2013-05-20 02:44:49.000 2013-05-28 09:04:51.227

5 2013-05-21 03:28:28.000 2013-05-28 09:04:51.227

Next time, if you have a task that you consider to be urgent then post readily consumable sample data and you'll get much faster and more accurate responses.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

geoff5 Hall of Fame Points: 3560 More actions · Answer 5

I am scratching my head at the two proposed solutions that suggest a basic DATEDIFF() calculation. A basic DATEDIFF() solution will not yield the desired answer. "Day," "D" and "DD" are all synonymous terms for the time interval, and they all calculate the number of days that have elapsed between the first and second date parameters. There is no time interval value for DATEDIFF() that only counts weekdays; it always includes weekends.

Having said that, in my own earlier solution I overlooked the requirement to accept NULL values and display "N/A." Not only that, a NULL value for the posting_create_date would break the query because of the way I am calculating the TOP parameter in the tally table subquery. So here is a new version that takes NULL values into account:

WITH

raw_data AS

(SELECT posting_create_date = '5/17/2013' UNION ALL

SELECT '5/19/2013' UNION ALL

SELECT '5/20/2013'UNION ALL

SELECT '5/21/2013' UNION ALL

SELECT null)

SELECT

posting_create_date,

number_of_weekdays = CASE WHEN posting_create_date IS NULL THEN 'N/A' ELSE

CAST((SELECT SUM(CASE WHEN DATEPART(dw, elapsed_dates.elapsed_date) BETWEEN 2 AND 6 THEN 1 ELSE 0 END)

FROM

(SELECT TOP (DATEDIFF(DAY, ISNULL(posting_create_date, GETDATE()), GETDATE()))

elapsed_date = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, posting_create_date)

FROM master.sys.all_columns)

elapsed_dates) AS VARCHAR(4)) END

FROM

raw_data

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

May I suggest reading this article: http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/

geoff5 Hall of Fame Points: 3560 More actions · Answer 7

Thanks for the link, Lynn; that does explain it well, and I can see that it is the same solution recommended by cadavre. It is definitely better than my more complex solution; although, ironically, my own solution was also inspired by Jeff Moden.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 8

I prefer to use calendar tables for these sort of thing (as it may be required to count in bank holidays and other off-days):

Example of small dedicated calendar table dedicated to the required task (real one would have proper indexes):

SELECT dt AS cal_Day

,CASE WHEN DATENAME(weekday, dt) IN ('Saturday','Sunday') THEN 0 ELSE 1 END AS cal_WesternWorkDay

INTO #calendar

FROM (

SELECT TOP 40000 DATEADD(d, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1,CAST('1 Jan 2001' AS DATE))AS dt

FROM sys.columns s1 cross join sys.columns s2

) q

Now you can count whatever you like without thinking about calculating out working days:

;WITH raw_data AS

(SELECT posting_create_date = '17 May 2013' UNION ALL

SELECT '19 May 2013' UNION ALL

SELECT '20 May 2013'UNION ALL

SELECT '21 May 2013')

SELECT rd.*, cc.no_of_workdays

FROM raw_data rd

CROSS APPLY (SELECT COUNT(*) AS no_of_workdays

FROM #calendar c

WHERE c.cal_Day BETWEEN rd.posting_create_date AND GETDATE() AND cal_WesternWorkDay = 1) cc

Let say you want remove bank holidays out of it, if you have a table of bank holidays (or a flag in the calendar table itself), the above query would need just a small enhancement to filter out them...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

dhananjay.nagarkar Default port Points: 1496 More actions · Answer 9

Thanks to all of you here for helping me, gave me some idea ... and I rewrote the code and now seems Im seeing the results the user expects-

CAST ((datediff(dd,[WorkForce_JobPosting].[Job Posting Create Date_JP], (dateadd(day,-1,[WorkForce_JobPosting].[Report Run Date_BYR])))+1)

- (datediff(WK,[WorkForce_JobPosting].[Job Posting Create Date_JP],(dateadd(day,-1,[WorkForce_JobPosting].[Report Run Date_BYR])))*2)

- (CASE when DATENAME(DW,[WorkForce_JobPosting].[Job Posting Create Date_JP]) = 'Sunday' then 1 else 0 END)

- (CASE when DATENAME(DW,(dateadd(day,-1,[WorkForce_JobPosting].[Report Run Date_BYR]))) = 'Saturday' then 1 else 0 END) AS VARCHAR(255))-- AS Age (Working Days)

Kind Regards

Dhananjay