Aggregate Query Help

I am attempting to wrie a query 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

I strongly advise you to create real columns for Hierarchy and CourseCode. It will simplify everything, and querying with substring criteria is a disaster for performance.

Joining with a View is somewhat dangerous, since the query plan can give you more rows than you expect.

How is the View defined?

|||

Ok what is the best way to accomplish this.

I've attached sample data for each table.

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)

Sample PM_QMActual Data

ResultIDCourseCodePersonNumber SessionNameSessionMidSessionLidStartDateEndDateTotalScoreScoreBandTitleHierarchyHierarchyAll-371630300CARD1672001CARD167200_More_Module_1968821393949674116:02.024:00.07FailedEZHBAEZHBA...AC-371637743CARD1672001CARD167200_More_Module_1968821393949674124:52.025:54.010PassedEZHBAEZHBA...AC-371558197CARD1672011CARD167201_More_Module_223620891638252528:49.033:34.06FailedEZHBAEZHBA...AC-371560271CARD1672011CARD167201_More_Module_223620891638252534:30.035:50.07FailedEZHBAEZHBA...AC-371579578CARD1672011CARD167201_More_Module_223620891638252536:44.037:57.010PassedEZHBAEZHBA...AC-371589091CARD1672021CARD167202_More_Module_3450774271690432345:13.046:32.04FailedEZHBAEZHBA...AC-371561743CARD1672021CARD167202_More_Module_3450774271690432347:22.048:01.06PassedEZHBAEZHBA...AC-357270804CARD1672041CARD167204_More_Module_4_CUSTOMER_SATISFACTION297214375675925933:50.036:23.011PassedEZHBGEZHBG...AA-382315943CARD1672021CARD167202_More_Module_3450774271690432341:46.043:31.06PassedEZHBGEZHBG...AA-382448705CARD1672001CARD167200_More_Module_1968821393949674150:22.053:59.010PassedEZHBGEZHBG...AA-382317954CARD1672011CARD167201_More_Module_223620891638252504:08.012:43.09PassedEZHBGEZHBG...AA-374600403CARD1672001CARD167200_More_Module_1968821393949674145:46.048:11.09PassedEZHBDEZHBD...AA-374575303CARD1672011CARD167201_More_Module_223620891638252549:04.032:44.09PassedEZHBDEZHBD...AA-373276591CARD1672021CARD167202_More_Module_3450774271690432340:34.041:25.03FailedEZHBDEZHBD...AA-373271117CARD1672021CARD167202_More_Module_3450774271690432342:16.042:54.06PassedEZHBDEZHBD...AA-374385179CARD1672021CARD167202_More_Module_3450774271690432330:21.034:36.06PassedEZHBGEZHBG...AA-374505709CARD1672001CARD167200_More_Module_1968821393949674138:42.047:07.09PassedEZHBGEZHBG...AA-374465177CARD1672011CARD167201_More_Module_223620891638252552:39.002:01.010PassedEZHBGEZHBG...AA-357277598CARD1672041CARD167204_More_Module_4_CUSTOMER_SATISFACTION297214375675925933:58.039:13.012PassedEZHBGEZHBG...AA-381914391CARD1672021CARD167202_More_Module_3450774271690432320:52.023:40.06PassedEZHBAEZHBA...AC-392795564CARD1672001CARD167200_More_Module_1968821393949674109:25.018:26.010PassedEZHBAEZHBA...AC-392798106CARD1672011CARD167201_More_Module_223620891638252525:38.042:54.010PassedEZHBAEZHBA...AC-384293893CARD1672001CARD167200_More_Module_1968821393949674135:57.040:01.08PassedEZHBAEZHBA...AC-384236041CARD1672011CARD167201_More_Module_223620891638252545:29.052:54.05FailedEZHBAEZHBA...AC-384224651CARD1672011CARD167201_More_Module_223620891638252554:54.056:13.07FailedEZHBAEZHBA...AC-384234655CARD1672011CARD167201_More_Module_223620891638252556:52.057:45.010PassedEZHBAEZHBA...AC

Sample Forecast data

SiteHierarchyCourseCodeCourseNameStartTimeEndTimeForecastCustomer SatisfactionEZHBACARD167202Module 38:00 AM6:00 PM800Customer SatisfactionEZHBDCARD167201Module 2 5:00 PM9:00 PM100Customer SatisfactionEZHBGCARD167202Module 31:00 PM9:00 PM25Customer SatisfactionEZHBDCARD167204Module 48:00 AM5:00 PM300Customer SatisfactionEZHBDCARD167201Module 2 10:00 AM5:00 PM145Customer SatisfactionEZHBACARD167204Module 48:00 AM12:00 PM100Customer SatisfactionEZHBGCARD167204Module 46:00 AM9:00 AM300Customer SatisfactionEZHBGCARD167202Module 39:00 AM5:00 PM45Customer SatisfactionEZHBACARD167201Module 2 8:00 AM6:00 PM900Customer SatisfactionEZHBACARD167204Module 48:00 AM6:00 PM700Customer SatisfactionEZHBACARD167200Module 11:00 AM9:00 AM999

|||

Something like this...

SELECT dt.Hierarchy, dt.CourseCode,dt.TotalForeCast,COUNT(d.PersonNumber)as TotalActualAttendedFROM(SELECT f.Hierarchy, f.CourseCode,SUM(ForeCast)as TotalForeCastFROM PM_ForecastGROUP BY f.Hierarchy, f.CourseCode) dtLEFTJOIN PM_QMDetails dON dt.Hierarchy=d.HierarchyAND dt.CourseCode=d.CourseCode

|||

Thanks that is exactly what I needed to get the final result. Here it is.

[Code]

SELECT dt.Hierarchy,

dt.CourseCode,

dt.TotalForeCast,

COUNT(d.PersonNumber)as TotalActualAttended

FROM

(

SELECT f.Hierarchy, f.CourseCode,SUM(ForeCast)as TotalForeCast

FROM dbo.PM_ForecastView f

GROUPBY f.Hierarchy, f.CourseCode

) dt

LEFTJOIN PM_QMDetails dON dt.Hierarchy=d.Hierarchy

AND dt.CourseCode=d.CourseCode

GroupBy dt.Hierarchy,

dt.CourseCode,

dt.TotalForeCast

[/CODE]