March 17, 2009 at 4:22 pm
Ok, I have one here that as stumped some people where I work and myself. Not sure what we are doing wrong. Any help is greatly appreciated. Ok here it goes. I will simplify the tables to make it easier to work with. There are three main tables that recieve hour information. So in a 24 hr period these three tables will get 24 records. One table is meter. One table is Station, and last table is Tank. The station is the main table. For each station there is one and only one meter and for each station there can be up to three tanks. In this case, this station has three tanks and one meter to its station. What I need to happen is for the data to appear in this form on the report.
Date Received, Suction, Discharge, Meter Flow, Meter Total, Meter Temp, Tank 1, Tank 2, Tank 3.
Each tank has a seperate value. Doesnt matter what that is for this case. The table structure for Meter is:
Unique_ID, Station_id, Meter_id, Meter_name, meter_flow, meter_total, meter_temp, actual date time
The table structure for tank is Unique_ID, Station_id, tank_id tank_name, tank_ft, tank_inches, actual_date_time
The table stucture for Station is Unique_ID, Station_id, suction, discharge, actual_date_time.
So far I have come up with this but it does not work.
SELECT DISTINCT(DATEPART(hh, S.actual_date_time)),
S.actual_date_time,
MAX(CS.station_name) as station_name,
MAX(S.suction) as suction,
MAX(S.discharge) as discharge,
MAX(M.flow) as meter_flow,
MAX(M.total) as meter_total,
MAX(M.temperature) as meter_temperature,
MAX(M.meter_id) as meter_id,
MAX(CM.meter_name) as meter_name,
T.tank_id,
MAX(CT.tank_name) as tank_name,
MAX(T.level_feet) as tank_level_feet,
MAX(T.level_inches) as tank_level_inches
FROM
station S INNER JOIN config_station CS
ON S.pipeline_id = CS.pipeline_id AND
S.station_id = CS.station_id --AND
S.actual_date_time BETWEEN @end_date AND @start_date
LEFT OUTER JOIN
(
meter M INNER JOIN config_meter CM
ON M.pipeline_id = CM.pipeline_id AND
M.station_id = CM.station_id AND
M.meter_id = CM.meter_id --AND
M.actual_date_time BETWEEN @end_date AND @start_date
)
ON S.station_id = M.station_id AND
S.pipeline_id = M.pipeline_id
LEFT OUTER JOIN
(
tank T INNER JOIN config_tank CT
ON T.pipeline_id = CT.pipeline_id AND
T.station_id = CT.station_id AND
T.tank_id = CT.tank_id --AND
T.actual_date_time BETWEEN @end_date AND @start_date
)
ON
S.pipeline_id = T.pipeline_id AND
S.station_id = T.station_id
WHERE
S.pipeline_id = 1 AND
S.station_id = @station_id
GROUP BY S.actual_date_time,T.tank_id
ORDER BY S.actual_date_time desc
I get the same value over and over for Meter and Tank. Please ignore the config_ tables. they are just informational for each of the tables.
Thanks
Thomas
March 18, 2009 at 7:33 am
Duplicate post and in the wrong forum. Please post answers here.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply