date wise report

• bruce_reid

  SSCrazy

  Points: 2419

  More actions

  September 29, 2003 at 2:42 pm

  #160782

  I have a table sales with columns date,no_of_items_sold and cost.

  dateno_of_items_soldcost

  9/22/2003 10120

  9/22/2003 672

  9/22/2003 12144

  9/23/2003 224

  9/23/2003 30360

  9/26/2003 11132

  9/26/2003 12144

  9/29/2003 896

  I need to run a report which should be in the following format.

  date sum(no_of_items_sold)

  9/22/200328

  9/23/200332

  9/24/20030

  9/25/20030

  9/26/200323

  9/27/20030

  9/28/20030

  9/29/20038

  I want to display all the dates evethough there is no data on that day.That date should have zero as sum(no_of_items_sold).

  This will stretch for one year.Can anyone give me a solution.

  Thanks.

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  September 29, 2003 at 2:59 pm

  #476275

  CREATE TABLE Days(doy smallint)

  declare @i smallint

  set @i = 0

  while @i < 366 begin

  insert Days

  values(@i)

  set @i = @i + 1 end

  DECLARE @StartDate datetime

  SET @StartDate = '20030922'

  SELECT @StartDate + d.doy Date, SUM(ISNULL(No_of_Items_Sold,0))

  FROM SalesTable s RIGHT JOIN Days d ON s.Date = @StartDate + d.doy

  GROUP BY @StartDate + d.doy

  ORDER BY Date

  --Jonathan

  
  
  --Jonathan

• 5409045121009-7368

  SSCoach

  Points: 15740

  More actions

  September 30, 2003 at 4:50 am

  #476335

  Agree with Jonathans solution, maybe first sum per day for available data and then join to year dates. But then again it depends on the distribution of the data.