SQLTeam.com | Weblogs | Forums

Can SQL do this?


#1

Greetings All,
I am working with a small DB. This is industrial data acquired from different sensors. There are 11 different tags (column labeled "Tagindex"). Each record contains one of the 11 Tagindex numbers. The data includes a timestamp and the collected data.
The report I can get is pulling the daily average, daily max and daily min for that data all within a selected time range (usually one month). This report contains 5 columns. (Timestamp, max, min, avg and Tagindex.)
What I would like to achieve is a report that looks like this;
Tag1 time Tag1 Max Tag1 Min Tag1 Avg Tag2 Time Tag2 Max Tag2 Min. Etc.....
So instead of 5 columns I will have 44. Tag#, Max, Min and Avg for each Tagindex number.

Is there a way to do this?

Thanks in advance for your thoughts


#2

Sorry, Time, Max, Min and Avg for each Tagindex number.


#3

Rather vague, and no sample data. Wouldn't the "time" for each tag be the same, that day? At any rate, maybe this will get you close at least:

SELECT 
    DATEADD(DAY, DATEDIFF(DAY, 0, datetime), 0) AS Day,
    MAX(CASE WHEN tag = 'tag1' THEN value END) AS tag1_max,
    MIN(CASE WHEN tag = 'tag1' THEN value END) AS tag1_min,
    AVG(CASE WHEN tag = 'tag1' THEN value END) AS tag1_avg,
    MAX(CASE WHEN tag = 'tag2' THEN value END) AS tag2_max,
    MIN(CASE WHEN tag = 'tag2' THEN value END) AS tag2_min,
    AVG(CASE WHEN tag = 'tag2' THEN value END) AS tag2_avg   
FROM tablename
WHERE
    datetime >= '20150801' AND
    datetime < '20150901'
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, datetime), 0)

#4

Thank you for responding. Yes you are right. Since I'm not dealing with the raw data, just summaries for each day, The "Time" will be just a date.

I'll try and apply this to my situation. I'll get back to you with some code and results.

I really appreciate your help!

Thanks

Glen


#5

I won't be testing this until Monday, but here is what it looks like so far.

SELECT convert(char(10),HistoricalData.DateAndTime,120) AS 'Groupby date', Max(HistoricalData.DateAndTime) AS 'Timestamp',

