August 19, 2016 at 8:59 am
Hi there,
I have a table of calendar dates with a Y/N flag to denote whether or not the date is a working day.
I wish to add a new column with the following criteria:
If the WorkingDay = 'Y' then I want to see the same CALDATE
If the WorkingDay = 'N' then I want to see the next CALDATE where the WorkingDay = 'Y'
For example, in the below, for CALDATE 19/08/2016 my new column would be 19/08/2016, however, for CALDATE 14/08/2016 my new column would be 15/08/2016
Please help
CALDATE WORKINGDAY
19/08/2016 Y
18/08/2016 Y
17/08/2016 Y
16/08/2016 Y
15/08/2016 Y
14/08/2016 N
August 19, 2016 at 9:42 am
what version of MS SQL are you using?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 19, 2016 at 10:19 am
This should be fine with SQL Server 2005+
-- sample data
CREATE TABLE #calendarTable (calDate datetime NOT NULL, workingDay char(1) NOT NULL);
WITH base AS
(
SELECT TOP (366)
dt = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1, '20160101')
FROM sys.all_columns
)
INSERT #calendarTable
SELECT dt, CASE WHEN DATEPART(WEEKDAY,dt) IN (7,1) THEN 'N' ELSE 'Y' END
FROM base;
-- calculating the next working day
SELECT calDate,
workingDay,
nextWorkingDay
FROM #calendarTable ct
CROSS APPLY
(
SELECT TOP (1) calDate MIN(caldate)
FROM #calendarTable ct2
WHERE ct.calDate <= ct2.calDate AND ct2.workingDay = 'Y'
) NextWorkingDay(NextWorkingDay);
Edit: MIN(caldate) will be much, much faster than TOP (1)
-- Itzik Ben-Gan 2001
August 22, 2016 at 2:49 am
Great that worked thanks Alan
September 1, 2016 at 2:06 am
This looks useful but whrere you have this line
SELECT TOP (1) calDate MIN(caldate)
Is the strikethrough deliberate?
September 6, 2016 at 10:43 am
turlteman.mike (9/1/2016)
This looks useful but whrere you have this lineSELECT TOP (1) calDate MIN(caldate)
Is the strikethrough deliberate?
Sorry I almost missed this.
Yes, I was saying that using MIN seemed to perform better than the TOP statement.
-- Itzik Ben-Gan 2001
September 9, 2016 at 6:08 am
Hi Alan/Mike,
Following on from this, I'm looking to add a DATEDIFF calculation between today's date 'GETDATE()' and each individual date but only where the Working Day flag is set to 'Y'.
Any ideas how I script this ?
For example, using today's date of 09/09/2016 in the below scenario, the date differences should be:
Date Working Day DATEDIFF
09/09/2016 Y 0
08/09/2016 Y 1
07/09/2016 Y 2
06/09/2016 Y 3
05/09/2016 Y 4
04/09/2016 N 4
03/09/2016 N 4
Thanks
September 9, 2016 at 6:20 am
chris.evans 94907 (9/9/2016)
Hi Alan/Mike,Following on from this, I'm looking to add a DATEDIFF calculation between today's date 'GETDATE()' and each individual date but only where the Working Day flag is set to 'Y'.
Any ideas how I script this ?
For example, using today's date of 09/09/2016 in the below scenario, the date differences should be:
Date Working Day DATEDIFF
09/09/2016 Y 0
08/09/2016 Y 1
07/09/2016 Y 2
06/09/2016 Y 3
05/09/2016 Y 4
04/09/2016 N 4
03/09/2016 N 4
Thanks
It looks like you want the difference in months. You say you only want "Working Day flag is set to 'Y'" but have N in your expected results.
Will this get you moving in the right direction?
SELECT calDate, DATEDIFF(month, GETDATE(), calDate)
FROM #calendarTable
WHERE workingDay = 'Y'
ORDER BY calDate DESC;
September 9, 2016 at 7:01 am
Sorry it's the difference in days not months but only where the Working Day flag is set to 'Y'
September 9, 2016 at 8:52 am
Ed Wagner (9/9/2016)
chris.evans 94907 (9/9/2016)
Hi Alan/Mike,Following on from this, I'm looking to add a DATEDIFF calculation between today's date 'GETDATE()' and each individual date but only where the Working Day flag is set to 'Y'.
Any ideas how I script this ?
For example, using today's date of 09/09/2016 in the below scenario, the date differences should be:
Date Working Day DATEDIFF
09/09/2016 Y 0
08/09/2016 Y 1
07/09/2016 Y 2
06/09/2016 Y 3
05/09/2016 Y 4
04/09/2016 N 4
03/09/2016 N 4
Thanks
It looks like you want the difference in months. You say you only want "Working Day flag is set to 'Y'" but have N in your expected results.
Will this get you moving in the right direction?
SELECT calDate, DATEDIFF(month, GETDATE(), calDate)
FROM #calendarTable
WHERE workingDay = 'Y'
ORDER BY calDate DESC;
Haha, silly American! I think "06/09/2016 " is September 6th where the OP is from. 😛
I think they they're looking for something like:
SELECT calDate,
workingDay,
nextWorkingDay,
DtDIFF = RANK() OVER (ORDER BY NextWorkingDay DESC)-1
FROM #calendarTable ct
CROSS APPLY
(
SELECT MIN(caldate)
FROM #calendarTable ct2
WHERE ct.calDate <= ct2.calDate AND ct2.workingDay = 'Y'
) NextWorkingDay(NextWorkingDay)
WHERE caldate BETWEEN CAST(getdate()-6 AS date) AND CAST(getdate() AS date)
ORDER BY calDate DESC; -- not required, including for presentation
-- Itzik Ben-Gan 2001
September 9, 2016 at 9:28 am
When posting dates, it's best to use a location neutral formatting like YYYY-MM-DD. The dates posted can be interpreted as either DD/MM/YYYY or MM/DD/YYYY, which cause some confusion.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 9, 2016 at 10:54 am
Alan.B (9/9/2016)
Haha, silly American! I think "06/09/2016 " is September 6th where the OP is from. 😛
Touche, sir. I never thought to check the profile to see where the OP was from. :hehe:
Nice catch, Alan, thanks.
September 9, 2016 at 10:56 am
drew.allen (9/9/2016)
When posting dates, it's best to use a location neutral formatting like YYYY-MM-DD. The dates posted can be interpreted as either DD/MM/YYYY or MM/DD/YYYY, which cause some confusion.Drew
Agreed.
And I did just check the OP's profile and there is no location information. Still, I should have thought to ask.
September 9, 2016 at 2:14 pm
Ed Wagner (9/9/2016)
Alan.B (9/9/2016)
Haha, silly American! I think "06/09/2016 " is September 6th where the OP is from. 😛Touche, sir. I never thought to check the profile to see where the OP was from. :hehe:
Nice catch, Alan, thanks.
I got caught by this all the time. Another reason I like the ol YYYYMMDD format 🙂
-- Itzik Ben-Gan 2001
September 12, 2016 at 3:40 am
Hi Alan,
Thanks for the reply.
The script you provided returns the following results:
calDate workingDay nextWorkingDay DtDIFF
12/09/2016 Y 12/09/2016 0
11/09/2016 N 12/09/2016 0
10/09/2016 N 12/09/2016 0
09/09/2016 Y 09/09/2016 3
08/09/2016 Y 08/09/2016 4
07/09/2016 Y 07/09/2016 5
06/09/2016 Y 06/09/2016 6
However, rather than an exact DATEDIFF I only need a count of workingDay = 'Y' so for calDate 09/09/2016 the DtDIFF value should be 1 rather than 3 and for calDate 08/09/2016 the DtDIFF value should be 2 rather than 4
Is this possible ?
Thanks again
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply