Charmed Quark Systems, Ltd. - Support Forums and Community

Full Version: How To: DataLogDB multiple columns in one query
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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.