Can you solve this SQL question??

Let's say I have an invoice table named "Invoice" and it looks like:

InvNum       int
InvDate      datetime
Customer     varchar(10)
InvTotal     money
InvBalance   money

I know it's not normalized, but anyway...

What's a SQL statement that will satisfy this question:

Show me all customers that have invoice balances that are within 30
days old, 31 to 60 days old, 61 to 90 days old, and more than 90 days
old. Also include the total amount for each time bracket. Sort by
customer.

In other words I need an aging table with six columns, such as:

Customer, TotalOutstanding, 30Days, 31-60Days, 61-90Days, 90+Days

(By the way, I'm using MS-SQL Server v6.0)

Thanks in advance!

---

Visit my Web page at http://www.*-*-*.com/ ~lesgainous

"This message may not reflect the views of anyone or any
organization, other than myself."

..

Yes there is way, use sub-queries:

select distinct t.customer,
    'TotalOutstanding'=(select sum(t.invtotal)
            from Invoice s
            where s.invnum=t.invnum),
   '30days'=(select sum(t.invtotal)
            from Invoice s
            where s.invnum=t.invnum and datediff(day, s.invdate, getdate())<31),
   '31-60Days'=(select sum(t.invtotal)
            from Invoice s
            where s.invnum=t.invnum and datediff(day, s.invdate, getdate()) betwen 31 and 60),
   '61-90Days'=(select sum(t.invtotal)
            from Invoice s
            where s.invnum=t.invnum and datediff(day, s.invdate, getdate()) betwen 61 and 90),
   '90+Days'=(select sum(t.invtotal)
            from Invoice s
            where s.invnum=t.invnum and datediff(day, s.invdate, getdate())>90)
from Invoice t

This is the general idea, I have not syntax checked it but it should work..

Good luck
--
---------------------------------------------------------
"The rules of morality are not the conclusion of
our reason", David Hume
                                Oystein Johnsen

---------------------------------------------------------

I don't think so.

What you could do is place the columns Col30To59; Col31To60;
Col61To90; Col91Up

Then each day run an update querey to update those columns dependant
on the other fields criteria.

This is not normalised either.

                            _
                         ,,' ',
Noel Harland           ,~     |
Perth,                {  W.A. |
Australia              }      |

                       <_,--'''

 >

 >
 > >Can you solve this SQL question??
 >
 > >Let's say I have an invoice table named "Invoice" and it looks like:
 >
 > >InvNum       int
 > >InvDate      datetime
 > >Customer     varchar(10)
 > >InvTotal     money
 > >InvBalance   money
 >
 > >I know it's not normalized, but anyway...
 >
 > >What's a SQL statement that will satisfy this question:
 >
 > >Show me all customers that have invoice balances that are within 30
 > >days old, 31 to 60 days old, 61 to 90 days old, and more than 90 days
 > >old. Also include the total amount for each time bracket. Sort by
 > >customer.
 >
 > >In other words I need an aging table with six columns, such as:
 >
 > >Customer, TotalOutstanding, 30Days, 31-60Days, 61-90Days, 90+Days
 >
 > >(By the way, I'm using MS-SQL Server v6.0)
 >
 > >Thanks in advance!
 >
 > >---

 > >Visit my Web page at http://home.earthlink.net/~lesgainous
 >
 > >"This message may not reflect the views of anyone or any
 > >organization, other than myself."
 >
 > I don't think so.
 >
 > What you could do is place the columns Col30To59; Col31To60;
 > Col61To90; Col91Up
 >

I Think it would be something like:

  SELECT
      Customer,
      SUM(InvBalance) AS TotalOutstanding,
      SUM(IIF(InvDate-Date()<=30, InvBalance, 0))
        AS Col30,
      SUM(IIF(InvDate-Date()>30 And InvDate-Date()<=60, InvBalance, 0))
        AS Col31To60,
      SUM(IIF(InvDate-Date()>60 And InvDate-Date()<=90, InvBalance, 0))
        AS Col61To90,
      SUM(IIF(InvDate-Date()>90, InvBalance, 0))
        AS Col91Up
    FROM Inventory
    GROUP BY Customer
  ;

I have had some success with this type of summary query in MS Access...
You may need to do some modifications for your package.

Good Luck.
-patrick

Or you could have a query for each of the 30, 31-60, 61-90, 90+ days, and
totals columns, then a query to tie the previous five querys together.  I
do something similar at the moment and it works ok for me.

Can I suggest that you create a table to join to, such as

create table DaysOutstanding (
    mindays  integer,
    maxdays  integer,
    desc     text(30)
);

where the primary key is (mindays,maxdays) and the rows are

values (   0,  30, "Zero to 30 days" )
values (  31,  60, "31 to 60 days" )
values (  61,  90, "61 to ninety days" )
values (  91, 120, "91 to 120 days" )
values ( 121, 999, "Over 120 days" )

Then all you have to do is to "join" your transaction days outstanding a la  
"where trxdays between DaysOutstanding.mindays and DaysOutstanding.maxdays"
in order to group in a completely normalized fashion, complete with
count, sum and other standard functions available to you.

I have used this and similar structures to simplify many otherwise complex
computational procedures.

How's that?

p.s. Please excuse the pseudo-SQL, especially for the table create.

Assistant Professor             |  voice:  541-885-1346
Management Information Systems  |  fax:    541-885-1687
Oregon Institute of Technology  |  The Purpose of Education is *Action*

instead of/in addition to date. In this way you have a better
search-criteria (also due to problems of syncronizy between
database-date-format and VB-date-format)

Then you can quite simply do a select :

"SELECT * FROM X WHERE ((DayNo>=1 AND (DayNo<=30))"

etc...

Hope it's a bit helpful

Sincre regards,
Lars Brenna
Tempus AS

Try this,

SELECT DISTINCTROW
IIf(Date()-[InvDate]<=30,"0-30",IIf(Date()-[InvDate]<=60,"31-60",IIf(Date()-[InvDate]<=90,"61-90","90+")))
AS [Age Group], Sum(Table1.InvTotal) AS [Sum of Total]
FROM Table1
GROUP BY
IIf(Date()-[InvDate]<=30,"0-30",IIf(Date()-[InvDate]<=60,"31-60",IIf(Date()-[InvDate]<=90,"61-90","90+")));

--
Cheers.

Here's one more...

If you're using SQL Server 6.0, just use a stored procedure.
Inside the stored procedure you could do 3 or 4 queries, adding the
result sets to a temporary table and ouput the temporary table when
finished.

Depending on the what you're doing with the final resultset this may
or not work for you.

e = (42 + m * c) * (c) - (42 * c)
// I knew Douglas Adams had something with that 42 thing...

You will not need 3-4 queries, since the SQL Server CASE statement, within SELECT,
should do just fine.

--
----------------------------------------------------------------------------

Knowledge Management Consultants, PO Box 72267, Parkview, 2122, South Africa
  << Leaders in VB, Access, C/C++, SQL Server Development and Training >>
  << call us about our IT Developer Training Centre in the Drakensberg >>
TEL: +27-11-880-9153       FAX: +27-11-442-5529        CELL: +27-82-881-0380