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 2012 Forums
 Transact-SQL (2012)
 find rows in sql
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

collie
Constraint Violating Yak Guru

399 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
52325 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
52325 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

399 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
52325 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

399 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

547 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

399 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

399 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
52325 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

399 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
52325 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

399 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
52325 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

399 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
52325 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

399 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
52325 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

399 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
52325 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

399 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
 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.3 seconds. Powered By: Snitz Forums 2000