Skip to Content

Query for sales register

Hi

I am not very advanced yet in SAP B1, but have written a few queries.

I need a query to pull a sales register showing a list of all invoices and credits for a certain period with Date, Document Number,Customer, Document total (Incl. tax) and tax. But because of my UNION, the date criteria does not work. Can somebody please help.

SELECT T0.[DocDate], T0.[DocNum], T0.[CardCode], T0.[CardName],T0.[DocTotal], T0.[VatSum] FROM OINV T0
UNION ALL
SELECT T0.[DocDate], T0.[DocNum], T0.[CardCode], T0.[CardName], -1 * T0.[DocTotal], -1 * T0.[VatSum] FROM ORIN T0
WHERE T0.DocDate>='[%0]' and T0.DocDate<='[%1]'
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Oct 10, 2017 at 02:07 AM

    Hi Joe,

    You can add your condition for your first query too.

    SELECT 
    	T0.[DocDate], 
    	T0.[DocNum], 
    	T0.[CardCode], 
    	T0.[CardName], 
    	T0.[DocTotal], 
    	T0.[VatSum] 
    FROM 
    	OINV T0 
    WHERE T0.DocDate>='[%0]' and T0.DocDate<='[%1]'
    UNION ALL
    SELECT 
    	T0.[DocDate], 
    	T0.[DocNum], 
    	T0.[CardCode], 
    	T0.[CardName], 
    	-1 * T0.[DocTotal], 
    	-1 * T0.[VatSum] 
    FROM 
    	ORIN T0
    WHERE 
    	T0.DocDate>='[%0]' and T0.DocDate<='[%1]'
    

    Or do something like this:

    SELECT 
    	*
    FROM
    	(SELECT 
    		T0.[DocDate], 
    		T0.[DocNum], 
    		T0.[CardCode], 
    		T0.[CardName], 
    		T0.[DocTotal], 
    		T0.[VatSum] 
    	FROM 
    		OINV T0 
    	UNION ALL
    	SELECT 
    		T0.[DocDate], 
    		T0.[DocNum], 
    		T0.[CardCode], 
    		T0.[CardName], 
    		-1 * T0.[DocTotal], 
    		-1 * T0.[VatSum] 
    	FROM 
    		ORIN T0) T0
    WHERE
    	T0.DocDate>='[%0]' and T0.DocDate<='[%1]'
    

    Hope it helps.

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 10, 2017 at 05:43 AM

    Thanks Diego that is perfect!

    If I have grouped the query by the first column on my function configuration, is there any way I can display a subtotal per group? for example if I group by BP, can i get a subtotal for each BP showing?

    Add comment
    10|10000 characters needed characters exceeded