January 12, 2015 at 3:26 pm
Hello,
I have a scenario where I would need to add +4 IDs with the existing IDs, below is an example:
IDWorkloadUnits
1EXO 3
7SPO 4
15LYO 10
Desired output should be as follows:
IDWorkloadUnits
1EXO 3
2
3
4
5
7SPO 4
8
9
10
11
15LYO 10
16
17
18
19
I am not worried about other attributes in the same table. Could you please help me with some pointers on the same.
Thanks in advance!
January 12, 2015 at 3:48 pm
srinivas.akyana (1/12/2015)
Hello,I have a scenario where I would need to add +4 IDs with the existing IDs, below is an example:
IDWorkloadUnits
1EXO 3
7SPO 4
15LYO 10
Desired output should be as follows:
IDWorkloadUnits
1EXO 3
2
3
4
5
7SPO 4
8
9
10
11
15LYO 10
16
17
18
19
I am not worried about other attributes in the same table. Could you please help me with some pointers on the same.
Thanks in advance!
Quick inline tally solution, should be sufficient to get you passed this hurdle
😎
USE tempdb;
GO
SET NOCOUNT ON;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,SAMPLE_DATA(ID,[Workload],Units) AS
( SELECT * FROM (VALUES
(1, 'EXO',3 )
,(7, 'SPO',4 )
,(15, 'LYO',10)) AS X(ID,[Workload],Units)
)
,NUMS(N) AS (SELECT TOP((SELECT MAX(ID) FROM SAMPLE_DATA) + 4) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5) -- 10^5
SELECT
NM.N
,SD.[Workload]
,SD.Units
FROM NUMS NM
LEFT OUTER JOIN SAMPLE_DATA SD
ON NM.N = SD.ID;
Results
N Workload Units
---- -------- -------
1 EXO 3
2 NULL NULL
3 NULL NULL
4 NULL NULL
5 NULL NULL
6 NULL NULL
7 SPO 4
8 NULL NULL
9 NULL NULL
10 NULL NULL
11 NULL NULL
12 NULL NULL
13 NULL NULL
14 NULL NULL
15 LYO 10
16 NULL NULL
17 NULL NULL
18 NULL NULL
19 NULL NULL
January 12, 2015 at 3:54 pm
srinivas.akyana (1/12/2015)
I am not worried about other attributes in the same table. Could you please help me with some pointers on the same.
From the looks of it, you should be worried. 😉 What is this for? We might be able to suggest a better thing to do.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 12, 2015 at 4:55 pm
I am working on calculating a Churn for which I would need to look at the current data + 4 months. My current solution is derived using CROSSJOIN (was applying by brining in all the MonthIDs based on my time criteria) which is quite slow as data volume is quite high.
What I am trying to achieve is, instead of CROSSJOINing with all MonthIDs - trying to restrict to only +4 months so that I can save some processing time. The reason why I said I was not concerned about other attributes because I was thinking of just repeat them that would do for me. I have attached a screenshot with original and desired data set.
Please let me know if you need any further information on this regard.
Thanks in advance!
January 26, 2015 at 7:53 am
I implemented similar task in PLSQL with functions 'lag' and 'lead' to generate recursively ids between sorted ids current row and next row.
Could not find them in 2008 MSSQL... So below is the recursive example with left join (with ‘lag and lead’ it could be ‘union’).
WITH s_Recursive(LEVEL)
AS
( SELECT 0 as level --initial value 0
union all
SELECT smr.level+1 as level -- incriment value 1
FROM s_Recursive as smr Where smr.level< 15 -- max value
)
SELECT LEVEL,x.[Workload],x.Units FROM s_Recursive
left outer join
(SELECT * FROM (VALUES
(1, 'EXO',3 )
,(7, 'SPO',4 )
,(15, 'LYO',10)) AS X (ID,[Workload],Units) ) x
on s_Recursive.level = x.id
January 26, 2015 at 11:47 am
Tally needs only 5 members because OP needs to derive only 4 rows from each original row. Provided there are no 2 original rows where abs(r1.id-r2.id) < 5 , try
WITH SAMPLE_DATA(ID,[Workload],Units) AS (
SELECT * FROM (VALUES
(1, 'EXO',3 )
,(7, 'SPO',4 )
,(15, 'LYO',10)) AS X(ID,[Workload],Units)
)
SELECT d.ID + t.N, d.Workload, d.Units
FROM (VALUES (0),(1),(2),(3),(4)) t(N),
SAMPLE_DATA d
January 26, 2015 at 2:40 pm
I got it. Here is the recursive solution to add 4 rows per each original row
with org as
(SELECT * FROM (VALUES (1, 'EXO',3 ),(7, 'SPO',4 ),(15, 'LYO',10)) AS X (ID,Workload,Units) )
, s_Recursive(ID,Workload,Units) as
(
(SELECT * FROM (VALUES (1, 'EXO',3 ),(7, 'SPO',4 ),(15, 'LYO',10)) AS X (ID,Workload,Units) )
union all
SELECT smr.id+1 as id,smr.Workload,null as Units -- incriment value 1
FROM s_Recursive as smr join org on smr.Workload = org.Workload
where smr.id < org.id+4
)
SELECT ID,Workload,Units FROM s_Recursive
order by id
;
IDWorkloadUnits
1EXO3
2EXO
3EXO
4EXO
5EXO
7SPO4
8SPO
9SPO
10SPO
11SPO
15LYO10
16LYO
17LYO
18LYO
19LYO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply