April 16, 2017 at 11:45 pm
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
April 17, 2017 at 4:15 am
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);
April 17, 2017 at 8:37 pm
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;
April 18, 2017 at 6:08 am
It worked, Thank you guys! Awesome!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply