View previous topic :: View next topic
|
Author |
Message |
gsbrar16
New User
Joined: 21 Dec 2006 Posts: 27 Location: india
|
|
|
|
Hi All,
I want to formulate a query in such a way that i want to compare the EFF_DATE field of a row with the EFF_DATE field of the last row(order by primary key). Can it be done?
Actually in my case data in table is like this
EFF_DATE
2006-09-01
2006-08-01
2006-07-01
2006-05-01
2006-04-01
i want to find rows where there is discontinuation in the EFF_DATE like the one shown above in bold letters. Ideally there must have been 2006-06-01 after 2006-07-01, But it is not there.
Please help me in this..
Thanks
Guralam Singh |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
This should be straight forward if you declare a cursor that selects the rows in descending date order. As you fetch each row, compare the date of the row just fetched against the previous date.
If they are not one day different, do what you need to. . . . |
|
Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005 Posts: 178 Location: Copenhagen, Denmark
|
|
|
|
I'm afraid, You cannot do that just in a SQL query as per my knowledge. |
|
Back to top |
|
|
gsbrar16
New User
Joined: 21 Dec 2006 Posts: 27 Location: india
|
|
|
|
HI All,
Thanks for the replies. I dont want to use the cursor. I just want to use a query. Can it be done through the use of a correlated sub query.
Thanks,
Guralam Singh |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
And you don't want to use a cursor because. . .? |
|
Back to top |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006 Posts: 133 Location: India
|
|
|
|
Hi Guralam,
Since I am not very sure of your table keys, I have created a similar solution with an emp table having emp_no and eff_dt with key as the emp_no. This query will make sure that all the records which are not having higher the next date or lower key having the previous date. I think we can tune the query still..But this can give you a starting point.
This query will also return the min and max key record as that also not satifies the condition you have mentioned.(If you want to remove it, may be you can use min and max fn in base table). Please see the query below:
SELECT * FROM EMP_TBL TMP
WHERE EMP_NO NOT IN(
SELECT TM.EMP_NO FROM EMP_TBL TM
WHERE EXISTS
( SELECT 1 FROM EMP_TBL TM1
WHERE TM1.EFF_DT = TM.EFF_DT + 1 DAY
AND TM1.EMP_NO IN
(SELECT MIN(TM2.EMP_NO)
FROM EMP_TBL TM2
WHERE TM2.EMP_NO > TM.EMP_NO) )
AND EXISTS
( SELECT 1 FROM EMP_TBL TM3
WHERE TM3.EFF_DT = TM.EFF_DT - 1 DAY
AND TM3.EMP_NO IN
(SELECT MAX(TM4.EMP_NO)
FROM EMP_TBL TM4
WHERE TM4.EMP_NO < TM.EMP_NO) ) )
Please see if this helps |
|
Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007 Posts: 199 Location: Sri Lanka
|
|
|
|
If you are willing to do the date comparision part, which shouldn't be too difficult, here is an SQL to check for out of sequence rows.
The table1 here is assumed to contain only one column with numerc data. This displays col1 of any row if it doesn't find any other row with col1 where the difference is only 1.
Code: |
SELECT T1.COL1
FROM TABLE1 T1
WHERE EXISTS (SELECT 1 FROM TABLE1 T2
WHERE T2.COL1-T1.COL1 > 1
AND NOT EXISTS(
SELECT 1 FROM TABLE1 T3
WHERE T3.COL1>T1.COL1
AND T3.COL1<T2.COL1)) |
|
|
Back to top |
|
|
gsbrar16
New User
Joined: 21 Dec 2006 Posts: 27 Location: india
|
|
|
|
Hi Wanderer,Prajesh
Thanks for your replies. With some modification/addition to your queries, i was able to solve my problem. |
|
Back to top |
|
|
|