Group by Month

• davidhyder

  SSC Veteran

  Points: 262

  More actions

  September 15, 2017 at 11:04 am

  #328396

  I used to be the go-to-guy for SQL and SSRS in another life. But alas, I'm now manager who rarely gets to write queries or build reports. So the answer to this simple question escapes me and I can't seem to find a solution posted anywhere.

  I want to group dates by month.
  
  Here is my query:

  
select l.location_name Center, convert (date, a.appt_date) Date, count(a.appt_id) [Kept Appointments]
  from appointments a
  join location_mstr l on a.location_id = l.location_id
  where a.appt_date between '20170101' and getdate()
  group by l.location_name, a.appt_id, a.appt_date
  order by center, date
  

  This is an example of what I get:

  Location	Date	Count
  Los Angeles	1/1/2017	1
  Los Angeles	1/1/2017	1
  Los Angeles	1/1/2017	1
  Los Angeles	1/2/2017	1
  Los Angeles	1/2/2017	1
  Los Angeles	1/2/2017	1
  Los Angeles	1/3/2017	1
  Los Angeles	1/3/2017	1
  Los Angeles	1/3/2017	1
  New York	1/1/2017	1
  New York	1/1/2017	1
  New York	1/1/2017	1
  New York	1/2/2017	1
  New York	1/2/2017	1
  New York	1/2/2017	1
  New York	1/3/2017	1
  New York	1/3/2017	1
  New York	1/3/2017	1
  Chicago	1/1/2017	1
  Chicago	1/1/2017	1
  Chicago	1/1/2017	1
  Chicago	1/2/2017	1
  Chicago	1/2/2017	1
  Chicago	1/2/2017	1
  Chicago	1/3/2017	1
  Chicago	1/3/2017	1
  Chicago	1/3/2017	1

  This is what I'm looking for: (drill down report)

  

  
  Thanks!

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  September 15, 2017 at 5:57 pm

  #1959675

  It's your Group By - it's too specific.

  Firstly you are grouping by a.appt_id, so your Count will always be 1.
  Secondly you are grouping by a.appt_date, but displaying convert (date, a.appt_date) - which suggests a.appt_date is a datetime and therefore your Group By on a.appt_date is grouping by time and date.

  Try changing your GROUP BY to this:

  group by l.location_name, convert (date, a.appt_date)

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]