Query design with two GROUP BY headers
Posted on 2001-08-29
I'm using VB6, SQL Server 7, and CR8.5.
I think this is more of a SQL design question but a large part of it depends on how I want the data displayed in my Crystal Report.
My report should be grouped twice:
The outer group should be:
GROUP BY ANALYST
And the inner group should be:
GROUP BY BILLNUM
I think I need either a temporary table or I think I may be able to get the data from a nested SELECT statement; maybe an INNER QUERY or a SUBQUERY.
This is very complicated but I'll try to explain it the best I can.
In my inner group, since the billnum is the key field, the rest of the data here is correct.
But, when I add my outer group which is based on a different key field, Analyst; the outer group key field takes precedence over what is beneath it and screws up the values of the inner group.
I think I need a subquery because I need to get the value of the outer group first, and then group on that value for the inner group, or vice versa.
It should fall down like a hierarchy;
There's not that much code but the whole thing is that I have a stored procedure which takes a parameter and calls a view, which is a big select statement with all of the values I need for my report.
Here's my stored procedure:
CREATE PROCEDURE rptspMysp
SELECT * FROM rptvwMyview
WHERE rptvwMyView.Sponsor = @p1
ORDER BY rptvwMyview.Analyst, rptvwMyview.BillNum
And here's the view that it calls:
CREATE VIEW rptvwMyview
SELECT TOP 100 PERCENT
Bill.BillNum, Bill.Sponsor, Bill.Title, Bill.OriginalCommittee,
Bill.CommitteeDate, Bill.BAMCode, Bill.BillMemoCode,
Bill.Bill99Code, Bill.SenateNum, Requests.Requester,
LEFT JOIN BillAssignments
ON Bill.BillNum = BillAssignments.BillNum
LEFT JOIN Requests
ON Bill.BillNum = Requests.BillNum
LEFT JOIN CommitteeActions
ON Bill.BillNum = CommitteeActions.BillNum
LEFT JOIN BillComment
ON Bill.BillNum = BillComment.BillNum
(Requests.BillNum IS NOT NULL) OR
(Bill.Sponsor <> 'Budget') AND (Bill.Bill99Code = 'Y') AND
(CommitteeActions.ActionCode IS NULL) OR
(Bill.Sponsor <> 'Budget') AND
(CommitteeActions.ActionCode IS NULL) AND
(Bill.InWAM = 'Y') AND (Bill.BAMCode IN ('R', 'X', 'U'))
ORDER BY BillAssignments.Analyst, Bill.BillNum
Maybe I'm soing too much here (or not enough) or maybe there is a better way.
Anyway, since I'm at a standstill, I'll take any ideas or solutions.
Here's a sample of how I'd like my report to look:
BillNum Title CommDate BAMCode Analyst(subreport)
A00100 title 01/01/01 A Johnson
Thanks in Advance.