Comparing records in same table based on date

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)

Comparing records in same table based on date

Author

Topic

mpaleo
Starting Member

3 Posts

Posted - 2012-05-10 : 14:09:46

I'm trying to find a non-cursor answer to a question I've solved using a cursor. I need a list of records where participants have multiple records with consecutive rundates.

participant_id nchar(10)
participant_name nvarchar(50)
rundate datetime
participant_addr nvarchar(100)

1, PartName1, 1/1/2012, PartAddr1
1, PartName1, 1/2/2012, PartAddr1
1, PartName1, 4/1/2012, PartAddr1
2, PartName2, 1/1/2012, PartAddr2
3, PartName3, 1/1/2012, PartAddr3
4, PartName4, 2/1/2012, PartAddr4
4, PartName4, 3/1/2012, PartAddr4
4, PartName4, 3/2/2012, PartAddr4
5, PartName5, 1/1/2012, PartAddr5

The results should be:

1, PartName1, 1/1/2012, PartAddr1
1, PartName1, 1/2/2012, PartAddr1
4, PartName4, 3/1/2012, PartAddr4
4, PartName4, 3/2/2012, PartAddr4

I have used a cursor to store the previous participant_id and rundate and compared the next record using participant_id and dateadd(dd,1,rundate). It doesn't give me both records, but I can deal with that. If anyone has a non-cursor select suggestion, I'd appreciate it.

(Moved from Script Library)

RL
Starting Member

15 Posts

Posted - 2012-05-10 : 15:34:20

Here's a fairly easy way to do it:


SELECT *
FROM participants P1
WHERE EXISTS (SELECT 1 FROM participants
               WHERE participant_id = P1.participant_id
                 AND rundate = P1.rundate + 1)
UNION
SELECT *
FROM participants P1
WHERE EXISTS (SELECT 1 FROM participants
               WHERE participant_id = P1.participant_id
                 AND rundate = P1.rundate - 1)

The first SELECT lists all except the final row of a consecutive series, and the second SELECT lists all except the first row of the series. UNION automatically eliminates the duplicates (UNION ALL would include them).

NOTE: I added a third consecutive row for PartName1 to make sure this worked.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 15:56:53

[code]
SELECT t.*
FROM table t
CROSS APPLY (SELECT COUNT(*) AS Cnt
FROM table
WHERE participant_id = P1.participant_id
AND ABS(DATEDIFF(dd,rundate,t.rundate)) = 1
)t1
WHERE Cnt > 0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mpaleo
Starting Member

3 Posts

Posted - 2012-05-10 : 16:53:22

Thank you both. Very neat and clean. I used the first (RL's) successfully.

I was able to get rows where the dates were equal (another requirement) using a temp table holding participant_ids with count(*) > 1. I tried modifying the above to do the same thing, but just removing the + and -1 wasn't successful. Is this method unsuitable or am I just boneheaded?

I tip my hat to you both.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 20:02:14

quote:
Originally posted by mpaleo

Thank you both. Very neat and clean. I used the first (RL's) successfully.

I was able to get rows where the dates were equal (another requirement) using a temp table holding participant_ids with count(*) > 1. I tried modifying the above to do the same thing, but just removing the + and -1 wasn't successful. Is this method unsuitable or am I just boneheaded?

I tip my hat to you both.

Sorry your other requirement is not clear
Are you trying to return distinct list from this resultset removing duplicates?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mpaleo
Starting Member

3 Posts

Posted - 2012-05-11 : 08:30:08

No, Visakh. I am trying to identify participants with multiple rundates. I used a group by to get participants with count > 1 and put that result in a temp table. I then used the temp table to go against the original table to pull those participant id/rundate matches.

select participant_id,rundate into temptable1
from table
group by participant_id,rundate
having COUNT(*) > 1

select participant_id,rundate
from table tx, temptable1 tt
where tx.participant_id = tt.participant_id
and tx.rundate = tt.rundate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-11 : 15:42:58

quote:
Originally posted by mpaleo

No, Visakh. I am trying to identify participants with multiple rundates. I used a group by to get participants with count > 1 and put that result in a temp table. I then used the temp table to go against the original table to pull those participant id/rundate matches.

select participant_id,rundate into temptable1
from table
group by participant_id,rundate
having COUNT(*) > 1

select participant_id,rundate
from table tx, temptable1 tt
where tx.participant_id = tt.participant_id
and tx.rundate = tt.rundate

for that isnt this enough?


select participant_id,rundate
from table 
group by  participant_id,rundate
having COUNT(*) > 1

if you want to return other details also use


SELECT t.*
FROM table t
INNER JOIN
(select participant_id,rundate
from table 
group by  participant_id,rundate
having COUNT(*) > 1
)t1
ON t1.participant_id = t.participant_id
AND t1.rundate = t.rundate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-12 : 11:21:53

[code]one more solution

Solution1:

SELECT P1.participant_id,P1.participant_name,P1.rundate,P1.PARTICIPANT_ADDR
FROM PARTICIPANT P1 INNER JOIN PARTICIPANT P2 ON
P1.participant_name = P2.participant_name AND P1.rundate!=P2.rundate AND MONTH(P1.RUNDATE)=
MONTH(P2.RUNDATE)

Solution2:

SELECT P1.participant_id,P1.participant_name,P1.rundate,P1.PARTICIPANT_ADDR
FROM PARTICIPANT P1 cross apply (select * from PARTICIPANT p2 where
P1.participant_name = P2.participant_name AND
P1.rundate!=P2.rundate AND MONTH(P1.RUNDATE)=MONTH(P2.RUNDATE))tab

Solution3:

SELECT * FROM PARTICIPANT P1 WHERE EXISTS ( SELECT * FROM PARTICIPANT P2
WHERE P1.participant_name = P2.participant_name
AND P1.participant_addr =P2.participant_addr
AND P1.rundate!=P2.rundate AND MONTH(P1.RUNDATE)=MONTH(P2.RUNDATE))

[/code]

Vijay is here to learn something from you guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-12 : 19:57:46

quote:

Originally posted by vijays3

one more solution 

Solution1:

      SELECT P1.participant_id,P1.participant_name,P1.rundate,P1.PARTICIPANT_ADDR 
      FROM PARTICIPANT P1 INNER JOIN PARTICIPANT P2 ON 
      P1.participant_name = P2.participant_name AND P1.rundate!=P2.rundate AND MONTH(P1.RUNDATE)=
      MONTH(P2.RUNDATE)  


Solution2:

      SELECT P1.participant_id,P1.participant_name,P1.rundate,P1.PARTICIPANT_ADDR 
      FROM PARTICIPANT P1 cross apply (select * from  PARTICIPANT p2 where 
      P1.participant_name = P2.participant_name AND 
      P1.rundate!=P2.rundate AND MONTH(P1.RUNDATE)=MONTH(P2.RUNDATE))tab


Solution3:

      SELECT * FROM PARTICIPANT P1 WHERE EXISTS ( SELECT * FROM PARTICIPANT P2
                                                WHERE P1.participant_name = P2.participant_name
                                                    AND P1.participant_addr =P2.participant_addr
                                                    AND P1.rundate!=P2.rundate AND MONTH(P1.RUNDATE)=MONTH(P2.RUNDATE))

Vijay is here to learn something from you guys.

This will cause hidden RBAR issue

see
http://www.sqlservercentral.com/articles/T-SQL/61539/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources