Good day
I tried to do a query with the total sales ($) of the week, I used the code below, and it works perfectly
SELECT DISTINCT
YEAR(T0."DocDate") AS "Year",
MONTH(T0."DocDate") AS "Month",
MONTHNAME(T0."DocDate") AS "Month Name",
WEEK(T0."DocDate") AS "Week Number",
SUM(T0."DocTotal")
Over (Partition by
YEAR(T0."DocDate"),
MONTH(T0."DocDate"),
WEEK(T0."DocDate")) AS "Total Sales USD"
FROM
OINV T0
WHERE
T0."CANCELED" ='N' AND
WEEK(CURRENT_DATE)= WEEK(T0."DocDate") AND
YEAR(CURRENT_DATE)= YEAR(T0."DocDate")
ORDER BY
YEAR(T0."DocDate"),
MONTH(T0."DocDate") DESC,
WEEK(T0."DocDate") DESC
but, How do I add the warehouse to this query? I try with the next code, but the SUM is not right, I think is because duplicates the DocTotal of some invoices
SELECT DISTINCT
YEAR(T0."DocDate") AS "Year",
MONTH(T0."DocDate") AS "Month",
MONTHNAME(T0."DocDate") AS "Month Name",
WEEK(T0."DocDate") AS "Week Number",
T1."WhsCode",
SUM(T0."DocTotal")
Over (Partition by
YEAR(T0."DocDate"),
MONTH(T0."DocDate"),
WEEK(T0."DocDate"),
T1."WhsCode"
) AS "Total Sales USD"
FROM
OINV T0 INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
WHERE
T0."CANCELED" ='N' AND
WEEK(CURRENT_DATE)= WEEK(T0."DocDate") AND
YEAR(CURRENT_DATE)= YEAR(T0."DocDate")
ORDER BY
YEAR(T0."DocDate"),
MONTH(T0."DocDate") DESC,
WEEK(T0."DocDate") DESC