Timestamp/data problem

Grayco · October 13, 2015, 11:57pm

Greetings everyone I have 12 data points identified as "TagIndex". The code I am attempting to use is

SELECT HistoricalData.DateAndTime, round(HistoricalData.Val,2)
, CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END AS 'PTL-3 Temp'
, CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END AS 'PTL-2 Temp'
, CASE WHEN (HistoricalData.TagIndex) = '2' THEN (HistoricalData.Val/10) END AS 'PCW 4 Temp'
, CASE WHEN (HistoricalData.TagIndex) = '3' THEN (HistoricalData.Val) END AS 'PCW 4 PSI'
, CASE WHEN (HistoricalData.TagIndex) = '4' THEN (HistoricalData.Val) END AS 'PCW 2 PSI'
, CASE WHEN (HistoricalData.TagIndex) = '5' THEN (HistoricalData.Val) END AS 'PCW 2 Temp'
, CASE WHEN (HistoricalData.TagIndex) = '6' THEN (HistoricalData.Val/10) END AS 'PTL-2 Hum'
, CASE WHEN (HistoricalData.TagIndex) = '7' THEN (HistoricalData.Val/10) END AS 'PTL-3 Hum'
, CASE WHEN (HistoricalData.TagIndex) = '8' THEN (HistoricalData.Val) END AS 'CDA PSI'
, CASE WHEN (HistoricalData.TagIndex) = '9' THEN (HistoricalData.Val/100 END AS 'City pH'
, CASE WHEN (HistoricalData.TagIndex) = '12' THEN (HistoricalData.Val/10 END AS 'CWS A Temp'
, CASE WHEN (HistoricalData.TagIndex) = '10' THEN (HistoricalData.Val) END AS 'CWS B Temp'

FROM rsview.dbo.HistoricalData HistoricalData
WHERE (HistoricalData.DateAndTime>?) AND (HistoricalData.DateAndTime<?) AND (HistoricalData.Val <>0)

The code runs but the results are not usable. It seems each TagIndex number records at different times, seconds apart. So instead of nice rows of data (maybe 10,000 records) I have over 200K lines filled mostly with air.

I put a picture of it here: https://boatcoating.shutterfly.com/pictures/14
I seems to me I need to run this so I have a timestamp column and a data column for each TagIndex number.

Is there a good way to change my code to obtain a timestamp and data for each of the TagIndex numbers?

khtan · October 14, 2015, 2:04am

Not sure about the nature of the data, here i use MAX () , you can change to SUM () if you want to have a total by

SELECT HistoricalData.DateAndTime, --round(HistoricalData.Val,2)
, MAX(CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Temp'
, MAX(CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Temp'
, MAX(CASE WHEN (HistoricalData.TagIndex) = '2' THEN (HistoricalData.Val/10) END) AS 'PCW 4 Temp'
, MAX(CASE WHEN (HistoricalData.TagIndex) = '3' THEN (HistoricalData.Val) END) AS 'PCW 4 PSI'
, MAX(CASE WHEN (HistoricalData.TagIndex) = '4' THEN (HistoricalData.Val) END) AS 'PCW 2 PSI'
, MAX(CASE WHEN (HistoricalData.TagIndex) = '5' THEN (HistoricalData.Val) END) AS 'PCW 2 Temp'
, MAX(CASE WHEN (HistoricalData.TagIndex) = '6' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Hum'
, MAX(CASE WHEN (HistoricalData.TagIndex) = '7' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Hum'
, MAX(CASE WHEN (HistoricalData.TagIndex) = '8' THEN (HistoricalData.Val) END) AS 'CDA PSI'
, MAX(CASE WHEN (HistoricalData.TagIndex) = '9' THEN (HistoricalData.Val/100 END) AS 'City pH'
, MAX(CASE WHEN (HistoricalData.TagIndex) = '12' THEN (HistoricalData.Val/10 END) AS 'CWS A Temp'
, MAX(CASE WHEN (HistoricalData.TagIndex) = '10' THEN (HistoricalData.Val) END) AS 'CWS B Temp'

FROM rsview.dbo.HistoricalData HistoricalData
WHERE (HistoricalData.DateAndTime>?) AND (HistoricalData.DateAndTime<?) AND (HistoricalData.Val <>0)
GROUP BY HistoricalData.DateAndTime

Grayco · October 14, 2015, 4:19am

Thanks khtan! What is the MAX going to do?

khtan · October 14, 2015, 4:21am

if using MAX(), it will show only one value per DateAndTime,

if using SUM(), it will total up all the value

Grayco · October 14, 2015, 12:33pm

The problem I'm facing is that I'm currently only getting one value per TimeAndDate. Each TagIndex is being sampled at different times. Some times it's only seconds apart and sometimes it's longer. The result is that each TimeAndDate is being reported and the tagIndex which was not reported at that time shows blank.

I'll try this this morning and see if it solves the problem. I'm probably not understanding what the result will be.

Thanks again for your response.
Glen

JeffModen · October 14, 2015, 1:18pm

What khtan wrote is known as a classic "CROSSTAB" and is used to summarize and pivot data. He explained the difference between using MAX and SUM (MAX is used for non-numeric values, SUM is used for numeric values). It's also a bit faster than PIVOT as it is and can easily be nearly twice as fast if you learn the technic known as "Pre-Aggregation" (phrase coined by Peter Larsson). For more information on it all, please see the following articles, the first being on the basics of CrossTabs and the second being how to easily create them dynamically..

There are several of other things that I did in the code.

The use of single quotes around column alias names has been
deprecated so I replaced all of those with the much preferred
brackets.
I removed a lot of the horizontal clutter by using a much shorter
but still relevant table alias.
Ragged right column names are difficult to read when trouble
shooting so I moved them all to the left and squared up the code to
increase readability and to make "vertical copy" possible in the
future. Of course, changing unformatted code takes longer than it
does to simply do it that way from the git.
I also assumed that you wanted only whole days of data to be
returned and so I modified the parameters in the WHERE clause to
modify the input parameters using the preferred method.
Not using a semi-colon as a statement terminator has also been
deprecated so I added one.

With all that in mind, I've converted khtan's good code based on what I saw in the graphic that you provided a link to. Here's the code.

 SELECT  hd.DateAndTime
        ,[PTL-3 Temp]   = SUM(CASE WHEN (hd.TagIndex) =  '0' THEN (hd.Val/10)   END)
        ,[PTL-2 Temp]   = SUM(CASE WHEN (hd.TagIndex) =  '1' THEN (hd.Val/10)   END)
        ,[PCW 4 Temp]   = SUM(CASE WHEN (hd.TagIndex) =  '2' THEN (hd.Val/10)   END)
        ,[PCW 4 PSI]    = SUM(CASE WHEN (hd.TagIndex) =  '3' THEN (hd.Val)      END)
        ,[PCW 2 PSI]    = SUM(CASE WHEN (hd.TagIndex) =  '4' THEN (hd.Val)      END)
        ,[PCW 2 Temp]   = SUM(CASE WHEN (hd.TagIndex) =  '5' THEN (hd.Val)      END)
        ,[PTL-2 Hum]    = SUM(CASE WHEN (hd.TagIndex) =  '6' THEN (hd.Val/10)   END)
        ,[PTL-3 Hum]    = SUM(CASE WHEN (hd.TagIndex) =  '7' THEN (hd.Val/10)   END)
        ,[CDA PSI]      = SUM(CASE WHEN (hd.TagIndex) =  '8' THEN (hd.Val)      END)
        ,[City pH]      = SUM(CASE WHEN (hd.TagIndex) =  '9' THEN (hd.Val/100)  END)
        ,[CWS A Temp]   = SUM(CASE WHEN (hd.TagIndex) = '12' THEN (hd.Val/10)   END)
        ,[CWS B Temp]   = SUM(CASE WHEN (hd.TagIndex) = '10' THEN (hd.Val)      END)
   FROM rsview.dbo.HistoricalData hd
  WHERE hd.DateAndTime >= DATEADD(dd,DATEDIFF(dd, 0,?),0)
    AND hd.DateAndTime <  DATEADD(dd,DATEDIFF(dd,-1,?),0) --Less than the next day to include all times
    AND hd.Val <> 0
  GROUP BY hd.DateAndTime
;

Last but certainly not least, the use of 3 and 4 part naming conventions in the code is a really bad idea because if the database is ever moved to another server, you have to go through all of the code and change it. It's much better to use a 2 part naming convention and then use a synonym or pass-through view to create the 2 part reference to the 3 or 4 part object. I didn't make that change to your code but strongly suggest hat you do.

Grayco · October 14, 2015, 4:24pm

Your code is certainly much better looking than my messy clutter. I really like the way you create the column headings and then define them. That's much better than creating an alias.

You mentioned that this type code is for summarization. I'm trying to retrieve all the data for each TagIndex number.

Each DateAndTime value is only associated to one TagIndex number's record. So when I report a DateAndTime value, there are 10 columns in the report that do not have a "Val" value associated to that DateAndTime value.

This report is pulling one month's data. Some of the TagIndex values are collected every few seconds and some, every few minutes. I don't think I can report all the TagIndex numbers from a single column of DateAndTime values.

I think what I need is 2 columns for each TagIndex number. The first needs to be the DateAndTime value and the second needs to be the Val value that is associated to that DateAndTime value.

Your code ran and produced about half the total rows but I am still looking at a mass of blank cells.

I Tried this type approach but I still gat mostly blank cells.
SELECT
, (CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.DateAndTime) END) AS 'DAT0'
, (CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Temp'

, (CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.DateAndTime) END) AS 'DAT1'
, (CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Temp'
ETC ETC ETC.......

Grayco · October 14, 2015, 5:03pm

Here is code that retrieves all the data for any one TagIndex number.

SELECT HistoricalData.DateAndTime, round(HistoricalData.Val/10,2)
FROM rsview.dbo.HistoricalData HistoricalData
WHERE (HistoricalData.DateAndTime>?) AND (HistoricalData.DateAndTime<?) AND (HistoricalData.TagIndex=?) and (HistoricalData.Val <>0)

If I were to create 11 connections in Excel, fix the TagIndex numbers in each and have one file launch the next, I think it could work.

Is there a command that will do this?

Grayco · October 14, 2015, 5:07pm

I just realized, if I create 11 files, I can just "REFRESH ALL". They don't need to be triggered by another file.

JeffModen · October 17, 2015, 10:50pm

To be honest, I have no idea what you're talking about in your last two posts.

Also, the code you posted 3 posts above doesn't have the aggregations that I included in my code. Despite the fact that we're using aggregates, they will resolve to correctly pivoting multiple rows to just one row without all of the blanks you speak of if the object of the GROUP BY is unique. And, don't forget that if you have missing values, expect empty "cells" in the output.

Grayco · October 18, 2015, 4:51pm

Thanks again for your reply Jeff, I am an "AS NEEDED" user of SQL, so please forgive my inability to explain things clearly.

This code is accessing one table and selecting three columns.

DateAndTime. This is a timestamp that is recorded everytime the data acquisition system retrieves a value from a PLC.
TagIndex. There are 11 sensors that we are logging data from. They are identified as numbers 1,2,3,4,5,6,7,8,9,10 and12
Val. The data acquisition system logs data from each of the TagIndex sensors and stores it in the val column.

Each TagIndex number is being logged at a unique DateAndTime moment. As a result any given DateAndTime (timestamp) will only find a [Val] for one TagIndex. When I reported all the TagIndex records in a single report, I retrieved mostly blank spaces, because each timestamp only has one of eleven TagIndex [Val} values logged.

My solution; I created eleven different codes. One for each TagIndex number. It turned out six TagIndex numbers reported over 8600 values each. The rest ranged between 14000 and 78000 records.

I'm accessing the data through Excel 2010 (with Microsoft Query) There is in the Excel program the ability to Refresh an individual SQL code (refered to as a "Connection" in Excel) or you can choose to "Refresh All" which runs each "Connection" in the Excel file.

I was hoping to find a single code that would replicate the results of the eleven files I created.

For now this works. It is not elegant or efficient It takes about 30 seconds for the total of 12 files to refresh.

I am using the data for a monthly report that includes 11 individual graphs that display Max, Min and Average data for the 11 TagIndex numbers. I also included a dynamic graph that changes datasets by moving an ActiveX slidebar. This is where I show the actual data with 3 sigma and alarm limits.

Thank You again for your time and inputs on this.
Glen