Weekly sales report

Question

Weekly sales report

mailramk

Valued Member

Points: 62
More actions
September 21, 2023 at 12:03 pm

#4291051

I have two tables InvHeader and InvDetail
InvHeader table has the columns:
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.
[![SQL output](https://i.stack.imgur.com/B1kqb.png)](https://i.stack.imgur.com/B1kqb.png)
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
InvoiceHeader OH
JOIN
InvoiceDetail OD ON OH.InvNo = OD.InvNo
WHERE
OH.InvDate BETWEEN '2023-01-01' AND '2023-08-31'
GROUP BY
DATEADD(WEEK, DATEDIFF(WEEK, 0, OH.InvDate), 0),
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|

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

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.

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

pietlinden SSC Guru Points: 63503 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

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.

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

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.

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