MAX(CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END) AS PTL-3 Max Temp,
MIN(CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END) AS PTL-3 Min Temp,
AVG(CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END) AS PTL-3 Avg Temp,
MAX(CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END) AS PTL-2 Max Temp,
MIN(CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END) AS PTL-2 Min Temp,
AVG(CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END) AS PTL-3 Avg Temp, MAX(CASE WHEN (HistoricalData.TagIndex) = '2' THEN (HistoricalData.Val/10) END) AS PCW 4 Max PSI,
MIN(CASE WHEN (HistoricalData.TagIndex) = '2' THEN (HistoricalData.Val/10) END) AS PCW 4 Min PSI,
AVG(CASE WHEN (HistoricalData.TagIndex) = '2' THEN (HistoricalData.Val/10) END) AS PCW 4 Avg PSI, MAX(CASE WHEN (HistoricalData.TagIndex) = '3' THEN (HistoricalData.Val/10) END) AS PCW 2 Max PSI,
MIN(CASE WHEN (HistoricalData.TagIndex) = '3' THEN (HistoricalData.Val/10) END) AS PCW 2 Min PSI,
AVG(CASE WHEN (HistoricalData.TagIndex) = '3' THEN (HistoricalData.Val/10) END) AS PCW 2 Avg PSI, MAX(CASE WHEN (HistoricalData.TagIndex) = '4' THEN (HistoricalData.Val/10) END) AS tag4 _max,
MIN(CASE WHEN (HistoricalData.TagIndex) = '4 ' THEN (HistoricalData.Val/10) END) AS tag4 _min,
AVG(CASE WHEN (HistoricalData.TagIndex) = '4' THEN (HistoricalData.Val/10) END) AS tag4 _Avg,
MAX(CASE WHEN (HistoricalData.TagIndex) = '5' THEN (HistoricalData.Val/10) END) AS tag5 _max,
MIN(CASE WHEN (HistoricalData.TagIndex) = '5' THEN (HistoricalData.Val/10) END) AS tag5 _min,
AVG(CASE WHEN (HistoricalData.TagIndex) = '5' THEN (HistoricalData.Val/10) END) AS tag5 _Avg,
MAX(CASE WHEN (HistoricalData.TagIndex) = '6' THEN (HistoricalData.Val/10) END) AS PTL-2 Max Hum,
MIN(CASE WHEN (HistoricalData.TagIndex) = '6' THEN (HistoricalData.Val/10) END) AS PTL-2 Min Hum,
AVG(CASE WHEN (HistoricalData.TagIndex) = '6' THEN (HistoricalData.Val/10) END) AS PTL-2 Avg Hum,
MAX(CASE WHEN (HistoricalData.TagIndex) = '7' THEN (HistoricalData.Val/10) END) AS PTL-3 Max Hum,
MIN(CASE WHEN (HistoricalData.TagIndex) = '7' THEN (HistoricalData.Val/10) END) AS PTL-3 Min Hum,
AVG(CASE WHEN (HistoricalData.TagIndex) = '7' THEN (HistoricalData.Val/10) END) AS PTL-3 Avg Hum,
MAX(CASE WHEN (HistoricalData.TagIndex) = '8' THEN (HistoricalData.Val/10) END) AS CDA Max PSI,
MIN(CASE WHEN (HistoricalData.TagIndex) = '8' THEN (HistoricalData.Val/10) END) AS CDA Min PSI,
AVG(CASE WHEN (HistoricalData.TagIndex) = '8' THEN (HistoricalData.Val/10) END) AS CDA Avg PSI,
MAX(CASE WHEN (HistoricalData.TagIndex) = '9' THEN (HistoricalData.Val/10) END) AS City Max pH,
MIN(CASE WHEN (HistoricalData.TagIndex) = '9' THEN (HistoricalData.Val/10) END) AS City Min pH,
AVG(CASE WHEN (HistoricalData.TagIndex) = '9' THEN (HistoricalData.Val/10) END) AS City Avg pH,
MAX(CASE WHEN (HistoricalData.TagIndex) = '10' THEN (HistoricalData.Val/10) END) AS CWS B Max Temp,
MIN(CASE WHEN (HistoricalData.TagIndex) = '10' THEN (HistoricalData.Val/10) END) AS CWS B Min Temp,
AVG(CASE WHEN (HistoricalData.TagIndex) = '10' THEN (HistoricalData.Val/10) END) AS CWS B Avg Temp,
MAX(CASE WHEN (HistoricalData.TagIndex) = '11' THEN (HistoricalData.Val/10) END) AS CWS A Max Temp,
MIN(CASE WHEN (HistoricalData.TagIndex) = '11' THEN (HistoricalData.Val/10) END) AS CWS A Min Temp,
AVG(CASE WHEN (HistoricalData.TagIndex) = '11' THEN (HistoricalData.Val/10) END) AS CWS A Avg Temp

FROM rsview.dbo.HistoricalData HistoricalData
WHERE (HistoricalData.DateAndTime>?) AND (HistoricalData.DateAndTime<?) AND (HistoricalData.Val <>0)
GROUP BY convert(char(10), HistoricalData.DateAndTime,120)
ORDER BY convert(char(10), HistoricalData.DateAndTime,120)


#6

For performance, GROUP BY just the original column HistoricalData.DateAndTime. Don't do the "cutesy" formatting for output until after the roll up has completed. That is, make the main rollup query a subquery/derived tabl,e and do the formatting in an outer query.


#7

OK, I'll try that. This crashed in Excel. The Error reads
"[Microsoft][SQL Server Native Client 11.0] [SQL Server]Incorrect syntax near '''.

I've reduced the code to try to figure out where the problem is. So far I've spent a couple hours but have not come upon it.

SELECT convert(char(10),HistoricalData.DateAndTime,120) AS ‘Groupby date’, Max(HistoricalData.DateAndTime) AS ‘Timestamp’,
MAX(CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END) AS PTL-3 Max Temp
FROM rsview.dbo.HistoricalData HistoricalData
WHERE (HistoricalData.DateAndTime>?) AND (HistoricalData.DateAndTime<?) AND (HistoricalData.Val <>0)
GROUP BY convert(char(10), HistoricalData.DateAndTime,120)
ORDER BY convert(char(10), HistoricalData.DateAndTime,120)


#8

I thought I could figure out what you are telling me here. The deeper I dug the less I understand. Let's see what I understand.

  1. In my Group By statement drop the "convert" statement.

  2. That's about it.

