Joins Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Joins Problem

Author

Topic

partap26
Starting Member

27 Posts

Posted - 2013-06-06 : 08:46:40

SELECT sd.site_id,
avg(av.dc_volt) as dc_volt, avg(av.curr_temp) as curr_temp,
SUM(ISNULL(at.eb_time,0)) as eb_time, SUM(ISNULL(at.bb_time,0)) as bb_time,
SUM(ISNULL(at.dg_time,0)) as dg_time
FROM site_details sd
INNER JOIN avg_values av ON av.site_id = sd.site_id
LEFT OUTER JOIN alarm_time at ON at.site_id = sd.site_id
WHERE sd.site_id='S001'
GROUP BY sd.site_id, sd.site_name;

Some output is as for eb_time = 36450

If I remove the INNER JOIN as
SELECT sd.site_id,
SUM(ISNULL(at.eb_time,0)) as eb_time, SUM(ISNULL(at.bb_time,0)) as bb_time,
SUM(ISNULL(at.dg_time,0)) as dg_time
FROM site_details sd
LEFT OUTER JOIN alarm_time at ON at.site_id = sd.site_id
WHERE sd.site_id='S001'
GROUP BY sd.site_id, sd.site_name;
I get the correct output for eb_time as 12150

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-06 : 09:34:34

I'm assuming that the table avg_values has duplicate records (based on your join criteria of site_id)
If you run the code "select site_id, count(1) from avg_values group by site_id having count(1) > 1" - do you get any records back ?

If so, these records are causing duplication in your aggregations and as a result they are inflating those values.
You will need to find a way of sorting out the granularity here.

Duane.

partap26
Starting Member

27 Posts

Posted - 2013-06-06 : 09:50:19

Ya, in avg_values table the value for S001 is as
site_id dc_volt curr_temp avg_date
S001 43.00 21.00 2013-05-31
S001 43.00 21.00 2013-06-01
S001 43.00 21.00 2013-06-06

But the date is different........ Then what should i Do

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-06 : 09:58:21

That is more of a business rules problem than anything else.
You need to decide how you want to use the values you get from avg_values.

Average them per site_id across all dates, or maybe match by site_id and the closest date.
Once you know that then you can look at how to write the query.

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources