SQL Exercise -14

SQL Exercise:

Write a SQL query to classify invoices based on aging buckets:

• Due within the last 30 days: Due date is within the last 30 days from today.
• Due within the last 60 days: Due date is between 31 and 60 days in the past.
• 60+ Days Dues: Due date is more than 60 days in the past.

 

Create table script:

CREATE TABLE Invoices (
InvoiceID INT PRIMARY KEY,
DueDate DATE NOT NULL
);

Data Insertion script:

 INSERT INTO Invoices (InvoiceID, DueDate) VALUES
(1, '2024-12-10'),
(2, '2024-11-30'),
(3, '2024-11-01'),
(4, '2024-10-15'),
(5, '2024-09-15');

Solution:

SELECT 
InvoiceID,
DueDate,
CASE 
WHEN DATEDIFF(DAY, DueDate, GETDATE()) <= 30 THEN 'Due within last 30 days'
WHEN DATEDIFF(DAY, DueDate, GETDATE()) BETWEEN 31 AND 60 THEN 'Due within last 60 days'
ELSE '60+ Days Dues'
END AS AgingBucket
FROM 
Invoices;

Output:

Explanation:

• Use the SQL DATEDIFF function to calculate the number of days between the current date (GETDATE()) and the due date. The current date is ’12/15/2024′.
• Classify invoices into buckets based on the difference:
  • If the difference is ≤ 30, the invoice falls into the “Due within last 30 days” bucket.
  • If the difference is between 31 and 60, the invoice falls into the “Due within last 60 days” bucket.
  • If the difference is > 60, the invoice falls into the “60+ Days Dues” bucket.
• Steps in Query:
  • Use a CASE statement to implement the conditions for each bucket.
  • Calculate aging for each invoice using the DATEDIFF function.
  • Group invoices into the defined aging categories.