What I don't understand

  1. "after the roll up has completed" I find a "rollup" statement that is used to summarize data, but I'm don't see how to use it here.

  2. I can probably figure out how to use a subquery but a derived table with an outer query is throwing me.


#9

Sorry, I was using "rollup" generically as in the use of all the max and min statements. I was thinking of something like this:

SELECT Convert(varchar(10), [Groupby date derived], 120) AS [Groupby date],
    [Timestamp],
    [PTL-3 Max Temp],
    [PTL-3 Min Temp],
    [PTL-3 Avg Temp], 
    [PTL-2 Max Temp],
    [PTL-2 Min Temp],
    [PTL-3 Avg Temp], 
    [PCW 4 Max PSI],
    [PCW 4 Min PSI],
    [PCW 4 Avg PSI], 
    [PCW 2 Max PSI],
    [PCW 2 Min PSI],
    [PCW 2 Avg PSI], 
    [tag4 _max],
    [tag4 _min],
    [tag4 _Avg],
    [tag5 _max],
    [tag5 _min],
    [tag5 _Avg],
    [PTL-2 Max Hum],
    [PTL-2 Min Hum],
    [PTL-2 Avg Hum],
    [PTL-3 Max Hum],
    [PTL-3 Min Hum],
    [PTL-3 Avg Hum],
    [CDA Max PSI],
    [CDA Min PSI],
    [CDA Avg PSI],
    [City Max pH],
    [City Min pH],
    [City Avg pH],
    [CWS B Max Temp],
    [CWS B Min Temp],
    [CWS B Avg Temp],
    [CWS A Max Temp],
    [CWS A Min Temp],
    [CWS A Avg Temp]
