locked
Interesting date table challenge RRS feed

  • Question

  • I have a date table that has a ShippingDay column. This column is an incremental number per month (It starts at 1 at the beginning of each month). However, each Weekend, the number repeats what Friday was because we don't ship on the weekend. So it looks something like this

    Date DayOfWeek DayOfWeek DayOfMonth ShippingDay IsWeekend
    12/31/2006 Sunday 1 1 1 1
    1/1/2007 Monday 2 2 1 0
    1/2/2007 Tuesday 3 3 2 0
    1/3/2007 Wednesday 4 4 3 0
    1/4/2007 Thursday 5 5 4 0
    1/5/2007 Friday 6 6 5 0
    1/6/2007 Saturday 7 7 5 1
    1/7/2007 Sunday 1 8 5 1
    1/8/2007 Monday 2 9 6 0
    1/9/2007 Tuesday 3 10 7 0
    1/10/2007 Wednesday 4 11 8 0
    1/11/2007 Thursday 5 12 9 0
    1/12/2007 Friday 6 13 10 0
    1/13/2007 Saturday 7 14 10 1
    1/14/2007 Sunday 1 15 10 1

    In the past I have populated Shippingday manually in Excel. I would like to programatically do this though. Does anyone have any suggestions or code that would do this?

    Thanks

    Frank


    Frank
    Thursday, November 4, 2010 1:43 AM

