CODEWITHSUNDEEP
t-sqlfilterinterbase
Sam
Sam

Reputation: 35

Filtering records based on where customers were invoiced one month and paid in another

So i currently have a list of records that i am trying to filter based on when they were invoiced and when they paid for the appointment I want only records where the customer was invoiced and did not pay until the next month ie they had their appointment and were invoiced in Feb but didnt pay till March

My query so far:

SELECT TRANS.WHENTRANS ACDATE,
    TRANS.AMTUNRES UNPAID,
    TRANS.TRANTYPE,
    TRANS.INCPROVCODE IPROV,
    ACHOLDERACH.ACBALANCE BALANCE,
    ACHOLDERANY.PREFORMATTEDINTERN ACCOUNTNAMECHART,
    Amount
FROM (  TRANS
  LEFT JOIN ACHOLDER ACHOLDERACH ON ACHOLDERACH.ACHOLDERID = TRANS.ACHOLDERID)
  LEFT JOIN ANYBODY ACHOLDERANY ON ACHOLDERANY.ANYBODYID = TRANS.ACHOLDERID



 where TranType = 'I' and amount = x
and TranType = 'P' and amount = -x and date = current month

Upvotes: 0

Views: 44

Answers (1)

BusinessIntelligenceGuru
BusinessIntelligenceGuru

Reputation: 11

The following worked for me assuming that the Invoice and Payment are two separate rows in the table.

CREATE TABLE #TEST (ACDATE DATE, UNPAID VARCHAR(1), TRANTYPE VARCHAR(1), IPROV VARCHAR(5), ACBALANCE FLOAT, ACCOUNTNAMECHART VARCHAR(100), AMOUNT FLOAT)
INSERT INTO #TEST
(ACDATE, UNPAID, TRANTYPE, IPROV, ACBALANCE, ACCOUNTNAMECHART, AMOUNT)
VALUES
('8/1/2016', 'Y', 'I', '003', 0, 'TESTNAME', 150.50),
('9/1/2016', 'N', 'P', '003', 0, 'TESTNAME',150.50)
SELECT A.ACDATE AS INVOICEDATE, A.AMOUNT AS INVOICEAMT, B.ACDATE AS PAYMENTDATE, B.AMOUNT AS PAYMENTAMT
FROM
(
    SELECT *
    FROM #TEST
    WHERE TRANTYPE = 'I'
) A
LEFT JOIN
(
    SELECT *
    FROM #TEST
    WHERE TRANTYPE = 'P'
) B ON A.ACCOUNTNAMECHART = B.ACCOUNTNAMECHART -- OR YOU CAN USE WHATEVER KEY FIELDS YOU HAVE TO HOOK TH INVOICE TO THE PAYMENT
WHERE MONTH(A.ACDATE) = MONTH(DATEADD(MM,-1,B.ACDATE))

Upvotes: 1

Related Questions