a4apple (6/30/2015)
@Steve, your query failed with an error message LAG doesn't have an OVER clause. So I added that with partition between AccountID.Next, It actually prints the flag for the next month also where as it shouldn't.
AccountIDDatekey1234
------------------------------------------------
1 2015011000
1 2015020000
1 2015041100
2 2015041100
3 2015011000
3 2015031111
4 2015041111
It should actually be like below
AccountIDDatekey1234
1 2015011000
1 2015020000
1 2015040100
2 2015041100
3 2015011000
3 2015030111
4 2015041111
Thank you for your time. 🙂
Okay... a little challenging for me to troubleshoot until I get home this evening, but here's a second shot, with the added OVER clause for LAG included, and some logic that I'm hoping solves the other problem:
DECLARE @test-2 AS TABLE (
AccountID INT,
Datekey INT,
RunningTotal INT
);
DECLARE @Points AS TABLE (
Lev INT,
Points INT
);
INSERT INTO @Points VALUES (1,2), (2,5), (3,10), (4,15);
INSERT INTO @test-2 VALUES (1, 201501, 2), (1, 201502, 4), (1, 201504, 7);
WITH TIMINGS AS (
SELECT T.AccountID, T.Datekey, T.RunningTotal AS Points, P.Lev,
ROW_NUMBER() OVER(PARTITION BY T.AccountID ORDER BY T.Datekey) AS RN
FROM @test-2 AS T
CROSS APPLY (SELECT MAX(PT.Lev) AS Lev FROM @Points AS PT WHERE PT.Points <= T.RunningTotal) AS P
)
SELECT T.AccountID,
T.Datekey,
T.Points,
T.Lev AS LevelAchieved,
CASE LAG(T.Lev, 1) OVER(PARTITION BY T.AccountID ORDER BY T.RN)
WHEN T.Lev THEN 0
WHEN T.Lev + 1 THEN 0
WHEN T.Lev + 2 THEN 1
ELSE CASE WHEN T.Lev >= 1 THEN 1 ELSE 0 END
END AS L1,
CASE LAG(T.Lev, 1) OVER(PARTITION BY T.AccountID ORDER BY T.RN)
WHEN T.Lev THEN 0
WHEN T.Lev + 1 THEN 0
WHEN T.Lev + 2 THEN 1
ELSE CASE WHEN T.Lev >= 2 THEN 1 ELSE 0 END
END AS L2,
CASE LAG(T.Lev, 1) OVER(PARTITION BY T.AccountID ORDER BY T.RN)
WHEN T.Lev THEN 0
WHEN T.Lev + 1 THEN 0
WHEN T.Lev + 2 THEN 1
ELSE CASE WHEN T.Lev >= 3 THEN 1 ELSE 0 END
END AS L3,
CASE LAG(T.Lev, 1) OVER(PARTITION BY T.AccountID ORDER BY T.RN)
WHEN T.Lev THEN 0
WHEN T.Lev + 1 THEN 0
WHEN T.Lev + 2 THEN 1
ELSE CASE WHEN T.Lev >= 4 THEN 1 ELSE 0 END
END AS L4
FROM TIMINGS AS T
ORDER BY T.AccountID, T.RN
Let me know,,,
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)