# Weekly sales report

• I have two tables InvHeader and InvDetail

InvNo, InvDate

InvDetail table has the columns:

InvNo, ProductNo, QTY, Price

I Would like to generate weekly sales for the period 1/1/2023 to 8/31/2023. The week starts with Sunday. Amount column is calculated Qty\*Price.

Thanks

I tried this:

`

SELECT

DATEADD(WEEK, DATEDIFF(WEEK, 0, OH.InvDate), 0) AS WeekDate,

OD.ProductNo,

SUM(OD.QTY) AS TotalQty,

SUM(OD.QTY * OD.UnitPrice) AS TotalAmount

FROM

JOIN

InvoiceDetail OD ON OH.InvNo = OD.InvNo

WHERE

OH.InvDate BETWEEN '2023-01-01' AND '2023-08-31'

GROUP BY

OD.ProductNo

ORDER BY

WeekDate,

OD.ProductNo;

`

I am getting the output as below:

|Weekdate |ProductNo |TotalQty |TotalAmount|

|-|-|-|-|

|1/1/2023 |orangejuice |12 |120|

|1/1/2023 |GrapeJuice |17 |170|

|1/8/2023 |orangejuice |1 |10|

|1/8/2023 |GrapeJuice |21 |210|

|1/15/2023 |orangejuice |5 |50|

|1/16/2023 |GrapeJuice |58 |580|

• Next year, the 1st of January occurs on a Monday.  What do you want to do with the previous Sunday, which is from the previous year?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• If you're going to do lots of date-related analysis, I'd just create a Calendar table with all the columns you want to group by, and then join to that in your query. Maybe I've been doing BI stuff for too long, though.

• pietlinden wrote:

If you're going to do lots of date-related analysis, I'd just create a Calendar table with all the columns you want to group by, and then join to that in your query. Maybe I've been doing BI stuff for too long, though.

Not that you would Pieter, but I've seen a whole lot of people do that wrong for performance (including a younger me).  Too many join during the aggregation instead of after.

The OP still hasn't answered my question about the first week of next year.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Heh... apparently, the OP has left the building. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.