t-sql help, urgent please!!

• Mh-397891

  SSCertifiable

  Points: 6704

  More actions

  July 30, 2009 at 10:28 am

  #207520

  Hi, I am trying to get the number of orders booked over the last 4 saturdays over a span of 10 minutes based on orderdate,

  trying to get the follow output so that I can compare the order counts of the last 4 saturdays.

  Also providing the sample table. Thanks a bunch for your help.

  Output:

  day(sat.) timeperiod orderscount

  2009-07-04 5:00-5:10 2

  2009-07-04 5.10-5.20 0

  2009-07-04 5.20-5.30 0

  ...

  ...

  2009-07-04 8.00-8.10 2

  2009-07-04 8.10-8.20 1

  2009-07-11 7:00-7:10 2

  2009-07-11 8:00-8:10 2

  ..

  2009-07-11 14:00-14:101

  ..

  2009-07-18 06:00-6:102

  2009-07-25 08:00-8:103

  create table #orders

  (

  orderdate datetime,

  order_id uniqueidentifier)

  INSERT INTO #orders

  SELECT '2009-07-25 07:00:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-25 07:15:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-25 08:00:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-25 08:03:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-25 08:09:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-25 08:15:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-18 06:00:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-18 06:09:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-18 08:00:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-18 08:07:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-18 08:16:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-18 14:01:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-18 14:16:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-18 14:27:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-18 16:16:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-18 19:01:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-11 07:00:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-11 07:09:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-11 08:02:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-11 08:07:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-11 08:16:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-11 14:01:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-11 14:16:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-11 14:27:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-11 18:16:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-11 20:01:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-04 05:00:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-04 05:09:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-04 08:02:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-04 08:07:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-04 08:16:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-04 14:01:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-04 14:16:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-04 15:27:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-04 18:16:44.000', NEWID()

  UNION ALL

  SELECT '2009-07-04 20:01:44.000', NEWID()

  SELECT * FROM #orders

  ORDER BY 1

• drew.allen

  SSC Guru

  Points: 76997

  More actions

  July 30, 2009 at 10:42 am

  #1032528

  It's not necessarily pretty, but this will work. Convert the datetime to a string in Canonical ODBC format (20 or 120), but truncate the string at the first digit of the minutes. Group by that truncated string.SELECT Convert(varchar(15), orderdate, 120), * FROM #orders

  ORDER BY 1 This should get you started.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 30, 2009 at 10:52 am

  #1032535

  Here is another way to attack this problem.

  Sorry, had to upload it as an attachment from work.

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  July 30, 2009 at 12:12 pm

  #1032587

  select

  Order_10_Minute =

  dateadd(mi,(datepart(mi,orderdate)/10)*10,dateadd(hh,datediff(hh,0,orderdate),0)),

  Order_Count = count(*)

  from

  #orders

  group by

  dateadd(mi,(datepart(mi,orderdate)/10)*10,dateadd(hh,datediff(hh,0,orderdate),0))

  order by

  dateadd(mi,(datepart(mi,orderdate)/10)*10,dateadd(hh,datediff(hh,0,orderdate),0))

  Results:

  Order_10_Minute Order_Count

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

  2009-07-04 05:00:00.000 2

  2009-07-04 08:00:00.000 2

  2009-07-04 08:10:00.000 1

  2009-07-04 14:00:00.000 1

  2009-07-04 14:10:00.000 1

  2009-07-04 15:20:00.000 1

  2009-07-04 18:10:00.000 1

  2009-07-04 20:00:00.000 1

  2009-07-11 07:00:00.000 2

  2009-07-11 08:00:00.000 2

  2009-07-11 08:10:00.000 1

  2009-07-11 14:00:00.000 1

  2009-07-11 14:10:00.000 1

  2009-07-11 14:20:00.000 1

  2009-07-11 18:10:00.000 1

  2009-07-11 20:00:00.000 1

  2009-07-18 06:00:00.000 2

  2009-07-18 08:00:00.000 2

  2009-07-18 08:10:00.000 1

  2009-07-18 14:00:00.000 1

  2009-07-18 14:10:00.000 1

  2009-07-18 14:20:00.000 1

  2009-07-18 16:10:00.000 1

  2009-07-18 19:00:00.000 1

  2009-07-25 07:00:00.000 1

  2009-07-25 07:10:00.000 1

  2009-07-25 08:00:00.000 3

  2009-07-25 08:10:00.000 1

  (28 row(s) affected)

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 30, 2009 at 12:30 pm

  #1032606

  Michael Valentine Jones (7/30/2009)

  ---

  select

  Order_10_Minute =

  dateadd(mi,(datepart(mi,orderdate)/10)*10,dateadd(hh,datediff(hh,0,orderdate),0)),

  Order_Count = count(*)

  from

  #orders

  group by

  dateadd(mi,(datepart(mi,orderdate)/10)*10,dateadd(hh,datediff(hh,0,orderdate),0))

  order by

  dateadd(mi,(datepart(mi,orderdate)/10)*10,dateadd(hh,datediff(hh,0,orderdate),0))

  Results:

  Order_10_Minute Order_Count

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

  2009-07-04 05:00:00.000 2

  2009-07-04 08:00:00.000 2

  2009-07-04 08:10:00.000 1

  2009-07-04 14:00:00.000 1

  2009-07-04 14:10:00.000 1

  2009-07-04 15:20:00.000 1

  2009-07-04 18:10:00.000 1

  2009-07-04 20:00:00.000 1

  2009-07-11 07:00:00.000 2

  2009-07-11 08:00:00.000 2

  2009-07-11 08:10:00.000 1

  2009-07-11 14:00:00.000 1

  2009-07-11 14:10:00.000 1

  2009-07-11 14:20:00.000 1

  2009-07-11 18:10:00.000 1

  2009-07-11 20:00:00.000 1

  2009-07-18 06:00:00.000 2

  2009-07-18 08:00:00.000 2

  2009-07-18 08:10:00.000 1

  2009-07-18 14:00:00.000 1

  2009-07-18 14:10:00.000 1

  2009-07-18 14:20:00.000 1

  2009-07-18 16:10:00.000 1

  2009-07-18 19:00:00.000 1

  2009-07-25 07:00:00.000 1

  2009-07-25 07:10:00.000 1

  2009-07-25 08:00:00.000 3

  2009-07-25 08:10:00.000 1

  (28 row(s) affected)

  Very similar to mine, more concise in calculating the 10 minute breaks. I like it.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 30, 2009 at 12:32 pm

  #1032608

  But I just noticed a slight problem with both. Looks like we need to do a left outer join to a tally table calculating the date/time in 10 minute increments so that 0 values can be shown as well.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  July 30, 2009 at 1:34 pm

  #1032645

  I have a Numbers table that goes from 1 to 10,000 that I use in this.

  ;

  with Timeslots

  as (select

  dateadd(minute, 10 * (number - 1), '2009-07-04') STime,

  dateadd(minute, 10 * number, '2009-07-04') ETime

  from

  dbo.Numbers

  where

  number = Timeslots.STime

  and Orders.OrderDate < Timeslots.ETime)

  select

  STime,

  ETime,

  sum(OrderPlaced) as OrderQty

  from

  OrderSub

  group by

  STime,

  ETime

  order by

  STime ;

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  July 30, 2009 at 3:06 pm

  #1032704

  Also see this blog post for alternate ways to display your data.

  ---

  N 56°04'39.16"
  E 12°55'05.25"