March 22, 2011 at 3:02 pm
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 DDM
USE [abc]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[My_Table](
[i_serial] [nchar](10) NULL,
[i_State] [varchar](50) NULL,
[i_State2] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
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_Statei_State2
1TRUENULL
2NULLNULL
3NULLNULL
4TRUENULL
5TRUENULL
6NULLNULL
7NULLFALSE
8NULLNULL
9TRUENULL
10NULLFALSE
11NULLNULL
12NULLFALSE
13NULLFALSE
14NULLNULL
15TRUENULL
16TRUENULL
17TRUENULL
18TRUENULL
19TRUENULL
20TRUENULL
21NULLNULL
22NULLNULL
23NULLNULL
24NULLNULL
25NULLFALSE
and Now Quetion is here
How would i get below output format...
i_serial i_State i_State2I_F_S1I_F_S2
1TRUENULLTRUENULL
2NULLNULLNULLNULL
3NULLNULLNULLNULL
4TRUENULLNULLNULL
5TRUENULLNULLNULL
6NULLNULLNULLNULL
7NULLFALSENULLFALSE
8NULLNULLNULLNULL
9TRUENULLTRUENULL
10NULLFALSENULLFALSE
11NULLNULLNULLNULL
12NULLFALSENULLNULL
13NULLFALSENULLNULL
14NULLNULLNULLNULL
15TRUENULLTRUENULL
16TRUENULLNULLNULL
17TRUENULLNULLNULL
18TRUENULLNULLNULL
19TRUENULLNULLNULL
20TRUENULLNULLNULL
21NULLNULLNULLNULL
22NULLNULLNULLNULL
23NULLNULLNULLNULL
24NULLNULLNULLNULL
25NULLFALSENULLFALSE
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...
trying to learn SQL Query World
March 22, 2011 at 3:29 pm
I don't understand what you are trying to do, can you maybe take one or two of the rows of the results you want and walk us through how you are calculating the two new fields?
Like what causes you to put TRUE in I_F_S1 for number 15, but not number 16?
March 22, 2011 at 3:30 pm
Man I have to say that you come up with the most bizarre requirements ever. You really should consider using proper datatypes to hold your data. You are using nchar(10) to hold numbers (instead of int), varchar(50) to hold string values 'True' and 'False' (instead of bit).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 22, 2011 at 3:33 pm
Sean Lange (3/22/2011)
Man I have to say that you come up with the most bizarre requirements ever. You really should consider using proper datatypes to hold your data. You are using nchar(10) to hold numbers (instead of int), varchar(50) to hold string values 'True' and 'False' (instead of bit).
I have to add more to my running commentary. it looks like you are using the column i_State as True and i_State2 as False? This looks like a two column table. Serial int, [State] bit. :crazy:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 22, 2011 at 4:27 pm
To continue on with what Sean has suggested, I would convert your table like this:
DECLARE @New_Table TABLE (
i_serial INT PRIMARY KEY CLUSTERED,
i_TrueState BIT);
WITH cte AS
(
SELECT i_serial,
TrueState = COALESCE(i_state, i_State2)
FROM dbo.My_Table
)
INSERT INTO @New_Table
SELECT * FROM cte;
SELECT * FROM @New_Table;
I would then update the empty i_TrueState columns like this:
-- declare and initialize variables needed for the update statement.
DECLARE @State BIT,
@Sequence INTEGER,
@i_serial INTEGER;
SET @Sequence = 0;
SET @State = 0;
/*
This form of the UPDATE statement has some rules for proper usage.
See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/
for a complete discussion of how this works, and all of the rules for utilizing it.
>>>>>> If you don't follow ALL the rules, you WILL mess up your data!!! <<<<<<
Also, see http://www.sqlservercentral.com/Forums/FindPost980118.aspx
and http://www.sqlservercentral.com/Forums/FindPost981258.aspx for the logic
behind the safety check process.
*/
WITH SafeTable AS
(
-- generate table with a sequence column in clustered index order
-- in order to verify that update is happening in the correct order
SELECT i_serial,
i_TrueState,
Sequence = ROW_NUMBER() OVER (ORDER BY i_serial)
FROM @New_Table
)
UPDATE t
-- Verify in proper sequence order; if not, throw an error so nothing is updated
-- Safety check on variable being carried over to the next row is to prevent
-- rows from being updated in the wrong order.
SET @State = i_TrueState = CASE WHEN Sequence = @Sequence + 1 THEN
CASE WHEN i_TrueState IS NULL THEN @State
ELSE i_TrueState END
ELSE 1/0 END, -- not in proper sequence order; throw an error
@Sequence = @Sequence + 1, -- update safety check column
@i_serial = i_serial -- anchor column (first column in clustered index)
FROM SafeTable t WITH (TABLOCKX) -- lock table to prevent changes by others
OPTION (MAXDOP 1); -- prevent parallelism!
Note that for table variables, the TABLOCKX and MAXDOP options aren't necessary - but it doesn't hurt to have them, and if this is a temp or permanent table then they are needed.
Edit: Note that if the first row were to somehow have a null i_TrueState, it would be defaulted to false.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 22, 2011 at 10:17 pm
Hi UMG Developer,
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
Thanks in Advance...
trying to learn SQL Query World
March 22, 2011 at 10:24 pm
Hi Sean Lange,
Thanks for pointing me to the right data types use's I have chnaged my data types as suggested
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
Thanks in Advance...
trying to learn SQL Query World
March 22, 2011 at 10:33 pm
HI WayneS ,
you gave me to convert the complete table... My apologies that i have re-created as suggested by Sean Lange.
Please help me to get the query to get the final out.
Thanks in advance,
JJ
Thanks in Advance...
trying to learn SQL Query World
March 22, 2011 at 10:48 pm
Celko, Sorry for my English wrong!!! I may be poor this time to explain.
Sean Lange corrected me that not to use VARCHAR(50) and use bit Now my all data is in BIT
Thanks again
Thanks in Advance...
trying to learn SQL Query World
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply