Help With Query For Credit Value By Date Range

[RahF]

Hello All

I have written a query to check for all credit notes entered in SAP between a date range

It works fine, but i need to tweek it a bit and i have been unable to do so

The query is as below

SELECT T0.[DocDate],

T0.[CardName],

T0.[NumAtCard],

T0.[DocNum],

T1.[ItemCode],

T1.[Dscription],

T1.[Quantity],

T1.[PriceBefDi],

T1.[DiscPrcnt],

T1.[LineTotal],

T0.[Comments]

FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.[DocDate] > [%0] and T0.[DocDate] < [%1]

ORDER BY T0.[DocDate]

What i want to achieve is

I need the query to group by customer and show sub total for each customer

I need the query to only show credit notes that have the word GRA# in the remarks column

The reason being is people make mistakes sometime and sometimes a credit note needs to be entered to correct the error

I want the query to exclude these credit notes. All genunine returns have the word GRA# in the remarks column

Is this possible?

[Former Member · Accepted solution]

Hi,

Try:

SELECT T0.[CardName],

T0.DocDate,

T0.[NumAtCard],

T0.[DocNum],

T1.[ItemCode],

T1.[Dscription],

T1.[Quantity],

T1.[PriceBefDi],

T1.[DiscPrcnt],

T1.[LineTotal],

T0.[Comments]

FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.[DocDate] > [%0] and T0.[DocDate] < [%1] and T0.[Comments] Like '%%GRA#%%'

UNION ALL

SELECT T0.[CardName],getdate(), 'Total','','','',sum(T1.[Quantity]),0,0,sum(T1.[LineTotal]),''

FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.[DocDate] > [%0] and T0.[DocDate] < [%1] and T0.[Comments] Like '%%GRA#%%'

Group By T0.[CardName]

Order By T0.[CardName], T0.[DocDate]

Thanks,

Gordon

[RahF]

Hi Nagarajan

I tried it

It is selecting the credit notes with the word GRA# in it

That works perfectly

But it doesn't group by Customer Name and i am not getting a sub total for each customer

[kothandaraman_nagarajan]

Hi,

Yes possible.

Try this:

SELECT T0.[DocDate],

T0.[CardName],

T0.[NumAtCard],

T0.[DocNum],

T1.[ItemCode],

T1.[Dscription],

sum(T1.[Quantity]),

T1.[PriceBefDi],

T1.[DiscPrcnt],

sum(T1.[LineTotal]),

T0.[Comments]

FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.[DocDate] > [%0] and T0.[DocDate] < [%1] and T0.[Comments] Like '%%GRA#%%'

GROUP BY T0.[DocDate],

T0.[CardName],

T0.[NumAtCard],

T0.[DocNum],

T1.[ItemCode],

T1.[Dscription],

T1.[PriceBefDi],

T1.[DiscPrcnt],

T0.[Comments]  ORDER BY T0.[DocDate]

Thanks & Regards,

Nagarajan