Posted by Jack Vamvas on 10/02/83 11:51

what do you mean by , "the dates don't correspond from 1 table to the
other"?


----
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________


"Frank" <mrpubnight@hotmail.com> wrote in message
news:1151369612.360817.191930@c74g2000cwc.googlegroups.com...
> 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.
>

[Back to original message]