Click here to monitor SSC
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
Divya Agrawal
Divya Agrawal
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 604
Comments posted to this topic are about the item Compare Dates in an iterative manner for a particular set of records

--Divya
sharmago
sharmago
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 22
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
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6785 Visits: 1407
Nice one Divya. Smile



sharmago
sharmago
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 22
can anyone perform this task with two tables only....
Divya Agrawal
Divya Agrawal
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 604
Yes it will optimize the performance of the query

--Divya
Divya Agrawal
Divya Agrawal
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 604
Thanks

--Divya
Divya Agrawal
Divya Agrawal
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 604
Performing task with two tables??
Could you please clarify your query??

--Divya
James Goodwin
James Goodwin
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1399 Visits: 1107
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


sharmago
sharmago
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 22
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
SqlAsSecondLanguage
SqlAsSecondLanguage
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 138
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...
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