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 12:12 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 11:03 PM
Points: 143, Visits: 550
Comments posted to this topic are about the item Compare Dates in an iterative manner for a particular set of records

--Divya
Post #601163
Posted Wednesday, November 12, 2008 2:51 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
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
Post #601209
Posted Wednesday, November 12, 2008 3:13 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,375, Visits: 1,391
Nice one Divya. :)


Post #601219
Posted Wednesday, November 12, 2008 3:26 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
can anyone perform this task with two tables only....
Post #601222
Posted Wednesday, November 12, 2008 5:59 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 11:03 PM
Points: 143, Visits: 550
Yes it will optimize the performance of the query

--Divya
Post #601276
Posted Wednesday, November 12, 2008 6:00 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 11:03 PM
Points: 143, Visits: 550
Thanks

--Divya
Post #601278
Posted Wednesday, November 12, 2008 6:26 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 11:03 PM
Points: 143, Visits: 550
Performing task with two tables??
Could you please clarify your query??


--Divya
Post #601296
Posted Wednesday, November 12, 2008 6:49 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
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

Post #601316
Posted Wednesday, November 12, 2008 6:56 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
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
Post #601321
Posted Wednesday, November 12, 2008 7:20 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 2:52 PM
Points: 165, Visits: 137
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...
Post #601347
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse