The query output should be

• karthikeyan-146504

  Ten Centuries

  Points: 1055

  More actions

  August 5, 2009 at 9:34 pm

  #137113

  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: 1004329

  More actions

  August 5, 2009 at 11:24 pm

  #1035183

  Duplicate post... no responses here please. Goto http://www.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)