Ruhaan Ruhaan - 2 months ago 7
SQL Question

Need output for following with Comparison of Current row and previous row

I am using SQL Server 2008R2. I have following table with Profile Name and T1 as column names, I need to add two more columns t2 and t3 based on some conditions

Profile Name T1 T2 T3
----------------------------------
IP Singles 0
IP Singles 90
IP Singles 100
Disputes 180
IP Multis 145
Performance 378
IP Color 420
Disputes 170
IP Multis 104
Insurance 340
Insurance 120
Insurance 1335


Conditions for both T2 and T3 columns are as follows:

For T2:

When T1 < 900 And
Current row of Profile Name <> Previuos row of Profil Name then output of T2 will be same as T1 Else it will be 0.


For T3:

When T1 <= 900 And Current row of Profile Name = Previous row of Profile Name then output of T3 will be same as T1 Else it will be 0.


For example:


  • If T1 is greater than 900 AND current row of ProfileName is not equal to Previous row of ProfileName then value of T2 will be T1 Else 0.

  • Similarly, If T1 is greater than or equal to 900 AND current row of ProfileName is equal to Previous row of ProfileName then value of T3 will be T1 Else 0.



My expected output is below:

Profile Name T1 T2 T3
--------------------------------
IP Singles 0 0 0
IP Singles 90 0 90
IP Singles 100 0 100
Disputes 180 180 0
IP Multis 145 145 0
Performance 378 378 0
IP Color 420 420 0
Disputes 170 170 0
IP Multis 104 104 0
Insurance 340 340 0
Insurance 120 0 120
Insurance 1335 0 0


I hope I am clear with the question, Please help me to achieve that output with
SELECT
query.

Answer

This could be done with the help of ROW_NUMBER() and CTE:

;WITH cte AS (
SELECT  [Profile Name], 
        T1,
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as RN
FROM YourTable
)


SELECT  c1.[Profile Name],
        c1.T1,
        CASE WHEN c1.T1 < 900 AND c1.[Profile Name] != c2.[Profile Name] THEN c1.T1 ELSE 0 END as T2,
        CASE WHEN c1.T1 <= 900 AND c1.[Profile Name] = c2.[Profile Name] THEN c1.T1 ELSE 0 END as T3
FROM cte c1
LEFT JOIN cte c2
    ON c1.RN = c2.RN +1

Output:

Profile Name    T1      T2  T3
IP Singles      0       0   0
IP Singles      90      0   90
IP Singles      100     0   100
Disputes        180     180 0
IP Multis       145     145 0
Performance     378     378 0
IP Color        420     420 0
Disputes        170     170 0
IP Multis       104     104 0
Insurance       340     340 0
Insurance       120     0   120
Insurance       1335    0   0