Recursive Column Check and Fetch

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)

Recursive Column Check and Fetch

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2014-04-02 : 06:26:54

Hi,
I got a Table with some Columns. Need to check a Column where the Column is not Equal to 0 from Start. But it can be 0 in the Middle. Need to fetch the Data from Column not equal to 0 to end.

TableA:
[ID] [DATE] [VOL] [AMT]
1 Apr 2010 0 150
2 May 2010 0 250
3 Jun 2010 0 125
4 Jul 2010 100 175
5 Aug 2010 75 200
6 Sep 2010 0 250
7 Oct 2010 0 350
8 Nov 2010 25 400
9 Dec 2010 40 350

My Required Output will be
Output Table:
[ID] [DATE] [VOL] [AMT]
4 Jul 2010 100 175
5 Aug 2010 75 200
6 Sep 2010 0 250
7 Oct 2010 0 350
8 Nov 2010 25 400
9 Dec 2010 40 350

Here the Start Month Apr 2010 Vol Column is 0 and It ends upto Jun 2010. But some Month have Value 0 for Column Vol.
Need to remove those which starts and Ends with 0 and starts with a Value.

Regards,
Kalai

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-02 : 08:55:35

an ugly solution ( i hope it's a solution)


with TableA
AS
(
	select 1 [ID], 'Apr 2010'[DATE], 0 [VOL],150 [AMT] union all
	select 2,'May 2010', 0,250 union all
	select 3,'Jun 2010', 0,125 union all
	select 4,'Jul 2010', 100, 175 union all
	select 5, 'Aug 2010', 75, 200 union all
	select 6, 'Sep 2010', 0 , 250 union all
	select 7, 'Oct 2010', 0 , 350 union all
	select 8, 'Nov 2010', 25 ,400 union all
	select 9 ,'Dec 2010', 40 ,350 union all
	select 10 ,'Jan 2011', 0 ,350 union all
	select 11 ,'Feb 2011', 0 ,250 union all
	select 12 ,'Mar 2011', 0 ,100)


select * from TableA
where ID > =
(

select top 1 
   A.ID
from
(
select A.* 
	--,B.*
	,
	case when (A.Vol=B.Vol OR 	isnull(A.VOL,0)=0 )  then 0
	      else 1 end as zeroColumnn
from 
	TableA A
	full join TableA B
	ON A.ID+1 = B.ID
) A
where zeroColumnn<>0
order by A.ID)

AND
ID<=(
select top 1 
   A.ID
from
(
select A.* 
	--,B.*
	,
	case when (A.Vol=B.Vol OR 	isnull(A.VOL,0)=0 )  then 0
	      else 1 end as zeroColumnn
from 
	TableA A
	full join TableA B
	ON A.ID+1 = B.ID
) A
where zeroColumnn<>0
order by A.ID desc
)

output


ID	DATE	VOL	AMT
4	Jul 2010	100	175
5	Aug 2010	75	200
6	Sep 2010	0	250
7	Oct 2010	0	350
8	Nov 2010	25	400
9	Dec 2010	40	350

S

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-02 : 09:03:39

with CTE combination... the same solution


;with TableA
AS
(
	select 1 [ID], 'Apr 2010'[DATE], 0 [VOL],150 [AMT] union all
	select 2,'May 2010', 0,250 union all
	select 3,'Jun 2010', 0,125 union all
	select 4,'Jul 2010', 100, 175 union all
	select 5, 'Aug 2010', 75, 200 union all
	select 6, 'Sep 2010', 0 , 250 union all
	select 7, 'Oct 2010', 0 , 350 union all
	select 8, 'Nov 2010', 25 ,400 union all
	select 9 ,'Dec 2010', 40 ,350 union all
	select 10 ,'Jan 2011', 0 ,350 union all
	select 11 ,'Feb 2011', 0 ,250 union all
	select 12 ,'Mar 2011', 0 ,100)
,TableA1
AS
	(select top 1 
			A.ID
		from
			(select A.* 				
				,case when (A.Vol=B.Vol OR 	isnull(A.VOL,0)=0 )  then 0
					  else 1 end as zeroColumnn
			from 
				TableA A
				full join TableA B
				ON A.ID+1 = B.ID
			) A
			where zeroColumnn<>0
			order by A.ID)
,TableA2
AS
	(select top 1 
			A.ID
		from
		(	select A.* 
			,case when (A.Vol=B.Vol OR 	isnull(A.VOL,0)=0 )  then 0
				  else 1 end as zeroColumnn
		from 
			TableA A
			full join TableA B
			ON A.ID+1 = B.ID
		) A
		where zeroColumnn<>0
		order by A.ID desc)



select * from TableA
where 
	ID > =(select ID from TableA1)
	AND
	ID<=(select ID from TableA2)

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-02 : 11:23:02

a better approche (not tested yet)


with CTE combination... the same solution


;with TableA
AS
(
	select 1 [ID], 'Apr 2010'[DATE], 0 [VOL],150 [AMT] union all
	select 2,'May 2010', 0,250 union all
	select 3,'Jun 2010', 0,125 union all
	select 4,'Jul 2010', 100, 175 union all
	select 5, 'Aug 2010', 75, 200 union all
	select 6, 'Sep 2010', 0 , 250 union all
	select 7, 'Oct 2010', 0 , 350 union all
	select 8, 'Nov 2010', 25 ,400 union all
	select 9 ,'Dec 2010', 40 ,350 union all
	select 10 ,'Jan 2011', 0 ,350 union all
	select 11 ,'Feb 2011', 0 ,250 union all
	select 12 ,'Mar 2011', 0 ,100)
,TableZero
AS
(
  select ID,
	,case when isnull(A.VOL,0)=0 then 0
	      else 1 end as zeroColumnn

from 
	TableA)

,TableA1
AS
	(select MIN(ID) as ID
		from TablaZero
		Where A.zeroColumnn=1)
,TableA2
AS
	(select MAX(ID) as ID
		from TablaZero
		Where A.zeroColumnn=1)



select * from TableA
where 
	ID > =(select ID from TableA1)
	AND
	ID < =(select ID from TableA2)

sabinWeb MCP

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-02 : 17:46:23

KalaiSelvan, please see below...


DECLARE @Input TABLE
([ID] INT,
 [DATE] DATE,
 [VOL] INT,
 [AMT] INT
)

INSERT INTO @Input VALUES(1, '2010/04/01', 0, 150), (2, '2010/05/01', 0, 250), (3, '2010/06/01', 0, 125), (4, '2010/07/01', 100, 175)
,(5, '2010/08/01', 75, 200), (6, '2010/09/01', 0, 250), (7, '2010/10/01', 0, 350), (8, '2010/11/01', 25, 400), (9, '2010/12/01', 40, 350)

;WITH CTE AS
(
SELECT MIN(ID) AS Id
FROM @Input
WHERE Vol <> 0
)
SELECT *
FROM @Input
WHERE ID >= (SELECT Id FROM CTE)

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-03 : 01:00:41

[code]

;with TableA
AS
(
select 1 [ID], 'Apr 2010'[DATE], 0 [VOL],150 [AMT] union all
select 2,'May 2010', 0,250 union all
select 3,'Jun 2010', 0,125 union all
select 4,'Jul 2010', 100, 175 union all
select 5, 'Aug 2010', 75, 200 union all
select 6, 'Sep 2010', 0 , 250 union all
select 7, 'Oct 2010', 0 , 350 union all
select 8, 'Nov 2010', 25 ,400 union all
select 9 ,'Dec 2010', 40 ,350 union all
select 10 ,'Jan 2011', 0 ,350 union all
select 11 ,'Feb 2011', 0 ,250 union all
select 12 ,'Mar 2011', 0 ,100)
,TableMinMax
AS
(
SELECT min(ID) as minID
,max(ID) as maxID
FROM
TableA as A
WHERE A.VOL <>0 )

select * from TableA
where
ID > =(select minID from TableMinMax)
AND
ID < =(select maxID from TableMinMax)
[/code]

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources