Count number of transaction in each period of time

• molecule_kaab

  SSC Eights!

  Points: 826

  More actions

  April 18, 2008 at 1:38 am

  #71482

  I want to count number of transactions in each period of time from the column that is datetime datatype

  for example

  time numberOfTransaction

  8:00 - 11:59 5

  12:00 - 17:59 10

  how Can write sql to do this?

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  April 18, 2008 at 3:43 am

  #804178

  How's this?

  --data

  declare @transactions table (TransactionDate datetime)

  insert @transactions

  select '20080418 08:31'

  union all select '20080418 09:13'

  union all select '20080418 10:26'

  union all select '20080418 10:47'

  union all select '20080418 11:59'

  union all select '20080418 12:12'

  union all select '20080418 12:51'

  union all select '20080418 13:45'

  union all select '20080418 14:28'

  union all select '20080418 14:34'

  union all select '20080418 15:51'

  union all select '20080418 16:30'

  union all select '20080418 16:37'

  union all select '20080418 17:14'

  union all select '20080418 17:46'

  --calculation

  declare @rows table (DateFrom datetime, DateTo datetime)

  insert @rows

  select '20080418 08:00', '20080418 12:00'

  union all select '20080418 12:00', '20080418 18:00'

  select DateFrom, DateTo, count(*) as NumberOfTransactions

  from @rows a inner join @transactions b on DateFrom <= TransactionDate and TransactionDate < DateTo group by DateFrom, DateTo

  /* results

  DateFrom DateTo NumberOfTransactions

  ----------------------- ----------------------- --------------------

  2008-04-18 08:00:00.000 2008-04-18 12:00:00.000 5

  2008-04-18 12:00:00.000 2008-04-18 18:00:00.000 10

  */

  If this isn't what you want, please give more information about your problem - your table structure, some example data, and the results you would want for that data.

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.