cancel
Showing results for 
Search instead for 
Did you mean: 

Query Generator

lesleysherry7775
Explorer
0 Kudos
144

I am trying to write a query using query generator.  I need to display

BP Code, BP Name from OINV table and monthly total of Weight1 from INV1 table.

I can get all invoices with weight totals separately but I cannot get a total weight per month.

I also need to be able to select a date range.

Can anyone help please.

Many thanks

Accepted Solutions (0)

Answers (1)

Answers (1)

fabiobilicki
Active Contributor

Hi this is basic SQL

Rgs

SELECT 
T0."CardCode", 
T0."CardName",
MONTH(T0."DocDate") AS 'Month', 
YEAR(T0."DocDate") AS 'Year',
SUM(T1."Weight1") AS 'Sum Weight1'
FROM OINV T0  
INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry" 
GROUP BY 
T0."CardCode", 
T0."CardName",
MONTH(T0."DocDate"), 
YEAR(T0."DocDate")
ORDER BY
T0."CardCode", 
T0."CardName",
MONTH(T0."DocDate"), 
YEAR(T0."DocDate")