By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,570 Members | 970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,570 IT Pros & Developers. It's quick & easy.

Compound aggregate (Max of Count) procedure question

P: 2
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

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE GetErrCountBySite_Type (@sDate AS SMALLDATETIME, @eDate AS SMALLDATETIME)
  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
  14. INNER JOIN
  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
  23. INNER JOIN
  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
  32. INNER JOIN
  33.     dbo.tblSatellite
  34. ON tblSatellite.pkSatelliteID=A.fkSatelliteID
  35. INNER JOIN
  36.     dbo.tlkpProblemTypes
  37. ON tlkpProblemTypes.pkProblemTypeID = B.fkProblemTypeID
  38. INNER JOIN
  39.     dbo.tlkpManager
  40. ON tlkpManager.pkManagerID=tblSatellite.fkManagerID
  41.  
Feb 14 '08 #1
Share this Question
Share on Google+
4 Replies


Delerna
Expert 100+
P: 1,134
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
Expert 100+
P: 897
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 :)
Feb 15 '08 #3

P: 2
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
Feb 15 '08 #4

Delerna
Expert 100+
P: 1,134
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
  5.  
  6.    IF @v=1
  7.    BEGIN
  8.       SELECT 1 as F1,1 as F2,'string' as F3
  9.    END ELSE BEGIN
  10.       SOME CODE
  11.       MORE CODE
  12.       EVEN MORE CODE
  13.  
  14.      SELECT F1,F2,F3 FROM MyDemoTable
  15.    END
  16.    GO
  17.  

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.