2

I have a table with the information below, this is a small subset of a much larger dataset (about 100k rows) Each row is an incoming request, what i am trying to do is get the correct query to allow me to show the data in a line chart as a total amount of requests per hour, using the start time. not all hours are present in the dataset but will still need to be represented as 0. The eventual goal is to display on a google visualisation line chart. 

    ID  Duration    Store   Start                   End                     MacID   
    7   31          1       2013-06-08 07:46:10.000 2013-06-08 08:17:00.000 8532
    4   2           1       2013-06-08 18:42:53.000 2013-06-08 18:44:06.000 8530
    2   1           1       2013-06-08 14:31:20.000 2013-06-08 14:32:08.000 8529    
    11  213         1       2013-06-08 12:43:55.000 2013-06-08 16:16:11.000 8534
    6   585         1       2013-06-08 14:03:58.000 2013-06-08 23:48:44.000 8531
    28  287         1       2013-06-08 07:15:40.000 2013-06-08 12:02:10.000 8542

The eventual format needs to be 

   ['Hour', 'Total'],
     ['00', 1000 ],
     ['01', 1170 ],
     ['02', 0],
     ['03', 1030]

I am using sql 2012 and entity framework. i have tried the following query 

SELECT CAST(start as date) AS ForDate,
       DATEPART(hour,start) AS OnHour,
       COUNT(*) AS Totals
from (
SELECT * from visits
GROUP BY CAST(start as date),
       DATEPART(hour,start)

However, it only brings back the results for hours that have data. What would be the best way to get the data? SQL or Entity Framework and how would i go about it?

Deza
  • 111
  • 2
  • 9
  • Similar problem: http://stackoverflow.com/q/17086120/861716. Best option is to fill in the empty hours in memory. – Gert Arnold Jun 18 '13 at 14:39
  • Yep makes sense, but what about the query to group the data in the first place, keep is as i have it? or would it be better to do it with EF? – Deza Jun 18 '13 at 14:42
  • Probably with EF, so you're more flexible to modify it from C# source code. – Gert Arnold Jun 18 '13 at 14:46

1 Answers1

0

Can you try this,

visits.Where(u=> u.Start != null).GroupBy(u => u.Start.GetValueOrDefault().Hour).Select(g => new { OnHour = g.Key, Totals = g.Count() })

And Sql Query;

SELECT COUNT(*) AS [Totals], [t1].[value] AS [OnHour]
FROM (
    SELECT DATEPART(Day, COALESCE([t0].[LASTCONNECTIONTIME],'1.1.0001 00:00:00')) AS [value], [t0].[Start]
    FROM [visits] AS [t0]
    ) AS [t1]
WHERE [t1].[Start] IS NOT NULL
GROUP BY [t1].[value]
ramazanulucay
  • 186
  • 9