asked on

SQL, Date, Daily Row Count and Total Accumulated Row Count

I need a Stored Procedure to return 'Date, Daily Row Count and Total Accumulated Row Count'?
So I thought this must be a simple common functionality and I could just find something on Google to modify???

I have a table with new signups daily, capturing the CID and CreateTime. Of course the boss wants to know how many each day and how many in total. I have not been able to get "Group By Date" to work, I think, however I Cast, Convert, Trim, or Format SQL sees each smallDateTime as a "new different Group" due to the time portion of smallDateTime, I think I got smallDateTime Converted to just a Date, but then I can't use the alias in the Group By clause. Hmmm?

Table-
CID I smallDateTime
1 I 2015-04-28 06:33:00
2 I 2015-04-28 06:34:00
3 I 2015-04-28 06:36:00
4 I 2015-04-29 06:00:00
5 I 2015-04-29 06:02:00
6 I 2015-04-30 05:00:00

Result(desired)
Date I Count I Total
2015-04-28 I 3 I 3
2015-04-29 I 2 I 5
2015-04-30 I 1 I 6

Thanks for the assistance.
Sam

ASKER CERTIFIED SOLUTION

UnifiedIS

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SamCash

ASKER

Thank you very much!

I hope it is OK to share the final solution, as it may help other newbees like me?

SELECT CONVERT(date, CreateDateTime) as myDate,
            COUNT(ContactID) as myCount
            , (SELECT COUNT(*)FROM Contacts x
                  WHERE x.CreateDateTime <= DATEADD(day, 1, CONVERT(date, t.CreateDateTime))
                  ) mySum
FROM Contacts t
GROUP BY Convert(date, CreateDateTime)
ORDER by Convert(date, CreateDateTime)

Mark Ely

SELECT CAST(N.SDT AS DATE) AS D, COUNT(*) AS DaysInGroup, (SELECT COUNT(*) AS RunningTotal FROM dbo.NameOfYourTable AS N2 WHERE CAST(N2.SDT AS DATE) <= CAST(N.SDT AS DATE)) AS RunningTotal
FROM dbo.NameOfYourTable AS N		
GROUP BY CAST(SDT AS DATE)

Open in new window

SamCash

ASKER

Thanks again.
Sam

Mike Eghtebas

This is for future readers with temp table:

Select Convert(Date, smallDateTime) As Dates 
   , Count(*) As 'Count'
   , (Select Count(*) From #t tt
     Where Convert(Date, tt.smallDateTime)<=Convert(Date, t.smallDateTime)
) AS total 
From #t t
Group By Convert(Date, smallDateTime)
Order By Convert(Date, smallDateTime)

CREATE TABLE #t
(
    CID   int         
    ,smallDateTime   datetime
);

Insert #t(CID , smallDateTime) values
(1 ,   '2015-04-28 06:33:00')
,(2 ,   '2015-04-28 06:34:00')
,(3 ,   '2015-04-28 06:36:00')
,(4 ,   '2015-04-29 06:00:00')
,(5 ,   '2015-04-29 06:02:00')
,(6 ,   '2015-04-30 05:00:00')

Open in new window

PortletPaul

for SQL Server versions that support SUM() OVER() you can achieve the running sum without the correlated subquery:

select
myDate
, myCount
, SUM(MyCount) OVER(order by MyDate) as mySum
from (
select
cast(CreateDateTime as date) as myDate
, count(*) as myCount
from Contacts
group by
cast(CreateDateTime as date)
) g
;

e.g. http://sqlfiddle.com/#!6/f30a9/5