November 12, 2008 at 12:12 am
Comments posted to this topic are about the item Compare Dates in an iterative manner for a particular set of records
--Divya
November 12, 2008 at 2:51 am
Hi Divya, it gud..
you just can minimize the query length a little by removing the highlighted text...
SELECT *,
(SELECT CASE
WHEN (SUM(CASE WHEN B.DEDate =1
THEN 1 ELSE 0
END AS isawk
from PersonRecord B CROSS JOIN PersonRecord C
WHERE B.PersonID=A.PersonID
AND C.PersonID=A.PersonID
AND B.VERSION<>C.VERSION
AND B.VERSION<C.VERSION) AS [Is ImProper]
from PersonRecord A
November 12, 2008 at 3:13 am
Nice one Divya. 🙂
November 12, 2008 at 3:26 am
can anyone perform this task with two tables only....
November 12, 2008 at 5:59 am
Yes it will optimize the performance of the query
--Divya
November 12, 2008 at 6:00 am
Thanks
--Divya
November 12, 2008 at 6:26 am
Performing task with two tables??
Could you please clarify your query??
--Divya
November 12, 2008 at 6:49 am
Why use the cross join?
SELECT *
FROM PersonRecord A
INNER JOIN PersonRecord B
ON A.PersonID = B.PersonID
AND A.VersionID = B.VersionID - 1
WHERE B.DEDate > A.DEDate
November 12, 2008 at 6:56 am
yes the task could have been done this way but she wants all the records to be called improper if any of the date is not proper
November 12, 2008 at 7:20 am
Nice ideas - I work in the insurance industry and use this often to look for out-of-sequence endorsements and transactions. Here's another way to acheive it, without a join.
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
Only catch here (and for others) is that both fileds you are comparing over a given personid must be unique...
November 12, 2008 at 7:26 am
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
November 12, 2008 at 7:33 am
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
November 12, 2008 at 7:37 am
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
November 12, 2008 at 7:39 am
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
November 12, 2008 at 7:42 am
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
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply