Can SQL do this?

Grayco · September 18, 2015, 9:13pm

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

Grayco · September 18, 2015, 9:17pm

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

ScottPletcher · September 18, 2015, 9:41pm

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)

Grayco · September 18, 2015, 11:19pm

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

Grayco · September 19, 2015, 2:23pm

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)

ScottPletcher · September 21, 2015, 3:51pm

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.

Grayco · September 21, 2015, 7:54pm

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)

Grayco · September 22, 2015, 2:57pm

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.

In my Group By statement drop the "convert" statement.
That's about it.

What I don't understand

"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.
I can probably figure out how to use a subquery but a derived table with an outer query is throwing me.

ScottPletcher · September 22, 2015, 3:49pm

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]

Grayco · September 22, 2015, 5:05pm

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?

ScottPletcher · September 22, 2015, 5:12pm

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

Grayco · September 22, 2015, 11:09pm

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

Grayco · September 23, 2015, 4:03pm

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?

ScottPletcher · September 23, 2015, 5:32pm

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.]

Grayco · September 23, 2015, 6:47pm

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

Grayco · September 23, 2015, 7:25pm

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], ?

ScottPletcher · September 23, 2015, 7:58pm

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.

Grayco · September 25, 2015, 9:28pm

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)