Advanced JOIN from 2 different tables

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 2012 Forums

Transact-SQL (2012)

Advanced JOIN from 2 different tables

Author

Topic

MatsG
Starting Member

3 Posts

Posted - 2014-03-19 : 05:42:13

Hello!
I've been sitting for 3 nights tearing my hair trying to figure this out. Hope someone can help me.
I have two 1-wire counters on two different power counters in my house, one that counts all power consumption in my house and one that counts consumption on my heater, 1 tick is 1 watt.
They save the values in separate tables in SQL Server 2012 every minute.
index - TimeStamp - Counter_C
110485 - 2014-03-19 10:24:00.000 - 76546
110484 - 2014-03-19 10:23:00.000 - 76480
110483 - 2014-03-19 10:22:00.000 - 76413
Both tables has the same column-names.

Right now i got this SQL-query that shows me the difference between the counted value, every hour, so i know how much power my house consumed every hour.
I got this from en example for MySQL but has adapted it for SQL Server
SELECT CAST(energy.timegroup AS DATETIME) as "Hour",
sum(energy.countdiff / 1) as "WhSum"
FROM(SELECT (i.counter_c - c.counter_c) as "countdiff",
c.counter_c as "startcounter",
FORMAT(c.timestamp, 'yyyy-MM-dd HH:00') as "timegroup"
FROM MSure.dbo.PowerCounter as c
join (SELECT * FROM MSure.dbo.PowerCounter) as i
on c.[index] = i.[index]-1 and c.[timestamp] between dateadd(Hour, -24, GetDate()) and GetDate()) as energy
WHERE (energy.countdiff > 0)
GROUP BY energy.timegroup

This gives me the following:
Hour - WhSum
2014-03-18 10:00:00.000 - 974
2014-03-18 11:00:00.000 - 1772
2014-03-18 12:00:00.000 - 1019
2014-03-18 13:00:00.000 - 1570
2014-03-18 14:00:00.000 - 985
...
And so on for every hour this day

Now to my problem:
What i want to do is get another column in the same result, like this
Hour - WhSum - WhSumHeater
2014-03-18 10:00:00.000 - 974 - 183
2014-03-18 11:00:00.000 - 1772 - 470
2014-03-18 12:00:00.000 - 1019 - 309
2014-03-18 13:00:00.000 - 1570 - 480
2014-03-18 14:00:00.000 - 985 - 312
Where WhSumHeater is the same SQL-query but from the table thats called MSure.dbo.PowerCounterBVP

Hope it makes sense :)
If anyone can help me it would be much appreciated !!

With regards
Mats
Sweden

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-19 : 05:52:05

[code]

;with cteHeater
AS
(select '2014-03-18 10:00:00.000' as [Hour],974 as WhSum union all
select '2014-03-18 11:00:00.000',1772 union all
select '2014-03-18 12:00:00.000',1019 union all
select '2014-03-18 13:00:00.000',1570 union all
select '2014-03-18 14:00:00.000',985)
,ctePower
AS
(select '2014-03-18 10:00:00.000' as [Hour],183 as WhSumHeater union all
select '2014-03-18 11:00:00.000',470 union all
select '2014-03-18 12:00:00.000',309 union all
select '2014-03-18 13:00:00.000',480 union all
select '2014-03-18 14:00:00.000',312)

select
coalesce(H.[Hour],P.[Hour]) as [Hour]
,H.WhSum
,P.WhSumHeater

from cteHeater H
full join ctePower P
on H.[Hour]=P.[Hour]

[/code]

output
[code]

Hour WhSum WhSumHeater
2014-03-18 10:00:00.000 974 183
2014-03-18 11:00:00.000 1772 470
2014-03-18 12:00:00.000 1019 309
2014-03-18 13:00:00.000 1570 480
2014-03-18 14:00:00.000 985 312
[/code]

S

sabinWeb MCP

MatsG
Starting Member

3 Posts

Posted - 2014-03-19 : 06:45:41

Oh my !
I am truly impressed !
10 minutes !

Didn't get it at first but this is what the result query turned out to be:

with cteHeater AS (
SELECT CAST(energy.timegroup AS DATETIME) as "Hour",
sum(energy.countdiff * 2) as "WhSumHeater"

FROM(SELECT (i.counter_c - c.counter_c) as "countdiff",
c.counter_c as "startcounter",
FORMAT(c.timestamp, 'yyyy-MM-dd HH:00') as "timegroup"
FROM MSure.dbo.PowerCounterBVP as c
join (SELECT * FROM MSure.dbo.PowerCounterBVP) as i
on c.[index] = i.[index]-1 and c.[timestamp] between dateadd(Hour, -24, GetDate()) and GetDate()) as energy

WHERE (energy.countdiff > 0)
GROUP BY energy.timegroup)
, ctePower AS (SELECT CAST(energy.timegroup AS DATETIME) as "Hour",
sum(energy.countdiff / 1) as "WhSum"

FROM(SELECT (i.counter_c - c.counter_c) as "countdiff",
c.counter_c as "startcounter",
FORMAT(c.timestamp, 'yyyy-MM-dd HH:00') as "timegroup"
FROM MSure.dbo.PowerCounter as c
join (SELECT * FROM MSure.dbo.PowerCounter) as i
on c.[index] = i.[index]-1 and c.[timestamp] between dateadd(Hour, -24, GetDate()) and GetDate()) as energy

WHERE (energy.countdiff > 0)
GROUP BY energy.timegroup)

SELECT coalesce(H.[Hour],P.[Hour]) as [Hour]
,P.WhSum
,H.WhSumHeater

from cteHeater H
full join ctePower P
on H.[Hour]=P.[Hour]

And the result
Hour WhSum- WhSumHeater
2014-03-18 11:00:00.000- 468- 342
2014-03-18 12:00:00.000- 1019 -618
2014-03-18 13:00:00.000- 1570 -960
2014-03-18 14:00:00.000- 985 -624
2014-03-18 15:00:00.000- 1095 -732
2014-03-18 16:00:00.000- 1227 -868
2014-03-18 17:00:00.000- 1543 -1182
2014-03-18 18:00:00.000- 2077 -1050
2014-03-18 19:00:00.000- 2044 -1352
2014-03-18 20:00:00.000- 1912 -1220
2014-03-18 21:00:00.000- 2055 -1466
2014-03-18 22:00:00.000- 2722 -1904
2014-03-18 23:00:00.000- 2885 -1654
2014-03-19 00:00:00.000- 2367 -2012

Thanx a million times!

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-19 : 06:59:43

your welcome!
Glade to help!

sabinWeb MCP

MatsG
Starting Member

3 Posts

Posted - 2014-03-22 : 07:13:59

If anybody is interested, i then combined another table that contains outside temperature and presented it all with ASP.Net Charts

The real page has tooltip-info and allows me to step back and forth between dates and i plan to be able to select week-view and month, and year eventually when i have enough data.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-22 : 08:12:05

Very impressive!

Congratulation!

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources