Skip to Content
0
Former Member
May 20, 2010 at 07:04 AM

Doc number range

37 Views

Hi all

I need a solution for a report that I've designed and its based on a sql query.

The information displayed on the report is

Invoice details and credit note details, so when a user runs this report they need to get a prompt for credit note and invoice number, parameters :-

From Invoice Number : (Mandatory)

To Invoice Number : Optional

From Credit Note Num: (Mandatory)

To credit Note Num : (Optional)

Date range : from and to.

The code I used is :

SELECT T1.[ObjType] as 'Object No', T1.[DocNum] as 'Doc Number', T1.[DocDate] as ' Doc Date', T1.[U_RefDocNo] as 'Internal Order', T1.[CardCode] as 'Customer Code', T1.[CardName] as 'Customer Name', T1.[NUmatCard] as ' Customer Order Number', (T1.[TotalExpns] *-1) as 'Delivery Charge', (T1.[VatSumSy] * -1) as 'Tax',( T1.[DocTotal] * -1) as 'Doc. Total' FROM ORIN T1

UNION ALL

SELECT T0.[ObjType] as ' Object No', T0.[DocNum] as 'Doc Number', T0.[DocDate] as 'Doc Date', T0.[U_RefDocNo] as 'Internal Order', T0.[CardCode] as 'Customer Code', T0.[CardName] as 'Customer Name',T0.[NUmatCard] as ' Customer Order Number', T0.[TotalExpns] as 'Delivery Charge', T0.[VatSumSy] as 'Tax', T0.[DocTotal] as 'Doc Total' FROM OINV T0 ORDER BY T1.[ObjType] DESC, T1.[DocNum]

Hope this is clear.

Regards

Bongani Dlamini