How to find status Difference using date field

Database

How to find status Difference using date field

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

2 REPLIES
Junior Contributor

Re: How to find status Difference using date field

Hi Josh,

this should match your expected output:

  CASE
WHEN status =200 THEN -1
ELSE MIN(CASE WHEN status =200 THEN CAST(status_date AS DATE) END)
OVER (PARTITION BY vin_r
ORDER BY status_date
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - CAST(status_date AS DATE)
END

Dieter

Re: How to find status Difference using date field

Thanks Lot Dnoeth,

I am really happy ....no words to express my Sincere thanks to your great help...

In the above query Is working fine. Once again my thanks to your effort...

Thanks & Regards

Josh