The query output should be

• karthikeyan-146504

  SSC Eights!

  Points: 945

  More actions

  August 5, 2009 at 9:39 pm

  #135179

  We have database called store, I have an issue to generate the hourly count report dynamically.

  Whenever I execute the below query and I get the output as below

  Date_Time MsgCount

  2009-08-06 01 2452

  2009-08-06 02 2210

  2009-08-06 03 4173

  2009-08-06 04 6450

  2009-08-06 05 5452

  2009-08-06 06 2452

  2009-08-06 07 2462

  2009-08-06 10 4521

  But I need the output should be

  Date_Time MsgCount

  2009-08-06 01 2452

  2009-08-06 02 2210

  2009-08-06 03 4173

  2009-08-06 04 6450

  2009-08-06 05 5452

  2009-08-06 06 2452

  2009-08-06 07 2462

  2009-08-06 08 0

  2009-08-06 09 0

  2009-08-06 10 4521

  I am using the query to generate the report as below,

  select convert(varchar(13), lastupdated, 120) As Date_Time, count(*) As MsgCount from store_details(nolock)

  group by convert(varchar(13), lastupdated, 120)

  order by convert(varchar(13), lastupdated, 120)

  Please help me how will I get the output.

  Thanks in Advance

  ---

  Kindest Regards,

  karthik
  

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  August 5, 2009 at 11:21 pm

  #1035182

  Duplicate post... no responses here please. Goto http://qa.sqlservercentral.com/Forums/Topic765935-8-1.aspx

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions