Value carry forword

I have below SQL Query..

SELECT     dbo.O3.[s.no] AS [O3_Sn.NO], dbo.O5.[s.no] AS [O5_Sn.NO], dbo.O3.O3, dbo.O5.O5,
case when dbo.O3.[s.no] > dbo.O5.[s.no] then 'true' else 'false' end as ComparisonColumn
FROM         dbo.O3 INNER JOIN
                      dbo.O5 ON dbo.O3.[s.no] = dbo.O5.[s.no]

When I run I am getting below output..

O3_Sn.NO    O5_Sn.NO    O3  O5  ComparisonColumn
1          1           10   11  TRUE
2          2           12   13  TRUE
3          3            11  10  FALSE
4          4            13  11  FALSE
5          5            15  16  TRUE
6          6            10  11  TRUE
7          7           12   13  TRUE

I want to remember the value of TRUE / False and should ignore if it is repeated untill i get a reverse case i.e., for TRUE , False.. and FOR False .. TRUE

Below is the out i should get it..

O3_Sn.NO O5_Sn.NO O3  O5    ComparisonColumn New_Case_Carry_value
1        1      10    11    TRUE             TRUE
2        2      12    13    TRUE 开发者_JAVA技巧            NULL
3        3      11    10    FALSE            FALSE
4        4      13    11    FALSE            NULL
5        5      15    16    TRUE             TRUE
6        6      10    11    TRUE             NULL
7        7      12    13    TRUE             NULL

You can order your output using row_number() to compare a value with a value of the previous record:

with cIntermediate as (
SELECT dbo.O3.[s.no] AS [O3_Sn.NO], dbo.O5.[s.no] AS [O5_Sn.NO], dbo.O3.O3, dbo.O5.O5, 
       case when dbo.O3.[s.no] > dbo.O5.[s.no] then 'true' else 'false' end 
          as ComparisonColumn,
       rowno = row_number() over 
          (order by dbo.O3.[s.no], dbo.O5.[s.no], dbo.O3.O3, dbo.O5.O5)
FROM   dbo.O3 INNER JOIN dbo.O5 ON dbo.O3.[s.no] = dbo.O5.[s.no] 
)
select i1.*, 
       case when i1.ComparisonColumn = i2.ComparisonColumn then null 
          else i1.ComparisonColumn end as NewCaseCarryValue
from cIntermediate i1
left join cIntermediate i2 on i2.rowno=i1.rowno-1

Ok, I will explain the concept to you, and I am sure you will be able to figure it out for yourself.

Your final result you published with all the true and false columns, that needs to become a temporary table, like this, but with some kind of identity column:

SELECT dbo.o3.[s.no]  AS [O3_Sn.NO]
       ,dbo.o5.[s.no] AS [O5_Sn.NO]
       ,dbo.o3.o3
       ,dbo.o5.o5
       ,CASE
          WHEN dbo.o3.[s.no] > dbo.o5.[s.no] THEN 'true'
          ELSE 'false'
        END           AS comparisoncolumn
       , ROW_NUMBER() OVER(ORDER BY dbo.o3.[s.no]) AS ident_col
INTO #temp
FROM   dbo.o3
       INNER JOIN dbo.o5
         ON dbo.o3.[s.no] = dbo.o5.[s.no]

Then what you need to do is to select from #temp, and self join to #temp in a similar manner as here:

SELECT a.*
      , CASE WHEN a.comparisoncolumn = b.comparisoncolumn THEN NULL ELSE a.comparisoncolumn END AS final_comparisoncolumn
FROM #temp a
    LEFT JOIN #temp b ON a.ident_col = b.ident_col - 1

Then do a case statement to figure out if you need to print null, or true or false.

Play around with this concept, I am sure you will be able to work it out from here.

You can do an left join on the table itself (SN = SN-1) and compare the ComparisonColumn