0

I want to get report data in below scenarios using the sample table provided below(data is huge in my db)

  1. List item(same week, prior year) sales and (same day, same week, prior year) sales.
  2. Rolling 6 month avg weekly selling
id date_week date_value sales
Item1 2020/01-04 20200120 230
Item2 2020/06-03 20200608 23.0
Item3 2019/11-03 20191111 null
Item4 2020/07-04 20200720 123
Item5 2019/08-01 20190729 456
Item6 2019/09-03 20190909 1234
Item7 2020/06-02 20200601 4556
Item8 2020/09-01 20200824 23
Item9 2021/09-02 20210906 1223

in above table date_week is year/month_week ( so here i get the week number)

Am trying the below query to achieve

SELECT
  DATEPART(week, date_value) AS Week,
  id ,
  sum(sales) AS sales
FROM table
WHERE date_value <= date_value
 AND date_value < DATEADD(year, 1, date_value)
GROUP BY DATEPART(week, date_value), id
ORDER BY DATEPART(week, date_value);

Please suggest me how to achieve the scenarios am looking for.

alihaide
  • 1
  • 2
  • You are mixing weeks and months. That requires a lot of explanation. Please explain exactly what the results look like and how to deal with the fact that "6 months" is not an exact number of weeks. – Gordon Linoff Jun 08 '21 at 12:04
  • @GordonLinoff, ignore date_week and how can i get 6 month average selling using normal calender – alihaide Jun 08 '21 at 15:24
  • I think there is still some confusion here. Your sample code suggests a date range of 1 year (`WHERE date_value <= date_value AND date_value < DATEADD(year, 1, date_value)`) but your previous comment asks for a "6 month average". Also, you may need to provide a sample and/or schema of your source table so we know what types we are dealing with. Finally "6 month avg weekly selling" doesn't make much sense. Are you wanting to total up all sales over a six months period and then get a weekly average by diving this total by [[however many weeks occur in six months]]? Thanks – Chris Walsh Jun 08 '21 at 15:45
  • @ChrisWalsh, My bad am unable to explain my problem statement. Actually am looking for "6 month rolling avg". – alihaide Jun 08 '21 at 16:41

1 Answers1

0
  1. You can do these with a join. First I would separate my date columns to Year | Month | Week | Day. If this is the only format you have available for your dates, you can use left(), right() functions. Always better to have datetime format tho.

After your query can look like:

SELECT t1.Year, t1.Week, t1.Id, t1.Sales, t2.Sales as Last_year_this_week_sales
from (
    SELECT
      cast(right(date_value,2) as int) AS Week,
      cast(left(date_value,4) as int) as Year
      id ,
      sum(sales) AS sales
    FROM table
    GROUP BY right(date_value,2),
    left(date_value,4), id ) t1
left join  (
    SELECT
      cast(right(date_value,2) as int) AS Week,
      cast(left(date_value,4) as int) as Year
      id ,
      sum(sales) AS sales
    FROM table
    GROUP BY right(date_value,2),
    left(date_value,4), id ) t2 ON t1.week = t2.week and t2.year = t1.year-1 and t1.id = t2.id';

I am assuming you want to have results on id level. if not, you need to remove it from your grouping and join. And you can do the same for the daily results replacing week with day.

  1. You can do with a subquery - if you need to take the average of weekly totals in the last 6 months. And again, if you need it on item level, keep the id in your select and group by statements.

If not, you can do:

SELECT avg(sales) as Sales_avg  
FROM 
    (SELECT
    cast(right(date_value,2) as int) AS Week,
    cast(left(date_value,4) as int) as Year,
    sum(sales) AS sales
    FROM table
    where date_value>='20210101' --replace with the date you need
    GROUP BY right(date_value,2),
    left(date_value,4) ) t1;
Chris Walsh
  • 3,423
  • 2
  • 42
  • 62
Oulke
  • 26
  • 2