on 07-26-2011 2:12 AM
Hi Experts,
The Following query is about to show incoming payment list by customers / Search By payment date.
SELECT
T0.[CardName] AS 'Customer/Supplier Name',
T0.[DocTotal] AS 'Document Total',
T0.[DocDate] AS 'Payment Date'
FROM ORCT T0
WHERE T0.[DocDate] = [%0]
ORDER BY T0.[CardName]
I need to show following requirements.
1) exclude incoming payment history for today's sales payment
2) Sub Total by Payment Method whether Cash, Cheque, Credit Card or Bank Transfer.
Is it possible?
Edited by: Stephen Kang on Jul 26, 2011 3:18 AM
Hi Stephen......
Try this sample query.....
SELECT 'Cash', T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[CashAcct], T0.[CashSum] FROM ORCT T0 Where T0.[CashSum]>0
Union All
Select 'Cheque', T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[CheckAcct], T0.[CreditSum] From ORCT T0 INNER JOIN RCT1 T1 ON T0.DocEntry = T1.DocNum Where T0.[CheckSum]>0
Union All
Select 'Credit', T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T1.[CreditAcct], T0.[CreditSum] From ORCT T0 INNER JOIN RCT3 T1 ON T0.DocEntry = T1.DocNum Where T0.[CreditSum]>0
Union All
SELECT 'Transfer', T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[TrsfrAcct], T0.[TrsfrSum] FROM ORCT T0 Where T0.[TrsfrSum]>0
Hope you get an Idea......
Regards,
Rahul
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Rahul.
Thanks for reply.
SELECT
'CHEQUE',
T0.DocDate as 'Posting Date',
T1.CardName,
T1.DocDate as 'Payment Date',
T1.DocTotal as 'Paid To Date'
FROM OINV T0
LEFT JOIN ORCT T1 ON T0.ReceiptNum = T1.DocEntry
LEFT JOIN RCT1 T2 ON T1.DocNum = T2.DocNum
Left Join OSLP T3 ON T3.SlpCode= T0.SlpCode
WHERE T1.[CheckSum] > 0 AND DateDiff(D,T1.DocDate,GetDate()) = 0 AND DateDiff(D,T0.DocDate,GetDate()) > 0
UNION ALL
SELECT
'CREDIT',
T0.DocDate as 'Posting Date',
T1.CardName,
T1.DocDate as 'Payment Date',
T1.DocTotal as 'Paid To Date'
FROM OINV T0
LEFT JOIN ORCT T1 ON T0.ReceiptNum = T1.DocEntry
LEFT JOIN RCT1 T2 ON T1.DocNum = T2.DocNum
Left Join OSLP T3 ON T3.SlpCode= T0.SlpCode
WHERE T1.[CreditSum] > 0 AND DateDiff(D,T1.DocDate,GetDate()) = 0 AND DateDiff(D,T0.DocDate,GetDate()) > 0
UNION ALL
SELECT
'CASH',
T0.DocDate as 'Posting Date',
T1.CardName,
T1.DocDate as 'Payment Date',
T1.DocTotal as 'Paid To Date'
FROM OINV T0
LEFT JOIN ORCT T1 ON T0.ReceiptNum = T1.DocEntry
LEFT JOIN RCT1 T2 ON T1.DocNum = T2.DocNum
Left Join OSLP T3 ON T3.SlpCode= T0.SlpCode
WHERE T1.[CashSum] > 0 AND DateDiff(D,T1.DocDate,GetDate()) = 0 AND DateDiff(D,T0.DocDate,GetDate()) > 0
UNION ALL
SELECT
'BANK TRANSFER',
T0.DocDate as 'Posting Date',
T1.CardName,
T1.DocDate as 'Payment Date',
T1.DocTotal as 'Paid To Date'
FROM OINV T0
LEFT JOIN ORCT T1 ON T0.ReceiptNum = T1.DocEntry
LEFT JOIN RCT1 T2 ON T1.DocNum = T2.DocNum
Left Join OSLP T3 ON T3.SlpCode= T0.SlpCode
WHERE T1.[TrsfrSum] > 0 AND DateDiff(D,T1.DocDate,GetDate()) = 0 AND DateDiff(D,T0.DocDate,GetDate()) > 0
The above "where" statement is perfect, however, the Paid amount is duplicating if incoming payment link to multiple posting date's invoice. How can I keep "WHERE" statement and just show incoming payment total for each customer only?
Hi,
Try this to see if it return what you need for one kind of payment:
SELECT
'BANK TRANSFER' 'Type',
T0.DocDate as 'Posting Date',
T1.CardName,
T1.DocDate as 'Payment Date',
SUM(T2.SumApplied) as 'Paid To Date'
FROM RCT2 T2
INNER JOIN OINV T0 ON T0.DocEntry=T2.DocEntry
LEFT JOIN ORCT T1 ON T2.DocNum = T1.DocNum
Left Join OSLP T3 ON T3.SlpCode= T0.SlpCode
WHERE T1.TrsfrSum > 0 AND DateDiff(D,T1.DocDate,GetDate()) != 0 AND DateDiff(D,T0.DocDate,GetDate()) > 0
GROUP BY T0.DocDate,T1.CardName,T1.DocDate
Thanks,
Gordon
Hi Gordon. Thanks for reply.
The above query result in all the payment date's record and shows seperated amount per records as paid to date even though one incoming payment was.
Conditions:
1) Records might not include today's sales incoming payment. It need to show today's incoming payment which linked previous date of A/R invoice only.
2) Incoming Payment Amount need to show only total amount of incoming payment not seperated as document total for sales A/R Invoice.
Is it possible?
Type CardName Payment Date Incoming Payment Amount
Bank Transfer ABC 29.07.11 10.00
User | Count |
---|---|
86 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.