none
Assign a Day of week value to each table row?

    Question

  • I have a table which is used as reference for a daily SQL Agent job

    The table contains a column named 'Day'.

    The intention is that the job will run daily using values from rows in the table where the 'Day' value equals the current day of the week (i.e. on Monday the job will execute using parameters in the table from rows whereby the column 'Day' has a value of 'Monday', on Tuesday the job will execute using parameters in the table from rows where the 'Day' column value equals 'Tuesday' etc..)

    My question is, how can I code some T-SQL to automatically assign the Day of the week value to each row in the table whenever the table is recreated/updated?

    So if for example I have 14 rows in the table I want rows 1 & 8 to have the value of 'Monday' in the 'Day' column, rows 2 & 9 the value 'Tuesday', rows 3 & 10 the value 'Wednesday' etc... etc...

    If there were 8 rows then rows 1 - 7 should have the Day values of 'Monday' - 'Sunday' and row 8 the value 'Monday'

     

    Thanks in advance,

    Phil

    Monday, January 30, 2012 11:40 AM

Answers

  • It's very easy to turn select into update in SQL 2005 and up, just make the select a CTE, e.g.

     

    ;with cte as (SELECT *, DATENAME(weekday,DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [frag_Percent] DESC) - 1, '19000101')) AS [NewDay]
    
    FROM indexMonitor)
    
    UPDATE cte set Day = NewDay
    

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by phil_e Monday, January 30, 2012 4:59 PM
    Monday, January 30, 2012 4:49 PM

