Value carry forword - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Value carry forword

Author

Topic

best_boy26
Starting Member

42 Posts

Posted - 2011-03-08 : 00:44:50

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             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

Sachin.Nand

2937 Posts

Posted - 2011-03-08 : 05:02:45

The below query takes into consideration that O3_SnNO always will be in serial number.If that is not the case then
you can create a serial number using row_number() and replace O3_SnNO with the row_number column.


declare @t table(O3_SnNO int ,Col varchar(10))
insert @t
select 1,'TRUE' union  
select 2,'TRUE'  union       
select 3,'FALSE' union  
select 4,'FALSE' union  
select 5,'TRUE'	union   
select 6,'TRUE' union  
select 7,'TRUE' 

select O3_SnNO,T1.Col,nullif(t1.col,isnull(t.Col,' '))NewCol from @t t1
outer apply(select top 1 col from @t t2 where t1.Col=t2.Col and t1.O3_SnNO=t2.O3_SnNO+1 order by O3_SnNO desc)T

PBUH

best_boy26
Starting Member

42 Posts

Posted - 2011-03-08 : 05:30:51

Looks liek this is not a dynamic... I want as a dynamic for entire table... it will be deficult to give every time a numbers like.. 1, 2,3,4,5,6,7.. etc..

best_boy26
Starting Member

42 Posts

Posted - 2011-03-08 : 05:30:51

Looks liek this is not a dynamic... I want as a dynamic for entire table... it will be deficult to give every time a numbers like.. 1, 2,3,4,5,6,7.. etc..

Sachin.Nand

2937 Posts

Posted - 2011-03-08 : 06:15:40

Try this


;with cte
as
(
	SELECT *,row_number()over(order by (select 1))id (
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]
)


)

select id,T1.ComparisonColumn,nullif(t1.ComparisonColumn,isnull(t.Col,' '))NewCol from cte t1
outer apply(select top 1 ComparisonColumn from cte t2 where t1.ComparisonColumn=t2.ComparisonColumn 
and t1.id=t2.id+1 order by id desc)T

PBUH

best_boy26
Starting Member

42 Posts

Posted - 2011-03-08 : 14:21:10

Sachin Iam Getting error when i ran the sql query...

[url]Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ')'.[/url]

best_boy26
Starting Member

42 Posts

Posted - 2011-03-08 : 14:53:51

Thanks Sachin .. I got the results...

Once again thank you for the support and the blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources