David Ebert wrote:
> I'd like to create this as a view to write a report to which I can pass
> program and date range parameters. The view would include WEEKNUMBER,
> SUM(UNITS), COUNT(CLIENTS).
Dave,
How about doing a CREATE TEMP TABLE....
and then load up all the records you need in
order to do this. Then use an UPDATE
statement to fill the TEMP TABLE in a bit?
Like this:
CREATE TEMP TABLE tmpTableName +
(ClientId INT, CaseNumber INT, Week INT, CountSvc INT)
Now fill the temp table with the first shot:
INSERT INTO tmpTableName +
(ClientId, CaseNumber) +
SELECT ClientId, CaseNumber +
FROM ClientTable +
WHERE .....
Now create a view that has the aggregate
data you want:
CREATE VIEW vViewName AS +
SELECT COUNT (DIST CaseNumber) AS CaseCount, +
WeekNumber +
FROM Service +
GROUP BY WeekNumber
Now go back and get the other data:
UPDATE tmpTable SET +
Week = t2.WeekNumber, +
CountSvc = t2.CaseCount +
FROM tmpTable t1, vViewName t2 +
WHERE t1.CaseNumber = t2.CaseNumber
Or whatever links the two... my examples are
really poor in terms of the column names and
such but I think you still get the idea.
Then create your report based on the temp
table.
And a suggestion... if you do this, you may want
to create a real table at first. Drop off the TEMP
part until you're all done. Otherwise, if you exit
the DB to go have lunch, you'll have to re-create
your table. When you've got it as you want it,
then drop the permanent table and put the word
TEMP back in your command file.
Scott
====
Scott J. Salisbury
Matrix Data Systems / The R:Street Journal
5114 Point Fosdick Drive NW, Suite E-272
Gig Harbor, WA 98335-1733
E-Mail Address: [EMAIL PROTECTED]
Temporary URL: www.seanet.com/~sjs (downloads available)
New Web Address: www.rStreetJournal.Com
Direct Line: 206-423-7679
Toll Free: 888-386-5040
Seattle: 206-448-7975
Fax: 305-425-0491
