Query for sales register

[jimbo1234]

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]'

[former_member185682 · Accepted solution]

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

[jimbo1234]

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?

[gonzalogomez]

You have to insert group by, for example with cardcode, but in the select yo must to insert te function sum to the fields to summarize.

It´s better use crystal reports to your requirement

[jimbo1234]

Sorry, I'm a newbie, is there a resource on using Crystal as you suggested?