|
|
Posted by Frank on 10/02/12 11:51
Hi there, I'm trying to generate a report for an old database and I'm
having trouble coming up with an elegant way of going about it. Using
cursors and other 'ugly' tools I could get the job done but 1) I don't
want the report to take ages to run, 2) I'm not a big fan of cursors!
Basically there are tables that track history and each table tends to
track only a specific value housed within a date range. I'm trying to
combine the tables to get a snap-shot of the complete history. I'm
having problems dealing with the Start/End Dates from the two tables
and building the dates in the final table to be broken down by 'history
type'.
Here are a few sample records and the results I'm trying to achieve:
Table 1:
CAgyHist (ProdID,AgyID,StartDate,EndDate)
1 1 Jan 1, 2006 Jan 5, 2006
1 2 Jan 5, 2006 Jan 25, 2006
1 1 Jan 25, 2006 NULL
Table 2:
CInvHist (ProdID, InvID,StartDate,EndDate)
1 1 Jan 1, 2006 Jan 23, 2006
1 2 Jan 23, 2006 Jan 15, 2006
1 1 Jan 15, 2006 NULL
Desired End Result:
CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)
1 1 1 Jan 1, 2006 Jan 5, 2006
1 2 1 Jan 5, 2006 Jan 15, 2006
1 2 2 Jan 15, 2006 Jan 23, 2006
1 2 1 Jan 23, 2006 Jan 25, 2006
1 1 1 Jan 25, 2006 NULL
My challenge thus far has been dealing with the dates as they don't
necessarily correspond - from one table to the other.
I am by no means a database expert of any level and any help would be
greatly appreciated.
Thanks,
Frank.
Navigation:
[Reply to this message]
|