Answers

  • Here is another possible solution. The approach is similar to the one used by Plamen, but it uses an anchor date that happend to be a Saturday, so it is independent of the language being used or the setting of DATEFIRST.

    WITH rs(Date, [DayOfWeek], dw, dm, ShippingDay, IsWeekend ) AS (
    SELECT '12/31/2006','Sunday', 1, 1, 1, 1 UNION ALL
    SELECT '1/1/2007','Monday', 2, 2, 1, 0 UNION ALL
    SELECT '1/2/2007','Tuesday', 3, 3, 2, 0 UNION ALL
    SELECT '1/3/2007','Wednesday', 4, 4, 3, 0 UNION ALL
    SELECT '1/4/2007','Thursday', 5, 5, 4, 0 UNION ALL
    SELECT '1/5/2007','Friday', 6, 6, 5, 0 UNION ALL
    SELECT '1/6/2007','Saturday', 7, 7, 5, 1 UNION ALL
    SELECT '1/7/2007','Sunday', 1, 8, 5, 1 UNION ALL
    SELECT '1/8/2007','Monday', 2, 9, 6, 0 UNION ALL
    SELECT '1/9/2007','Tuesday', 3, 10, 7, 0 UNION ALL
    SELECT '1/10/2007','Wednesday', 4, 11, 8, 0 UNION ALL
    SELECT '1/11/2007','Thursday', 5, 12, 9, 0 UNION ALL
    SELECT '1/12/2007','Friday', 6, 13, 10, 0 UNION ALL
    SELECT '1/13/2007','Saturday', 7, 14, 10, 1 UNION ALL
    SELECT '1/14/2007','Sunday', 1, 15, 10, 1 
    )
    SELECT
     Date,
     [DayOfWeek],
     dw,
     dm,
     IsWeekend,
     ShippingDay,
     DENSE_RANK() OVER(PARTITION BY YEAR(Date), MONTH(Date) ORDER BY dm - CASE WHEN T.c1 > 1 THEN 0 ELSE T.c1 + 1 END) AS calc_ShippingDay,
     T.c1,
     CASE WHEN T.c1 > 1 THEN 0 ELSE T.c1 + 1 END AS c2,
     dm - CASE WHEN T.c1 > 1 THEN 0 ELSE T.c1 + 1 END AS c3
    FROM
    	rs
    	CROSS APPLY
    	(SELECT DATEDIFF([day], '18991230', Date) % 7) AS T(c1)
    ORDER BY
     CAST(Date AS datetime);
    GO
    
    I included some of the calculations as columns, so you can have an idea of the numbers used behind the scene.
    AMB

    Some guidelines for posting questions...

    • Marked as answer by Frank Kearney Thursday, November 4, 2010 5:10 PM
    Thursday, November 4, 2010 3:31 PM
  • When I proposed the julainized work day trick (http://www.simple-talk.com/sql/t-sql-programming/temporal-data-techniques-in-sql-/), I did it for the whole calendar table over a big range of years (50 or 100).  Since it was only done once, I did not care about trying to make it one statement or speed.  

    I used the ROW_NUMBER()  on the first pass. The second pass decremented Saturday and Sunday values with an UPDATE. The final passes were the hard ones; I took non-working holidays into account.  Christmas, New Years and some other fixed date holidays were easy, but finding Easter for the next 100 years took some research. I finally wrote a short procedure that took one date and "holiday-ized" it. 



    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    • Proposed as answer by Naomi N Thursday, November 4, 2010 6:36 PM
    • Marked as answer by Kalman Toth Friday, November 12, 2010 11:26 PM
    Thursday, November 4, 2010 6:31 PM

All replies

  • Hi Frank,
     
    Here is one solution. It is based on making the Saturday and Sunday dates the
    same as Friday and then using DENSE_RANK to assign the shipping day number:
     
    SELECT [date],
           DENSE_RANK() OVER(PARTITION BY CONVERT(CHAR(7), [date], 120)
                             ORDER BY
                                CASE DayOfWeek
                                  WHEN 'Saturday' THEN DATEADD(DAY, -1, [date])
                                  WHEN 'Sunday' THEN DATEADD(DAY, -2, [date])
                                  ELSE [date]
                                END) AS shipping_day
    FROM Foo;
     

    Plamen Ratchev
    • Proposed as answer by Naomi N Thursday, November 4, 2010 3:58 AM
    Thursday, November 4, 2010 3:05 AM
  •  

    Hi Frank, if there is a field in the table that contains the information of weekend (ISWEEKEND), why not use this quey :

    select * from YourTable where isweekend=0


    Best regards
    Thursday, November 4, 2010 7:56 AM
  • I keep getting this error

    "Conversion failed when converting the varchar value 'Saturday' to data type int."


    Frank
    Thursday, November 4, 2010 1:58 PM
  • I assumed that the column DayOfWeek is the text representing the day of the week
    (Monday, Tuesday, etc.). At least this is what it looks like from the sample you
    posted. If that is not the case please replace in the CASE expression with the
    appropriate column name. Or provide CREATE table and INSERT statements for
    sample data to generate your table.
     

    Plamen Ratchev
    Thursday, November 4, 2010 2:22 PM
  • Here is another possible solution. The approach is similar to the one used by Plamen, but it uses an anchor date that happend to be a Saturday, so it is independent of the language being used or the setting of DATEFIRST.

    WITH rs(Date, [DayOfWeek], dw, dm, ShippingDay, IsWeekend ) AS (
    SELECT '12/31/2006','Sunday', 1, 1, 1, 1 UNION ALL
    SELECT '1/1/2007','Monday', 2, 2, 1, 0 UNION ALL
    SELECT '1/2/2007','Tuesday', 3, 3, 2, 0 UNION ALL
    SELECT '1/3/2007','Wednesday', 4, 4, 3, 0 UNION ALL
    SELECT '1/4/2007','Thursday', 5, 5, 4, 0 UNION ALL
    SELECT '1/5/2007','Friday', 6, 6, 5, 0 UNION ALL
    SELECT '1/6/2007','Saturday', 7, 7, 5, 1 UNION ALL
    SELECT '1/7/2007','Sunday', 1, 8, 5, 1 UNION ALL
    SELECT '1/8/2007','Monday', 2, 9, 6, 0 UNION ALL
    SELECT '1/9/2007','Tuesday', 3, 10, 7, 0 UNION ALL
    SELECT '1/10/2007','Wednesday', 4, 11, 8, 0 UNION ALL
    SELECT '1/11/2007','Thursday', 5, 12, 9, 0 UNION ALL
    SELECT '1/12/2007','Friday', 6, 13, 10, 0 UNION ALL
    SELECT '1/13/2007','Saturday', 7, 14, 10, 1 UNION ALL
    SELECT '1/14/2007','Sunday', 1, 15, 10, 1 
    )
    SELECT
     Date,
     [DayOfWeek],
     dw,
     dm,
     IsWeekend,
     ShippingDay,
     DENSE_RANK() OVER(PARTITION BY YEAR(Date), MONTH(Date) ORDER BY dm - CASE WHEN T.c1 > 1 THEN 0 ELSE T.c1 + 1 END) AS calc_ShippingDay,
     T.c1,
     CASE WHEN T.c1 > 1 THEN 0 ELSE T.c1 + 1 END AS c2,
     dm - CASE WHEN T.c1 > 1 THEN 0 ELSE T.c1 + 1 END AS c3
    FROM
    	rs
    	CROSS APPLY
    	(SELECT DATEDIFF([day], '18991230', Date) % 7) AS T(c1)
    ORDER BY
     CAST(Date AS datetime);
    GO
    
    I included some of the calculations as columns, so you can have an idea of the numbers used behind the scene.
    AMB

    Some guidelines for posting questions...

    • Marked as answer by Frank Kearney Thursday, November 4, 2010 5:10 PM
    Thursday, November 4, 2010 3:31 PM
  • That worked Hunchback!

    Can I ask what the % 7 is in the Cross Apply?


    Frank
    Thursday, November 4, 2010 5:11 PM
  • % 7 is the remainder of the division of number of days passed from the fixed Saturday's date and the current date. Take a look here

    SQL Server arithmetic operators

    So, if the remainder is 0 or 1, then it's a weekend, otherwise, it's a weekday. That's the same principle that Plamen used in his query, but he based his query on the name of the day of the week instead. You can get name of the day of the week by using DATENAME function DATENAME function BOL Reference


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, November 4, 2010 5:49 PM
  • Thanks, Naomi.

    Yes, the principle is the same as the one used by Plamen, so kudos to him (I did vote for his suggestion).


    AMB

    Some guidelines for posting questions...

    Thursday, November 4, 2010 5:53 PM
  • Frank,

    If you divide by 7 the difference in days between two dates, then you will know how many full 7 days have occurred between the two dates. If the target date is not the same as the anchor, then you will get a reminder which is the number of days after the last multiple. Something like 1 - Sun, 2 - Mon, 3 - Tue, ..., 6 - Fri, 0 - Sat, 1 - Sun, etc.

    Here is an example:

    DECLARE @dt datetime;
    
    SET @dt = '18991230';
    
    WITH rs AS (
    SELECT @dt AS anchor_dt, @dt AS dt, DATEDIFF([day], @dt, @dt) % 7 AS rem
    UNION ALL
    SELECT R.anchor_dt, T.dt, DATEDIFF([day], R.anchor_dt, T.dt) % 7
    FROM rs AS R CROSS APPLY (SELECT DATEADD([day], 1, dt)) AS T(dt)
    WHERE R.dt < '19000201'
    )
    SELECT
    	anchor_dt,
    	dt,
    	DATENAME(dw, dt) AS dw,
    	rem
    FROM
    	rs
    ORDER BY
      dt;
    GO
    

    You can learn more about date and time calculations from the last book of Itzik Ben-Gan about T-SQL Programming. He has a whole chapter dedicated to Date and Time.

     Inside Microsoft SQL Server 2008: T-SQL Programming

    I used CROSS APPLY operator to avoid doing same calculation inside the CASE expression.

     


    AMB

    Some guidelines for posting questions...

    Thursday, November 4, 2010 6:19 PM
  • When I proposed the julainized work day trick (http://www.simple-talk.com/sql/t-sql-programming/temporal-data-techniques-in-sql-/), I did it for the whole calendar table over a big range of years (50 or 100).  Since it was only done once, I did not care about trying to make it one statement or speed.  

    I used the ROW_NUMBER()  on the first pass. The second pass decremented Saturday and Sunday values with an UPDATE. The final passes were the hard ones; I took non-working holidays into account.  Christmas, New Years and some other fixed date holidays were easy, but finding Easter for the next 100 years took some research. I finally wrote a short procedure that took one date and "holiday-ized" it. 



    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    • Proposed as answer by Naomi N Thursday, November 4, 2010 6:36 PM
    • Marked as answer by Kalman Toth Friday, November 12, 2010 11:26 PM
    Thursday, November 4, 2010 6:31 PM