Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 find rows in sql
 Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

collie
Constraint Violating Yak Guru

400 Posts

Posted - 07/11/2013 :  02:34:18  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 07/11/2013 :  02:39:48  Show Profile  Reply with Quote

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


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

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/11/2013 :  02:41:57  Show Profile  Reply with Quote
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
Go to Top of Page

collie
Constraint Violating Yak Guru

400 Posts

Posted - 07/11/2013 :  03:33:19  Show Profile  Reply with Quote
Thanks :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/11/2013 :  03:52:50  Show Profile  Reply with Quote
welcome

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

collie
Constraint Violating Yak Guru

400 Posts

Posted - 07/21/2013 :  11:55:33  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 07/21/2013 :  13:14:06  Show Profile  Reply with Quote
Is this what you want:

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';
Go to Top of Page

collie
Constraint Violating Yak Guru

400 Posts

Posted - 07/22/2013 :  01:06:16  Show Profile  Reply with Quote
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

Go to Top of Page

collie
Constraint Violating Yak Guru

400 Posts

Posted - 07/22/2013 :  01:37:39  Show Profile  Reply with Quote
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]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/22/2013 :  01:40:36  Show Profile  Reply with Quote
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
Go to Top of Page

collie
Constraint Violating Yak Guru

400 Posts

Posted - 07/22/2013 :  02:09:57  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/22/2013 :  02:41:20  Show Profile  Reply with Quote
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
Go to Top of Page

collie
Constraint Violating Yak Guru

400 Posts

Posted - 07/22/2013 :  03:49:30  Show Profile  Reply with Quote

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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/22/2013 :  03:55:22  Show Profile  Reply with Quote

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


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

collie
Constraint Violating Yak Guru

400 Posts

Posted - 07/23/2013 :  08:42:59  Show Profile  Reply with Quote
Hi,

How about a CTE and partitioning?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/24/2013 :  01:18:51  Show Profile  Reply with Quote
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
Go to Top of Page

collie
Constraint Violating Yak Guru

400 Posts

Posted - 07/29/2013 :  04:40:13  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/29/2013 :  05:02:34  Show Profile  Reply with Quote
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
Go to Top of Page

collie
Constraint Violating Yak Guru

400 Posts

Posted - 07/29/2013 :  06:00:21  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/29/2013 :  06:20:30  Show Profile  Reply with Quote

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


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

collie
Constraint Violating Yak Guru

400 Posts

Posted - 07/29/2013 :  15:47:28  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 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.12 seconds. Powered By: Snitz Forums 2000