find rows in sql - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

find rows in sql

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-07-11 : 02:34:18

CREATE TABLE [dbo].[Packages](
[PackageNo] [int] IDENTITY(1,1) NOT NULL,
[IdNo] [int] NOT NULL,

[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NULL)

INSERT INTO [Packages] (PackageNo,[IdNo] ,[StartDate] ,[EndDate] ) VALUES (687300 ,2 ,'2010-12-15' ,'2012-06-24')
INSERT INTO [Packages] (PackageNo,[IdNo] ,[StartDate] ,[EndDate] ) VALUES (730685 ,2011 ,'2005-01-01' ,'2010-04-29')
INSERT INTO [Packages] (PackageNo,[IdNo] ,[StartDate] ,[EndDate] ) VALUES (730686 ,2011 ,'2010-04-30' ,NULL)
INSERT INTO [Packages] (PackageNo,[IdNo] ,[StartDate] ,[EndDate] ) VALUES (730687 ,1556 ,'2005-01-01' ,'2012-04-23')
INSERT INTO [Packages] (PackageNo,[IdNo] ,[StartDate] ,[EndDate] ) VALUES (730688 ,1556 ,'2012-04-24' ,NULL)
INSERT INTO [Packages] (PackageNo,[IdNo] ,[StartDate] ,[EndDate] ) VALUES (730689 ,23456 ,'2005-01-01' ,'2010-12-14')
INSERT INTO [Packages] (PackageNo,[IdNo] ,[StartDate] ,[EndDate] ) VALUES (730690 ,23456 ,'2010-12-15' ,'2012-06-24')

I need to return rows with duplicate IdNo where one row contains startdate and enddate and the other row with same IdNo has a startdate
that is startdate+ 1 day of previous row and the second row has NULL value for enddate.

In the example above I will return PackageNo,[IdNo] ,[StartDate] ,[EndDate] for IdNos 2011,1556 only.

How will I achieve the desired output? The query must be efficient as I might need to reuse it.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-11 : 02:39:48

[code]
SELECT p1.PackageNo,p1.[IdNo] ,p1.[StartDate],p1.[EndDate]
FROM Packages p1
INNER JOIN Packages p2
ON p1.IdNo = p2.IdNo
AND p2.StartDate = p1.EndDate + 1
WHERE p2.EndDate IS NULL
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-11 : 02:41:57

if you want both the records to be returned you need this


SELECT p1.PackageNo,p1.[IdNo] ,p1.[StartDate],p1.[EndDate]
FROM Packages p1
INNER JOIN Packages p2
ON p1.IdNo = p2.IdNo
AND ABS(DATEDIFF(dd,p2.StartDate,p1.EndDate))=1
WHERE p2.EndDate IS NULL 
OR p1.EndDate IS NULL

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

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-07-11 : 03:33:19

Thanks :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-11 : 03:52:50

welcome

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

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-07-21 : 11:55:33

Hi,

I need to further identify in the results the row(idno+PackageNo) contains enddate=null and startdate=ABS(DATEDIFF(dd,p2.StartDate,p1.EndDate))=1 and the idno+PackageNo row that has startdate=2005-01-01. I will need to update different values for each row.

Thanks

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-21 : 13:14:06

Is this what you want:
[CODE]
SELECT p1.PackageNo,p1.[IdNo] ,p1.[StartDate],p1.[EndDate]
FROM Packages p1
INNER JOIN Packages p2
ON p1.IdNo = p2.IdNo
AND ABS(DATEDIFF(dd,p2.StartDate,p1.EndDate))=1
WHERE (p2.EndDate IS NULL
OR p1.EndDate IS NULL)
AND P1.[StartDate] = '2005-01-01';
[/CODE]

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-07-22 : 01:06:16

Thanks. The query that you gave me returns the exact results that I need. In addition I need to identify which row has enddate=null and which doesn't. Example say add a column called Identifier with Key1 where enddate in not null and startdate=01/01/2005

I need the result as follows:
PackageNo idno startdate enddate identifier
730685 2011 01/01/2005 04/29/2010 Key1
730686 2011 04/30/2010 NULL Key2
730687 1556 01/01/2005 04/23/2012 Key1
730688 1556 04/24/2012 NULL Key2

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-07-22 : 01:37:39

How is this?

 select  PackageNo,[IdNo] ,[StartDate],[EndDate],case when enddate is null then 'Key2' else 'Key1' end as identifier
 from #packages where idno in(
 SELECT p1.idno
FROM #Packages p1
INNER JOIN #Packages p2
ON p1.IdNo = p2.IdNo
AND ABS(DATEDIFF(dd,p2.StartDate,p1.EndDate))=1
WHERE (p2.EndDate IS NULL 
OR p1.EndDate IS NULL) 
AND P1.[StartDate] = '2005-01-01' 
 )order by  [IdNo]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 01:40:36

quote:
Originally posted by collie

Thanks. The query that you gave me returns the exact results that I need. In addition I need to identify which row has enddate=null and which doesn't. Example say add a column called Identifier with Key1 where enddate in not null and startdate=01/01/2005

I need the result as follows:
PackageNo idno startdate enddate identifier
730685 2011 01/01/2005 04/29/2010 Key1
730686 2011 04/30/2010 NULL Key2
730687 1556 01/01/2005 04/23/2012 Key1
730688 1556 04/24/2012 NULL Key2

