All Forums General
joshvignesh 3 posts Joined 02/13
01 Feb 2013
How to find status Difference using date field

Hi all,
Please help me how to find difference between two dates based on the status.
Here I provided the Source Table for your verification
 

VIN_R

STATUS

STATUS_DATE

DIFFERENCE

A1234

20

10/20/2011 10:01

-1

A1234

40

11/3/2011 0:00

-1

A1234

70

11/4/2011 0:00

-1

A1234

190

11/7/2011 11:35

-1

A1234

190

11/7/2011 12:01

-1

A1234

150

11/8/2011 13:08

-1

A1234

200

11/9/2011 18:00

-1

A1234

190

1/12/2012 16:54

-1

A1234

150

1/17/2012 12:12

-1

A1234

200

1/17/2012 18:00

-1

A1234

190

2/23/2012 17:45

-1

A1234

150

2/28/2012 12:15

-1

A1234

200

2/28/2012 14:45

-1

 
Logic is:
In the above table we have 3 record for STATUS = 200.
1) it should take the first 200 status date (11/9/2011) and find the difference for all the records which is less  than     status_date '11/9/2001'
2) it should take the second 200 status date (1/17/2012) and find the difference for all records  where
status date between ('11/9/2011' and 1/17/2012') ---------  (first 200 status date and second 200 status date)
3)  it should take the third 200 status date (2/28/2012) and find the difference for all records where
status date between ('1/17/2012' and '2/28/2012') ---------  (second 200 status date and third 200 status date)
all are based on VIN_R
OUTPUT SHOULD COME LIKE THIS SHOWN IN DIIERENCE FIELD
 
 
 

VIN_R

STATUS

STATUS_DATE

DIFFERENCE

A1234

20

11/1/2011 10:01

8

A1234

40

11/3/2011 0:00

6

A1234

70

11/4/2011 0:00

5

A1234

190

11/7/2011 11:35

2

A1234

190

11/7/2011 12:01

2

A1234

150

11/8/2011 13:08

1

A1234

200

11/9/2011 18:00

-1

A1234

190

1/12/2012 16:54

5

A1234

150

1/17/2012 12:12

0

A1234

200

1/17/2012 18:00

-1

A1234

190

2/23/2012 17:45

5

A1234

150

2/28/2012 12:15

0

A1234

200

2/28/2012 14:45

-1

Please provide the query for the above scenario
Thanks in Advance
Josh

Adeel Chaudhry 773 posts Joined 04/08
03 Feb 2013

Duplicate post.

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.