Creating a View on historical data
-
I have a table that stores the history that a job has gone through. I need to display when a job sells and cancels after, these are stored in row by row format. I need to show the sequence as one record. Please see the example below.
Hist_Table
job histCode histDescription histDate
13 3 Sale 1/2/03
13 10 Cancel 1/15/03
13 1 NewSubmit 1/20/03
13 3 Sale 2/2/03
13 10 Cancel 2/9/03
13 1 NewSubmit 2/10/03
13 3 Sale 2/11/03
15 3 Sale 1/2/03
15 10 Cancel 1/15/03
15 1 NewSubmit 1/20/03
15 3 Sale 2/2/03
15 10 Cancel 2/9/03
15 1 NewSubmit 2/10/03
I need to show the data as such
job saledate canceldate
13 1/2/03 1/15/03
13 2/2/03 2/9/03
13 2/11/03 NULL
15 1/2/03 1/15/03
15 2/2/03 2/9/03
If anyone could be so kind as to point me in the right direction here I would greatly appreciate it!
Marty
-
Select
Job
, histDate as SaleDates
, (Select Min(T1.histDate)
From dbo.Hist_Table T1
Where T.Job = T1.Job
and T1.histCode =10
and T.histDate < T1.histDate ) as CancelDate
From
dbo.Hist_Table T where histCode = 3
Order by T.Job, T.histDate
HTH
* Noel -
So far so good, thanks Noel!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply