Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 604
  • Last Modified:

Complex SQL calculation with single query

The table below describes this situation:
- I have some Managers (let's say X and Y)
- Several of them report to a Senior Manager
- Each manager is in charge of several jobs (App1, App2, App3)
- Each job is running several times every week and the result is coded (A, B, C)
- The database is recording the number of times each code occured within a week
- I'm retrieving (and displaying) all data for last week and I have to calculate:
   - Sum by App (Row)
   - Sum by Code (Column)
   - Total by Manager
   - Total by Sr. Manager

Unfortunately, the text is not displayed in ASCII, if you
want to see the table, I can e-mail it. Or you can look at
this Page Source, it looks better.

                   A | B | C |TotRow
Sr. Mgr
  ManagerX   App1  1 | 0 | 0 | 1
             App2  2 | 0 | 2 | 4
                  --- --- --- ---
   TotalX          3   0   2   5

  ManagerY   App3  2 | 1 | 3 | 6
                  --- --- --- ---
    TotalY         2   1   3   6

Total Sr. Mgr                 11
 
Can all this be done with a single query (it has to be a single query, due to some middleware limitations)? And if yes, how?
Thanks for your help,
regards,
Ion
0
iamari
Asked:
iamari
  • 9
  • 7
1 Solution
 
iamariAuthor Commented:
Edited text of question
0
 
iamariAuthor Commented:
Edited text of question
0
 
iamariAuthor Commented:
Edited text of question
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
iamariAuthor Commented:
Edited text of question
0
 
iamariAuthor Commented:
Edited text of question
0
 
BangerterCommented:
Is this something that is going to be put into a report? If so, if I understand you right, it can be done. If it is not going to a report then what are you doing with the results?
0
 
iamariAuthor Commented:
Yes, it's going to a report written in Cold Fusion. Because of
some grouping limitations in CF, it has to be done with a single
query
0
 
IanHinsonCommented:
To demonstrate the crosstab query you require, which is shown below, I set up some tables which correspond to the situation you described.
Even though the structure/fieldnames might not match exactly what you currently have, I would ask that you create the tables and query (in a new database if necessary) as described at first, so that you can more readily see the crosstab working and follow other instructions I have provided about the report construction.

Senior Managers table
---------------------
Structure:
SnrMgrID autonumber
SnrMgrName Text

Data:
1 Sr.Mgr1
2 Sr.Mgr2

Managers table
--------------
Structure:
MgrID    autonumber
MgrName  text
SnrMgrID number(long) ;who the manager reports to

Data:
1  X  1
2  Y  1

Jobs table
----------
Structure:
JobID  autonumber
Job Name  text
MgrID     number(long)

Data:
1  App1  1
2  App2  1
3  App3  2

Job Results table
-----------------
Structure:
JobID  Number(long)
Date   Date/Time
Result Text

Data: (Date shown in D/M/Y format - enter as M/D/Y if required)
1 8/12/97 A
2 8/12/97 A
2 9/12/97 C
2 10/12/97 A
2 11/12/97 C
3 8/12/97 A
3 9/12/97 B
3 10/12/97 A
3 11/12/97 C
3 12/12/97 B

The query that provides the source for the report is:

TRANSFORM Count([Job Results].JobID) AS CountOfJobID
SELECT 1+[Date]-Weekday([Date]) AS WeekStart, [Senior Managers].SnrMgrID, [Senior Managers].SrMgrName, Managers.MgrID, Managers.MgrName, Jobs.JobID, Jobs.[Job Name], Count([Job Results].JobID) AS CountOfJobID1
FROM (([Senior Managers] INNER JOIN Managers ON [Senior Managers].SnrMgrID = Managers.SnrMgrID) INNER JOIN Jobs ON Managers.MgrID = Jobs.MgrID) INNER JOIN [Job Results] ON Jobs.JobID = [Job Results].JobID
GROUP BY 1+[Date]-Weekday([Date]), [Senior Managers].SnrMgrID, [Senior Managers].SrMgrName, Managers.MgrID, Managers.MgrName, Jobs.JobID, Jobs.[Job Name]
PIVOT [Job Results].Result In ("A","B","C");

To create this query:
1. Create a new query and switch it from design view into SQL view.
2. Paste the above text into the SQL view

The report does not need any subreports, but you should create grouping headers and footers to show some totals which are calculated directly on the report.
I'm not familiar with your report package hope it includes the abilty to group data based on certain fields in the underlying query and to include calculated controls in the headers/footers of those groups.

Here are the groupings you need to produce the format you set out in your question.
First Grouping: [WeekStart] Header - show WeekStart field, no footer

Second Grouping: [SnrMgrID]
Header - show SnrMgrName
Footer - include a textbox with control source =Sum([CountOfJobID1])

Third Grouping: [MgrID]
Header - show MgrName
Footer - show Sum([A]),Sum([B]),Sum([C]),Sum([CountOfJobID1]) in separate text boxes.

Fourth Grouping: [JobID] No header, no footer
In detail section show: [Job Name], [A],[B],[C],[CountOfJobID1]

It may be worthwhile experimenting with an Access report first.

Ian.

0
 
IanHinsonCommented:
Oops. Typographic error: 'SnrMgrName' should be 'SrMgrName' to be consistent with fieldname referenced in the query.

You can check whether the query looks ok before running it by switching it back into design view after entering the Sql.
There shouldn't be any automatically named fields e.g.'Expr1'. If there is then the fieldname is unrecognised and spelling of the fieldname should be checked.

0
 
iamariAuthor Commented:
I'm using MSAccess 7.0 for Windows95. Using the query under MSAccess, I get the message: Syntax error in JOIN operation.
Under MS Query, I get: This program has performed an illegal operation and will be shut down
MSQUERY caused a general protection fault
in module ODBC.DLL at 0003:000003c8.
Registers:
EAX=00004667 CS=47f7 EIP=000003c8 EFLGS=00000202
EBX=3a9f1c6c SS=4547 ESP=00009c72 EBP=00009c9a
ECX=0000466f DS=4827 ESI=00021c6c FS=321f
EDX=ffff0000 ES=0000 EDI=00020000 GS=0000
Bytes at CS:EIP:
26 ff b7 1a 06 9a 0d 01 2f 01 89 46 ea 89 56 ec
Stack dump:
19b83a97 13b8ffff 1bd3466f 00000000 00000000 101a0000 002104c0 c0e00000 15be80eb 51000051 04af9cac 9eca3a9f 00024547 005119b8 9ed83a97 3a9f0e24
 
Any suggestions?

Ion
0
 
IanHinsonCommented:
First, check that the query runs OK in MS Access. (I expect you would have done that already anyway.) I ran it in Access ok here, so sort that part out first before continuing with MS Query.

MSQuery is not as powerful a query engine as Access, and may not be able to handle this syntax directly. But you can still use the query in MSQuery by:
1) After verifying that you have got it working in Access, then Save it in Access with some name.
2) Refer to the saved query directly by name using MSQuery just as you would refer to a table contained inside the Access mdb.

0
 
iamariAuthor Commented:
I made the following changes:
- I eliminated all spaces from field names, it seems to create problems.
- I renamed Date to JDate, I think is kind of confusing for the ODBC driver.
- Renamed the SnrMgrID and SnrMgrName to SrMgrID and SrMgrID
- I've added a Max clause to the date field, because that's what the report is supposed to show anyway.
The modified query is:

TRANSFORM Count(JobResults.JobID) AS CountOfJobID
SELECT SeniorManagers.SrMgrID, SeniorManagers.SrMgrName, Managers.MgrID, Managers.MgrName, Jobs.JobID, Jobs.JobName, Count(JobResults.JobID) AS CountOfJobID1
FROM ((SeniorManagers INNER JOIN Managers ON SeniorManagers.SrMgrID = Managers.SrMgrID) INNER JOIN Jobs ON Managers.MgrID = Jobs.MgrID) INNER JOIN JobResults ON Jobs.JobID = JobResults.JobID
WHERE (((JobResults.JDate)=(SELECT Max(JDate) FROM JobResults)))
GROUP BY JobResults.JDate, SeniorManagers.SrMgrID, SeniorManagers.SrMgrName, Managers.MgrID, Managers.MgrName, Jobs.JobID, Jobs.JobName
PIVOT JobResults.JobResults In ("A","B","C");

The query works, I get all the data and the total by row (Count of JobID). However, I still need a count by column for "A", "B" and "C" grouped by Manager and then a grand total grouped by Senior Manager.
Is this possible within the same query?

Regards,

Ion
0
 
IanHinsonCommented:
On re-reading your comment, I realise that you may already be doing what I suggested in 1) and 2).
Since obviously there is no point trying to hook up MSQuery to an Access query which is known not to work properly, I assumed you were trying to reconstruct the query inside of MSQuery instead.

I retested the query in MSAccess, and after fixing the incorrect field reference to 'SrMgrName' which I mentioned in an earlier comment, it works fine.
In order to debug the join problem you are having I would need:
1)the Sql of the query you are using, and
2)the structures of the tables you have created.


0
 
IanHinsonCommented:
PLEASE DISREGARD THE ABOVE COMMENT TO THE EXTENT THAT IT WAS WRITTEN OFF-LINE BEFORE YOUR COMMENT ABOVE IT WAS KNOWN ABOUT BY ME.  THANKS FOR THE EXTRA INFO. WILL GET BACK TO YOU.
0
 
IanHinsonCommented:
Some of the problems, as I see it, with your new query are:
1) The criteria ((JobResults.JDate)=(SELECT Max(JDate) FROM JobResults)) means that the ONLY rows (i.e. job results) that will be counted are those which occurred on the latest job-result day. I'm sure you want to include rows other than that in the count.
2) Your major grouping in JobResults.JDate means that totals you obtain will each relate to a given day, not a given week.

Do you still require the totals to be over a one week period? If so, do you want the immediate past week or the previous Sun-Sat week?
I modified the query to give only the previous Sun-Sat week's results.
(Also I guess that, since you are pivoting on JobResults.JobResults, the 'Results' field has been renamed to 'JobResults' and have made that assumption for the query below.)

PARAMETERS [Enter Date:] DateTime;
TRANSFORM Count(JobResults.JobID) AS CountOfJobID
SELECT 1+[JDate]-Weekday([JDate]) AS WeekStart, SeniorManagers.SrMgrID, SeniorManagers.SrMgrName, Managers.MgrID, Managers.MgrName, Jobs.JobID, Jobs.JobName, Count(JobResults.JobID) AS CountOfJobID1
FROM ((SeniorManagers INNER JOIN Managers ON SeniorManagers.SrMgrID = Managers.SrMgrID) INNER JOIN Jobs ON Managers.MgrID = Jobs.MgrID) INNER JOIN JobResults ON Jobs.JobID = JobResults.JobID
WHERE (((1+[JDate]-Weekday([JDate]))=1+[Enter Date:]-Weekday([Enter Date:])))
GROUP BY 1+[JDate]-Weekday([JDate]), SeniorManagers.SrMgrID, SeniorManagers.SrMgrName, Managers.MgrID, Managers.MgrName, Jobs.JobID, Jobs.JobName
PIVOT JobResults.Result In ("A","B","C");

The prompt [Enter Date:] purely temporary to enable the query to be tested with different notional Date's. The [Enter Date:] should be replaced by Date() when you are satisfied the query works - and also remove the PARAMETERS clause at the same time.

As for getting a total for each Senior Manager in the same query:
1) It can only be done by writing a separate query that will calculate those totals, and then joining that query with the one above.
2) Are you sure you can't calculate that total in the SrMgr's group footer of the report?


0
 
iamariAuthor Commented:
Perhaps I should have explained this: I get an ASCII file to load in my database. The file contains only jobs ran during last week, so the Max function would do just fine, since all jobs will show the same "ending date".
The reporting ability of CF is very limited, indeed, there are no footers. On top of that, it only allows output from a single query (identified by name) within one grouping.
So, my biggest problem is really to count jobs by Manager and by Senior Manager within the same query (I'm not allowed to assign the same name to 2 different queries either).
If I join a main query to another one for a Senior Manager grand total, it might work, if I can use a single query name.
As for the new query you posted, it's still not showing a total for managers, but just a total by row (CountOfJobID1)

Regards,

Ion
0
 
IanHinsonCommented:
The query you posted is fine, given that JDates represent week-ending dates.
The only change I made to it is to include JDate as a RowHeading for joining it with other queries later. So it now reads as:

TRANSFORM Count(JobResults.JobID) AS CountOfJobID
SELECT JobResults.JDate, SeniorManagers.SrMgrID, SeniorManagers.SrMgrName, Managers.MgrID, Managers.MgrName, Jobs.JobID, Jobs.JobName, Count(JobResults.JobID) AS CountOfJobID1
FROM ((SeniorManagers INNER JOIN Managers ON SeniorManagers.SrMgrID = Managers.SrMgrID) INNER JOIN Jobs ON Managers.MgrID = Jobs.MgrID) INNER JOIN JobResults ON Jobs.JobID = JobResults.JobID
WHERE (((JobResults.JDate)=(SELECT Max(JDate) FROM JobResults)))
GROUP BY JobResults.JDate, SeniorManagers.SrMgrID, SeniorManagers.SrMgrName, Managers.MgrID, Managers.MgrName, Jobs.JobID, Jobs.JobName
PIVOT JobResults.JobResults In ("A","B","C");

Please save the above query as JobCounts as it is referred to in other queries below.

Construct a second query and save it as SrMgrJobCounts having Sql:
SELECT JobResults.JDate, Managers.SrMgrID, Count(JobResults.JobID) AS SrMgrJobs
FROM (Managers INNER JOIN Jobs ON Managers.MgrID = Jobs.MgrID) INNER JOIN JobResults ON Jobs.JobID = JobResults.JobID
GROUP BY JobResults.JDate, Managers.SrMgrID;

Construct a third query and save it as MgrJobCounts having Sql:
SELECT JobResults.JDate, Jobs.MgrID, Count(JobResults.JobID) AS MgrJobs
FROM Jobs INNER JOIN JobResults ON Jobs.JobID = JobResults.JobID
GROUP BY JobResults.JDate, Jobs.MgrID;

Now the final query joins JobCounts with the other two queries which becomes the recordsorce for the report.

SELECT SrMgrJobCounts.SrMgrJobs, MgrJobCounts.MgrJobs, JobCounts.*
FROM (JobCounts INNER JOIN MgrJobCounts ON (JobCounts.MgrID = MgrJobCounts.MgrID) AND (JobCounts.JDate = MgrJobCounts.JDate)) INNER JOIN SrMgrJobCounts ON (JobCounts.SrMgrID = SrMgrJobCounts.SrMgrID) AND (JobCounts.JDate = SrMgrJobCounts.JDate);

Hope this helps,
Ian.


0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now