SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 consecutive rows sql server 2008
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

collie
Constraint Violating Yak Guru

399 Posts

Posted - 08/19/2013 :  02:43:48  Show Profile  Reply with Quote
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

India
22755 Posts

Posted - 08/19/2013 :  04:09:07  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

collie
Constraint Violating Yak Guru

399 Posts

Posted - 08/19/2013 :  05:52:09  Show Profile  Reply with Quote
Thanks. I need to check that collector=1 DateCreated is before the DateCreated value where collector=2

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/19/2013 :  05:57:42  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/19/2013 :  06:05:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 08/19/2013 06:05:52
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/19/2013 :  06:43:06  Show Profile  Reply with Quote
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
Go to Top of Page

collie
Constraint Violating Yak Guru

399 Posts

Posted - 08/19/2013 :  07:44:36  Show Profile  Reply with Quote
Thanks guys. Just the earliest 1 and 2
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/19/2013 :  07:49:56  Show Profile  Reply with Quote

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]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000