CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Briskstar Technologies
Briskstar Technologies

Reputation: 2251

List of count for each date

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.

Upvotes: 0

Views: 100

Answers (2)

Lamak
Lamak

Reputation: 70638

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'

Upvotes: 2

paparazzo
paparazzo

Reputation: 45096

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

Upvotes: 1

Related Questions