January 3, 2012 at 9:23 am
Hi All i have this data:
declare @tblusers TABLE (cuser varchar(200),cdate datetime,cmonth int,cday int ,cweek int ,cvisits int);
INSERT INTO @tblusers
SELECT 'jperez_albela',29/09/2011,9,29,40,1 UNION ALL
SELECT 'avargas',29/09/2011,9,29,40,1 UNION ALL
SELECT 'avargas',29/09/2011,9,29,40,1 UNION ALL
SELECT 'fgalup',29/09/2011,9,29,40,1 UNION ALL
SELECT 'fgalup',29/09/2011,9,29,40,1 UNION ALL
SELECT 'jperez_albela',29/09/2011,9,29,40,1 UNION ALL
SELECT 'jperez_albela',29/09/2011,9,29,40,1 UNION ALL
SELECT 'jperez_albela',29/09/2011,9,29,40,1 UNION ALL
SELECT 'jperez_albela',29/09/2011,9,29,40,1 UNION ALL
SELECT 'jperez_albela',29/09/2011,9,29,40,1 UNION ALL
SELECT 'ojuarez',29/09/2011,9,29,40,1 UNION ALL
SELECT 'ojuarez',29/09/2011,9,29,40,1 UNION ALL
SELECT 'ojuarez',29/09/2011,9,29,40,1 UNION ALL
SELECT 'ojuarez',29/09/2011,9,29,40,1 UNION ALL
SELECT 'ojuarez',29/09/2011,9,29,40,1 UNION ALL
SELECT 'ojuarez',29/09/2011,9,29,40,1 UNION ALL
SELECT 'ojuarez',29/09/2011,9,29,40,1 UNION ALL
SELECT 'jperez_albela',29/09/2011,9,29,40,1 UNION ALL
SELECT 'ojuarez',29/09/2011,9,29,40,1 UNION ALL
SELECT 'kzuniga',29/09/2011,9,29,40,1 UNION ALL
SELECT 'kzuniga',29/09/2011,9,29,40,1 UNION ALL
SELECT 'kzuniga',29/09/2011,9,29,40,1 UNION ALL
SELECT 'kzuniga',29/09/2011,9,29,40,1 UNION ALL
SELECT 'fgalup',29/09/2011,9,29,40,1 UNION ALL
SELECT 'lruiz',29/09/2011,9,29,40,1 UNION ALL
SELECT 'lruiz',29/09/2011,9,29,40,1 UNION ALL
SELECT 'oosnayo',29/09/2011,9,29,40,1 UNION ALL
SELECT 'ojuarez',30/09/2011,9,30,40,1 UNION ALL
SELECT 'ojuarez',30/09/2011,9,30,40,1 UNION ALL
SELECT 'ojuarez',30/09/2011,9,30,40,1 UNION ALL
SELECT 'lruiz',30/09/2011,9,30,40,1 UNION ALL
SELECT 'kzuniga',30/09/2011,9,30,40,1 UNION ALL
SELECT 'ojuarez',30/09/2011,9,30,40,1 UNION ALL
SELECT 'rtrinidad',30/09/2011,9,30,40,1 UNION ALL
SELECT 'echavez',30/09/2011,9,30,40,1 UNION ALL
SELECT 'atraverso',30/09/2011,9,30,40,1 UNION ALL
SELECT 'atraverso',30/09/2011,9,30,40,1 UNION ALL
SELECT 'rtrinidad',30/09/2011,9,30,40,1 UNION ALL
SELECT 'atraverso',30/09/2011,9,30,40,1 UNION ALL
SELECT 'rtrinidad',30/09/2011,9,30,40,1 UNION ALL
SELECT 'rtrinidad',30/09/2011,9,30,40,1 UNION ALL
SELECT 'cchangm',30/09/2011,9,30,40,1 UNION ALL
SELECT 'hcasas',30/09/2011,9,30,40,1 UNION ALL
SELECT 'hcasas',30/09/2011,9,30,40,1 UNION ALL
SELECT 'atraverso',30/09/2011,9,30,40,1 UNION ALL
SELECT 'fguillen',30/09/2011,9,30,40,1 UNION ALL
SELECT 'atraverso',30/09/2011,9,30,40,1 UNION ALL
SELECT 'fguillen',30/09/2011,9,30,40,1 UNION ALL
SELECT 'rtrinidad',30/09/2011,9,30,40,1 UNION ALL
SELECT 'rtrinidad',30/09/2011,9,30,40,1 UNION ALL
SELECT 'fguillen',30/09/2011,9,30,40,1 UNION ALL
SELECT 'rtrinidad',30/09/2011,9,30,40,1 UNION ALL
SELECT 'rtrinidad',30/09/2011,9,30,40,1 UNION ALL
SELECT 'rtrinidad',30/09/2011,9,30,40,1 UNION ALL
SELECT 'rtrinidad',30/09/2011,9,30,40,1 UNION ALL
SELECT 'fgalup',30/09/2011,9,30,40,1 UNION ALL
SELECT 'atraverso',30/09/2011,9,30,40,1 UNION ALL
SELECT 'atraverso',30/09/2011,9,30,40,1 UNION ALL
SELECT 'hcasas',30/09/2011,9,30,40,1 UNION ALL
SELECT 'hcasas',30/09/2011,9,30,40,1 UNION ALL
SELECT 'hcasas',30/09/2011,9,30,40,1 UNION ALL
SELECT 'hcasas',30/09/2011,9,30,40,1 UNION ALL
SELECT 'lruiz',02/10/2011,10,2,40,1 UNION ALL
SELECT 'lruiz',02/10/2011,10,2,40,1 UNION ALL
SELECT 'lruiz',02/10/2011,10,2,40,1 UNION ALL
SELECT 'lruiz',02/10/2011,10,2,40,1 UNION ALL
SELECT 'lruiz',03/10/2011,10,3,41,1 UNION ALL
SELECT 'lruiz',03/10/2011,10,3,41,1 UNION ALL
SELECT 'lruiz',03/10/2011,10,3,41,1 UNION ALL
SELECT 'ojuarez',03/10/2011,10,3,41,1 UNION ALL
SELECT 'ojuarez',03/10/2011,10,3,41,1 UNION ALL
SELECT 'ojuarez',03/10/2011,10,3,41,1 UNION ALL
SELECT 'ojuarez',03/10/2011,10,3,41,1 UNION ALL
SELECT 'ojuarez',03/10/2011,10,3,41,1 UNION ALL
SELECT 'jperez_albela',06/10/2011,10,6,41,1 UNION ALL
SELECT 'jperez_albela',06/10/2011,10,6,41,1 UNION ALL
SELECT 'jperez_albela',06/10/2011,10,6,41,1 UNION ALL
SELECT 'echavez',13/10/2011,10,13,42,1 UNION ALL
SELECT 'singa',13/10/2011,10,13,42,1 UNION ALL
SELECT 'singa',13/10/2011,10,13,42,1 UNION ALL
SELECT 'singa',13/10/2011,10,13,42,1 UNION ALL
SELECT 'singa',13/10/2011,10,13,42,1 UNION ALL
SELECT 'singa',13/10/2011,10,13,42,1 UNION ALL
SELECT 'singa',13/10/2011,10,13,42,1 UNION ALL
SELECT 'singa',13/10/2011,10,13,42,1 UNION ALL
SELECT 'singa',13/10/2011,10,13,42,1 UNION ALL
SELECT 'singa',13/10/2011,10,13,42,1 UNION ALL
SELECT 'singa',13/10/2011,10,13,42,1 UNION ALL
SELECT 'ojuarez',13/10/2011,10,13,42,1 UNION ALL
SELECT 'ojuarez',13/10/2011,10,13,42,1 UNION ALL
SELECT 'ojuarez',13/10/2011,10,13,42,1 UNION ALL
SELECT 'lruiz',13/10/2011,10,13,42,1 UNION ALL
SELECT 'lruiz',13/10/2011,10,13,42,1 UNION ALL
SELECT 'lruiz',13/10/2011,10,13,42,1 UNION ALL
SELECT 'jperez_albela',17/10/2011,10,17,43,1 UNION ALL
SELECT 'jperez_albela',19/10/2011,10,19,43,1 UNION ALL
SELECT 'jperez_albela',20/10/2011,10,20,43,1 UNION ALL
SELECT 'oosnayo',21/10/2011,10,21,43,1 UNION ALL
SELECT 'singa',14/11/2011,11,14,47,1 UNION ALL
SELECT 'singa',14/11/2011,11,14,47,1 UNION ALL
SELECT 'jperez_albela',16/11/2011,11,16,47,1 UNION ALL
SELECT 'jperez_albela',16/11/2011,11,16,47,1 UNION ALL
SELECT 'jperez_albela',17/11/2011,11,17,47,1 UNION ALL
SELECT 'avargas'e,14/12/2011,12,14,51,1 UNION ALL
SELECT 'singa',15/12/2011,12,15,51,1 UNION ALL
SELECT 'jperez_albela',20/12/2011,12,20,52,1 UNION ALL
SELECT 'jperez_albela',20/12/2011,12,20,52,1 UNION ALL
SELECT 'singa',20/12/2011,12,20,52,1 UNION ALL
SELECT 'jperez_albela',20/12/2011,12,20,52,1 UNION ALL
SELECT 'singa',20/12/2011,12,20,52,1 UNION ALL
SELECT 'singa',20/12/2011,12,20,52,1 UNION ALL
SELECT 'jperez_albela',20/12/2011,12,20,52,1 UNION ALL
SELECT 'jperez_albela',20/12/2011,12,20,52,1 UNION ALL
SELECT 'jperez_albela',20/12/2011,12,20,52,1 UNION ALL
SELECT 'jperez_albela',20/12/2011,12,20,52,1 UNION ALL
SELECT 'jperez_albela',20/12/2011,12,20,52,1 UNION ALL
SELECT 'jperez_albela',20/12/2011,12,20,52,1 UNION ALL
SELECT 'oosnayo',27/12/2011,12,27,53,1 UNION ALL
SELECT 'singa',29/12/2011,12,29,53,1 UNION ALL
SELECT 'jperez_albela',29/12/2011,12,29,53,1
select * from @tblusers
I need to know how many times a user(cuser) visits(cvisits) per month (CMONTH) Any recommendations?
____________________________________________________________________________
Rafo*
January 3, 2012 at 9:31 am
Look up GROUP BY in books on line (BOL).
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 3, 2012 at 9:31 am
Count() and Group By
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 3, 2012 at 12:16 pm
and please keep your datetime column in datetime datatype as long as you can !!
select dateadd(mm, datediff(mm, 0, thedatetimecolumn), 0) BeginOfMonth
, theuser
, count(*) nVisits
from ...
where ...
group by dateadd(mm, datediff(mm, 0, thedatetimecolumn), 0) -- produces 1/thismonth/thisyear
, ...
order by ....
ref: http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx By Lynn Pettis
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply