consecutive rows sql server 2008

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)

consecutive rows sql server 2008

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-08-19 : 02:43:48

Hi,

I need to find idno that have policies both with collector 1 followed by collector 2. I need to find the first occurence of each.

If a certain IDNO has 2 occurrences of 1,2,1,2 i need to return only the first 1,2. If idno has only 1 or 2 then it's not relevant.

I would appreciate the help please. Thanks :-)

CREATE TABLE #Payments(
	[ID] [int] IDENTITY NOT NULL,
	[Policy] [int] NOT NULL,
	[IdNo] [int] NOT NULL,
	[DateCreated] [datetime] NULL)--2009-12-30 00:00:00.000
	INSERT INTO #Payments VALUES(5667088,30043256,'2012-08-07') 
	 
	INSERT INTO #Payments VALUES (5634342,30043256,'2012-08-10') 
	 
	INSERT INTO #Payments VALUES (7854544,30043256,'2012-09-07') 
	 
	INSERT INTO #Payments VALUES (4345477,30043256,'2012-10-08') 
	 
	INSERT INTO #Payments VALUES (651754,27569106,'2012-09-07') 
	 
	INSERT INTO #Payments VALUES (651723,27569106,'2012-09-08') 
	 
	INSERT INTO #Payments VALUES (181754,0234199,'2009-12-27') 
	 
	INSERT INTO #Payments VALUES (655349,0234199,'2009-12-30') 
	 
	INSERT INTO #Payments VALUES (181074,2905645,'2012-09-07') 
	 
	INSERT INTO #Payments VALUES (123978,2905645,'2012-09-08') 
CREATE TABLE #POLICIES(
	[Policy] [int] NOT NULL,
	[IdNo] [int] NOT NULL,
	[Collector] [int] NOT NULL)
	INSERT INTO #POLICIES VALUES (5667088,30043256,1) 
	 
	INSERT INTO #POLICIES VALUES (5634342,30043256,2) 
	 
	INSERT INTO #POLICIES VALUES (7854544,30043256,1) 
	 
	INSERT INTO #POLICIES VALUES (4345477,30043256,2) 
	 
	INSERT INTO #POLICIES VALUES (651754,27569106,1) 
	 
	INSERT INTO #POLICIES VALUES (651723,27569106,2) 
	 
	INSERT INTO #POLICIES VALUES (181754,0234199,1) 
	 
	INSERT INTO #POLICIES VALUES (655349,0234199,1) 
	 
	INSERT INTO #POLICIES VALUES (181074,2905645,2) 
	 
	INSERT INTO #POLICIES VALUES (123978,2905645,2)

output:

IDNO Policy COLLECTOR DateCreated
30043256 5667088 1 2012-08-07

30043256 5634342 2 2012-08-10

27569106 651754 1 2012-09-07

27569106 651723 2 2012-09-08

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-08-19 : 04:09:07

One of the methods is


select distinct t1.idno,t1.policy,t1.collector,t2.datecreated from
(
select t1.*,row_number() over (partition by t1.idno order by t1.idno) as sno from #policies as t1 inner join
(
select idno from #policies group by idno having min(collector)=1 and max(collector)=2
  ) as t2 on t1.idno=t2.idno
  ) as t1 inner join #payments as t2 on t1.idno=t2.idno and t1.policy=t2.policy
where t1.sno in (1,2)

Madhivanan

Failing to plan is Planning to fail

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-08-19 : 05:52:09

Thanks. I need to check that collector=1 DateCreated is before the DateCreated value where collector=2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-19 : 05:57:42

[code]
select IDNo,Policy,Collector,DateCreated
from
(
select p.IDNo,p.Policy,p.Collector,pm.DateCreated,
ROW_NUMBER() OVER (PARTITION BY IdNo,Collector ORDER BY Policy) AS Seq,
SUM(CASE WHEN p.Collector= 1 THEN 1 ELSE ) END) OVER (PARTITION BY IdNo) AS [1Cnt],
SUM(CASE WHEN p.Collector= 2 THEN 1 ELSE ) END) OVER (PARTITION BY IdNo) AS [2Cnt]
from #POLICIES p
INNER JOIN #Payments pm
ON pm.Policy = p.Policy
)t
WHERE [1Cnt] > 0
AND [2Cnt] > 0
AND Seq = 1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-08-19 : 06:05:29

quote:
Originally posted by visakh16

select IDNo,Policy,Collector,DateCreated
from
(
select p.IDNo,p.Policy,p.Collector,pm.DateCreated,
ROW_NUMBER() OVER (PARTITION BY IdNo,Collector ORDER BY Policy) AS Seq,
SUM(CASE WHEN p.Collector= 1 THEN 1 ELSE 0 END) OVER (PARTITION BY IdNo) AS [1Cnt],
SUM(CASE WHEN p.Collector= 2 THEN 1 ELSE 0 END) OVER (PARTITION BY IdNo) AS [2Cnt]
from #POLICIES p
INNER JOIN #Payments pm
ON pm.Policy = p.Policy
)t
WHERE [1Cnt] > 0
AND [2Cnt] > 0
AND Seq = 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-19 : 06:43:06

quote:
Originally posted by collie

Thanks. I need to check that collector=1 DateCreated is before the DateCreated value where collector=2

what about cases where you've multiple 1 and 2 records? you want to check if all cases this is satisfied? or you just need to consider the earliest 1 and 2 records?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-08-19 : 07:44:36

Thanks guys. Just the earliest 1 and 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-19 : 07:49:56

[code]
select IDNo,Policy,Collector,DateCreated
from
(
select p.IDNo,p.Policy,p.Collector,pm.DateCreated,
ROW_NUMBER() OVER (PARTITION BY IdNo,Collector ORDER BY Policy) AS Seq,
MIN(CASE WHEN p.Collector= 1 THEN DateCreated END) OVER (PARTITION BY IdNo) AS [1Date],
MIN(CASE WHEN p.Collector= 2 THEN DateCreated END) OVER (PARTITION BY IdNo) AS [2Date]
from #POLICIES p
INNER JOIN #Payments pm
ON pm.Policy = p.Policy
)t
WHERE Seq = 1
AND [1Date] < [2Date]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources