SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Compare Dates in an iterative manner for a particular set of records


Compare Dates in an iterative manner for a particular set of records

Author
Message
ccz99
ccz99
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 746
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



gerhard-356547
gerhard-356547
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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
Adam Haines
Adam Haines
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6118 Visits: 3135
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
Divya Agrawal
Divya Agrawal
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1030 Visits: 604
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
Divya Agrawal
Divya Agrawal
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1030 Visits: 604
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
Carla Wilson-484785
Carla Wilson-484785
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2272 Visits: 1951
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.
James Goodwin
James Goodwin
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1659 Visits: 1107
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
sharmago
sharmago
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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
Adam Haines
Adam Haines
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6118 Visits: 3135
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 Sad. I will edit my post.



My blog: http://jahaines.blogspot.com
Carla Wilson-484785
Carla Wilson-484785
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2272 Visits: 1951
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search