December 21, 2007 at 8:14 pm
I am attempting to wrie a qury that will return aggregate totals from two different tables. The problem is that the TotalForecast totals are way to high.
How do I write a query to obtain the correct totals?
Table 1 - dbo.QM_Results
Columns - dbo.QM_Results.Special8, dbo.QM_Results.SessionName, dbo.QM_Results.PersonNumber
Table 2 - dbo.PM_ForecastView
Columns - dbo.PM_ForecastView.Hierarchy, dbo.PM_ForecastView.Forecast
Select substring(dbo.QM_Results.Special8,0,6) AS Hierarchy,
substring(dbo.QM_Results.SessionName,0,11) As CourseCode,
count(dbo.QM_Results.PersonNumber) TotalAssociates,
sum(dbo.PM_ForecastView.Forecast) TotalForecast
From dbo.QM_Results INNER JOIN dbo.PM_ForecastView
ON dbo.PM_ForecastView.Hierarchy = substring(dbo.QM_Results.Special8,0,6)
where SessionMid in ('96882139', '23620891', '45077427', '29721437')
AND substring(dbo.QM_Results.Special8,0,6) in ('EZHBA')
Group By substring(dbo.QM_Results.Special8,0,6),
substring(dbo.QM_Results.SessionName,0,11)
Sample of data returned with my current query.
Hierarchy CourseCode TotalAssociates TotalForecast
EZHBA CARD167200 1179 141480
EZHBA CARD167201 1416 169920
EZHBA CARD167202 1119 134280
EZHBA CARD167204 99 11880
Results when I run aggregate query separately
Actual Total taken
Hierarchy CourseCode TotalTaken
EZHBA CARD167200 393
EZHBA CARD167201 472
EZHBA CARD167202 373
EZHBA CARD167204 33
Forecasted Total taken
Hierarchy CourseCode Forecast
EZHBA CARD167200 999
EZHBA CARD167201 900
EZHBA CARD167202 800
EZHBA CARD167204 800
December 21, 2007 at 9:16 pm
The join condition gives you everything in the second table matched on Hierarchy and does not take into account the coursecode.
so for every sessionname record in your qm_results table you get added in the count and sum of all the records in your pm_forecastview table.
Here is an example skinnied down...
Declare @QM_Results table
(Special8 varchar(8), SessionName varchar(11), PersonNumber INT)
Declare @PM_ForecastView table
(Hierarchy varchar(6), Forecast int)
insert into @qm_results
select 'ezhba', 'CARD167200', 2 Union all -- 2 records for 1st class
select 'ezhba', 'CARD167200', 1 Union all
select 'ezhba', 'CARD167201', 2 Union all -- 3 records for 2nd class
select 'ezhba', 'CARD167201', 1 Union all
select 'ezhba', 'CARD167201', 3 Union all
select 'ezhba', 'CARD167202', 2 Union all -- 1 record for 3rd class
select 'ezhba', 'CARD167204', 3 -- 1 record for 4th class
insert into @PM_ForecastView
select 'ezhba', 2 Union all
select 'ezhba', 2 Union all
select 'ezhba', 2 Union all
select 'ezhba', 2
Select substring(Special8,0,6) AS Hrarchy,
substring(SessionName,0,11) As CourseCode,
count(PersonNumber) TotalAssociates,
sum(Forecast) TotalForecast
From @QM_Results inner JOIN @PM_ForecastView
ON Hierarchy = substring(Special8,0,6)
Group By substring(Special8,0,6),
substring(SessionName,0,11)
-----------------------------------
----------------- results ------------------
----------------------------------------------
Hrarchy CourseCode TotalAssociates TotalForecast
------- ----------- --------------- -------------
ezhba CARD167200 8 16
ezhba CARD167201 12 24
ezhba CARD167202 4 8
ezhba CARD167204 4 8
is there another way to link the records in the forecast table to the coursecode? If so, add that to the JOIN condition.
Let me know if this helps.
Toni
December 21, 2007 at 10:02 pm
Yes there is a field in the forcast table for course code.
dbo.PM_ForecastView.CourseCode. What would the additional join statement look like?
I am looking at your code and I am trying to understand how it works. Do I just need to replace the @QM_Results table and @PM_ForecastView table with my existing tables?
December 22, 2007 at 7:06 am
Other than creating test tables and data, I just trimmed your code and did not change any logic. The table names are not relevant.
The only part of your code to change is the Join condition by adding an additional condition to the ON portion. The rest of the code can be left alone as far as I can tell - I really can not follow the purpose but assume you also have trimmed out the code and tables.
From dbo.QM_Results
INNER JOIN dbo.PM_ForecastView
ON dbo.PM_ForecastView.Hierarchy = substring(dbo.QM_Results.Special8,0,6)
AND dbo.PM_ForecastView.CourseCode = substring(dbo.QM_Results.SessionName,0,11)
This way you will be able to aggregate the results by coursecode and hierarchy.
Toni
December 22, 2007 at 7:42 am
Perhaps if you could supply some test data to be sure I am addressing the right problem that would help.
It would be good to see what you are actually trying to accomplish and how (in a description as well as the code).
There is a possibility that the JOIN of the tables might not make logical sense.
Thanks
December 22, 2007 at 8:51 am
I've attached sample data for each table view.
I just want to return
Hierarchy, -- From Forecast table
Course Code, -- From Forecast Table
Total forecast, -- From Forecast table
TotalActualAtteneded -- From PM_QMDetails table. Count(PersonNumber)
December 22, 2007 at 9:14 am
Here is a sample of the results that I want. I want to combine the results of these two querries into one query.
This is query 1
Select Hierarchy,
CourseCode,
count(PersonNumber) TotalTaken
From dbo.PM_QMDetails
where Hierarchy = 'EZHBA'
Group By CourseCode,Hierarchy
[/CODE]
Results:
Hierarchy CourseCode TotalTaken
EZHBA CARD167200 393
EZHBA CARD167201 472
EZHBA CARD167202 373
EZHBA CARD167204 33
Qurey 2
Select dbo.PM_Hierarchy.Hierarchy,
dbo.PM_CourseCodes.CourseCode,
sum(dbo.PM_Forecast.ParticipantForcast) Forecast
From dbo.PM_Forecast
LEFT JOIN dbo.PM_Hierarchy ON dbo.PM_Forecast.Hierarchy = dbo.PM_Hierarchy.ID
LEFT JOIN dbo.PM_CourseCodes ON dbo.PM_CourseCodes.ID = dbo.PM_Forecast.CourseCode
Where dbo.PM_Hierarchy.Hierarchy = 'EZHBA'
group By dbo.PM_Hierarchy.Hierarchy,
dbo.PM_CourseCodes.CourseCode
Results
Hierarchy CourseCode Forecast
EZHBA CARD167200 999
EZHBA CARD167201 900
EZHBA CARD167202 800
EZHBA CARD167204 800
Desired Results
Hierarchy CourseCode TotalTaken TotalForecast
EZHBA CARD167200 393 999
EZHBA CARD167201 472 900
EZHBA CARD167202 373 800
EZHBA CARD167204 33 800
Thank you for all of your help!
December 22, 2007 at 4:59 pm
Ok.. maybe someone else can verify and/or adjust my observation but it looks like you are joining two tables with many-to-many relationship and getting lots of duplicate data.
This might not be the prettiest way to get your results but it does combine them into one query and eliminates the odd results from the JOIN.
Reusing my test data....
Declare @QM_R table
(Hrarchy varchar(8), SessionName varchar(11), PersonNumber INT)
Declare @PM_F table
(Hierarchy varchar(6), coursecode varchar(11), Forecast int)
insert into @qm_r
select 'ezhba', 'CARD167200', 2 Union all -- 2 records for 1st class
select 'ezhba', 'CARD167200', 1 Union all
select 'ezhba', 'CARD167201', 2 Union all -- 3 records for 2nd class
select 'ezhba', 'CARD167201', 1 Union all
select 'ezhba', 'CARD167201', 3 Union all
select 'ezhba', 'CARD167202', 2
insert into @PM_F
select 'ezhba', 'CARD167200', 1 Union all
select 'ezhba', 'CARD167200', 1 Union all
select 'ezhba', 'CARD167201', 1 Union all
select 'ezhba', 'card167202', 3 union all
select 'ezhba', 'CARD167202', 1
select distinct hrarchy,coursecode, (select count(personnumber) from @qm_r
where hierarchy = hrarchy and coursecode=sessionname) totaltaken,
(select sum(forecast) from @PM_F
where hierarchy = hrarchy and coursecode=sessionname) totalforecast
from @pm_f,@qm_R
Where hierarchy = hrarchy and coursecode = sessionname
My best guess at what the table and field names are - your query would be:
Select distinct dbo.PM_ForecastView.Hierarchy, -- hierarchy
dbo.PM_ForecastView.CourseCode, -- Course Code
(select sum(dbo.PM_ForecastView.Forecast) -- total forecast for hierarchy/coursecode
from dbo.PM_ForecastView
where dbo.PM_ForecastView.Hierarchy = dbo.QM_Results.hierarchy and
dbo.PM_ForecastView.Coursecode= dbo.QM_Results.CourseCode) totalforecast,
(select count(dbo.QM_Results.personnumber) -- total Persons for hierarchy/coursecode
from dbo.QM_Results
where dbo.PM_ForecastView.Hierarchy = dbo.QM_Results.hierarchy and
dbo.PM_ForecastView.Coursecode= dbo.QM_Results.CourseCode) totaltaken
From dbo.PM_ForecastView, dbo.QM_Results
where dbo.PM_ForecastView.Hierarchy = dbo.QM_Results.hierarchy and
dbo.PM_ForecastView.Coursecode= dbo.QM_Results.CourseCode
December 22, 2007 at 8:02 pm
That is exactly what I needed. Thanks
December 23, 2007 at 1:36 am
Glad I could help
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply