Time Striiping

• isa-369599

  SSCommitted

  Points: 1602

  More actions

  January 5, 2007 at 12:11 pm

  #94930

  Hello everyone , in Sql Server 2000 i have 3 tables

  1) Pins

  PinID

  PinStatusCode

  PinPrice

  PinSerial

  PinIssueDate

  PinExpireDate

  2)Pin StatusCodes

  PinStatusCode

  StatusName (1 mean ActivePins, 2 for used, 3 for Blocked, 4 for reserved)

  3)Buyers

  BuyerID------------------------

  now i want to make a StoredProcedure in this i pass a parameter of Date (any Date may be current or previous one ) my SP returns in the specific date how many Pins Sales (info abt those Pins who have statuscode is ::used)at each hour.

  I need this SP for making a "CHART". In this i have time but in hours(1 to 24)and (count of Pins used).Now tell me how from 1 datetime filed i extract the time not just the time but in breaks them in the hour and keep track of each hour for example let say at 9.00 am 13 pins sold at 10.00 pm 14, 11.00 pm 7, 12.00pm 1,1.00 am 4 ................. so on to 8.00 am.

  plz tell me how i do this , its very urgent plz help me

  Thanx in Advance.

• Mark Harr

  SSCrazy Eights

  Points: 9921

  More actions

  January 5, 2007 at 2:12 pm

  #681582

  Look at using the DatePart function to extract the hour, then Group By on that value.

  SELECT ...

  Group By DatePart(hour, PinIssueDate)

  
  
  Mark

• Jeff Moden

  SSC Guru

  Points: 1004365

  More actions

  January 5, 2007 at 10:30 pm

  #681620

  If you want it to be unique across days (11PM today is NOT the same as 11PM yesterday)...

  SELECT ...

  GROUP BY DATEDIFF(hh,0,PinIssueDate)

  --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)