Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How To: DataLogDB multiple columns in one query
#1
Wasn't sure how to title it for non-techies, but I needed a way to simplify creating a multi-line chart with my data in DataLogDB.

I have multiple temp sensors all being logged via DataLogDb. Each of these show up as a different row (Timestamp, Field, Value), but in order to make graphing easy I need it in a single row. IE, Time of collection, temp 1, temp 2, temp 3, temp 4, etc.  The DataLogDB collects all data at the same time as i have identical intervals.

Fortunately I remembered enough from being a database dude back in the day that I knew how to do self joins. In my case I have 7 different temp sensors (actually more but this is what I have SQL'ed up so far).

Figured y'all might be interested in this, it's not super hard stuff but if you don't know SQL it would suck to write.

This performs phenomenally fast, so no runaway query issues. And now it'll be trivial to do a multi-line chart against it, as I can declare the first column as the label, and data series is in columns 2-8.

Quote:set nocount on;

select a.Timestamp, 
a.Value AS TempAmara, 
b.Value AS TempAtticEquip, 
c.Value AS TempAtticOuter, 
d.Value AS TempDen, 
e.Value AS TempDining, 
f.Value AS TempEntryKP, 
G.Value AS TempPantryKP

from CQC.dbo.DataLogValues A
inner join CQC.dbo.DataLogValues B
ON a.TimeStamp = b.TimeStamp
INNer join CQC.dbo.DataLogValues C
ON a.TimeStamp = C.TimeStamp
INNER join CQC.dbo.DataLogValues D 
ON a.TimeStamp = D.TimeStamp
INNER join CQC.dbo.DataLogValues E
ON a.TimeStamp = E.TimeStamp
INNER join CQC.dbo.DataLogValues F 
ON a.TimeStamp = F.TimeStamp
INNER join CQC.dbo.DataLogValues G
ON a.TimeStamp = G.TimeStamp

where a.Descriptor='Temp-Amara'
and b.Descriptor = 'Temp-AtticEquip'
and c.Descriptor = 'Temp-AtticOuter'
and d.Descriptor = 'Temp-Den'
and E.Descriptor = 'Temp-Dining'
and F.Descriptor = 'Temp-EntryKP'
and G.Descriptor = 'Temp-PantryKP'

when you call sqlcmd from a command line make sure you specify the column separator. IE, -s "," to parse the columsn right.
------------------------------------
Devices I'm phasing out: ISY, NuVo
My vlogs: https://www.youtube.com/c/IVBsHomeAutomation
Reply


Possibly Related Threads...
Thread Author Replies Views Last Post
  create a grid of temp values from DataLogDB IVB 6 3,055 12-13-2015, 09:37 PM
Last Post: IVB
  How to get the DataLogdb driver up and running Zanthic 20 21,386 01-17-2013, 03:20 PM
Last Post: Dean Roddey

Forum Jump:


Users browsing this thread: 1 Guest(s)