December 21, 2004 at 8:52 pm
Hi All,
I am attempting to do the following. There are 3 tables with fields:
The Batch History and Trend table data is generated by the same program say X. A different program generates the External_Data table data say Z. My main objective here is to compare the batch information of these two programs for consistency using the Batch_History table information (Pls refer below for example) and generate a new table BatchHistory_Trend_ External_Data as report. This table will be sent as a text file using DTS to another computer. What is the easiest way to accomplish this? What should I know in order to do this?
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
X program
Batch History (Tank_no,Batch_No, Start_time, End_time)
Tank_No Batch_NoStart_TimeEnd_Time
1 112/21/2004 2:56:45 PM 12/21/2004 2:56:50 PM
--------------------------------------------------------------------------------------------------------
Trend (Tank_No, Time_Stamp, Output_Reading)
Tank_No Time_StampOutput_Reading
112/21/2004 2:56:40 PM107
112/21/2004 2:56:45 PM104
112/21/2004 2:56:47 PM101
112/21/2004 2:56:50 PM98
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
Z program
External_Data (Tank_No, Time_Stamp, Output_Reading)
Tank_No Time_StampOutput_Reading
112/21/2004 2:56:40 PM107
112/21/2004 2:56:45 PM105
112/21/2004 2:56:49 PM103
112/21/2004 2:56:50 PM99
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
Example:
Using the Batch History table Start_Time and End_Time we get from Trend table
112/21/2004 2:56:45 PM104
112/21/2004 2:56:50 PM98
Trend table difference = 104 – 98
Trend_Tonne= 6
Using the Batch History table Start_Time and End_Time we get from External_Data table
112/21/2004 2:56:40 PM107
112/21/2004 2:56:50 PM99
Trend table difference = 107 – 99
External_Data_Tonne= 8
The BatchHistory_Trend_ External_Data table fields
(Tank_no, Start_time, End_time,Trend_Tonne, External_Data_Tonne)
Any help would be greatly appreciated. Thanks.
December 23, 2004 at 1:22 am
Hi,
try this one, I hope it will help:
select
bh.Tank_no, bh.Start_time, bh.End_time,
ts.Output_Reading - te.Output_Reading as Trend_Tonne,
eds.Output_Reading - ede.Output_Reading as External_Data_Tonne
from
Batch_History as bh
inner join Trend as ts -- Trend start
on (ts.Tank_no = bh.Tank_no) and (ts.Time_Stamp = bh.Start_time)
inner join Trend as te -- Trend end
on (te.Tank_no = bh.Tank_no) and (te.Time_Stamp = bh.End_time)
inner join External_Data as eds -- External data start
on (eds.Tank_no = eds.Tank_no) and (eds.Time_Stamp = bh.Start_time)
inner join External_Data as ede -- External data end
on (ede.Tank_no = bh.Tank_no) and (ede.Time_Stamp = bh.End_time)
order by
bh.Tank_no
By the way, is the example incorrect ? Shouldn't the Output_Reaging value for Start_Time (12/21/2004 2:56:45 PM) be 105 instead of 107 ?
Regards,
Goce.
December 23, 2004 at 6:43 pm
Goce Smilevski - Thanks for you reply and you are right the Output_Reaging value for Start_Time (12/21/2004 2:56:45 PM) be 105.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply