Help with Stored Procedure

• xRafo

  SSChasing Mays

  Points: 623

  More actions

  January 3, 2012 at 9:23 am

  #254276

  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*

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  January 3, 2012 at 9:31 am

  #1427806

  Look up GROUP BY in books on line (BOL).

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  January 3, 2012 at 9:31 am

  #1427808

  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/

• Johan Bijnens

  SSC Guru

  Points: 135369

  More actions

  January 3, 2012 at 12:16 pm

  #1427944

  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