How to find status Difference using date field

General

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

1 REPLY
Teradata Employee

Re: How to find status Difference using date field

Duplicate post.