How do I assign the correct date ?

Question

How do I assign the correct date ?

chris.evans 94907

Old Hand

Points: 300
More actions
August 19, 2016 at 8:59 am

#150500

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

Viewing 15 posts - 1 through 15 (of 19 total)

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

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)

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

chris.evans 94907 Old Hand Points: 300 More actions · Answer 3

chris.evans 94907

Old Hand

Points: 300

August 22, 2016 at 2:49 am

#1896573

Great that worked thanks Alan

turlteman.mike Valued Member Points: 59 More actions · Answer 4

This looks useful but whrere you have this line

SELECT TOP (1) calDate MIN(caldate)

Is the strikethrough deliberate?

Alan Burstein SSC Guru Points: 61136 More actions · Answer 5

turlteman.mike (9/1/2016)
This looks useful but whrere you have this line
SELECT 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.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

chris.evans 94907 Old Hand Points: 300 More actions · Answer 6

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

Ed Wagner SSC Guru Points: 287024 More actions · Answer 7

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;

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

chris.evans 94907 Old Hand Points: 300 More actions · Answer 8

Sorry it's the difference in days not months but only where the Working Day flag is set to 'Y'

Alan Burstein SSC Guru Points: 61136 More actions · Answer 9

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

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

drew.allen SSC Guru Points: 76944 More actions · Answer 10

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

Ed Wagner SSC Guru Points: 287024 More actions · Answer 11

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Ed Wagner SSC Guru Points: 287024 More actions · Answer 12

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Alan Burstein SSC Guru Points: 61136 More actions · Answer 13

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 🙂

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

chris.evans 94907 Old Hand Points: 300 More actions · Answer 14

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