May 29, 2013 at 5:50 am
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
May 29, 2013 at 6:11 am
Try this...
SELECT CASE WHEN StartDate IS NULL THEN 'N/A' ELSE CAST(DATEDIFF(dd,StartDate,EndDate) AS varchar) END Days
May 29, 2013 at 8:24 am
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
May 29, 2013 at 8:58 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 29, 2013 at 9:15 am
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.
May 29, 2013 at 9:23 am
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
May 29, 2013 at 9:37 am
May I suggest reading this article: http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/
May 29, 2013 at 9:50 am
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.
May 29, 2013 at 10:44 am
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...
May 29, 2013 at 3:04 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply