populate a weeks worth of data from daily data

• phingers

  Ten Centuries

  Points: 1041

  More actions

  September 1, 2008 at 9:53 am

  #77078

  Two tables, weekly and daily.

  daily contains a 'cost' for each 'productnumber' per day.

  weekly contains a summary of daily data for each week, by 'productnumber' (weekending Sunday).

  How do I populate the total 'cost' per 'productnumber' from daily, for each week using SQL?

• Christopher Stobbs

  SSC-Insane

  Points: 21098

  More actions

  September 1, 2008 at 9:59 am

  #865709

  Hi there,

  Normally it's a good Idea to show us what you have tried so far and then we can guide you in the right direction.

  Please refer to this ariticle for TIPS:

  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  thanks

  Chris

  ----------------------------------------------
  Try to learn something about everything and everything about something. - Thomas Henry Huxley

  :w00t:
  Posting Best Practices[/url]
  Numbers / Tally Tables[/url]

  SQL-4-Life

• phingers

  Ten Centuries

  Points: 1041

  More actions

  September 1, 2008 at 10:24 am

  #865716

  I haven't tried anything yet as I am not sure how to do it.

• Christopher Stobbs

  SSC-Insane

  Points: 21098

  More actions

  September 1, 2008 at 10:27 am

  #865720

  HI Again,

  In that case could you give us the full tables and some sample data.

  Thanks

  Chris

  ----------------------------------------------
  Try to learn something about everything and everything about something. - Thomas Henry Huxley

  :w00t:
  Posting Best Practices[/url]
  Numbers / Tally Tables[/url]

  SQL-4-Life

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  September 1, 2008 at 11:19 am

  #865728

  Oh well here is a quick and dirty solution

  INSERT INTO Dbo.Weekly(ProductNumer, WeeksCost)

  SELECT ProductNumber,SUM(Cost) FROM Daily GROUP BY ProductNumber

  Now go to Books On Line (BOL) are read the following:

  Group By

  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/40075914-6385-4692-b4a5-62fe44ae6cb6.htm

  Aggregate functions:

  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/0c06ae42-eb0a-4d77-9d74-aa1e7f344009.htm

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• phingers

  Ten Centuries

  Points: 1041

  More actions

  September 2, 2008 at 2:30 am

  #865870

  ok, i've changed the query to an update query as I already have data but want to update one field:

  update dbo.Weekly set WeeksCost = (

  SELECT SUM(Cost) FROM dbo.Daily GROUP BY ProductNumber, Salesdate)

  I still need a way of grouping the records by weekending based on the Salesdate in Daily.

  Any ideas how I can achieve this please?

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 2, 2008 at 2:40 am

  #865872

  Can you please give us the table structure, some sample data and an example of what you want out?

  Without that we're going to be guessing as to whether or not the solution we give you will work and will do what you want.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• phingers

  Ten Centuries

  Points: 1041

  More actions

  September 2, 2008 at 3:19 am

  #865884

  Weekly Table:

  IF OBJECT_ID('TempDB..#Weekly','U') IS NOT NULL

  DROP TABLE #Weekly

  CREATE TABLE [dbo].[#Weekly](

  [weekending] [datetime] NOT NULL,

  [line] [int] NOT NULL,

  [WeeksCost] [decimal](18, 2) NULL,

  CONSTRAINT [PK_#Weekly] PRIMARY KEY CLUSTERED

  (

  [weekending] ASC,

  [line] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  insert into #Weekly (line, weekending, WeeksCost)

  SELECT '8241','Jul 26 2008 12:00AM','0.00' UNION ALL

  SELECT '19292','Jul 26 2008 12:00AM','43639.50' UNION ALL

  SELECT '23752','Jul 26 2008 12:00AM','3288.64' UNION ALL

  SELECT '28213','Jul 26 2008 12:00AM','14743.00' UNION ALL

  SELECT '28960','Jul 26 2008 12:00AM','2227.50' UNION ALL

  SELECT '38528','Jul 26 2008 12:00AM','4056.00' UNION ALL

  SELECT '46734','Jul 26 2008 12:00AM','6308.10' UNION ALL

  SELECT '61138','Jul 26 2008 12:00AM','0.00' UNION ALL

  SELECT '70278','Jul 26 2008 12:00AM','6291.89' UNION ALL

  SELECT '72376','Jul 26 2008 12:00AM','4389.00' UNION ALL

  SELECT '88201','Jul 26 2008 12:00AM','4980.00' UNION ALL

  SELECT '88202','Jul 26 2008 12:00AM','6421.95' UNION ALL

  SELECT '88249','Jul 26 2008 12:00AM','16366.09' UNION ALL

  SELECT '88250','Jul 26 2008 12:00AM','0.00' UNION ALL

  SELECT '88256','Jul 26 2008 12:00AM','18585.60' UNION ALL

  SELECT '88263','Jul 26 2008 12:00AM','16955.00' UNION ALL

  SELECT '88393','Jul 26 2008 12:00AM','0.00' UNION ALL

  SELECT '98661','Jul 26 2008 12:00AM','6531.18' UNION ALL

  SELECT '8241','Aug 2 2008 12:00AM','0.00' UNION ALL

  SELECT '19292','Aug 2 2008 12:00AM','47273.50' UNION ALL

  SELECT '23752','Aug 2 2008 12:00AM','3164.36' UNION ALL

  SELECT '28213','Aug 2 2008 12:00AM','16353.00' UNION ALL

  SELECT '28960','Aug 2 2008 12:00AM','2175.00' UNION ALL

  SELECT '38528','Aug 2 2008 12:00AM','4056.00' UNION ALL

  SELECT '46734','Aug 2 2008 12:00AM','7572.30' UNION ALL

  SELECT '61138','Aug 2 2008 12:00AM','0.00' UNION ALL

  SELECT '70278','Aug 2 2008 12:00AM','6912.56' UNION ALL

  SELECT '72376','Aug 2 2008 12:00AM','4532.00' UNION ALL

  SELECT '88201','Aug 2 2008 12:00AM','5287.50' UNION ALL

  SELECT '88202','Aug 2 2008 12:00AM','5688.30' UNION ALL

  SELECT '88249','Aug 2 2008 12:00AM','16140.61' UNION ALL

  SELECT '88252','Aug 2 2008 12:00AM','0.00' UNION ALL

  SELECT '88256','Aug 2 2008 12:00AM','16857.60' UNION ALL

  SELECT '88263','Aug 2 2008 12:00AM','16076.82' UNION ALL

  SELECT '88932','Aug 2 2008 12:00AM','0.00' UNION ALL

  SELECT '98661','Aug 2 2008 12:00AM','5746.16' UNION ALL

  SELECT '391209','Aug 2 2008 12:00AM','1445.22' UNION ALL

  SELECT '8241','Aug 9 2008 12:00AM','3420.00' UNION ALL

  SELECT '10098','Aug 9 2008 12:00AM','0.00' UNION ALL

  SELECT '19292','Aug 9 2008 12:00AM','40526.00' UNION ALL

  SELECT '23752','Aug 9 2008 12:00AM','506.68' UNION ALL

  SELECT '28213','Aug 9 2008 12:00AM','14927.00' UNION ALL

  SELECT '28960','Aug 9 2008 12:00AM','1785.00' UNION ALL

  SELECT '38528','Aug 9 2008 12:00AM','3878.55' UNION ALL

  SELECT '46734','Aug 9 2008 12:00AM','6424.20' UNION ALL

  SELECT '61138','Aug 9 2008 12:00AM','0.00' UNION ALL

  SELECT '70278','Aug 9 2008 12:00AM','6364.91' UNION ALL

  SELECT '72376','Aug 9 2008 12:00AM','4477.00' UNION ALL

  SELECT '88201','Aug 9 2008 12:00AM','10236.03' UNION ALL

  SELECT '88202','Aug 9 2008 12:00AM','4371.75' UNION ALL

  SELECT '88249','Aug 9 2008 12:00AM','22538.37' UNION ALL

  SELECT '88250','Aug 9 2008 12:00AM','0.00' UNION ALL

  SELECT '88252','Aug 9 2008 12:00AM','0.00' UNION ALL

  SELECT '88256','Aug 9 2008 12:00AM','8777.24' UNION ALL

  SELECT '88263','Aug 9 2008 12:00AM','9800.51' UNION ALL

  SELECT '98661','Aug 9 2008 12:00AM','3857.00' UNION ALL

  SELECT '391209','Aug 9 2008 12:00AM','1145.76'

  Daily Table:

  IF OBJECT_ID('TempDB..#Daily','U') IS NOT NULL

  DROP TABLE #Daily

  CREATE TABLE [dbo].[#DAILY](

  [salesdate] [datetime] NOT NULL,

  [line] [int] NOT NULL,

  [cost] [decimal](18, 2) NULL,

  CONSTRAINT [PK_#DAILY] PRIMARY KEY CLUSTERED

  (

  [salesdate] ASC,

  [line] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  insert into #Daily (line, salesdate, cost)

  SELECT '8241','Jul 3 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 3 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 3 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 3 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 3 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 3 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 3 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 3 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 3 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 3 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 3 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 3 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 3 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 4 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 4 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 4 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 4 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 4 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 4 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 4 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 4 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 4 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 4 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 4 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 4 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 4 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 5 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 5 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 5 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 5 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 5 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 5 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 5 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 5 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 5 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 5 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 5 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 5 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 5 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 6 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 6 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 6 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 6 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 6 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 6 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 6 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 6 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 6 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 6 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 6 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 6 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 6 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 7 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 7 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 7 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 7 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 7 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 7 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 7 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 7 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 7 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 7 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 7 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 7 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 7 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 8 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 8 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 8 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 8 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 8 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 8 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 8 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 8 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 8 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 8 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 8 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 8 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 8 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 9 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 9 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 9 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 9 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 9 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 9 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 9 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 9 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 9 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 9 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 9 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 9 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 9 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 10 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 10 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 10 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 10 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 10 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 10 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 10 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 10 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 10 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 10 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 10 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 10 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 10 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 11 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 11 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 11 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 11 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 11 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 11 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 11 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 11 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 11 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 11 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 11 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 11 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 11 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 12 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 12 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 12 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 12 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 12 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 12 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 12 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 12 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 12 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 12 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 12 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 12 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 12 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 13 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 13 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 13 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 13 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 13 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 13 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 13 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 13 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 13 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 13 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 13 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 13 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 13 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 14 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 14 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 14 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 14 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 14 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 14 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 14 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 14 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 14 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 14 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 14 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 14 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 14 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 15 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 15 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 15 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 15 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 15 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 15 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 15 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 15 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 15 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 15 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 15 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 15 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 15 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 16 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 16 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 16 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 16 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 16 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 16 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 16 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 16 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 16 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 16 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 16 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 16 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 16 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 17 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 17 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 17 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 17 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 17 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 17 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 17 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 17 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 17 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 17 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 17 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 17 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 17 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 18 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 18 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 18 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 18 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 18 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 18 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 18 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 18 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 18 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 18 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 18 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 18 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 18 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 19 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 19 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 19 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 19 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 19 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 19 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 19 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 19 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 19 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 19 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 19 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 19 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 19 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 20 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 20 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 20 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 20 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 20 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 20 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 20 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 20 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 20 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 20 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 20 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 20 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 20 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 21 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 21 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 21 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 21 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 21 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 21 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 21 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 21 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 21 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 21 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 21 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 21 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 21 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 22 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 22 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 22 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 22 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 22 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 22 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 22 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 22 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 22 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 22 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 22 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 22 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 22 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 23 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 23 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 23 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 23 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 23 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 23 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 23 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 23 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 23 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 23 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 23 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 23 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 23 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 24 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 24 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 24 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 24 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 24 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 24 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 24 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 24 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 24 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 24 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 24 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 24 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 24 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 25 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 25 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 25 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 25 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 25 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 25 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 25 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 25 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 25 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 25 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 25 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 25 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 25 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 26 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 26 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 26 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 26 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 26 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 26 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 26 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 26 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 26 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 26 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 26 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 26 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 26 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 27 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 27 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 27 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 27 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 27 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 27 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 27 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 27 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 27 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 27 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 27 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 27 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 27 2008 12:00AM','1.19' UNION ALL

  SELECT '8241','Jul 28 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Jul 28 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 28 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 28 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 28 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 28 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 28 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 28 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 28 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 28 2008 12:00AM','0.36' UNION ALL

  SELECT '88256','Jul 28 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 28 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 28 2008 12:00AM','1.19' UNION ALL

  SELECT '19292','Jul 30 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 30 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 30 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 30 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 30 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 30 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 30 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 30 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 30 2008 12:00AM','0.46' UNION ALL

  SELECT '88256','Jul 30 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 30 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 30 2008 12:00AM','1.19' UNION ALL

  SELECT '391209','Jul 30 2008 12:00AM','0.41' UNION ALL

  SELECT '19292','Jul 31 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Jul 31 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Jul 31 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Jul 31 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Jul 31 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Jul 31 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Jul 31 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Jul 31 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Jul 31 2008 12:00AM','0.46' UNION ALL

  SELECT '88256','Jul 31 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Jul 31 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Jul 31 2008 12:00AM','1.19' UNION ALL

  SELECT '391209','Jul 31 2008 12:00AM','0.41' UNION ALL

  SELECT '8241','Aug 1 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Aug 1 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Aug 1 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Aug 1 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Aug 1 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Aug 1 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Aug 1 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Aug 1 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Aug 1 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Aug 1 2008 12:00AM','0.46' UNION ALL

  SELECT '88256','Aug 1 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Aug 1 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Aug 1 2008 12:00AM','1.19' UNION ALL

  SELECT '391209','Aug 1 2008 12:00AM','0.41' UNION ALL

  SELECT '19292','Aug 2 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Aug 2 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Aug 2 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Aug 2 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Aug 2 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Aug 2 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Aug 2 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Aug 2 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Aug 2 2008 12:00AM','0.46' UNION ALL

  SELECT '88256','Aug 2 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Aug 2 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Aug 2 2008 12:00AM','1.19' UNION ALL

  SELECT '391209','Aug 2 2008 12:00AM','0.41' UNION ALL

  SELECT '8241','Aug 3 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Aug 3 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Aug 3 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Aug 3 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Aug 3 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Aug 3 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Aug 3 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Aug 3 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Aug 3 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Aug 3 2008 12:00AM','0.46' UNION ALL

  SELECT '88256','Aug 3 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Aug 3 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Aug 3 2008 12:00AM','1.19' UNION ALL

  SELECT '391209','Aug 3 2008 12:00AM','0.41' UNION ALL

  SELECT '8241','Aug 4 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Aug 4 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Aug 4 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Aug 4 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Aug 4 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Aug 4 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Aug 4 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Aug 4 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Aug 4 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Aug 4 2008 12:00AM','0.46' UNION ALL

  SELECT '88256','Aug 4 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Aug 4 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Aug 4 2008 12:00AM','1.19' UNION ALL

  SELECT '391209','Aug 4 2008 12:00AM','0.41' UNION ALL

  SELECT '8241','Aug 5 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Aug 5 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Aug 5 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Aug 5 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Aug 5 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Aug 5 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Aug 5 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Aug 5 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Aug 5 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Aug 5 2008 12:00AM','0.46' UNION ALL

  SELECT '88256','Aug 5 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Aug 5 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Aug 5 2008 12:00AM','1.19' UNION ALL

  SELECT '391209','Aug 5 2008 12:00AM','0.41' UNION ALL

  SELECT '8241','Aug 6 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Aug 6 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Aug 6 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Aug 6 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Aug 6 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Aug 6 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Aug 6 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Aug 6 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Aug 6 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Aug 6 2008 12:00AM','0.46' UNION ALL

  SELECT '88256','Aug 6 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Aug 6 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Aug 6 2008 12:00AM','1.19' UNION ALL

  SELECT '391209','Aug 6 2008 12:00AM','0.41' UNION ALL

  SELECT '8241','Aug 7 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Aug 7 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Aug 7 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Aug 7 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Aug 7 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Aug 7 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Aug 7 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Aug 7 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Aug 7 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Aug 7 2008 12:00AM','0.46' UNION ALL

  SELECT '88256','Aug 7 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Aug 7 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Aug 7 2008 12:00AM','1.19' UNION ALL

  SELECT '391209','Aug 7 2008 12:00AM','0.41' UNION ALL

  SELECT '8241','Aug 8 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Aug 8 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Aug 8 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Aug 8 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Aug 8 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Aug 8 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Aug 8 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Aug 8 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Aug 8 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Aug 8 2008 12:00AM','0.46' UNION ALL

  SELECT '88256','Aug 8 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Aug 8 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Aug 8 2008 12:00AM','1.19' UNION ALL

  SELECT '391209','Aug 8 2008 12:00AM','0.41' UNION ALL

  SELECT '8241','Aug 9 2008 12:00AM','0.28' UNION ALL

  SELECT '19292','Aug 9 2008 12:00AM','1.34' UNION ALL

  SELECT '28213','Aug 9 2008 12:00AM','1.34' UNION ALL

  SELECT '28960','Aug 9 2008 12:00AM','0.23' UNION ALL

  SELECT '38528','Aug 9 2008 12:00AM','1.06' UNION ALL

  SELECT '46734','Aug 9 2008 12:00AM','0.81' UNION ALL

  SELECT '72376','Aug 9 2008 12:00AM','1.38' UNION ALL

  SELECT '88201','Aug 9 2008 12:00AM','0.68' UNION ALL

  SELECT '88202','Aug 9 2008 12:00AM','0.17' UNION ALL

  SELECT '88249','Aug 9 2008 12:00AM','0.46' UNION ALL

  SELECT '88256','Aug 9 2008 12:00AM','0.55' UNION ALL

  SELECT '88263','Aug 9 2008 12:00AM','0.70' UNION ALL

  SELECT '98661','Aug 9 2008 12:00AM','1.19' UNION ALL

  SELECT '391209','Aug 9 2008 12:00AM','0.41'

• Greg Edwards-268690

  SSC-Insane

  Points: 20676

  More actions

  September 2, 2008 at 8:26 am

  #866015

  Don't know if you need a Fiscal Calendar, - in ours, every week starts on Sunday and ends on Saturday, with 4wk, 4wk, 5 wk months. It has Calendar Date, Fiscal Year, Fiscal Month, Fiscal Week of Year, etc.

  This makes it very easy to sort and group the data.

  As far as populating a summary table, if performance is OK, a view might be better. Less to maintain. And it will always match up.

  Greg E

• phingers

  Ten Centuries

  Points: 1041

  More actions

  September 2, 2008 at 9:01 am

  #866030

  I've no problem with creating a view for this but there must be a way of specifying the weekending day of week to be Saturday and using that date as the grouping?

• Greg Edwards-268690

  SSC-Insane

  Points: 20676

  More actions

  September 2, 2008 at 9:11 am

  #866036

  check out DatePart in books online.

  Just remember that a week can span 2 Calendar months....

  Greg E

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  September 2, 2008 at 9:18 am

  #866043

  This should do what you want

  select

  Weekending =

  -- Find end of week date for week Sun-Sat

  dateadd(dd,((datediff(dd,-53684,a.salesdate)/7)*7)+6,-53684) ,

  a.line,

  sum(a.cost)

  from

  #DAILY a

  group by

  dateadd(dd,((datediff(dd,-53684,a.salesdate)/7)*7)+6,-53684) ,

  a.line

  order by

  dateadd(dd,((datediff(dd,-53684,a.salesdate)/7)*7)+6,-53684) ,

  a.line

• Greg Edwards-268690

  SSC-Insane

  Points: 20676

  More actions

  September 2, 2008 at 9:58 am

  #866069

  here's a snippet of taking a date and making a column for grouping by week.

  select

  *,

  cast(datepart(yy,salesdate)as char(4))+ ' - ' + cast(datepart(wk,salesdate) as char(2))

  from

  #daily

  you may want to experiment with mm too

  Greg E

• phingers

  Ten Centuries

  Points: 1041

  More actions

  September 3, 2008 at 4:37 am

  #866405

  Thanks Michael Valentine Jones, I can't say I understand it but that worked perfectly!! 😀

• Goldie Lesser

  SSCertifiable

  Points: 6155

  More actions

  September 3, 2008 at 7:25 am

  #866469

  Here's another way.

  SELECT DATEADD(wk, DATEDIFF(wk, 5, a.salesdate), 5) AS EndWeek

  ,a.line

  ,sum(a.cost)

  FROM #DAILY a

  GROUP BY DATEADD(wk, DATEDIFF(wk, 5, a.salesdate), 5)

  ,a.line

  ORDER BY EndWeek

  ,a.line