470,594 Members | 1,560 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,594 developers. It's quick & easy.

Compound aggregate (Max of Count) procedure question

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:

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

-fkManagerID (foreign key to tlkpManager)

-ProblemCode (description really)


Expand|Select|Wrap|Line Numbers
  2. AS
  3. SELECT tblSatellite.SatelliteCode, tblSatellite.NameOrLocation, tlkpManager.ManagerName, tlkpProblemTypes.ProblemCode, D.CountOfErrors
  4.     FROM    (SELECT TOP 100 PERCENT fkSatelliteID, MAX(CountOfErrors) AS maxcount
  5.         FROM    (SELECT TOP 100 PERCENT fkSatelliteID, fkProblemTypeID, COUNT(pkErrorID) AS CountOfErrors
  6.             FROM dbo.tblErrorList
  7.             WHERE (DateOfService >= CONVERT(DATETIME, @sDate, 102) AND DateOfService < CONVERT(DATETIME, DATEADD(d,1,@eDate), 102)) AND
  8.                 (fkResolutionTypeID IS NULL OR
  9.                 fkResolutionTypeID <> 8 AND fkResolutionTypeID <> 12)
  10.             GROUP BY fkSatelliteID, fkProblemTypeID
  11.             ORDER BY fkSatelliteID, fkProblemTypeID) C
  12.         GROUP BY fkSatelliteID
  13.         ORDER BY fkSatelliteID) A
  15.     (SELECT TOP 100 PERCENT fkSatelliteID, fkProblemTypeID, COUNT(pkErrorID) AS CountOfErrors
  16.         FROM dbo.tblErrorList
  17.             WHERE (DateOfService >= CONVERT(DATETIME, @sDate, 102) AND DateOfService < CONVERT(DATETIME, DATEADD(d,1,@eDate), 102)) AND
  18.             (fkResolutionTypeID IS NULL OR
  19.             fkResolutionTypeID <> 8 AND fkResolutionTypeID <> 12)
  20.         GROUP BY fkSatelliteID, fkProblemTypeID
  21.         ORDER BY fkSatelliteID, fkProblemTypeID) B
  22. ON B.fkSatelliteID=A.fkSatelliteID AND B.CountOfErrors=A.maxcount
  24.     (SELECT TOP 100 PERCENT fkSatelliteID, COUNT(pkErrorID) AS CountOfErrors
  25.         FROM dbo.tblErrorList
  26.             WHERE (DateOfService >= CONVERT(DATETIME, @sDate, 102) AND DateOfService < CONVERT(DATETIME, DATEADD(d,1,@eDate), 102)) AND
  27.             (fkResolutionTypeID IS NULL OR
  28.             fkResolutionTypeID <> 8 AND fkResolutionTypeID <> 12)
  29.         GROUP BY fkSatelliteID
  30.         ORDER BY fkSatelliteID) D
  31. ON D.fkSatelliteID=A.fkSatelliteID
  33.     dbo.tblSatellite
  34. ON tblSatellite.pkSatelliteID=A.fkSatelliteID
  36.     dbo.tlkpProblemTypes
  37. ON tlkpProblemTypes.pkProblemTypeID = B.fkProblemTypeID
  39.     dbo.tlkpManager
  40. ON tlkpManager.pkManagerID=tblSatellite.fkManagerID
Feb 14 '08 #1
4 3294
1,134 Expert 1GB
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
Feb 14 '08 #2
Jim Doherty
897 Expert 512MB
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?


Jim :)
Feb 15 '08 #3
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.

SQL Server 2000 SP4

Access 2003 SP2
Feb 15 '08 #4
1,134 Expert 1GB
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
Expand|Select|Wrap|Line Numbers
  1.    Create Proc prDemo
  2.    as
  3.    DECLARE @V int
  4.    set @v=0
  6.    IF @v=1
  7.    BEGIN
  8.       SELECT 1 as F1,1 as F2,'string' as F3
  10.       SOME CODE
  11.       MORE CODE
  12.       EVEN MORE CODE
  14.      SELECT F1,F2,F3 FROM MyDemoTable
  15.    END
  16.    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.
Feb 16 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by | last post: by
2 posts views Thread by Mikkel Wernberg Erup | last post: by
27 posts views Thread by Nate Eldredge | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.