Aging report

[myxmaster]

Hi,
I would like to create a report that shows all 'Bad" records 0 -30
days, 31-60 days, 61 -90 days., 90 - 180 days
The data would come from a table called transactions, bases on the
status field.
TIA

 

[Guest]

Try this --
SELECT Partition(DateDiff("d",[Status],Date()),1,9999,30) AS [Bad Days]
FROM transactions
GROUP BY Partition(DateDiff("d",[Status],Date()),1,9999,30);

 

[Michael Gramelspacher]

Hi,
I would like to create a report that shows all 'Bad" records 0 -30
days, 31-60 days, 61 -90 days., 90 - 180 days
The data would come from a table called transactions, bases on the
status field.
TIA

This is just by way of example since you did not furnish any information to
work with.

Open Northwind sample database and select new query and past this into the SQL
view. (watch for line wrapping)

SELECT Customers.CompanyName,
(SELECT SUM(IIF(DATEDIFF("d",[a.OrderDate],[a.ShippedDate]) < 5,+1,0))
FROM Orders AS a
WHERE Orders.CustomerID = a.CustomerID) AS [Less than 5 days],
(SELECT SUM(IIF(DATEDIFF("d",[a.OrderDate],[a.ShippedDate]) BETWEEN 6 AND 10,
+1,0))
FROM Orders AS a
WHERE Orders.CustomerID = a.CustomerID) AS [6 - 10 days],
(SELECT SUM(IIF(DATEDIFF("d",[OrderDate],[ShippedDate]) BETWEEN 11 AND 15, +
1,0))
FROM Orders AS A
WHERE Orders.CustomerID = a.CustomerID) AS [11 - 15 days],
(SELECT SUM(IIF(DATEDIFF("d",[OrderDate],[ShippedDate]) > 15, +1,0))
FROM Orders AS A
WHERE Orders.CustomerID = a.CustomerID) AS [Greater than 15 days]
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Orders.CustomerID,Customers.CompanyName;