Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Compare Dates in an iterative manner for a particular set of records Expand / Collapse
Author
Message
Posted Wednesday, November 12, 2008 7:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 9:07 AM
Points: 195, Visits: 499
Here is a solution not using CROSS JOIN:

SELECT A.*,
CASE WHEN EXISTS (
SELECT 1
FROM (
SELECT B.*,
(SELECT TOP 1 C.DEDate
FROM PersonRecord C
WHERE C.PersonID = B.PersonID AND C.VERSION > B.VERSION
ORDER BY C.Version) AS [NextDEDate]
FROM PersonRecord B
) AS [BC]
WHERE BC.PersonID = A.PersonID AND BC.NextDEDate < DEDate
)
THEN 1 ELSE 0 END AS [Is ImProper]
FROM PersonRecord A



Post #601351
Posted Wednesday, November 12, 2008 7:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 4:05 AM
Points: 3, Visits: 114
Not sure if this is what you are trying to do but how about:

SELECT
pr1.PersonID,pr1.Version,pr1.DEDate,
CASE
WHEN (pr1.DEDate < pr2.DEDate)
THEN 1
ELSE
0
END AS Improper
FROM
PersonRecord pr1
LEFT JOIN PersonRecord pr2
ON
pr1.PersonID = pr2.PersonID
AND
pr1.Version = pr2.Version + 1

Post #601366
Posted Wednesday, November 12, 2008 7:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:06 AM
Points: 2,278, Visits: 3,052
Good article!

This type of query can be handled much easier in SQL 2005, but I would still try to avoid a correlated subquery in SQL 2000. You can move the query into a derived table and make it inline. Obviously, this is over simplifying the solution but you get the idea.

SELECT 
PersonRecord.*,
Improper.[IsImProper]
FROM PersonRecord
INNER JOIN(
SELECT
a.PersonID,
MAX(CASE
WHEN a.Version < b.version and A.DEDate > B.DEDate
THEN 1
ELSE 0
END) as IsImProper
FROM PersonRecord A
INNER JOIN PersonRecord B
ON A.PersonID = B.PersonID AND
A.Version = B.Version -1
GROUP BY A.PersonId
) AS Improper
ON Improper.[PersonID] = PersonRecord.PersonID





My blog: http://jahaines.blogspot.com
Post #601375
Posted Wednesday, November 12, 2008 7:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 19, 2014 2:29 AM
Points: 143, Visits: 547
Reply to :Why use the cross join?

Yes it could be done in a way you have shown but then the whole personid should be marked as improper if any DEDATE is found mismatched


--Divya
Post #601378
Posted Wednesday, November 12, 2008 7:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 19, 2014 2:29 AM
Points: 143, Visits: 547
select personid, version, dedate,
row_number() over (partition by personid order by version) as VersionOrder,
row_number() over (partition by personid order by dedate) as DeOrder,
case when row_number() over (partition by personid order by version) -
row_number() over (partition by personid order by dedate) <> 0 then '1' else '0' end as IsImproper
from personrecord
order by personid, version

Yes its good but this was an option if you are using SQL Server 2005. My problem was to be solved in SQL Server 2000


--Divya
Post #601380
Posted Wednesday, November 12, 2008 7:57 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:49 AM
Points: 1,553, Visits: 1,846
Adam Haines (11/12/2008)
Good article!

This type of query can be handled much easier in SQL 2005, but I would still try to avoid a correlated subquery in SQL 2000. You can move the query into a derived table and make it inline. Obviously, this is over simplifying the solution but you get the idea.

SELECT 
PersonRecord.*,
Improper.[IsImProper]
FROM PersonRecord
INNER JOIN(
SELECT
a.PersonID,
MAX(CASE
WHEN a.Version B.DEDate
THEN 1
ELSE 0
END) as IsImProper
FROM PersonRecord A
INNER JOIN PersonRecord B
ON A.PersonID = B.PersonID AND
A.Version = B.Version -1
GROUP BY A.PersonId
) AS Improper
ON Improper.[PersonID] = PersonRecord.PersonID



correction: the CASE clause should read:
WHEN a.DEDate > B.DEDate

but this does give the correct results.
The 2005 (partition) technique does not give the desired result of marking all rows for the personID.
Post #601402
Posted Wednesday, November 12, 2008 8:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, January 27, 2014 10:14 AM
Points: 1,322, Visits: 1,091
Reply to :Why use the cross join?