All replies

  • You can make it a computed column which works off a date column.

    Unfortunately you cannot place an expression into a default definition.

    Related articles:

    http://www.sqlusa.com/bestpractices2005/calculatedcolumn/

    http://www.sqlusa.com/bestpractices/datetimeconversion/

    SELECT Weekday=datename(dw,CURRENT_TIMESTAMP)
    -- Monday
    

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES
    Monday, January 30, 2012 11:56 AM
  • Please give DDL, sample data and expected output
    Monday, January 30, 2012 11:56 AM
  • Ok, thanks for the replies so far..

    I think I might have made this sound more complex than need be...

    Below is a sample output from the table, what I want to do is assign values for the 'Day' column as detailed above, so for row 1 the value would be 'Monday', row 2 'Tuesday', row 3 'Wednesday' etc..  row after each 7 rows it resets, so row 8 would also be 'Monday' (if there were a row 13 this would be 'Saturday')

    The table and data will already exist, so I do not need this to be dynamic, just something that can be executed after the table data is added (the table will be deleted and recreated every week) so ideally I just want a statement I can run to populate the 'Day' column in this way...

        name     percent  Day
    1  Archive  99          NULL
    2  Service  98          NULL
    3  unit_no 81          NULL
    4  Source  80          NULL
    5  Req_no 78          NULL
    6  Req_up 68          NULL
    7  Merged 67          NULL
    8  dob       34          NULL

     

    Phil

    Monday, January 30, 2012 12:12 PM
  • Hi, try please :

    declare @TAB table ([name] varchar(50),[percent] int)
    insert @TAB 
    select 'Archive',99 union all
    select 'Service',98 union all 
    select 'unit_no',81 union all
    select 'Source',80 union all 
    select 'Req_no',78 union all 
    select 'Req_up',68 union all 
    select 'Merged',67 union all 
    select 'dob',34 
    ;with CTE as
    (select *,ROW_NUMBER() over (order by [percent] desc) as rn from @TAB)
    select [name],[percent],
    case when rn%7=1 then 'Monday'
    when rn%7=2 then 'Tuesday'
    when rn%7=3 then 'Wendsday'
    when rn%7=4 then 'Thursday'
    when rn%7=5 then 'Friday'
    when rn%7=6 then 'Saturday'
    when rn%7=0 then 'Sunday' End as [day]
    from CTE
    

     

     

    Best regards
    Monday, January 30, 2012 12:26 PM
  • Please try the below tsql - 

    DECLARE @Tbl TABLE (RowNum INT IDENTITY(1,1),[Day] VARCHAR(10))
    
    INSERT INTO @Tbl ([Day])
    SELECT NULL UNION ALL
    SELECT NULL UNION ALL
    SELECT NULL UNION ALL
    SELECT NULL UNION ALL
    SELECT NULL UNION ALL
    SELECT NULL UNION ALL
    SELECT NULL UNION ALL
    SELECT NULL UNION ALL
    SELECT NULL UNION ALL
    SELECT NULL UNION ALL
    SELECT NULL UNION ALL
    SELECT NULL
    
    ;WITH WeekDays AS (
    SELECT 0 DW,'Sunday' [Day] UNION ALL
    SELECT 1 DW,'Monday' [Day] UNION ALL
    SELECT 2 DW,'Tuesday' [Day] UNION ALL
    SELECT 3 DW,'Wedday' [Day] UNION ALL
    SELECT 4 DW,'Thursday' [Day] UNION ALL
    SELECT 5 DW,'Friday' [Day] UNION ALL
    SELECT 6 DW,'Saturday' [Day]
    )
    
    UPDATE @Tbl
      SET T.[Day] = WD.[Day]
    FROM
      @Tbl T
    INNER JOIN WeekDays WD
      ON (T.RowNum % 7) = WD.DW    
    
    SELECT 
      *
    FROM
      @Tbl  
    



    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Monday, January 30, 2012 12:33 PM
  • Thank you very much for the replies.

    Think I'm nearly there, so if I take Vinay's suggestion and add my table details (table name is indexMonitor) to give me:

    INSERT INTO dbo.indexMonitor ([Day])
    SELECT NULL;
    
    WITH WeekDays AS (
    SELECT 0 DW,'Sunday' [Day] UNION ALL
    SELECT 1 DW,'Monday' [Day] UNION ALL
    SELECT 2 DW,'Tuesday' [Day] UNION ALL
    SELECT 3 DW,'Wedday' [Day] UNION ALL
    SELECT 4 DW,'Thursday' [Day] UNION ALL
    SELECT 5 DW,'Friday' [Day] UNION ALL
    SELECT 6 DW,'Saturday' [Day]
    )
    
    UPDATE dbo.indexMonitor 
      SET [Day] = WD.[Day]
    FROM
      dbo.indexMonitor T
    INNER JOIN WeekDays WD
      ON (T.RowNum % 7) = WD.DW    
    
    
    SELECT 
      *
    FROM
      dbo.indexMonitor  
    

    Then I end up with this output (I had to add the rownum column prior to running the above):

    name    percent  Day          rownum
    Archive      99       Monday       1
    Service      98       Tuesday      2
    unit_no     81       Wedday      3
    Source      80       Thursday     4
    Req_no     78       Friday          5
    Req_up     68       Saturday     6
    Merged     67       Sunday        7
    dob           34       Monday       8
    NULL        NULL    NULL           NULL

     

    So a couple of further queries:

    How do I avoid the additional row of NULL values being added? (this was not there before running the above)

    Is there a way of modifying the above to make it more dynamic - i.e. remove the need for the rownum column and avoiding any hardcoding of column values?

    Thanks again,

    Phil

     

     

    Monday, January 30, 2012 2:08 PM
  • Why do you add NULL record into your table?

    INSERT INTO dbo.indexMonitor ([Day])
    SELECT NULL;

    This should not be in your code, as with this command you're adding NULL record.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, January 30, 2012 2:12 PM
  • A bit simpler solution than already suggested:

    declare @TAB table ([name] varchar(50),[percent] int)
    insert @TAB 
    select 'Archive',99 union all
    select 'Service',98 union all 
    select 'unit_no',81 union all
    select 'Source',80 union all 
    select 'Req_no',78 union all 
    select 'Req_up',68 union all 
    select 'Merged',67 union all 
    select 'dob',34 
    
    SELECT *, DATENAME(weekday,DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [Percent] DESC) - 1, '19000101')) AS [Day]
    FROM @TAB
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, January 30, 2012 2:14 PM
  • Thank you Naomi,

    Obviously left the insert statement from the previous suggestion from Vinay!

    Like I say I need to keep the code dynamic (no hardcoded values), but I think I have what I need now using the rownum column (without the INSERT statement!).

     

    WITH WeekDays AS (
    SELECT 0 DW,'Sunday' [Day] UNION ALL
    SELECT 1 DW,'Monday' [Day] UNION ALL
    SELECT 2 DW,'Tuesday' [Day] UNION ALL
    SELECT 3 DW,'Wednesday' [Day] UNION ALL
    SELECT 4 DW,'Thursday' [Day] UNION ALL
    SELECT 5 DW,'Friday' [Day] UNION ALL
    SELECT 6 DW,'Saturday' [Day]
    )
    
    UPDATE dbo.indexMonitor 
      SET [Day] = WD.[Day]
    FROM
      dbo.indexMonitor T
    INNER JOIN WeekDays WD
      ON (T.RowNum % 7) = WD.DW    
    
    
    SELECT 
      *
    FROM
      dbo.indexMonitor 
    


    Phil

    Monday, January 30, 2012 2:31 PM
  • I believe my query is simpler - it uses a fact that '19000101' (anchor date) is Monday.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, January 30, 2012 2:37 PM
  • I think you are probably right...

    However I am having trouble incorporating your statement as a UPDATE.

    The statement below returns the values I want, but how do I update the 'Day' column for my existing rows to include this output?

    SELECT DATENAME(weekday,DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [frag_Percent] DESC) - 1, '19000101')) AS [Day]
    FROM indexMonitor
    

     


    I don't necessarily need the ORDER as I already have existing data, just need to add the days...

    (as you may have guessed I am flirting with the boundaries of my SQL experience here!)

    Thanks,

    Phil





    • Edited by phil_e Monday, January 30, 2012 4:19 PM
    Monday, January 30, 2012 4:13 PM
  • Hi phil

    I just managed to get Naomi code to be working for an update.

    The below update works only if your name, percentage fields are unique or else you can add a unique identifier to your table schema instead.

     

    declare @TAB table 
    ([name] varchar(50),[percent] int,[day] varchar(50))
    insert @TAB 
    select 'Archive',99,NULL union all
    select 'Service',98,NULL union all 
    select 'unit_no',81,NULL union all
    select 'Source',80,NULL union all 
    select 'Req_no',78,NULL union all 
    select 'Req_up',68,NULL union all 
    select 'Merged',67,NULL union all 
    select 'dob',34,NULL 
    
    ;WITH TabDay AS
    (
    SELECT *, DATENAME(weekday,DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [Percent] DESC) - 1, '19000101')) AS [D]
    FROM @TAB
    )
    
    Update T
    Set T.[day] = TD.D
    FROM TabDay TD
    JOIN @TAB T
    ON T.[name] = TD.[name]
    AND T.[percent] = TD.[percent]
    
    SELECT * FROM @TAB
    
    
    
    



    Nothing is Permanent... even Knowledge....
    My Blog
    Monday, January 30, 2012 4:34 PM
  • It's very easy to turn select into update in SQL 2005 and up, just make the select a CTE, e.g.

     

    ;with cte as (SELECT *, DATENAME(weekday,DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [frag_Percent] DESC) - 1, '19000101')) AS [NewDay]
    
    FROM indexMonitor)
    
    UPDATE cte set Day = NewDay
    

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by phil_e Monday, January 30, 2012 4:59 PM
    Monday, January 30, 2012 4:49 PM
  • It is easy when you know how!

    Thank you very much

    I now have the solution I was after.

    Phil

    Monday, January 30, 2012 4:59 PM