Skip to Content
0

Query for sales register

Oct 10, 2017 at 12:51 AM

98

avatar image

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]'
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
DIEGO LOTHER Oct 10, 2017 at 02:07 AM
1

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

Share
10 |10000 characters needed characters left characters exceeded
Jim Bo Oct 10, 2017 at 05:43 AM
0

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?

Show 2 Share
10 |10000 characters needed characters left characters exceeded

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

0

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

0