Skip to Content
0
Jan 25, 2023 at 01:50 PM

Query sales ($) of the current week by Warehouse

17 Views

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