Count and Sum of records on Date basis.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Count and Sum of records on Date basis.

Author

Topic

Mits
Starting Member

48 Posts

Posted - 2011-06-21 : 12:11:51

Hello

Here is the structure on my tables:

Tables

User:
--UserId int
--UserName varchar

Notes:
--NoteId int
--UserId int
--CreatedDate datetime

I would like to display the results in following format

Date-----------User1----------User2-------User3------Total

01/06/2011------10-------------20----------30---------60
02/06/2011------20-------------20----------20---------60

Follwoing query will show Note Count for each user on current date(Today) in following format
User1 10
User2 20
User3 30

SELECT Users.UserName, COUNT(NoteId) as [Note Count]
FROM Notes INNER JOIN
Users ON Notes.SalesPersonId = Users.UserId
WHERE (DATEADD(dd, 0, DATEDIFF(dd, 0, Notes.CreatedDate)) = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())))
GROUP BY Users.UserName

Following shows the Total count of notes on dates in following format:
Day---------------Total Count
01/06/2011--------60
02/06/2011--------80

select

-- First day of month
[Day] = Convert(varchar,dateadd(dd,datediff(dd,0,Createddate ),0),106),
[Note Count] = count(*)
from
Notes
where CreatedDate > = '2011-06-01 00:00:00:000'
group by
-- First day of month
dateadd(dd,datediff(dd,0,Createddate),0)
order by
-- First day of month
dateadd(dd,datediff(dd,0,Createddate),0)

I need help to merege above 2 SQL in a stored procedure where I will pass a date and from the query should show me the results as mentioned above.

Thanks

Mits

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-21 : 12:16:11

select dateadd(dd,datediff(dd,0,Createddate),0) ,
user1 = sum(case when u.name = 'user1' then 1 else 0 end) ,
user2 = sum(case when u.name = 'user2' then 1 else 0 end) ,
...
from
User u
join
Notes n
on u.userid = n.userid
where CreatedDate > = '2011-06-01 00:00:00:000'
group by
-- First day of month
dateadd(dd,datediff(dd,0,Createddate),0)
order by
-- First day of month
dateadd(dd,datediff(dd,0,Createddate),0)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Mits
Starting Member

48 Posts

Posted - 2011-07-08 : 09:25:57

Thanks Nigel, your solution works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources