472,811 Members | 1,748 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,811 software developers and data experts.

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 3528
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

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

Similar topics

by: Job Lot | last post by:
Is it possible to use Aggregate functions with GROUP BY Clauses on DataTable. I have a DataTable with following values: Date Amount Int Balance 1/1/2004 5000.00 50.00 5050.00...
by: Thomas R. Hummel | last post by:
Hi, As I wrote my message the solution came to me, so I thought I would post anyway for others to see in case it was useful: Here is some sample DDL for this question: CREATE TABLE Source (...
by: | last post by:
I've written quite a few user-defined functions in vba in MS Access I was just wondering: Is there any way to write a user-defined aggregate function, like Access's Count, Min and Max functions...
by: wespvp | last post by:
I am using PostgreSQL 7.4.1 on RedHat 7.2. The query I am executing is something like (I replaced all the return values with 'count'): db=> explain select count(*) from messages m join (select...
by: Mikkel Wernberg Erup | last post by:
I took a lab exam today. However, though the exam is over I'm still thinking about one of the questions. I had a schema for a bookstore database defined like this: Book:...
by: BillCo | last post by:
I just wasted a long time figuring out this and I figure if I post it might save someone some pain! Jet (DAO) will allow you to to use nested aggregate functions like building blocks, e.g.: ...
by: wrldruler | last post by:
Hello, First, I know it's against "Access Law" to save calculations in a table, but....I want/need to. I currently have sub-totals being calculated inside a form, using DMax, DCount, and...
by: bflemi3 | last post by:
My previous post went unanswered. I now know what the problem is but can not think of a practical work around. Hopefully by making my question less confusing someone will be able to help...here...
by: Nate Eldredge | last post by:
Consider the following pseudo-code: #include <opaque.h> struct foo { int a; opaque_t op; int b; };
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
How does React native implement an English player?
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.