April 25, 2014 at 6:38 am
Hello everyone!
I have a challenging one .. at least for me :-D. I am trying to query a database being used to store value samples for building conditions. These samples are usually taken every 15 minutes and there are 3 different points I am hoping to display as columns, ordered by date/time.
I have attached the sql for each of the relevant tables required for the join. Here is what I have so far ..
SELECT s.site_number as 'Site Number', s.site_name as 'Site Name', e.entity_equipment_name as 'HVAC Unit', DATEADD(MINUTE, DATEDIFF(MINUTE, 0, h.timestamp), 0) AS 'Date/Time',
(SELECT stpt.numeric_value FROM history as stpt INNER JOIN historypoints as stptpoint ON stpt.config_id = stptpoint.config_id WHERE DATEADD(MINUTE, DATEDIFF(MINUTE, 0, stpt.timestamp), 0) = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, h.timestamp), 0) AND stpt.config_id = h.config_id AND stptpoint.histConfID LIKE '%ActiveSetpoint%') AS 'Active Setpoint',
(SELECT temp.numeric_value FROM history as temp INNER JOIN historypoints as temppoint ON temp.config_id = temppoint.config_id WHERE DATEADD(MINUTE, DATEDIFF(MINUTE, 0, temp.timestamp), 0) = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, h.timestamp), 0) AND temp.config_id = h.config_id AND temppoint.histConfID LIKE '%ZoneTemp%') AS 'Zone Temperature',
(SELECT dat.numeric_value FROM history as dat INNER JOIN historypoints as datpoint ON dat.config_id = datpoint.config_id WHERE DATEADD(MINUTE, DATEDIFF(MINUTE, 0, dat.timestamp), 0) = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, h.timestamp), 0) AND dat.config_id = h.config_id AND datpoint.histConfID LIKE '%Discharge$20Temp%') AS 'Discharge Temp'
FROM history as h INNER JOIN
historypoints as hp ON h.config_id = hp.config_id INNER JOIN
xcm as x ON hp.xcm_id = x.xcm_id INNER JOIN
site as s ON x.site_id = s.site_id INNER JOIN
point as p ON hp.point_id = p.point_id INNER JOIN
entity as e ON hp.entity_id = e.entity_id AND x.xcm_id = e.xcm_id AND p.entity_id = e.entity_id
WHERE DATEADD(MINUTE, DATEDIFF(MINUTE, 0, h.timestamp), 0) BETWEEN GETDATE() - 30 AND GETDATE() AND
((SELECT stpt.numeric_value FROM history as stpt INNER JOIN historypoints as stptpoint ON stpt.config_id = stptpoint.config_id WHERE stpt.history_id = h.history_id AND stptpoint.histConfID LIKE '%ActiveSetpoint%') IS NOT NULL ) OR
(SELECT temp.numeric_value FROM history as temp INNER JOIN historypoints as temppoint ON temp.config_id = temppoint.config_id WHERE temp.timestamp = h.timestamp AND temp.history_id = h.history_id AND temppoint.histConfID LIKE '%ZoneTemp%') IS NOT NULL OR
(SELECT dat.numeric_value FROM history as dat INNER JOIN historypoints as datpoint ON dat.config_id = datpoint.config_id WHERE dat.timestamp = h.timestamp AND dat.history_id = h.history_id AND datpoint.histConfID LIKE '%Discharge$20Temp%') IS NOT NULL
ORDER BY [Date/Time], [HVAC Unit]
Attached you will also find a snapshot of what the output looks like. The query appears to be almost there, however each of the records are staggered instead of grouped by the date/time. For instance if a sample was taken at 8:00am on 4/25/14, I am aiming at having all samples in the same record versus separate records.
Can you tell me what I am doing wrong and how to correct or do more efficiently?
April 25, 2014 at 7:48 am
Add GROUP BY on site_number,site_nameentity_equipment_name and 'Date/Time' summing the three values.
As for a better way, it depends, what is the performance currently like using real data?
Far away is close at hand in the images of elsewhere.
Anon.
April 25, 2014 at 8:12 am
Thanks for the reply!
When I add the Group By I get the following error?
"Each GROUP BY expression must contain at least one column that is not an outer reference."
Ideas?
April 25, 2014 at 8:30 am
Put your query in a cte and add the GROUP BY, SUM and ORDER BY to the SELECT FROM the cte
Far away is close at hand in the images of elsewhere.
Anon.
April 25, 2014 at 9:04 am
I think I have it working with the modified version below
WITH Summary (site_number, site_name, entity_equipment_name, timestamp, ActiveSetpoint, ZoneTemp)
AS
(
SELECT s.site_number, s.site_name, e.entity_equipment_name, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, h.timestamp),0 ), (select numeric_value where pointname LIKE '%ActiveSetpoint%'), (select numeric_value where pointname LIKE '%ZoneTemp%')
FROM history as h INNER JOIN
historypoints as hp ON h.config_id = hp.config_id INNER JOIN
xcm as x ON hp.xcm_id = x.xcm_id INNER JOIN
site as s ON x.site_id = s.site_id INNER JOIN
point as p ON hp.point_id = p.point_id INNER JOIN
entity as e ON hp.entity_id = e.entity_id AND x.xcm_id = e.xcm_id AND p.entity_id = e.entity_id
WHERE (pointname LIKE '%ActiveSetpoint%' AND 'Active Setpoint' IS NOT NULL) OR (pointname LIKE '%ZoneTemp%' AND 'Zone Temp' IS NOT NULL)
)
SELECT site_number as 'Site Number', site_name AS 'Site Name', entity_equipment_name AS 'HVAC Unit', timestamp as 'Date/Time', AVG(ActiveSetpoint) AS 'Active Setpoint', AVG(ZoneTemp) AS 'Zone Temp'
FROM Summary
GROUP BY site_number, site_name, entity_equipment_name, timestamp
ORDER BY timestamp
Can you tell me if the CTE is a standard SQL format that can be used outside of SQL Server if called from a 3rd party app?
April 25, 2014 at 10:34 am
mrupperman (4/25/2014)
Can you tell me if the CTE is a standard SQL format that can be used outside of SQL Server if called from a 3rd party app?
Sorry I only use and know SQL Server.
I believe Oracle supports common table expressions but is version specific.
Far away is close at hand in the images of elsewhere.
Anon.
April 25, 2014 at 10:37 am
thanks so much .. it works perfectly!! Can you tell me, could the same thing be accomplished using a View instead of the CTE and then adding the Group by in my query to the View?
April 25, 2014 at 10:41 am
It should do cannot see why it would not.
Best way is to try it
Far away is close at hand in the images of elsewhere.
Anon.
April 25, 2014 at 10:47 am
Thanks again!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply