Need help with SQL Query - Payment due with Aging

[ZABARVAN]

Hi

I need help with SQL Query to get outstanding payment still pending with customers sorted by salesman code. All the fields are in a single table.

The result need is as following :-

Over due more than 6 months (180 Days) = 99999 Due amount
Over due more than 3 months (90 days ) = 99999
between 1 and 2 months = 99999
Sorted by salesman code

fields we have invoice date, due amount, last payment date, salesman code all fields in a single table.

Your help appreciated.
Thanks
Zab

 

[bborissov]

Could you please post some example data and desired result from it?

Borislav Borissov
VFP9 SP2, SQL Server

 

[ZABARVAN]

eg.

LastPaymentDate > 180 day
then
Print sum(PaymentDue)
OR if lastPaymentDate >90 days AND lastPaymentDate <180 days
THEN PRINT SUM(PAYMENTDUE) // As amount due for more than 6 months
OR if LastPaymentDate <90 // as amount due for more than 3 months
Then Print SUM(Paymentdue) // As amount due for less than 3 months

LastPaymentdate is a DATE field.

Hope it helps....

 

[bborissov]

Nope, this is not data,
Give an example of your data and what you want from it.


Borislav Borissov
VFP9 SP2, SQL Server

 

[ZABARVAN]

Assume as following :-
Rec. inv_amt inv_date sales_man_code customer_code Amt_bal last_pay_date
1 5000.00 15/02/2015 005 A003 5000.00 Nil (Means Not Paid)
2 10200.00 21/03.2015 005 A007 0.00 07/04/2015 (Means Paid)
3 750.00 12/04/2015 005 A005 750.00 Nil (Means Not paid)
4 9500.00 25/06/2015 005 A003 9500.00 Nil (means Not paid)
5 15750.00 07/07/2015 005 A007 15750.00 Nil (means Not Paid)
6 250.00 10/07/2015 005 A010 0.00 12/07/2015 Paid on this date.
7 150.00 15/07/2015 005 A010 150.00 Nil Not paid.

We need a query to check the invoice amount NOT PAID yet, which has exceeded more than 6 months, like we have to sum all invoices whose invoice date is more than 6 months old and not paid yet (lasy_pay_dat=NIL) in one group.
Another group will be for pending payment for 3 months and last group for 1 month.
We need to group the sum of pending payments in the slabs of 6 months , 3 months and 1 month.
it is an payments aging report

Hope this helps.

Thansk

 

[bborissov]

DECLARE @test TABLE (rec int, inv_amt numeric(14,2), inv_date datetime, sales_man_code char(3), customer_code char(5), Amt_bal numeric(14,2),  last_pay_date datetime null)

INSERT INTO @test VALUES (1,  5000.00, '20150215','005','A003',5000.00,NULL)
INSERT INTO @test VALUES (2, 10200.00, '20150321','005','A007',   0.00,'20150407')
INSERT INTO @test VALUES (3,   750.00, '20150412','005','A005', 750.00,NULL)
INSERT INTO @test VALUES (4,  9500.00, '20150625','005','A003',9500.00,NULL)
INSERT INTO @test VALUES (5, 15750.00, '20150707','005','A007',15750.00,NULL)
INSERT INTO @test VALUES (6,   250.00, '20150710','005','A010',    0.00,'20150412')
INSERT INTO @test VALUES (7,   150.00, '20150715','005','A010',  150.00,NULL)


--- Just to remove time part from date
DECLARE @Today date = GETDATE()


SELECT sales_man_code
     , customer_code
     , SUM(inv_amt) AS inv_amt
     , CASE WHEN DATEDIFF(MM,inv_date,@Today) > 5
                 THEN '6 or more months'
            WHEN DATEDIFF(MM,inv_date,@Today) > 2 
                 THEN '4-5 months'
            WHEN DATEDIFF(MM,inv_date,@Today) > 0 
                 THEN '1 or 2 months'
            END AS Grp
FROM @test
WHERE last_pay_date IS NULL
GROUP BY sales_man_code
       , customer_code
       , CASE WHEN DATEDIFF(MM,inv_date,@Today) > 5
                   THEN '6 or more months'
              WHEN DATEDIFF(MM,inv_date,@Today) > 2 
                   THEN '4-5 months'
              WHEN DATEDIFF(MM,inv_date,@Today) > 0 
                   THEN '1 or 2 months'
         END

Borislav Borissov
VFP9 SP2, SQL Server

 

[ZABARVAN]

Thank you so much... Will test this code.