TRUE & False case Carry - 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)

TRUE & False case Carry

Author

Topic

best_boy26
Starting Member

42 Posts

Posted - 2011-03-22 : 16:56:53

Hi Experts,

I have a Table which contain the data of TRUE & False, I am looking for the Value carry forward i.e., instead of multiple TRUE and FALSE I want the first Case (If it is TRUE ) it should give me the continuos values in other column as NULL till it will get the False Value.

Below is my Table created query

USE [abc]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MY_Table](
        [i_serial] [int] NULL,
        [i_State] [bit] NULL,
        [i_State2] [bit] NULL
) ON [PRIMARY]

GO

and the DDL Data I have in this Table is


insert INTO dbo.My_Table

SELECT N'1' AS [i_serial], N'True' AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'2' AS [i_serial], NULL AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'3' AS [i_serial], NULL AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'4' AS [i_serial], N'True' AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'5' AS [i_serial], N'True' AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'6' AS [i_serial], NULL AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'7' AS [i_serial], NULL AS [i_State], N'False' AS [i_State2] UNION ALL
SELECT N'8' AS [i_serial], NULL AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'9' AS [i_serial], N'True' AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'10' AS [i_serial], NULL AS [i_State], N'False' AS [i_State2] UNION ALL
SELECT N'11' AS [i_serial], NULL AS [i_State], N'' AS [i_State2] UNION ALL
SELECT N'12' AS [i_serial], NULL AS [i_State], N'False' AS [i_State2] UNION ALL
SELECT N'13' AS [i_serial], NULL AS [i_State], N'False' AS [i_State2] UNION ALL
SELECT N'14' AS [i_serial], NULL AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'15' AS [i_serial], N'True' AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'17' AS [i_serial], N'True' AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'18'AS [i_serial], N'True' AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'19' AS [i_serial], N'True' AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'20' AS [i_serial], N'True' AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'21' AS [i_serial], N'True' AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'22' AS [i_serial], NULL AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'23' AS [i_serial], NULL AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'24' AS [i_serial], NULL AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'25' AS [i_serial], NULL AS [i_State], NULL AS [i_State2] UNION ALL
SELECT N'26' AS [i_serial], NULL AS [i_State], N'False' AS [i_State2]

and when i ran the select command

select * from dbo.My_Table

below is the output of the sample table...



i_serial      i_State	i_State2
1		TRUE	NULL
2		NULL	NULL
3		NULL	NULL
4		TRUE	NULL
5		TRUE	NULL
6		NULL	NULL
7		NULL	FALSE
8		NULL	NULL
9		TRUE	NULL
10		NULL	FALSE
11		NULL	NULL
12		NULL	FALSE
13		NULL	FALSE
14		NULL	NULL
15		TRUE	NULL
16		TRUE	NULL
17		TRUE	NULL
18		TRUE	NULL
19		TRUE	NULL
20		TRUE	NULL
21		NULL	NULL
22		NULL	NULL
23		NULL	NULL
24		NULL	NULL
25		NULL	FALSE

and Now Quetion is here

How would i get below output format...



i_serial     i_State  i_State2	I_F_S1	I_F_S2
1		TRUE	NULL	TRUE	NULL
2		NULL	NULL	NULL	NULL
3		NULL	NULL	NULL	NULL
4		TRUE	NULL	NULL	NULL
5		TRUE	NULL	NULL	NULL
6		NULL	NULL	NULL	NULL
7		NULL	FALSE	NULL	FALSE
8		NULL	NULL	NULL	NULL
9		TRUE	NULL	TRUE	NULL
10		NULL	FALSE	NULL	FALSE
11		NULL	NULL	NULL	NULL
12		NULL	FALSE	NULL	NULL
13		NULL	FALSE	NULL	NULL
14		NULL	NULL	NULL	NULL
15		TRUE	NULL	TRUE	NULL
16		TRUE	NULL	NULL	NULL
17		TRUE	NULL	NULL	NULL
18		TRUE	NULL	NULL	NULL
19		TRUE	NULL	NULL	NULL
20		TRUE	NULL	NULL	NULL
21		NULL	NULL	NULL	NULL
22		NULL	NULL	NULL	NULL
23		NULL	NULL	NULL	NULL
24		NULL	NULL	NULL	NULL
25		NULL	FALSE	NULL	FALSE

Just to explain more about on the table what iam looking is in the table when i get in i_state as True it is should ignore all comming trues and NULL's as NULL Value till it get the next FALSE in i_State2, Once it got the False I may get some more False and these should be ignore till i get the value in i_state as TRUE.

Thanks in advance,
JJ

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-22 : 17:14:55

Why do you want to do this?

best_boy26
Starting Member

42 Posts

Posted - 2011-03-22 : 23:51:38

There are lot of test values are comming very similar to this sample table and i want to read the first True as true and ignore the remaining values till i get NEXT False

best_boy26
Starting Member

42 Posts

Posted - 2011-03-23 : 00:34:38

Just to explain the output format...

The first True considered as TRUE Since the as per the below explanation there is nothing to check above rows values for TRUE or False. Next it should wait for the FALSE value to come in i_State2 and then it should end as false in the column I_F_S2, If it is First TRUE then it should in I_F_S1 and if it is FALSE then it should end as I_F_S2. So All I am looking is automatically two columns should be created with Values of first TRUE and FALSE (I_F_S1 and I_F_S2).

It is like a case statement that always should check the row values of above with current value for TRUE or False...

I would like to explain the complete expected output table ....

In the i_serial 1 I_state has TRUE and the above of the rows there is no True Value found and also this is the first value so it is ending as TRUE in I_F_S1 column.
in the i_State for I_serial 4 and 5 has again the value of TRUE but when we compare with last carry forward value is TRUE (No FALSE’s we recorded in I_F_S2 )and the coming all True should be ignore till we get the FALSE Value in i_state2. We can see in the i_Serial 7 has False now it should display the value to i_F_S2 as FALSE and now we can see in the 10th row the value is reverse that is to TRUE and this is the first Value in TRUE after False Hence the value ini_F_S2 end as False .Now if we can see 12th and 13 row has i_state2 as False however it can be treated as ignore since we have recorded the first False value and should waited for TRUE Value and in the 15th row we got the TRUE Value and it has recorded the i_F_S1 as TRUE and ignored the TRUE Values of 16th,17th,18th 19th and 20th and finally False Value is found in i_State2 after a TRUE value in I_F_S1

best_boy26
Starting Member

42 Posts

Posted - 2011-03-23 : 00:54:26

really looking for the help from forum...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-23 : 10:33:11

[code]
; with
state as
(
select i_serial, State = coalesce(i_State, i_State2), row_no = row_number() over (order by i_serial)
from My_Table
where i_State is not null
or i_State2 is not null
),
I_F_State as
(
select s1.i_serial,
I_F_State = case when s1.State <> s2.State or s2.State is null then s1.State end
from state s1
left join state s2 on s1.row_no = s2.row_no + 1
)
select t.i_serial, t.i_State, t.i_State2,
I_F_S1 = case when i.I_F_State = 1 then I_F_State end,
I_F_S2 = case when i.I_F_State = 0 then I_F_State end
from My_Table t
left join I_F_State i on t.i_serial = i.i_serial
[/code]

KH
[spoiler]Time is always against us[/spoiler]

best_boy26
Starting Member

42 Posts

Posted - 2011-03-23 : 12:00:30

KH,

Thanks Once again it worked for me