|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
July 16th, 2009, 01:00 PM
|
|||
|
|||
|
Lag/Lead not the full solution.
Here is what I'm trying to do, I have a table that has, among other things, the following columns:
EVENT_ID, AUDIT_ID, EVENT_DATE Here is some data Code:
EVENT_ID AUDIT_ID EVENT_DATE 100143 153 07/15/2009 100142 153 07/15/2009 100132 153 07/01/2009 100102 153 06/23/2009 100152 326 07/16/2009 100137 326 07/03/2009 100011 326 06/06/2009 100141 257 07/15/2009 100120 257 06/28/2009 100150 186 07/16/2009 100148 186 07/16/2009 100123 186 06/28/2009 What I am trying to accomplish is the following: - Get all of the events that happened either today or yesterday. This is the easy part. - Take the AUDIT_ID of those events and go back to the next event that did not happen today or yesterday and retreive the date that it happened. - Calculate the days between these events. This is also easy. So basically in the end I am looking for the data to be similar to this: Code:
AUDIT_ID EVENT_DATE PREV_DATE DAYS 153 07/15/2009 07/01/2009 15 326 07/16/2009 07/03/2009 14 257 07/15/2009 06/28/2009 18 186 07/16/2009 06/28/2009 17 I know I can use the Lag/Lead functions, but I can't figure out an efficent and accurate way to use them. Any pointers/functions/code would be greatly appriciated. Incase you were wondering, I am using oracle.
__________________
Click here to Join the most addicting site on the Net. Last edited by InsureDesign : July 16th, 2009 at 01:24 PM.
|
|
#2
September 23rd, 2009, 04:04 PM
|
|||
|
|||
|
I haven't worked with Oracle since 9i, so I don't know if this will work or not, but it works in ms sql server:
Code:
select distinct s.audit_id, s.Event_Date, d.Prev_date, DATEDIFF(d, d.Prev_date, s.Event_Date) as days from events s join (select audit_id, MAX(event_date) as prev_date from events WHERE event_date < dateadd(d,-1,'2009-07-16') GROUP BY audit_id) d on s.audit_id = d.Audit_id WHERE s.Event_Date >= dateadd(d,-1,'2009-07-16')
|
|
#3
October 12th, 2009, 05:27 AM
|
|||
|
|||
|
Re: Lag/Leap not the full solution
Hi,
I hope that the below SQL query will be very useful for you.... If you consider the event date as a current date, use the following query Code:
Select a.audit_id as Audit_id, convert(char,a.event_date,101) as Event_date, convert(char,b.event_date,101) as Previousdate, datediff(dd,convert(char,b.event_date,101),convert (char,a.event_date,101)) as Days from test2 a INNER JOIN (Select audit_id , event_date, rno from(select audit_id, event_date, row_number()over(partition by audit_id order by event_date desc)as rno from test2)tbl where rno =2)b on a.audit_id = b.audit_id where (convert(char,a.event_date,101) = convert(char,getdate(),101) or datepart(dd,convert(char,a.event_date,101)) = (datepart(dd,convert(char,getdate(),101))-1)) If you consider the event date as max date, use the following query. Code:
Select
a.audit_id as Audit_id,
max(convert(char,a.event_date,101))as Event_date,
convert(char,b.event_date,101) as Previousdate,
datediff(dd, convert(char,b.event_date,101),max (convert(char ,a.event_date,101)))as Days
from
test2 a,
(select
event_date,
row_number()over(partition by audit_id order by event_date desc)as rno from test2)b
where
b.rno = 2
group by
audit_id,
b.event_date
Note: Test2 is a table name Thanks & Regards Sakthimeenakshi.S ------------------------------------------------
|
|
| Viewing: ASP Free Forums > Database > SQL Development > Lag/Leap not the full solution. |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
