How do I assign the correct date ?

  • 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

  • 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

  • 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

  • Great that worked thanks Alan

  • This looks useful but whrere you have this line

    SELECT TOP (1) calDate MIN(caldate)

    Is the strikethrough deliberate?

  • 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

  • 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

  • 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;

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

  • 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

  • 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

  • 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.

  • 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.

  • 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

  • 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