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
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/29/2013 :  19:02:54  Show Profile  Reply with Quote
Have you tried DISTICT as shown in red:
quote:
Originally posted by visakh16

do you mean this then?

SELECT DISTINCT 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

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

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

SELECT PackageNo,[IdNo] ,[StartDate],[EndDate],[Identifier]
FROM
(
SELECT PackageNo,[IdNo] ,[StartDate],[EndDate],[Identifier],
SUM(CASE WHEN DATEDIFF(dd,PrevDate,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,
p1.PrevDate
FROM Packages p
CROSS APPLY (SELECT MAX(EndDate) AS PrevDate
             FROM Packages
             WHERE IdNo = p.IdNo
             AND EndDate < p.StartDate
            )p1
)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

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/30/2013 :  02:33:28  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

Have you tried DISTICT as shown in red:
quote:
Originally posted by visakh16

do you mean this then?

SELECT DISTINCT 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






it will still return IdNo 23456 which OP doesnt want


------------------------------------------------------------------------------------------------------
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/01/2013 :  02:56:47  Show Profile  Reply with Quote
Thanks visakh16 and MuMu88
:)

visakh16 that was the solution. MuMu88 distinct doesn't help me in my case.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/01/2013 :  03:11:08  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous 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.09 seconds. Powered By: Snitz Forums 2000