do you mean this?


SELECT p1.PackageNo,p1.[IdNo] ,p1.[StartDate],p1.[EndDate],
CASE WHEN P1.[StartDate] = '2005-01-01' AND p1.EndDate IS NULL THEN 'Key2' ELSE 'Key1' END AS [Identifier]
FROM Packages p1
INNER JOIN Packages p2
ON p1.IdNo = p2.IdNo
AND ABS(DATEDIFF(dd,p2.StartDate,p1.EndDate))=1
WHERE (p2.EndDate IS NULL 
OR p1.EndDate IS NULL) 
AND P1.[StartDate] = '2005-01-01';

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

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-07-22 : 02:09:57

Hi,

Thanks. Yea, but I also need to identify the row where enddate is Null as I will need to update different fields in both rows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 02:41:20

quote:
Originally posted by collie

Hi,

Thanks. Yea, but I also need to identify the row where enddate is Null as I will need to update different fields in both rows.

All rows with identifier field valus as 'Key2' will have EnDDate value as NULL

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

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-07-22 : 03:49:30

Thanks, the case statement should be like this
CASE WHEN P1.[StartDate] = '2005-01-01' AND p1.EndDate IS not NULL THEN 'Key2' ELSE 'Key1' END AS [Identifier]

This is the output I get

PackageNo IdNo StartDate EndDate Identifier
730685 2011 2005-01-01 00:00:00.000 2010-04-29 00:00:00.000 Key2
730687 1556 2005-01-01 00:00:00.000 2012-04-23 00:00:00.000 Key2

I don't get Key1 and I need also Key1 as I will need to update both key1 and key2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 03:55:22

[code]
SELECT p1.PackageNo,p1.[IdNo] ,p1.[StartDate],p1.[EndDate],
CASE WHEN P1.[StartDate] = '2005-01-01' AND p1.EndDate IS NOT NULL THEN 'Key2' ELSE 'Key1' END AS [Identifier]
FROM Packages p1
INNER JOIN Packages p2
ON p1.IdNo = p2.IdNo
AND ABS(DATEDIFF(dd,p2.StartDate,p1.EndDate))=1
[/code]

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

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-07-23 : 08:42:59

Hi,

How about a CTE and partitioning?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-24 : 01:18:51

Sorry didnt get need of CTE here. The explanation sounded like a simple case expression to me

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

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-07-29 : 04:40:13

Hi,

This is the output i get
PackageNo IdNo StartDate EndDate Identifier
730685 2011 2005-01-01 2010-04-29 Key2
730687 1556 2005-01-01 2012-04-23 Key2

And i need the following output :
PackageNo IdNo StartDate EndDate Identifier
730687 1556 2005-01-01 2012-04-23 1
730688 1556 2012-04-24 NULL 2
730685 2011 2005-01-01 2010-04-29 1
730686 2011 2010-04-30 NULL 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-29 : 05:02:34

do you mean this then?


SELECT p1.PackageNo,p1.[IdNo] ,p1.[StartDate],p1.[EndDate],
CASE WHEN P1.[StartDate] = '2005-01-01' AND p1.EndDate IS NULL THEN 'Key2' ELSE 'Key1' END AS [Identifier]
FROM Packages p1
INNER JOIN Packages p2
ON p1.IdNo = p2.IdNo
AND ABS(DATEDIFF(dd,p2.StartDate,p1.EndDate))=1
OR(p2.EndDate IS NULL 
OR p1.EndDate IS NULL) 
WHERE P1.[StartDate] = '2005-01-01';

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

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-07-29 : 06:00:21

I get duplicate rows and i shouldn't get IdNo 23456.
PackageNo IdNo StartDate EndDate Identifier
730685 2011 2005-01-01 2010-04-29 Key2
730685 2011 2005-01-01 2010-04-29 Key2
730687 1556 2005-01-01 2012-04-23 Key2
730687 1556 2005-01-01 2012-04-23 Key2
730689 23456 2005-01-01 2010-12-14 Key2
730689 23456 2005-01-01 2010-12-14 Key2
730689 23456 2005-01-01 2010-12-14 Key2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-29 : 06:20:30

[code]
SELECT PackageNo,[IdNo] ,[StartDate],[EndDate],[Identifier]
FROM
(
SELECT PackageNo,[IdNo] ,[StartDate],[EndDate],[Identifier],
SUM(CASE WHEN DATEDIFF(dd,PrevEndDate,StartDate) <> 1 THEN 1 ELSE 0 END) OVER (PARTITION BY IdNo) AS DDiffCnt
FROM
(
SELECT PackageNo,[IdNo] ,[StartDate],[EndDate],
CASE WHEN [StartDate] = '2005-01-01' AND EndDate IS NULL THEN 'Key2' ELSE 'Key1' END AS [Identifier],
SUM(CASE WHEN [EndDate] IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY IdNo ) AS Cnt,
LAG(EndDate,1) OVER (PARTITION BY IdNo ORDER BY PackageNo) AS PrevEnd
FROM Packages
)t
WHERE Cnt=1
)r
WHERE DDiffCnt =0
[/code]

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

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-07-29 : 15:47:28

yep thanks however, i made a mistake. At home I have sql server 2012 and although at work when I click About it says 2012 it's not. So I can't run lag functions

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources