March 17, 2009 at 4:23 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 6:42 am
You have posted the same question in 2 forums, 1 for SQL Server 2000 and one for SQL Server 2005, which version are you using?
Most posters look at the forum to determine what features can be used to solve the problem
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
March 18, 2009 at 7:31 am
Its 2005. I actually made a mistake on the first post but didnt realize it till after words. Then, figured what the heck, perhaps two responses might get generated. Sorry for the confusion.
March 18, 2009 at 7:35 am
Are you using Reporting Services to display the report?
Can you post sample data and expected results as suggested in the links in my signature line?
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
March 18, 2009 at 8:25 am
Using Crystal Reports Version 11. Weeee...I am so happy. NOT!!
Ok the output which I am trying to accomplish in the stored proc would send to the report is below.
Timestamp Suction Discharge Meter Flow Meter Total Meter Temp Tank1 Tank2 Tank 3
3/16/2009 9:00am 100.0 200.0 101.0 800.0 15.0 10-10 11-11 12-12
Keeping in mind that this a 24 hour report. One record being inserted every hour into the tables. So the above would have a 10:00am then 11:00am and so on. Also, keep in mind that there is one station, one meter to the station and three tanks to the station. Meter and tank have no relationship except by station. :w00t:
March 18, 2009 at 8:39 am
Can you provide some sample data in the format Jack has in his signature? It will take you a few minutes, and you will get quick answers on how to do your query.
Most posters here will not take the time to write your tables and sample data to build the query, but if you provide them in an easy to do manner (as the signature teaches you), you WILL get an answer.
Cheers,
J-F
March 23, 2009 at 1:07 pm
Ok, I have uploaded the files needed to create the tables and populate the config tables and then sample data into the main tables. I included six records.
The output of the stored proc which is included called cp_CRDB_reports_STATION_24_HOUR
will acutally produce the desired format just the data is wrong for meters and tanks. Once again if you could help in any way I would be most grateful. Is there anything else you need I will see what I can do to provide. All you have to do is
1. Create the database calling it CRDB
2. run the CRDBCreate.txt which creates the contents of the database
3. run the CRDB_Load_config_tables.txt
4. run the station_rpt_data.txt
And your ready to go.
thanks
Tom
March 24, 2009 at 4:09 pm
Never mind folks. I got it!
March 24, 2009 at 9:57 pm
tlbrackney (3/24/2009)
Never mind folks. I got it!
Great! Sorry I have not gotten back to the thread. I'm in training this week so I'm not on-line during the day.
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
March 25, 2009 at 6:35 am
I also have been really busy lately, and I completely forgot your post, 😉 Sorry about that. You might want to post your solution for others to see how you corrected the problem?
Thanks,
Cheers,
J-F
March 27, 2009 at 11:55 am
Try this. It uses a Tally table. If you haven't used a Tally table, see http://qa.sqlservercentral.com/articles/TSQL/62867/ for how to build.
;WITH cteTanks
AS (
SELECT
m.actual_date_time AS DateReceived,m.STATION_ID,m.meter_id,m.flow,m.total,m.TEMPERATURE
,t.N AS tanknum
FROM tempdb.dbo.tally t
cross JOIN meter m
INNER JOIN dbo.STATION s
ON m.STATION_ID = s.STATION_ID
AND m.ACTUAL_DATE_TIME = s.ACTUAL_DATE_TIME
WHERE t.n <= 3
)
SELECT c.*,t.*
FROM cteTanks c
INNER JOIN TANK t
ON c.STATION_ID = t.STATION_ID
AND c.DateReceived = t.ACTUAL_DATE_TIME
AND c.tanknum = t.TANK_ID
ORDER BY c.DateReceived,t.TANK_ID
March 27, 2009 at 12:05 pm
Thanks for the response. Already fixed it and works great. But, I appreciate the help.
Tom
March 27, 2009 at 12:31 pm
Can you share the solution with us?
Thanks
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply