Need Help on schedule days

• koti.raavi

  SSCrazy

  Points: 2756

  More actions

  April 16, 2017 at 11:45 pm

  #342513

  I have created temp table and inserted data, problem is we have schedule days column staring from 0-6 (Mon-Sun), But we received one more value 7 it means it is schedule everyday , I want to expand 7 as 0-6 like each row for each day. One row become 7rows, Thanks! Any help much appreciated.

  


  Create table #Temp

  (

  ID Int,

  
  

  Executed_Time Time,

  
  

  Schedule_Days SmallInt

  
  

  )

  
  

  Insert into #TEMP(ID,Executed_Time,Schedule_Days) Values(101,'17:30:00',0)

  
  

  Insert into #TEMP(ID,Executed_Time,Schedule_Days) Values(101,'17:30:00',1)

  
  

  Insert into #TEMP(ID,Executed_Time,Schedule_Days) Values(101,'17:30:00',2)

  
  

  Insert into #TEMP(ID,Executed_Time,Schedule_Days) Values(101,'17:30:00',3)

  
  

  Insert into #TEMP(ID,Executed_Time,Schedule_Days) Values(102,'15:30:00',7)

  
  

  Output:

  101,'17:30:00',0

  
  

  101,'17:30:00',1

  
  

  101,'17:30:00',2

  
  

  101,'17:30:00',3

  
  

  102,'15:30:00',0

  
  

  102,'15:30:00',1

  
  

  102,'15:30:00',2

  
  

  102,'15:30:00',3

  
  

  102,'15:30:00',4

  
  

  102,'15:30:00',5

  
  

  102,'15:30:00',6

• DesNorton

  SSC-Insane

  Points: 24283

  More actions

  April 17, 2017 at 4:15 am

  #1938281

  This should give you the results that you are looking for.

  
CREATE TABLE #TEMP (
  ID    Int
, Executed_Time Time
, Schedule_Days SmallInt
);

  Insert into #TEMP(ID,Executed_Time,Schedule_Days) Values(101,'17:30:00',0);
Insert into #TEMP(ID,Executed_Time,Schedule_Days) Values(101,'17:30:00',1);
Insert into #TEMP(ID,Executed_Time,Schedule_Days) Values(101,'17:30:00',2);
Insert into #TEMP(ID,Executed_Time,Schedule_Days) Values(101,'17:30:00',3);
Insert into #TEMP(ID,Executed_Time,Schedule_Days) Values(102,'15:30:00',7);

  WITH cteDays(Schedule_Days) AS (
SELECT N FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) AS X(N)
)
SELECT
  t.ID
, t.Executed_Time
, Schedule_Days = cte.Schedule_Days
FROM #TEMP AS t
INNER JOIN cteDays AS cte
 ON cte.Schedule_Days = ISNULL(NULLIF(t.Schedule_Days, 7), cte.Schedule_Days);


  ---

  How to post data/code on a forum to get the best help.

• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  April 17, 2017 at 8:37 pm

  #1938436

  Here's a slightly different way of producing the same results...
  
WITH cteDays(Schedule_Days) AS (
SELECT N FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) AS X(N)
)
SELECT
    t.ID,
    t.Executed_Time,
    Schedule_Days = COALESCE(cte.Schedule_Days, t.Schedule_Days)
FROM
    #TEMP AS t
    LEFT JOIN cteDays AS cte
        ON t.Schedule_Days = 7;


• koti.raavi

  SSCrazy

  Points: 2756

  More actions

  April 18, 2017 at 6:08 am

  #1938484

  It worked, Thank you guys! Awesome!