Announcement

Collapse
No announcement yet.

Help with SQL, ASP and Charts (GROUP BY)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Help with SQL, ASP and Charts (GROUP BY)

    Hello code gurus!

    How can I get hourly, daily, weekly, monthly, yearly, etc data from SQL (MS ACCESS DB)?

    I log to a database (using the mcsTemperature plugin) temperature, humidity and power data every minute and I'm looking for how to get the data from the DB.

    Lets say I want to get the temperature for the last 7 days... the problem I'm finding now is with the "GROUP BY" part...

    Why this query doesn't work?

    Code:
    StartDTime = "01/03/2014 00:01:00 AM"
    FinishDTime = "07/03/2014 11:59:59 PM"
    SQL = "SELECT DatePart(dd,SampleDate) as aDay, AVG(Q12) FROM Temperature WHERE SampleDate BETWEEN #" & StartDTime & "# AND #" & FinishDTime & "# " & "GROUP BY DatePart(dd,SampleDate)"
    I tried endless combinations and I always got some kind of error.
    Last edited by Mario from Spain; March 6th, 2014, 06:06 PM.

  • #2
    One more clue:

    This works:
    Code:
    SQL = "SELECT SampleDate, AVG(Q12) as TempMedia FROM Temperature WHERE SampleDate BETWEEN #" & StartDTime & "# AND #" & FinishDTime & "# " & " GROUP BY SampleDate"
    For:
    StartDTime = "01/01/2014 00:01:00 AM"
    FinishDTime = "01/01/2014 00:59:59 AM"

    It returns:
    01/01/2014 0:03:322030
    01/01/2014 0:04:332030
    01/01/2014 0:05:342030
    01/01/2014 0:06:352030
    01/01/2014 0:07:362030
    01/01/2014 0:08:372030
    01/01/2014 0:09:382030
    01/01/2014 0:10:392030
    01/01/2014 0:11:412030
    01/01/2014 0:12:422030
    01/01/2014 0:13:432030
    01/01/2014 0:14:442020
    01/01/2014 0:15:452020
    01/01/2014 0:16:532020
    01/01/2014 0:17:542020
    01/01/2014 0:18:552020
    01/01/2014 0:19:562020
    01/01/2014 0:20:572020
    01/01/2014 0:21:582020
    01/01/2014 0:22:592020
    01/01/2014 0:24:002020
    01/01/2014 0:25:012020
    01/01/2014 0:26:022020
    01/01/2014 0:27:042020
    01/01/2014 0:28:052020
    01/01/2014 0:29:062020
    01/01/2014 0:30:072020
    01/01/2014 0:31:082020
    01/01/2014 0:32:122020
    01/01/2014 0:33:132020
    01/01/2014 0:34:142020
    01/01/2014 0:35:152020
    01/01/2014 0:36:162020
    01/01/2014 0:37:202020
    01/01/2014 0:38:212020
    01/01/2014 0:39:212020
    01/01/2014 0:40:222020
    01/01/2014 0:41:272020
    01/01/2014 0:42:292020
    01/01/2014 0:43:292020
    01/01/2014 0:44:302020
    01/01/2014 0:45:312020
    01/01/2014 0:46:362010
    01/01/2014 0:47:372010
    01/01/2014 0:48:382010
    01/01/2014 0:49:392010
    01/01/2014 0:50:392010
    01/01/2014 0:51:432010
    01/01/2014 0:52:442010
    01/01/2014 0:53:452010
    01/01/2014 0:54:462010
    01/01/2014 0:55:472010
    01/01/2014 0:56:492010
    01/01/2014 0:57:502010
    01/01/2014 0:58:512010
    01/01/2014 0:59:522010

    This dosn't:
    Code:
    SQL = "SELECT SampleDate, AVG(Q12) as TempMedia FROM Temperature WHERE SampleDate BETWEEN #" & StartDTime & "# AND #" & FinishDTime & "# " & " GROUP BY DatePart(h,SampleDate)"
    It returns the error "
    "You tried to execute a query that does not include the specified expression 'SampleDate' as part of an aggregate function."

    Then I tried:

    Code:
    SQL = "SELECT SampleDate, AVG(Q12) as TempMedia, DatePart(hh,SampleDate) as TheHour FROM Temperature WHERE SampleDate BETWEEN #" & StartDTime & "# AND #" & FinishDTime & "# " & " GROUP BY DatePart(hh,SampleDate)
    and

    Code:
    SELECT SampleDate, AVG(Q12) as TempMedia, DatePart(hh,SampleDate) as TheHour FROM Temperature WHERE SampleDate BETWEEN #" & StartDTime & "# AND #" & FinishDTime & "# " & " GROUP BY TheHour
    but... same error...

    Comment


    • #3
      hmmm

      May be the error isn't with the SQL Query but with the "DatePart" function as this code dosn't works in a ASP page:

      Code:
      dim FinishDTime, Parte
      FinishDTime = "01/01/2014 00:59:59 AM"
      Parte = DatePart(hh, StartDTime)
      I tried also with "StartDTime = "01/01/2014 0:03:32" (same string returned by a valid SQL query) to be sure the problem is not with the date format.

      Comment


      • #4
        I think I have it!!!

        Code:
        SQL = "SELECT AVG(Q12) as TempMedia, DatePart(""d"",Sampledate) as aDate FROM Temperature WHERE SampleDate BETWEEN #" & StartDTime & "# AND #" & FinishDTime & "# " & " GROUP BY DatePart(""d"",Sampledate)"
        Results:

        2123,50964974982---1---
        2089,43531093638---2---
        2017,01428571429---3---
        2073,51851851852---4---
        2130,05801305294---5---
        2118,83435582822---6---
        2041,16780045351---7---
        1995,36926147705---8---
        2048,29510703364---9---
        2099,21413121846---10---
        2087,9415954416---11---
        2059,38091769847---12---
        2055,66220238095---13---
        2056,71783625731---14---
        2053,73744619799---15---
        1978,57142857143---16---
        2035,56899004267---17---
        1951,6678700361---18---
        1963,21981424149---19---
        2042,48091603053---20---
        2070,39705882353---21---
        2028,07547169811---22---
        2081,15796997856---23---
        2065,30410183876---24---
        2045,22334293948---25---
        2045,77304964539---26---
        2050,17692852088---27---
        2053,20028510335---28---
        2032,11882605583---29---
        2025,12747875354---30---
        1979,47368421053---31---

        Looks pretty logical and realistic

        Comment


        • #5
          I managed to convert the results to JSON by using the aspjson library from https://code.google.com/p/aspjson/

          Now I just need to figure out how to pass this data to highcharts. Truly I have no idea how to do it Anyone?

          Code:
          [{"TempMedia":2123.50964974982,"aDate":1},{"TempMedia":2089.43531093638,"aDate":2},{"TempMedia":2017.01428571429,"aDate":3},{"TempMedia":2073.51851851852,"aDate":4},{"TempMedia":2130.05801305294,"aDate":5},{"TempMedia":2118.83435582822,"aDate":6},{"TempMedia":2041.16780045351,"aDate":7},{"TempMedia":1995.36926147705,"aDate":8},{"TempMedia":2048.29510703364,"aDate":9},{"TempMedia":2099.21413121846,"aDate":10},{"TempMedia":2087.9415954416,"aDate":11},{"TempMedia":2059.38091769847,"aDate":12},{"TempMedia":2055.66220238095,"aDate":13},{"TempMedia":2056.71783625731,"aDate":14},{"TempMedia":2053.73744619799,"aDate":15},{"TempMedia":1978.57142857143,"aDate":16},{"TempMedia":2035.56899004267,"aDate":17},{"TempMedia":1951.6678700361,"aDate":18},{"TempMedia":1963.21981424149,"aDate":19},{"TempMedia":2042.48091603053,"aDate":20},{"TempMedia":2070.39705882353,"aDate":21},{"TempMedia":2028.07547169811,"aDate":22},{"TempMedia":2081.15796997856,"aDate":23},{"TempMedia":2065.30410183876,"aDate":24},{"TempMedia":2045.22334293948,"aDate":25},{"TempMedia":2045.77304964539,"aDate":26},{"TempMedia":2050.17692852088,"aDate":27},{"TempMedia":2053.20028510335,"aDate":28},{"TempMedia":2032.11882605583,"aDate":29},{"TempMedia":2025.12747875354,"aDate":30},{"TempMedia":1979.47368421053,"aDate":31}]

          Comment


          • #6
            Finally after many hours of work, trial and error I managed to get a basic Highcharts chart working and it looks fantastic!



            I copy here the code (ASP page) for future reference just in case it's useful for somebody who wants to play with creating charts with Highcharts.

            I think with this skeleton it will be fairly easy to modify it to create any chart (at least basic charts).

            Code:
            <!--#include virtual="JSON_2.0.4.asp"-->
            <!--#include virtual="JSON_UTIL_0.1.1.asp"-->
            <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
            <script src="http://code.highcharts.com/highcharts.js"></script>
            
            
            <% 
            Dim vcode, awaycode 
            response.write hs.GetPageHeader("Temperatures","","",False,False,False,False,False) 
            device = hs.GetDeviceCode("Abuela Temperatura Aeon 4in1") 
            response.write "The device is: " & device & "<br/>"
            %> 
            
            
            <%
            dim oConn, Rs, Parte
            SensorID = device
            dim StartDTime 
            dim FinishDTime 
            dim SampleDate
            StartDTime = "01/01/2014 00:01:00 AM"
            FinishDTime = "01/31/2014 00:59:59 AM"
            
            set oConn=CreateObject("ADODB.Connection")
            oConn.Provider="Microsoft.Jet.OLEDB.4.0"
            oConn.Open "C:\Archivos de Programa\HomeSeer HSPRO\data\mcsTemperature\mcsTemperature.mdb"
            set Rs=CreateObject("ADODB.recordset")
            ' SQL = "SELECT DatePart(""d"",Sampledate) as aDate, AVG(Q12) as TempMedia FROM Temperature WHERE SampleDate BETWEEN #" & StartDTime & "# AND #" & FinishDTime & "# " & " GROUP BY DatePart(""d"",Sampledate)"
            SQL = "SELECT DatePart(""d"",Sampledate) as aDate, AVG(" & device & ") as TempMedia FROM Temperature WHERE SampleDate BETWEEN #" & StartDTime & "# AND #" & FinishDTime & "# " & " GROUP BY DatePart(""d"",Sampledate)"
            Rs.open SQL, oConn, adOpenStatic
            miarray = rs.GetRows()
            
            dim i
            arraycategories = array()
            arrayseries = array()
            
            Response.Write("<br/>")
            
            For i = 0 to ubound(miarray, 2)
              ReDim Preserve arraycategories(UBound(arraycategories) + 1)
              arraycategories(UBound(arraycategories)) = CDbl(trim(miarray(0,i)))
              ReDim Preserve arrayseries(UBound(arrayseries) + 1)
              arrayseries(UBound(arrayseries)) = Round(CDbl(trim(miarray(1,i)))/100 , 1)
            next
            
            
            ' Response.Write toJSON(arraycategories)
            ' Response.Write toJSON(arrayseries)
            
            
            ' Clean up...
            Rs.close
            set Rs=nothing
            set oConn=nothing
            %>
            
            <div id="container" style="width:100%; height:400px;"></div>
            
            <script> 
            $(function () {
                    $('#container').highcharts({
                        chart: {
                            type: 'column'
                        },
                        title: {
                            text: 'Dayly Average Temperature',
                            x: -20 //center
                        },
                        subtitle: {
                            text: 'Source: mcsTemperature',
                            x: -20
                        },
                        xAxis: {
                            categories: <%Response.Write toJSON(arraycategories)%>
                        },
                        yAxis: {
                            title: {
                                text: 'Temperature (C)'
                            },
                            plotLines: [{
                                value: 0,
                                width: 1,
                                color: '#808080'
                            }]
                        },
                        tooltip: {
                            valueSuffix: 'C'
                        },
                        plotOptions: {
                            column: {
                                pointPadding: 0.2,
                                borderWidth: 0
                            }
                        },
                        legend: {
                            layout: 'vertical',
                            align: 'right',
                            verticalAlign: 'middle',
                            borderWidth: 0
                        },
                        series: [{
                            name: 'Abuela',
                            data: <%Response.Write toJSON(arrayseries)%>
                        }]
                    });
                });
                
            </script> 
            
            
            
            </body> 
            </html>

            Comment

            Working...
            X