Compound aggregate (Max of Count) procedure question

[vincibleman]

Howdy all,

Working my way into SQL from Access. Think I might have the hang of the basics, but would really appreciate a sanity check. The stored procedure listed below works, but I can't help but think there is a better way to do this.

Gist of what I'm doing:
I need aggregate error data for each satellite in tblSatellite.
-Satellite code, name, etc.
-Count of number of errors over a given frame of time
-The Problem Type with the most errors for each satellite over that given frame of time, which may be more than one record if two Max & Count records equal each other. Sticky part here, basically doing a Max of Count.
-I filter out records with fkResolutionTypeID = 8 or 12 for business reasons.

Thoughts and problems:
-Maybe building a temp table with just the results of the innermost query would be beneficial? One table query, then further queries off the table variable. Right now the below query hits tblErrorList 3 times. Also, for some reason Access Projects (which I'm using right now as the primary client app), don't like table variables. Just returns empty record set.
-I could separate out the Max part of this as a separate procedure or function. ie, client app calls the SQL server to get the max problem type for each satellite separately. Questionable efficiency? My thinking (please correct me), is that fewer queries typically equals better performance.
-I could also forget all this SQL garbage and go back to drawing on my etch-a-sketch.

Any ideas are much appreciated!

Tables with used fields:

tblErrorList
-pkErrorID (Primary key)
-fkSatelliteID (foreign key to tblSatellite)
-DateOfService
-fkProblemTypeID (foreign key to tlkpProblemTypes)
-fkResolutionTypeID (foreign key, used here just to filter out 'non' problems)

tblSatellite
-pkSatelliteID
-SatelliteCode
-fkManagerID (foreign key to tlkpManager)
-NameOrLocation

tlkpProblemTypes
-pkProblemTypeID
-ProblemCode (description really)

tlkpManager
-pkManagerID
-ManagerName

CREATE PROCEDURE GetErrCountBySite_Type (@sDate AS SMALLDATETIME, @eDate AS SMALLDATETIME)
AS
SELECT tblSatellite.SatelliteCode, tblSatellite.NameOrLocation, tlkpManager.ManagerName, tlkpProblemTypes.ProblemCode, D.CountOfErrors
    FROM    (SELECT TOP 100 PERCENT fkSatelliteID, MAX(CountOfErrors) AS maxcount
        FROM    (SELECT TOP 100 PERCENT fkSatelliteID, fkProblemTypeID, COUNT(pkErrorID) AS CountOfErrors
            FROM dbo.tblErrorList
            WHERE (DateOfService >= CONVERT(DATETIME, @sDate, 102) AND DateOfService < CONVERT(DATETIME, DATEADD(d,1,@eDate), 102)) AND
                (fkResolutionTypeID IS NULL OR
                fkResolutionTypeID <> 8 AND fkResolutionTypeID <> 12)
            GROUP BY fkSatelliteID, fkProblemTypeID
            ORDER BY fkSatelliteID, fkProblemTypeID) C
        GROUP BY fkSatelliteID
        ORDER BY fkSatelliteID) A
INNER JOIN
    (SELECT TOP 100 PERCENT fkSatelliteID, fkProblemTypeID, COUNT(pkErrorID) AS CountOfErrors
        FROM dbo.tblErrorList
            WHERE (DateOfService >= CONVERT(DATETIME, @sDate, 102) AND DateOfService < CONVERT(DATETIME, DATEADD(d,1,@eDate), 102)) AND
            (fkResolutionTypeID IS NULL OR
            fkResolutionTypeID <> 8 AND fkResolutionTypeID <> 12)
        GROUP BY fkSatelliteID, fkProblemTypeID
        ORDER BY fkSatelliteID, fkProblemTypeID) B
ON B.fkSatelliteID=A.fkSatelliteID AND B.CountOfErrors=A.maxcount
INNER JOIN
    (SELECT TOP 100 PERCENT fkSatelliteID, COUNT(pkErrorID) AS CountOfErrors
        FROM dbo.tblErrorList
            WHERE (DateOfService >= CONVERT(DATETIME, @sDate, 102) AND DateOfService < CONVERT(DATETIME, DATEADD(d,1,@eDate), 102)) AND
            (fkResolutionTypeID IS NULL OR
            fkResolutionTypeID <> 8 AND fkResolutionTypeID <> 12)
        GROUP BY fkSatelliteID
        ORDER BY fkSatelliteID) D
ON D.fkSatelliteID=A.fkSatelliteID
INNER JOIN
    dbo.tblSatellite
ON tblSatellite.pkSatelliteID=A.fkSatelliteID
INNER JOIN
    dbo.tlkpProblemTypes
ON tlkpProblemTypes.pkProblemTypeID = B.fkProblemTypeID
INNER JOIN
    dbo.tlkpManager
ON tlkpManager.pkManagerID=tblSatellite.fkManagerID
 

[Delerna]

If you want to try the table variable method you could shift your query over to a user defined function that returns a table variable and then your stored procedure would be a simple select from the user defined function.

Access shouldn't have any problem with that.

I have done that sort of thing with table variables so the records to be worked with are selected from the main table only once. and then another query works multiple times with the data in the table variable.

I have achieved good results for otherwise slow queries

[Jim Doherty]

Hi vincibleman,

Subscribing to this thread simply because I love a person who clearly pays attention to naming convention, thinks their process through analyses their own method, tries it out, questions it, seeks external opinion and in so doing provides metadata that instantly enables one to replicate the situation based on the post. Welcome to the scripts vincibleman.!!

Give me a short time to play with this on my box. Which version Access ADP and which version SQL Server are you using please?

Regards

Jim :)

[vincibleman]

Thanks Delerna for that idea. For some reason I hadn't thought of just splitting it off like that. 'Hiding' the table variable from Access will hopefully get rid of that problem (whatever that problem is).

And thanks for the confidence boost Jim. My 'schooling' in SQL so far has been off the web and out of books, so it's definitely good to hear my methods are generally sound from another living person.

Versions:
SQL Server 2000 SP4

Access 2003 SP2

[Delerna]

I've encountered that when connecting a data access page to a stored procedure that has other work to do besides the query that selects the records to return. The data access page dosn't even know the names of the fields that the final query is returning.

If however, i put a dummy query at the top of the procedure and wrap it up in an if else then it works.

for example

   Create Proc prDemo
   as
   DECLARE @V int
   set @v=0
 
   IF @v=1
   BEGIN
      SELECT 1 as F1,1 as F2,'string' as F3
   END ELSE BEGIN
      SOME CODE
      MORE CODE
      EVEN MORE CODE
 
     SELECT F1,F2,F3 FROM MyDemoTable
   END
   GO
 

It seems that all the code that comes before the query propper hides it from the Data Access page. I guess that putting the dummy query at the top enables the data access page to read the stored procedure and build a table scema that can handle the recordset returned by the stored proc.
I also suspect that once the DAP is finished being developed then i could delete the dummy query and the if construct and it would still work, because the table schema has been built in the DAP. Havn't tried that one.

This is my observations while working with DAP's and Stored Procs. Somebody else may be able to explain it in more detail.

By the way, when a stored proc contains a table variable, the above dosn't work, but my guess is that it will be for a similar reason.