January 23, 2015 at 1:03 am
Hello,
I am working on a scenario where I would need to repeat the same row for previous MonthIDs, here's the example (snapshot attached):
CustomerIDMonthIDRSCIDFlag1Flag2Flag3
1232943456000
1233001234011
1233033542010
1233032345111
1233043542010
1233042345111
1233053542010
1233052345111
1233062345111
1233063542010
1233072345111
1233073542010
1233083542010
1233093542010
1.For MonthIDs 309 & 307, I would need to add same combinations to 4 prior months (for 309, I would need the 309 row as 308, 307, 306, 305 and for 307, I would need to add 306, 305, 304, 303).
2.From the snapshot, Green rows are added for 309 MonthID and Orange rows are added for 307.
3.While we are adding 309 row to prior to 4 months, if any prior month has different combination than 309, that same combination need to be repeated for prior months.
I was using the below code to achieve the same:
declare @SC_j as int = 0;
declare @SC_k as int = 4;
while @SC_j <= 4
begin
insert into #Test
select distinct dt.CustomerID
, dt.FiscalMonthID - @SC_j as FiscalMonthID
, dt.RSCID
, dt.Flag1
, dt.Flag2
, dt.Flag3
, NULL as NetPaidUnits
from #Churn_STG_AdjustedLifeCycleUnit_Final as dt
join #MonthPosition_SC as fl
on dt.CustomerID = fl.CustomerID
and dt.FiscalMonthID <= fl.FiscalMonthID
and dt.FiscalMonthID >= fl.FiscalMonthID - @SC_k
;
set @SC_j = @SC_j + 1;
set @SC_k = @SC_k - 1;
end
;
But my code starts from 1st MonthID however I would want this to be starting from last MonthID which I am not able to achieve.
Request you to please look and let me know in case of any questions.
Thank You!
January 23, 2015 at 1:34 am
Srinivasan,
Here's the create table and insert scripts:
CREATE TABLE MyData(
CustomerID INT,
MonthIDINT,
RSCIDINT,
Flag1BIT,
Flag2BIT,
Flag3BIT);
GO
INSERT INTO MyData(CustomerID, MonthID, RSCID, Flag1, Flag2, Flag3)
VALUES(123,294,3456,0,0,0),
(123, 300, 1234,0,1,1),
(123,303, 3542,0,1,0),
(123,303,2345,1,1,1),
(123,304,3542,0,1,0),
(123,304,2345,1,1,1),
(123,305,3542,0,1,0),
(123,305,2345,1,1,1),
(123,306,2345,1,1,1),
(123,306,3542,0,1,0),
(123,307,2345,1,1,1),
(123,307,3542,0,1,0),
(123,308,3542,0,1,0),
(123,309,3542,0,1,0);
Could you give an example of your expected result?
January 23, 2015 at 1:42 am
Sorry, I missed it in my initial message and thanks for table creation script.
Please find the attached and let me know if I need to provide any further information the same.
Thank You!
January 23, 2015 at 1:50 am
I was looking for an explanation of a rule or something. Is there a pattern here I'm supposed to use to do this? If so, what is it? It almost looks like you're trying to fill gaps in a sequence, but that's not right either....
what "row combination" are you trying to repeat? And for what other [combination of] column(s)?
January 23, 2015 at 2:06 am
Your original table shows RSCID of 3542 associated with MonthID of 309. In the expected results table, rows with RSCID of 3542 (original month 309) are coloured both green and orange - yet you state green for month 309.
What rules determine how many months back you require for each MonthID in the original table?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 23, 2015 at 2:10 am
I did mention the same in my initial message but not sure if that explains enough about rules, however I attached another snapshot with comments. Hope that helps!
January 23, 2015 at 2:18 am
Hello ChrisM@Work,
Since I wanted to repeat the original combination 4 times prior for MonthIDs of 307 & 309, I would need to first start with latest month which is 309. And, original combination of 309 does adds an entry for 307 as well that means, 307 will have two entries i.e, RSCID = 2345 (original) & RSCID = 3542.
Once I am done with iteration for MonthID of 309 then I will need to the same process for MonthID of 307 as well. Since MonthID of 307 has two entries now, those two entries needs to be repeated for MonthIDs of 306, 305, 304 & 303.
Please let me know if you need any further information from my side.
Thanks in advance!
January 23, 2015 at 2:44 am
WITH cte1 (CustomerID,MonthID,RSCID,Flag1,Flag2,Flag3)
AS (
SELECTd.CustomerID,d.MonthID,d.RSCID,d.Flag1,d.Flag2,d.Flag3
FROMMyData d
UNION ALL
SELECTd.CustomerID,d.MonthID-n.N,d.RSCID,d.Flag1,d.Flag2,d.Flag3
FROMMyData d
CROSS JOIN (SELECT 1 AS [N] UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) n
WHEREd.MonthID = 309
),
cte2 (CustomerID,MonthID,RSCID,Flag1,Flag2,Flag3)
AS (
SELECTd.CustomerID,d.MonthID,d.RSCID,d.Flag1,d.Flag2,d.Flag3
FROMcte1 d
UNION ALL
SELECTd.CustomerID,d.MonthID-n.N,d.RSCID,d.Flag1,d.Flag2,d.Flag3
FROMcte1 d
CROSS JOIN (SELECT 1 AS [N] UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) n
WHEREd.MonthID = 307
)
SELECTDISTINCT CustomerID,MonthID,RSCID,Flag1,Flag2,Flag3
FROMcte2
ORDER BY MonthID ASC,RSCID ASC
Far away is close at hand in the images of elsewhere.
Anon.
January 23, 2015 at 3:02 am
srinivas.akyana (1/23/2015)
Hello ChrisM@Work,Since I wanted to repeat the original combination 4 times prior for MonthIDs of 307 & 309, I would need to first start with latest month which is 309. And, original combination of 309 does adds an entry for 307 as well that means, 307 will have two entries i.e, RSCID = 2345 (original) & RSCID = 3542.
Once I am done with iteration for MonthID of 309 then I will need to the same process for MonthID of 307 as well. Since MonthID of 307 has two entries now, those two entries needs to be repeated for MonthIDs of 306, 305, 304 & 303.
Please let me know if you need any further information from my side.
Thanks in advance!
Try this. I think it exposes a few flaws in your logic.
;WITH Extras AS (
SELECT d.CustomerID, SourceMonthID = d.MonthID, d.RSCID, d.Flag1, d.Flag2, d.Flag3, x.MonthID
FROM #MyData d
CROSS APPLY (
SELECT MonthID-1 UNION ALL
SELECT MonthID-2 UNION ALL
SELECT MonthID-3 UNION ALL
SELECT MonthID-4 UNION ALL
SELECT MonthID-5 UNION ALL
SELECT MonthID-6
) x (MonthID)
WHERE d.MonthID IN (307,309)
AND x.MonthID > 302
)
SELECT d.CustomerID, SourceMonthID = d.MonthID, d.MonthID, d.RSCID, d.Flag1, d.Flag2, d.Flag3, Colour = 'None'
FROM #MyData d
UNION ALL
SELECT CustomerID, SourceMonthID, MonthID, RSCID, Flag1, Flag2, Flag3, Colour = CASE WHEN x.cnt = 0 THEN 'Green' ELSE 'Orange' END
FROM Extras e
CROSS APPLY (SELECT cnt = COUNT(*) FROM Extras i WHERE i.MonthID = e.MonthID AND i.SourceMonthID = 307) x
ORDER BY MonthID, RSCID;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 23, 2015 at 7:54 am
srinivas.akyana (1/23/2015)
Hello ChrisM@Work,Since I wanted to repeat the original combination 4 times prior for MonthIDs of 307 & 309, I would need to first start with latest month which is 309. And, original combination of 309 does adds an entry for 307 as well that means, 307 will have two entries i.e, RSCID = 2345 (original) & RSCID = 3542.
Once I am done with iteration for MonthID of 309 then I will need to the same process for MonthID of 307 as well. Since MonthID of 307 has two entries now, those two entries needs to be repeated for MonthIDs of 306, 305, 304 & 303.
Please let me know if you need any further information from my side.
Thanks in advance!
Let's see if we can simplify this request a bit.
Are you saying that you want to find the maximum MonthID for each and every RSCID and then ensure (generate if necessary) that there are exactly 4 months (original and 3 previous) for each RSCID?
Your latest graphic seems to suggest that but I just want to make sure.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply