CODEWITHSUNDEEP
sqlsql-server-2008
Naveen
Naveen

Reputation: 124

Multiple Counts from the same row with range

I am trying to get number of transactions within the week given start date and end date.The below query works fine for one day 2011-10-14

SELECT COUNT(operationId) AS trans
  FROM hwfg_t_Tracking 
  WHERE hitTime BETWEEN '2011-10-14 00:00:00' AND '2011-10-14 23:59:59.99'
GO

How can I get the count on operationId where hitTime between 14,13,12,11,10,9,8(1 week) with the single SELECT statement. Like number of transactions for 2011-10-14 as a column, 2011-10-13 as another column and so on

Upvotes: 1

Views: 299

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657912

Try the following query for one row per day in your specified range:

SELECT cast(hitTime AS date) AS mydate, COUNT(operationId) AS trans
FROM   hwfg_t_Tracking
WHERE  hitTime >= '2011-10-14 00:00:00' AND hitTime < '2011-10-21 00:00:00'
GROUP  BY cast(hitTime AS date)

Or, if you want them all in one row:

SELECT COUNT(*) AS total_sum
      ,COUNT(CASE WHEN cast(dt As date) = '2011-10-14' THEN 1 ELSE NULL END) AS day14
      ,COUNT(CASE WHEN cast(dt As date) = '2011-10-15' THEN 1 ELSE NULL END) AS day15
      ,COUNT(CASE WHEN cast(dt As date) = '2011-10-16' THEN 1 ELSE NULL END) AS day16
-- etc.
FROM   hwfg_t_Tracking
WHERE  hitTime >= '2011-10-14 00:00:00' AND hitTime < '2011-10-21 00:00:00'

Upvotes: 2

Related Questions