FROM (
    SELECT 
    DATEADD(DAY, DATEDIFF(DAY, 0, HistoricalData.DateAndTime), 0) AS 'Groupby date derived', 
    Max(HistoricalData.DateAndTime) AS 'Timestamp',
    MAX(CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Max Temp',
    MIN(CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Min Temp',
    AVG(CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Avg Temp', 
    MAX(CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Max Temp',
    MIN(CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Min Temp',
    AVG(CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Avg Temp', 
    MAX(CASE WHEN (HistoricalData.TagIndex) = '2' THEN (HistoricalData.Val/10) END) AS 'PCW 4 Max PSI',
    MIN(CASE WHEN (HistoricalData.TagIndex) = '2' THEN (HistoricalData.Val/10) END) AS 'PCW 4 Min PSI',
    AVG(CASE WHEN (HistoricalData.TagIndex) = '2' THEN (HistoricalData.Val/10) END) AS 'PCW 4 Avg PSI', 
    MAX(CASE WHEN (HistoricalData.TagIndex) = '3' THEN (HistoricalData.Val/10) END) AS 'PCW 2 Max PSI',
    MIN(CASE WHEN (HistoricalData.TagIndex) = '3' THEN (HistoricalData.Val/10) END) AS 'PCW 2 Min PSI',
    AVG(CASE WHEN (HistoricalData.TagIndex) = '3' THEN (HistoricalData.Val/10) END) AS 'PCW 2 Avg PSI', 
    MAX(CASE WHEN (HistoricalData.TagIndex) = '4' THEN (HistoricalData.Val/10) END) AS 'tag4 _max',
    MIN(CASE WHEN (HistoricalData.TagIndex) = '4 ' THEN (HistoricalData.Val/10) END) AS 'tag4 _min',
    AVG(CASE WHEN (HistoricalData.TagIndex) = '4' THEN (HistoricalData.Val/10) END) AS 'tag4 _Avg',
    MAX(CASE WHEN (HistoricalData.TagIndex) = '5' THEN (HistoricalData.Val/10) END) AS 'tag5 _max',
    MIN(CASE WHEN (HistoricalData.TagIndex) = '5' THEN (HistoricalData.Val/10) END) AS 'tag5 _min',
    AVG(CASE WHEN (HistoricalData.TagIndex) = '5' THEN (HistoricalData.Val/10) END) AS 'tag5 _Avg',
    MAX(CASE WHEN (HistoricalData.TagIndex) = '6' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Max Hum',
    MIN(CASE WHEN (HistoricalData.TagIndex) = '6' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Min Hum',
    AVG(CASE WHEN (HistoricalData.TagIndex) = '6' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Avg Hum',
    MAX(CASE WHEN (HistoricalData.TagIndex) = '7' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Max Hum',
    MIN(CASE WHEN (HistoricalData.TagIndex) = '7' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Min Hum',
    AVG(CASE WHEN (HistoricalData.TagIndex) = '7' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Avg Hum',
    MAX(CASE WHEN (HistoricalData.TagIndex) = '8' THEN (HistoricalData.Val/10) END) AS 'CDA Max PSI',
    MIN(CASE WHEN (HistoricalData.TagIndex) = '8' THEN (HistoricalData.Val/10) END) AS 'CDA Min PSI',
    AVG(CASE WHEN (HistoricalData.TagIndex) = '8' THEN (HistoricalData.Val/10) END) AS 'CDA Avg PSI',
    MAX(CASE WHEN (HistoricalData.TagIndex) = '9' THEN (HistoricalData.Val/10) END) AS 'City Max pH',
    MIN(CASE WHEN (HistoricalData.TagIndex) = '9' THEN (HistoricalData.Val/10) END) AS 'City Min pH',
    AVG(CASE WHEN (HistoricalData.TagIndex) = '9' THEN (HistoricalData.Val/10) END) AS 'City Avg pH',
    MAX(CASE WHEN (HistoricalData.TagIndex) = '10' THEN (HistoricalData.Val/10) END) AS 'CWS B Max Temp',
    MIN(CASE WHEN (HistoricalData.TagIndex) = '10' THEN (HistoricalData.Val/10) END) AS 'CWS B Min Temp',
    AVG(CASE WHEN (HistoricalData.TagIndex) = '10' THEN (HistoricalData.Val/10) END) AS 'CWS B Avg Temp',
    MAX(CASE WHEN (HistoricalData.TagIndex) = '11' THEN (HistoricalData.Val/10) END) AS 'CWS A Max Temp',
    MIN(CASE WHEN (HistoricalData.TagIndex) = '11' THEN (HistoricalData.Val/10) END) AS 'CWS A Min Temp',
    AVG(CASE WHEN (HistoricalData.TagIndex) = '11' THEN (HistoricalData.Val/10) END) AS 'CWS A Avg Temp'
    FROM rsview.dbo.HistoricalData HistoricalData
    WHERE (HistoricalData.DateAndTime>?) AND (HistoricalData.DateAndTime<?) AND (HistoricalData.Val <>0)
    GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, HistoricalData.DateAndTime), 0)
) AS derived_table
ORDER BY [Groupby date derived]

#10

Wow! Thank you for doing that. I have one question. On the first FROM statement you have a "(". Does it need a ")" after the last "CASE" statement?


#11

The "FROM (" is closed by ") AS derived_table".


#12

Hello again Scott,
I ran this this afternoon. I got the data columns but not the date or Timestamp columns. any thoughts?


#13

I think I may have found the issue. I won't have time to test it until this afternoon, but the first select statement reads....
SELECT Convert(varchar(10), [Groupby date derived], 120) AS [Groupby date],

The 120 CONVERT style does not match the 10 character varchar data type. I believe 110 will work.
Am I on the right track?


#14

Yes, that gives you mm/dd/yyyy rather than yyyy-mm-dd. [I copied the "120" from your post, but any format code should be fine there.]


#15

Yea that didn't fix it. I still am not getting any dates. I'm getting data columns, just no date or timestamp.


#16

Could it be that this statement;
SELECT Convert(varchar(10), [Groupby date derived], 110) AS [Groupby date],

should read;
SELECT Convert(varchar(10), [HistoricalData.DateAndTime] AS [Groupby date derived], ?


#17

Try running the inner query by itself, the one with the GROUP BY. Also, I have no idea what the ?s are being replaced with. Maybe that is causing row(s) to be missed.


#18

I finally got it to work! Thank you for your help and support. You are awesome.
Here's what I ended up with.
By the way, the "?"s are parameters referencing dates on the Excel spreadsheet. I still have some Excel issues I'm wrestling with, but the data issue is handled.

SELECT convert(char(10),HistoricalData.DateAndTime,110) AS 'Groupby date'
, MAX(CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Max Temp'
, MIN(CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Min Temp'
,AVG(CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Avg Temp'
,MAX(CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Max Temp'
,MIN(CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Min Temp'
,AVG(CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Avg Temp'
,MAX(CASE WHEN (HistoricalData.TagIndex) = '2' THEN (HistoricalData.Val/10) END) AS 'PCW 4 Max PSI'
,MIN(CASE WHEN (HistoricalData.TagIndex) = '2' THEN (HistoricalData.Val/10) END) AS 'PCW 4 Min PSI'
,AVG(CASE WHEN (HistoricalData.TagIndex) = '2' THEN (HistoricalData.Val/10) END) AS 'PCW 4 Avg PSI'
,MAX(CASE WHEN (HistoricalData.TagIndex) = '3' THEN (HistoricalData.Val/10) END) AS 'PCW 2 Max PSI'
,MIN(CASE WHEN (HistoricalData.TagIndex) = '3' THEN (HistoricalData.Val/10) END) AS 'PCW 2 Min PSI'
,AVG(CASE WHEN (HistoricalData.TagIndex) = '3' THEN (HistoricalData.Val/10) END) AS 'PCW 2 Avg PSI'
,MAX(CASE WHEN (HistoricalData.TagIndex) = '4' THEN (HistoricalData.Val/10) END) AS 'tag4 _max'
,MIN(CASE WHEN (HistoricalData.TagIndex) = '4 'THEN (HistoricalData.Val/10) END) AS 'tag4 _min'
,AVG(CASE WHEN (HistoricalData.TagIndex) = '4' THEN (HistoricalData.Val/10) END) AS 'tag4 _Avg'
,MAX(CASE WHEN (HistoricalData.TagIndex) = '5' THEN (HistoricalData.Val/10) END) AS 'tag5 _max'
,MIN(CASE WHEN (HistoricalData.TagIndex) = '5' THEN (HistoricalData.Val/10) END) AS 'tag5 _min'
,AVG(CASE WHEN (HistoricalData.TagIndex) = '5' THEN (HistoricalData.Val/10) END) AS 'tag5 _Avg'
,MAX(CASE WHEN (HistoricalData.TagIndex) = '6' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Max Hum'
,MIN(CASE WHEN (HistoricalData.TagIndex) = '6' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Min Hum'
,AVG(CASE WHEN (HistoricalData.TagIndex) = '6' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Avg Hum'
,MAX(CASE WHEN (HistoricalData.TagIndex) = '7' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Max Hum'
,MIN(CASE WHEN (HistoricalData.TagIndex) = '7' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Min Hum'
,AVG(CASE WHEN (HistoricalData.TagIndex) = '7' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Avg Hum'
,MAX(CASE WHEN (HistoricalData.TagIndex) = '8' THEN (HistoricalData.Val/10) END) AS 'CDA Max PSI'
,MIN(CASE WHEN (HistoricalData.TagIndex) = '8' THEN (HistoricalData.Val/10) END) AS 'CDA Min PSI'
,AVG(CASE WHEN (HistoricalData.TagIndex) = '8' THEN (HistoricalData.Val/10) END) AS 'CDA Avg PSI'
,MAX(CASE WHEN (HistoricalData.TagIndex) = '9' THEN (HistoricalData.Val/10) END) AS 'City Max pH'
,MIN(CASE WHEN (HistoricalData.TagIndex) = '9' THEN (HistoricalData.Val/10) END) AS 'City Min pH'
,AVG(CASE WHEN (HistoricalData.TagIndex) = '9' THEN (HistoricalData.Val/10) END) AS 'City Avg pH'
,MAX(CASE WHEN (HistoricalData.TagIndex) = '11' THEN (HistoricalData.Val/10) END) AS 'CWS A Max Temp'
,MIN(CASE WHEN (HistoricalData.TagIndex) = '11' THEN (HistoricalData.Val/10) END) AS 'CWS A Min Temp'
,AVG(CASE WHEN (HistoricalData.TagIndex) = '11' THEN (HistoricalData.Val/10) END) AS 'CWS A Avg Temp'
,MAX(CASE WHEN (HistoricalData.TagIndex) = '10' THEN (HistoricalData.Val/10) END) AS 'CWS B Max Temp'
,MIN(CASE WHEN (HistoricalData.TagIndex) = '10' THEN (HistoricalData.Val/10) END) AS 'CWS B Min Temp'
,AVG(CASE WHEN (HistoricalData.TagIndex) = '10' THEN (HistoricalData.Val/10) END) AS 'CWS B Avg Temp'

FROM rsview.dbo.HistoricalData HistoricalData
WHERE (HistoricalData.DateAndTime>=?) AND (HistoricalData.DateAndTime<?) AND (HistoricalData.Val <>0)
GROUP BY convert(char(10), HistoricalData.DateAndTime,110)
ORDER BY convert(char(10), HistoricalData.DateAndTime,110)