AR Aged Debt Bucket Due dates

Good Afternoon guys. I need help to write the SQL code below:
I need to calculate Aged bucket debts in days where the invoice date compares with the number of days

For Example : calculation for 0 – 30 days.

Invoice total = $200.
Last Invoice date = 01/05/19

Aged Date = entered in dialogue = 22/05/19

I need to implement this logic in SQL: If the invoice date - 01/05/19 is greater than or equal to Aged Date minus 22/05/19 – 30 (22/04/19)
then it will display the total due for that particular invoice.

I hope this make sense.
Thank you in advance.
Bekky

Welcome, please help us help you by providing sample data as follows

---we dont know your table columns
declare @sample table(id int, invoicedate datetime, invoiceamount money) 

insert into @sample
select 1, getdate(), 1914.13

Thanks, Yosiasz for getting back. Please find my sample table below:

declare @sample table(CustID int, invoiceID varchar(20), invoicedate datetime, invoiceamount float)

INSERT INTO @sample (CustID, invoiceID, invoicedate, invoiceamount)
VALUES (46380, '9001429535', '2018-06-30',70.00);

INSERT INTO @sample (CustID, invoiceID, invoicedate, invoiceamount)
VALUES (46380, '9001709570', '2018-09-30',185.00);

INSERT INTO @sample (CustID, invoiceID, invoicedate, invoiceamount)
VALUES (46380, '1003998056', '2020-12-31',50.00);

INSERT INTO @sample (CustID, invoiceID, invoicedate, invoiceamount)
VALUES (106429, '0002436660', '2019-07-31',80.00)

INSERT INTO @sample (CustID, invoiceID, invoicedate, invoiceamount)
VALUES (106429, '0002729420', '2020-01-31',90.50)

INSERT INTO @sample (CustID, invoiceID, invoicedate, invoiceamount)
VALUES (106429, 0002988848, '2020-12-31',100.00)

INSERT INTO @sample (CustID, invoiceID, invoicedate, invoiceamount)
VALUES (119558, 9001599620, '2018-08-31',57.00)

INSERT INTO @sample (CustID, invoiceID, invoicedate, invoiceamount)
VALUES (119558, '9001866447', '2020-12-30',140.00)

INSERT INTO @sample (CustID, invoiceID, invoicedate, invoiceamount)
VALUES (119558, '0002134324', '2019-02-28',127.00)

Select CustID, invoiceID, invoicedate, invoiceamount, cast('' as datetime) AS DueDate, cast('' as int) AS DaysOutstanding
from @sample

Thanks in advance
Bekky

I fixed up your sample data - it had some issues...if I am understanding your requirement then it is really just as simple as selecting any invoice where the invoice date is greater than 30 days prior to the 'Aged Date'.

The problem is how you define the 'Aged Date' - which you just stated is from some 'dialogue' which doesn't exist in SQL Server.

Declare @sample Table(CustID int, invoiceID varchar(20), invoicedate datetime, invoiceamount float);

 Insert Into @sample (CustID, invoiceID, invoicedate, invoiceamount)
 Values ( 46380, '9001429535', '2018-06-30', 70.00)
      , ( 46380, '9001709570', '2018-09-30', 185.00)
      , ( 46380, '1003998056', '2020-12-31', 50.00)
      , (106429, '0002436660', '2019-07-31', 80.00)
      , (106429, '0002729420', '2020-01-31', 90.50)
      , (106429, '0002988848', '2020-12-31', 100.00)
      , (119558, '9001599620', '2018-08-31', 57.00)
      , (119558, '9001866447', '2020-12-30', 140.00)
      , (119558, '0002134324', '2019-02-28', 127.00);

Declare @agedDate datetime = '2019-05-22';

 Select *
      , dateadd(day, -30, @agedDate)
   From @sample s
  Where s.invoicedate > dateadd(day, -30, @agedDate);

Note: on this forum you create code segments using three back-ticks before and after the code segment. You can also include the type of code on the first part to identify the type of code.

image

I think op means a dialog from an application they run?

Thanks jeffw8713. I appreciate your help. You are right. What I want to do is to calculate on the fly, the standard 30 days payment plan to invoice date as my Due date calculated column and then used the due date to calculate the outstanding debts days. Does this make sense
Thanks

Not really - where are you getting the standard due date from? I would think that data is defined somewhere for that account - and each account would have a different due date.

Once you have that - it should be easy enough to calculate from the due date back 30 days (or is that really one month?).

The standard Due date is 30 days after the last invoice date. It is not held in any tables, so to get the Due date, 30 days will need to be added to the last invoice date and used the due date for the age bucket.

So how are you defining the age buckets?

It isn't clear where you are getting the aged date - or how that is going to be used. What is the expected result you want to see?

The expected result should look like this:

And how do you get to those results from the data you provided? There seems to be a lot of missing data - and requirements here.

1 Like

every post seems to be going into another rabbit hole. you need to provide much clearer requirements. which sample data you provided matches the expected result image you posted?

1 Like