SQL Server Average QTY Sold Per Weekday Over 30/60/90

I have the following code :

select 
Date,
Locations.Description as 'Site',
StockItems.Description as 'Item Description',
ItemQuantity,
CASE
WHEN DATEPART(hour,(Date))<12 THEN 'Before 12PM'
ELSE 'After 12PM'
END AS '12 OR AFTER',
DATENAME(WEEKDAY, (date)) as 'Day'
from Transactions 
left join TransactionsLine on TransactionsLine.TransactionID = Transactions.id
left join StockItems  on TransactionsLine.StockItemID = StockItems.ID
left join Departments on StockItems.DepartmentCode = Departments.Code
left join Locations on  Transactions.Location = Locations.Code
where DATEDIFF(DAY, CONVERT(datetime, Date,11), GETDATE()) <= 30
AND Departments.Description in ('Hot Food') 
and (ISNULL(dbo.TransactionsLine.StockItemID, '') <> '') 
AND (dbo.TransactionsLine.Vol <> - 1) 
AND (ISNULL(dbo.TransactionsLine.Department, '') <> '')

which worked good for a while but what i have now been asked is if I can change this so it has the daily Average.

I tired to do this with summing the ItemQuantity and doing a group but this keep erroing as i need to have the 12 OR AFTER section.

I even tired to "count" the days in said date range but that didn't work either as there are multiple transaction per day (I thought using distinct might fix that but failed) :(

Here is some conext if it helps

1st Monday Before 12 = Sold 1 Biscuits 1st Monday After 12 = Sold 2 Biscuits

2nd Monday Before 12 = Sold 3 Biscuits 2nd Monday After 12 = Sold 4 Biscuits

3rd Monday Before 12 = Sold 5 Biscuits 3rd Monday After 12 = Sold 6 Biscuits

4th Monday Before 12 = Sold 7 Biscuits 4th Monday After 12 = Sold 8 Biscuits

End Result Data

Monday Before 12 Avergae = 4 Monday After 12 Average = 5

i was just going to use the QTY / 4 but this doenst really work as the days in 30,60,90 varies

My end game is being able to have a table like this :

here is an example of data -

Im pretty sure what I need to do is sum the item QTY per item per store per day and then divide by how many "days" are in that month but for the life of me I keep failing :(

I wish every month had 4 Mondays etc so I could just divide the summed QTY by 4

@ Charlieface As for the data :

Correct I would like the average Monday results for all dates for example :

May – 2023 These are all Mondays 01/05/2023 08/05/2023 15/05/2023 22/05/2023 29/05/2023

If Site 1 sold Cookies

01/08 Before 12 - 2

08/05 Before 12 – 5

15/05 Before 12 – 6

22/05 Before 12 – 10

29/05 Before 12 – 20

01/08 After 12 - 1

08/05 After 12 – 6

15/05 After 12 – 7

22/05 After 12 – 9

29/05 After 12 – 3

The flat table I would like to see would be something like this : and would have more sites, days, Item Description with real data

+--------+--------+-------------+------------------+---------------+
| Site   | Day    | 12 OR AFTER | Item Description | Daily Average |
+--------+--------+-------------+------------------+---------------+
| Site 1 | Monday | BEFORE 12   | Cookies          | 8.6           |
+--------+--------+-------------+------------------+---------------+
| Site 1 | Monday | AFTER 12    | Cookies          | 5.2           |
+--------+--------+-------------+------------------+---------------+

The challenge im facing is pretty much trying to work out the best way / the way to

SUM the Items Sold and then divide them by how many “Days (Mondays, Tuesday …etc) are in the data range

You need to group by the SiteDateWithoutTimeDay12 OR AFTER and Item Description , and sum it up, then group again with using the DateWithoutTime and take the average.

It's more efficient if you add the primary/unique key of a table to the GROUP BY where possible.
To avoid repeating code, put the day and time calculations in a CROSS APPLY .
You don't need to check for nulls, as <> already excludes nulls.
The DATEDIFF calculation is going to prevent proper usage of indexes. Instead, move the Date reference to the one side of the comparison by itself, and use DATEADD instead.
Use short meaningful table aliases to make the code more readable.

SELECT
  Site,
  Day,
  [12 OR AFTER],
  [Item Description],
  AVG(1.0 * ItemQuantity) AS [Daily Average]
FROM (
    SELECT
      l.Code,
      si.ID,
      l.Description AS Site,
      v.DateWithoutTime,
      v.Day,
      v.[12 OR AFTER],
      si.Description as [Item Description],
      SUM(ItemQuantity) AS ItemQuantity
    FROM Transactions t
    JOIN TransactionsLine tl on tl.TransactionID = t.id
    JOIN StockItems si on tl.StockItemID = si.ID
    JOIN Departments d on si.DepartmentCode = d.Code
    JOIN Locations l on t.Location = l.Code
    CROSS APPLY (
        SELECT
          CASE
            WHEN DATEPART(hour, t.Date) < 12 THEN 'Before 12PM'
            ELSE 'After 12PM'
            END AS [12 OR AFTER],
          CAST(t.Date AS date) AS DateWithoutTime,
          DATENAME(WEEKDAY, t.Date) AS Day
    ) v
    WHERE t.Date < DATEADD(DAY, -30, CAST(CAST(GETDATE() AS date) AS datetime))
      AND d.Description in ('Hot Food') 
      AND tl.StockItemID <> ''
      AND tl.Vol <> - 1
      AND tl.Department <> ''
    GROUP BY
      l.Code,
      l.Description,
      si.ID,
      si.Description,
      v.[12 OR AFTER],
      v.Day,
      v.DateWithoutTime
) t
GROUP BY
  t.Code,
  t.Site,
  t.ID,
  t.[Item Description],
  t.[12 OR AFTER],
  t.Day;

SQL Server Average QTY Sold Per Weekday Over 30/60/90

1条答案

相关问题

热门标签

最新问答