0

i have a store sales table which looks like below containing two years of data. If the sales amount is '0' for a particular day for a particular store ,i have to take sales for same day last week.(current day-7) if those sales are also '0' then sales for current_day-8 if those sales are also '0' then sales for current_day-9 if those sales are also '0' then sales for current_day-10

Sales_table1            
day_id      week_id sales   Store
2/1/2014    201401  34566   1234
2/2/2014    201401  67777   567
2/3/2014    201401  3333    698
2/4/2014    201401  45644   345
2/5/2014    201401  2456    789
**2/6/2014  201401  3456    567**
2/7/2014    201401  5674    780
2/8/2014    201402  3333    1234
2/9/2014    201402  22222   567
2/10/2014   201402  111134  698
2/11/2014   201402  56789   345
2/12/2014   201402  4356    789
**2/13/2014 201402  0       567**
2/14/2014   201402  899     780

please give the query that i can use.

Tony
  • 9,672
  • 3
  • 47
  • 75
mamidipalli satish
  • 1
  • 2

1 Answers1

0

As you have not said which database you are using I'm giving a solution using SQL Server as that's what I'm most familiar with.

The logic of my query is to select all the rows where sales are greater than zero for each store, where the date of the sales info is either the MAX date in the table or the date is less than or equal to the MAX(date)-7 days (effectively exclude any sales in the previous 7 days). I'm doing this because you said you need to take the most recent sale or sales on day -7, -8, -9, etc.

From that set of rows the query then picks the most recent sale date.

;WITH Store_Dates AS (
  SELECT store, MAX(day_id) max_date, DATEADD(day, -7, MAX(day_id)) old_date
  FROM Sales_Table1
  GROUP BY store
)
,Sale_Days AS (
  SELECT MAX(day_id) day_id, Sales_Table1.store
  FROM Sales_Table1 INNER JOIN Store_Dates
  ON Sales_Table1.store = Store_Dates.store
  WHERE sales > 0
  AND (day_id = max_date 
     OR day_id <= old_date)
  GROUP BY Sales_Table1.store
)
SELECT Sales_Table1.* 
FROM Sales_Table1 INNER JOIN Sale_Days
ON Sales_Table1.store = Sale_Days.store
AND Sales_Table1.day_id = Sale_Days.day_id

SQL Fiddle

The only thing my query does not do is stop after going back X number of days. You could do that by limiting the number of rows returned from the Sale_Days query.

Tony
  • 9,672
  • 3
  • 47
  • 75
  • DAY_ID LOC_ID SALES 1/1/2010 0:00 124 1414.15 1/2/2010 0:00 124 4529.02 1/3/2010 0:00 124 4533.91 1/4/2010 0:00 124 3357.97 1/5/2010 0:00 124 2987.64 1/6/2010 0:00 124 3306.04 1/7/2010 0:00 124 0 1/8/2010 0:00 124 3652.77 1/9/2010 0:00 124 4004.86 1/10/2010 0:00 124 3794.29 1/11/2010 0:00 124 4028.4 1/12/2010 0:00 124 4254.05 1/13/2010 0:00 124 3147.31 1/14/2010 0:00 124 0 – mamidipalli satish Sep 11 '14 at 20:04
  • from above since the 14 day sales are zero for loc_id 124..it should take 7th day sales..and since 7th day is also zero ..it should take 8th day whose value is 3306.04 – mamidipalli satish Sep 11 '14 at 20:05
  • My query works for the new data you have provided, see this [SQL Fiddle](http://sqlfiddle.com/#!3/7b5d8/1); although it would have helped if you had kept the data format and field names the same... – Tony Sep 11 '14 at 21:03