Yes it could be done in a way you have shown but then the whole personid should be marked as improper if any DEDATE is found mismatched


Divya,
Your given query is essentially marking the personID as improper. In that case a query like:

SELECT PersonID, 1 as improper
FROM #PersonRecord A
INNER JOIN #PersonRecord B
ON A.PersonID = B.PersonID
AND A.Version = B.Version-1
WHERE A.DEDate >= B.DEDate
GROUP BY PersonID

Seems much more simple than the original code.

In Addition, the CROSS JOIN is not necessary (and probably removed by the optimizer) in the original code. The following is the same logic with the Cross join replaced with an INNER JOIN:

SELECT *, 
(SELECT CASE
WHEN (SUM(CASE WHEN B.DEDate =1
THEN 1 ELSE 0
END AS isawk
from #PersonRecord B INNER JOIN #PersonRecord C
ON B.PersonID = C.PersonID
AND B.Version < C.Version
WHERE B.PersonID=A.PersonID) AS [Is ImProper]
from #PersonRecord A

If I were aiming for this result, I would probably use something like:

SELECT A.PersonID, Version, DEDate, COALESCE(Improper,0) as Improper
FROM #PersonRecord A
LEFT JOIN (SELECT DISTINCT B.PersonID, 1 as improper
FROM #PersonRecord B
INNER JOIN #PersonRecord C
ON B.PersonID = C.PersonID
AND B.Version = C.Version-1
WHERE B.DEDate >= C.DEDate) as t
ON A.PersonID = t.PersonID

I prefer to put the subquery in the FROM Clause instead of in the SELECT Clause, if possible. The benefit of this is seen when you want to move that subquery into a VIEW so that it can be used as a source in multiple queries.
--
JimFive
Post #601408
Posted Wednesday, November 12, 2008 8:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 10, 2009 2:31 AM
Points: 4, Visits: 22
derailed from the topic and question.....


do not use more than two instances of given table...

try to give output like this.

PersonID IsImproper
1 1
2 1
3 0
4 1
Post #601417
Posted Wednesday, November 12, 2008 8:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:06 AM
Points: 2,278, Visits: 3,052
Carla Wilson (11/12/2008)
Adam Haines (11/12/2008)
Good article!

This type of query can be handled much easier in SQL 2005, but I would still try to avoid a correlated subquery in SQL 2000. You can move the query into a derived table and make it inline. Obviously, this is over simplifying the solution but you get the idea.

SELECT 
PersonRecord.*,
Improper.[IsImProper]
FROM PersonRecord
INNER JOIN(
SELECT
a.PersonID,
MAX(CASE
WHEN a.Version B.DEDate
THEN 1
ELSE 0
END) as IsImProper
FROM PersonRecord A
INNER JOIN PersonRecord B
ON A.PersonID = B.PersonID AND
A.Version = B.Version -1
GROUP BY A.PersonId
) AS Improper
ON Improper.[PersonID] = PersonRecord.PersonID



correction: the CASE clause should read:
WHEN a.DEDate > B.DEDate

but this does give the correct results.
The 2005 (partition) technique does not give the desired result of marking all rows for the personID.

Thanks. The site completely stripped my code . I will edit my post.




My blog: http://jahaines.blogspot.com
Post #601422
Posted Wednesday, November 12, 2008 9:31 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:49 AM
Points: 1,553, Visits: 1,846
I was curious how the different techniques performed, so I loaded up the PersonRecord table with more data using a Numbers table (please see article http://www.sqlservercentral.com/articles/TSQL/62867/ for Numbers table)
insert into PersonRecord
select N,0,'11/12/2008'
from Numbers
where N between 4 and 11000

insert into PersonRecord
select N,1,'11/13/2008'
from Numbers
where N between 4 and 11000

insert into PersonRecord
select N,2,'11/10/2008'
from Numbers
where (N between 4 and 11000) and N % 100 = 0

So total of 11000 PersonIds, and 22118 total rows in the PersonRecord table.

I then recorded the execution time for 4 of the techniques.

Original Query 610 Milliseconds duration

Adam Haines
- inner joins and CASE 576 Milliseconds duration

James Goodwin
- Left join on inner join 656 Milliseconds duration

Matt C - SQL2005
row_number() over (partition) 860 Milliseconds duration


Just thought you would find this interesting.

(edited to add link to Numbers table article)
Post #601488
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse