SQL STATEMENT 1

[zimmer9]

My goal is to create the following 2 Aging report formats:

Report As of 2/16/2005
(Date range is 8/1/2004 to 1/31/05)

Less Than 6 Months Old # of Accounts ACCT VALUE
------------------------- ------------- ----------
100K and Above
50 K up to less than 100K
under 50K
------------------------------------------------------
(Date range is 7/31/2004 and prior)

6 Months Old Or Greater # of Accounts ACCT VALUE
------------------------- ------------- ----------
100K and Above
50 K up to less than 100K
under 50K
------------------------------------------------------

I am processing a database and extracting records by Account Number. Each Account Number can have multiple records. Each record includes an Account Number, Date Lost field and a Stock field.

I need to create 1 summary record per account number and calculate the total dollar value of the Stock field. The
records need to be segregated by date into an Aging Report.

I compare the Date Lost field on the database records to the Report Date ranges to determine which report section the records are written to.

For ex: John Smith has the following records:

ACCTNUM DATELOST STOCKS
---------- ---------- ----------
12345 2004-09-01 30,000.00
12345 2004-09-01 20,000.00
12345 2004-09-01 10,000.00
---------
60,000.00

Joe Fields has the following records:

ACCTNUM DATELOST STOCKS
---------- ----------- ----------
23456 2002-07-01 4,000.00
23456 2002-07-01 5,000.00
23456 2002-07-01 1,000.00
----------
10,000.00

Report As of 2/16/2005

(Date range is 8/1/2004 to 1/31/05)
Less Than 6 Months Old # of Accounts ACCT VALUE
------------------------- ------------- ----------
100K and Above
50 K up to less than 100K 1 60,000.00
under 50K

(Date range is 7/31/2004 and prior)
6 Months Old Or Greater # of Accounts ACCT VALUE
------------------------- ------------- ----------
100K and Above
50 K up to less than 100K
under 50K 1 10,000.00

Would I use a SQL Union statement to generate the report totals and I was unsuccessful ?

I tried the following SQL statement but it will not work:

SELECT COUNT, SUM(STOCKS)
FROM CUSTOMERS AS C, PRODUCTS AS P
WHERE C.ACCTNUM = P.ACCTNUM AND DATELOST BETWEEN '2004-08-01' AND '2005-01-31'
GROUP BY ACCTNUM

UNION ALL

SELECT COUNT, SUM(STOCKS)
FROM CUSTOMERS AS C, PRODUCTS AS P
WHERE C.ACCTNUM = P.ACCTNUM AND DATELOST LESS THAN
'2005-01-31'
GROUP BY ACCTNUM

 

[SQLSister]

Have you tried using a < sign rather than Less than?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[zimmer9]

I made the change to use the < sign.

The error I get is "You tried to execute a query that does not include the specified expression Date Lost between 8/1/2003 and 1/31/2004 as part of an aggregate function".

Do I have to create a subselect ? I guess the problem is that when I try and create the query in the QBE grid, it converts the between date statement to the HAVING CLAUSE which is evaluated after the aggregate function. Thus, the date comparison can't be made after the records are summarized.

 

[PHV]

To avoid the having clause choose 'Where' in the dropdown list of actions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

You tried to execute a query that does not include the specified expression Date Lost between 8/1/2003 and 1/31/2004 as part of an aggregate function".

This error happens when you an aggregate function (like SUM, COUNT, etc) and have included a field in the select statement that is not included in the GROUP BY statement.

For instance, if I wanted to get a count of some field by date:

SELECT SomeDateField, Count(*) As "Counter" FROM MyTable
GROUP BY SomeDateField

I would end up with:

SomeDateField   "Counter"
1/1/05                5
1/2/05                7
1/3/05                9

If however I need another field, say sales type, I have to add that field to the GROUP BY clause as well as the SELECT statement:

SELECT SomeDateField, SalesType, Count(*) aS "Counter" FROM MyTable GROUP BY SomeDateField, SalesType

SomeDateField      SalesType    COunter
1/1/05             Sale            3
1/1/05             Return          2
1/2/05             Sale            5
1/2/05             Return          2
1/3/05             Sale            9

Leslie

 

[zimmer9]

Leslie I want to thank you for the detailed explanation.
You went the extra mile with an example which reinforced your point. Thanks for your time.