June 30, 2015 at 10:54 am
Hello everyone,
I was wondering if there is a way I can achieve this. I have a table like @test-2 which has all data like how many points has an account scored at the end of the month. I have a points table that has number of points associated a Level.
Now I want my output to be pivoted in such a way I will know the Levels such that the first month it has achieved that particular level.
Eg: If AccountID has achieved 7 points in 201501 and 3 points in 201503 then I want to see flag for 2 and 5 as 1 and the rest as 0 in 201501 , 201502 with all 0's and 201503 only 10 being 1 rest (2,5,15) with 0.
For the missing month's information I have a calendar table, so only Pivoting is the question.
DECLARE @test-2 TABLE
(
AccountID INT,
Datekey INT,
RunningTotal INT
)
DECLARE @Points 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)
--SELECT * FROM @test-2
--SELECT * FROM @Points
--Output like this
SELECT 1 AS AccountID, 201501 AS DateKey, 1 AS [1], 0 AS [2], 0 AS [3], 0 AS [4]-- 1 AS Flag
UNION ALL
SELECT 1 AS AccountID, 201502 AS DateKey, 0 AS [1], 0 AS [2], 0 AS [3], 0 AS [4]
UNION ALL
SELECT 1 AS AccountID, 201504 AS DateKey, 0 AS [1], 1 AS [2], 0 AS [3], 0 AS [4]
Thanks in advance for any suggestions.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
June 30, 2015 at 11:23 am
I tried to change the @points and tried the below code but the problem is I am seeing the flag active for other period also.
DECLARE @test-2 TABLE
(
AccountID INT,
Datekey INT,
RunningTotal INT
)
DECLARE @Points TABLE
(
Lev INT,
StartingPoints INT,
EndingPoints INT
)
INSERT INTO @Points VALUES (1,2,4), (2,5,9), (3,10,14), (4,15,24)
INSERT INTO @test-2 VALUES (1, 201501, 2), (1, 201502, 4), (1, 201504, 7)
--My Trail Query
SELECT a.*, CASE WHEN (b.Lev = 1 AND a.RunningTotal BETWEEN b.StartingPoints AND b.EndingPoints) THEN 1
WHEN (b.Lev = 2 AND a.RunningTotal BETWEEN b.StartingPoints AND b.EndingPoints) THEN 1
WHEN (b.Lev = 3 AND a.RunningTotal BETWEEN b.StartingPoints AND b.EndingPoints) THEN 1
WHEN (b.Lev = 4 AND a.RunningTotal BETWEEN b.StartingPoints AND b.EndingPoints) THEN 1
ELSE 0
END AS [Flag]
FROM @test-2 a, @Points b
ORDER BY Datekey
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
June 30, 2015 at 11:37 am
I was exactly going to suggest the @Points table because I had to use a triangular join which might cause problems.
Using your new Points table, here's a possible solution. I have to use the @test-2 table twice to get "inactive" rows.
WITH PivotCTE AS(
SELECT AccountID,
MIN( Datekey) Datekey,
CASE WHEN Lev = 1 THEN 1 ELSE 0 END AS [1],
CASE WHEN Lev = 2 THEN 1 ELSE 0 END AS [2],
CASE WHEN Lev = 3 THEN 1 ELSE 0 END AS [3],
CASE WHEN Lev = 4 THEN 1 ELSE 0 END AS [4]
FROM @test-2 t
JOIN @Points p ON t.RunningTotal >= p.MinPoints
AND t.RunningTotal <= p.MaxPoints
GROUP BY AccountID, Lev
)
SELECT t.AccountID,
t.Datekey,
ISNULL( p.[1], 0) AS [1],
ISNULL( p.[2], 0) AS [2],
ISNULL( p.[3], 0) AS [3],
ISNULL( p.[4], 0) AS [4]
FROM @test-2 t
LEFT
JOIN PivotCTE p ON t.AccountID = p.AccountID
AND t.Datekey = p.Datekey;
June 30, 2015 at 11:44 am
Luis Cazares (6/30/2015)
I was exactly going to suggest the @Points table because I had to use a triangular join which might cause problems.Using your new Points table, here's a possible solution. I have to use the @test-2 table twice to get "inactive" rows.
Thanks Luis for the reply. I forgot to add another question to the list. If I have an account achieving 7 points in the first month, I should see Flag for 2 and 5 as 1 , which means if the account has crossed one or more levels in the same month we should be able to see both of them.
INSERT INTO @test-2 VALUES (1, 201501, 2), (1, 201502, 4), (1, 201504, 7), (2, 201504, 7), (3, 201501, 2), (3,201503, 16)
I hope you got my question.
Thanks again for your time and the previous query.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
June 30, 2015 at 11:54 am
a4apple (6/30/2015)
Luis Cazares (6/30/2015)
I was exactly going to suggest the @Points table because I had to use a triangular join which might cause problems.Using your new Points table, here's a possible solution. I have to use the @test-2 table twice to get "inactive" rows.
Thanks Luis for the reply. I forgot to add another question to the list. If I have an account achieving 7 points in the first month, I should see Flag for 2 and 5 as 1 , which means if the account has crossed one or more levels in the same month we should be able to see both of them.
INSERT INTO @test-2 VALUES (1, 201501, 2), (1, 201502, 4), (1, 201504, 7), (2, 201504, 7), (3, 201501, 2), (3,201503, 16)
I hope you got my question.
Thanks again for your time and the previous query.
Are you using SQL Server 2012?
June 30, 2015 at 11:56 am
Are you using SQL Server 2012?
Yes I am.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
June 30, 2015 at 12:19 pm
Here's a possible solution. I included the full DDL and added some sample data. Take a look and ask any questions that you might have.
DECLARE @test-2 TABLE
(
AccountID INT,
Datekey INT,
RunningTotal INT
)
DECLARE @Points 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),
(2, 201501, 7), (2, 201502, 9), (2, 201504, 12);
WITH LagCTE AS(
SELECT *,
LAG(t.RunningTotal,1,0) OVER(PARTITION BY AccountID ORDER BY DateKey) PrevRunningTotal
FROM @test-2 t
),
PivotCTE AS(
SELECT AccountID,
Datekey,
MAX( CASE WHEN Lev = 1 THEN 1 ELSE 0 END) AS [1],
MAX( CASE WHEN Lev = 2 THEN 1 ELSE 0 END) AS [2],
MAX( CASE WHEN Lev = 3 THEN 1 ELSE 0 END) AS [3],
MAX( CASE WHEN Lev = 4 THEN 1 ELSE 0 END) AS [4]
FROM LagCTE t
JOIN @Points p ON p.Points > t.PrevRunningTotal
AND p.Points <= t.RunningTotal
GROUP BY AccountID, Datekey
)
SELECT t.AccountID,
t.Datekey,
ISNULL( p.[1], 0) AS [1],
ISNULL( p.[2], 0) AS [2],
ISNULL( p.[3], 0) AS [3],
ISNULL( p.[4], 0) AS [4]
FROM @test-2 t
LEFT
JOIN PivotCTE p ON t.AccountID = p.AccountID
AND t.Datekey = p.Datekey;
June 30, 2015 at 12:29 pm
Here's a possible solution. I included the full DDL and added some sample data. Take a look and ask any questions that you might have.
Thanks Luis. Works like a Charm. so I think the previous DDL with just one Points Column works fine.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
June 30, 2015 at 12:37 pm
a4apple (6/30/2015)
Luis Cazares (6/30/2015)
I was exactly going to suggest the @Points table because I had to use a triangular join which might cause problems.Using your new Points table, here's a possible solution. I have to use the @test-2 table twice to get "inactive" rows.
Thanks Luis for the reply. I forgot to add another question to the list. If I have an account achieving 7 points in the first month, I should see Flag for 2 and 5 as 1 , which means if the account has crossed one or more levels in the same month we should be able to see both of them.
INSERT INTO @test-2 VALUES (1, 201501, 2), (1, 201502, 4), (1, 201504, 7), (2, 201504, 7), (3, 201501, 2), (3,201503, 16)
I hope you got my question.
Thanks again for your time and the previous query.
Here's another shot at it - I don't have a 2012 instance to test it, and I can definitely learn something from someone testing it and letting me know if it fails:
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) WHEN T.Lev THEN 0 ELSE CASE WHEN T.Lev >= 1 THEN 1 ELSE 0 END END AS L1,
CASE LAG(T.Lev, 1) WHEN T.Lev THEN 0 ELSE CASE WHEN T.Lev >= 2 THEN 1 ELSE 0 END END AS L2,
CASE LAG(T.Lev, 1) WHEN T.Lev THEN 0 ELSE CASE WHEN T.Lev >= 3 THEN 1 ELSE 0 END END AS L3,
CASE LAG(T.Lev, 1) WHEN T.Lev THEN 0 ELSE CASE WHEN T.Lev >= 4 THEN 1 ELSE 0 END END AS L4
FROM TIMINGS AS T
ORDER BY T.AccountID, T.RN
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 30, 2015 at 12:46 pm
Here's another shot at it - I don't have a 2012 instance to test it, and I can definitely learn something from someone testing it and letting me know if it fails:
Sure Steve, will update you in a bit after my testing is done with your query. Thanks again.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
June 30, 2015 at 1:07 pm
@steve-2, 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. 🙂
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
June 30, 2015 at 2:14 pm
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)
June 30, 2015 at 2:19 pm
Steve,
You can test your code using SQL Fiddle
June 30, 2015 at 4:31 pm
Luis Cazares (6/30/2015)
Steve,You can test your code using SQL Fiddle
Was in progress using that site when 5pm showed up. Will look further at this later tonight...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 1, 2015 at 8:02 am
sgmunson (6/30/2015)
Luis Cazares (6/30/2015)
Steve,You can test your code using SQL Fiddle
Was in progress using that site when 5pm showed up. Will look further at this later tonight...
Finally got to this and used SQL Fiddle to do the testing. This appears to work, but I'd need to have a lot more test data to be sure it works correctly for all possible scenarios:
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), (2, 201501, 7), (2, 201502, 9), (2, 201504, 12);
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,
ISNULL(LAG(P.Lev, 1) OVER(PARTITION BY T.AccountID ORDER BY T.Datekey), 0) AS LAG_VALUE
FROM @test-2 AS T
CROSS APPLY (SELECT MAX(PT.Lev) AS Lev FROM @Points AS PT WHERE PT.Points <= T.RunningTotal) AS P
),
MINIMUM_LEVELS AS (
SELECT TM.AccountID, P.Lev, TM.MIN_DATE
FROM @Points AS P
OUTER APPLY (SELECT T.AccountID, MIN(T.Datekey) AS MIN_DATE FROM TIMINGS AS T WHERE T.Lev = P.Lev GROUP BY T.AccountID) AS TM
)
SELECT T.AccountID,
T.Datekey,
T.Points,
T.Lev AS LevelAchieved,
CASE
WHEN ML.AccountID IS NULL THEN 0
WHEN ML.Lev = 1 THEN 1
WHEN T.Lev > 1 AND T.LAG_VALUE = 0 THEN 1
ELSE 0
END AS L1,
CASE
WHEN ML.AccountID IS NULL THEN 0
WHEN ML.Lev = 2 THEN 1
WHEN T.Lev > 2 AND T.LAG_VALUE = 0 THEN 1
ELSE 0
END AS L2,
CASE
WHEN ML.AccountID IS NULL THEN 0
WHEN ML.Lev = 3 THEN 1
WHEN T.Lev > 3 AND T.LAG_VALUE = 0 THEN 1
ELSE 0
END AS L3,
CASE
WHEN ML.AccountID IS NULL THEN 0
WHEN ML.Lev = 4 THEN 1
WHEN T.Lev = 4 AND T.LAG_VALUE = 0 THEN 1
ELSE 0
END AS L4
FROM TIMINGS AS T
LEFT OUTER JOIN MINIMUM_LEVELS AS ML
ON T.AccountID = ML.AccountID
AND T.Lev = ML.Lev
AND T.Datekey = ML.MIN_DATE
ORDER BY T.AccountID, T.RN;
Let me know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply