Charmed Quark Systems, Ltd. - Support Forums and Community
How To: DataLogDB multiple columns in one query - Printable Version

+- Charmed Quark Systems, Ltd. - Support Forums and Community (https://www.charmedquark.com/vb_forum)
+-- Forum: Miscellaneous (https://www.charmedquark.com/vb_forum/forumdisplay.php?fid=7)
+--- Forum: How-To (https://www.charmedquark.com/vb_forum/forumdisplay.php?fid=20)
+--- Thread: How To: DataLogDB multiple columns in one query (/showthread.php?tid=10515)



How To: DataLogDB multiple columns in one query - IVB - 02-11-2018

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.