List of count for each date

Question

I have specific scenario to show list of count for each date. Below are query and what I tried.

I need datewise count for each day using below condition. (Count less then date)

 SELECT COUNT(*) 
   FROM Queues (nolock)
  WHERE InsDateTime < '2014-05-27'

Now, I need to use UNION if I need it for multiple days like below.

 SELECT COUNT(*) 
   FROM Queues (nolock)
  WHERE InsDateTime < '2014-05-27'

  UNION

 SELECT COUNT(*) 
   FROM Queues (nolock)
  WHERE InsDateTime < '2014-05-26'

And So on..

I need count for each day dynamically with above condition.. I am confused from where to start. As of now I am using union for multiple days to get output.

You should learn how to use `GROUP BY` clause. http://msdn.microsoft.com/en-us/library/ms177673.aspx — mordack550, May 28 '14 at 15:27
Does `InsDateTime` include a time? If so this would affect answers that use `GROUP BY InsDateTime` — Kevin Hogg, May 28 '14 at 15:27
I know GROUP BY - Thats why posted question due to confusion in query. — k-s, May 28 '14 at 15:28
Well your query can be resolved easily by using the GROUP BY clause... Blam's answer can give you the solution, unless we did not understand correcly what you want as output. — mordack550, May 28 '14 at 15:31

score 2 · Accepted Answer · answered May 28 '14 at 15:23

So, assuming that for instance you want those calculation for every day starting on May first:

SELECT  A.InsDateTime,
        B.N
FROM (  SELECT DISTINCT CONVERT(DATE,InsDateTime) InsDateTime
        FROM dbo.Queues) A
OUTER APPLY (SELECT COUNT(*) N
             FROM dbo.Queues
             WHERE InsDateTime < A.InsDateTime) B
WHERE A.InsDateTime >= '20140501'

score 1 · Answer 2 · answered May 28 '14 at 15:23

1

select d1.dt, count() 
  from queries d1 
  join queries d2
    on d2.dt < d1.dt 
   and d1.dt in ('2014-05-27', '2014-05-26')
 group by d1.dt 
 order by d1.dt

answered May 28 '14 at 15:23

paparazzo

44,497
23
105
176

List of count for each date

2 Answers2