Aging report

  • Follow


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

0
Reply myxmaster 6/10/2007 5:03:27 AM

In article <1181451807.600064.310670@a26g2000pre.googlegroups.com>, 
myxmaster@hotmail.com says...
> 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;
0
Reply Michael 6/10/2007 11:49:13 PM


1 Replies
749 Views

(page loaded in 0.02 seconds)


Reply: