Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
eric_ht
Starting Member
37 Posts |
 Posted - 2009-03-20 : 17:12:20
|
| I have a query that is dynamically built. The parameters passed to it determines the level of data that is returned. It could return data at the individual plant level, division level or an operating segment level.The query is made up of several subselects with one outer query that is joined to the group of selects based on the plant, division or operating segment. But I also need to be able to return entire company totals. At this point there is nothing to join to the outer query on. Hence I still want the outer query to return the summed totals to the select statement . I assume at this point it should be some type of sub-select and not a join but I can't seem to get the structure right. The below example is what it looks like being called by Operating Segment.--Here are the fields being returned......--The last_salaried_active, etc fields are being returned from the outer left join querySELECT_Flag, Segment, number_active_total_1, last_salaried_active_1, number_terminations_total_1--First Inner Query Subselect FROM (SELECT Segment, number_active_total_1 = (max_active_salaried_1 + max_active_hourly_1), number_terminations_total_1 --SubselectFROM (SELECT Segment = Monthly_Breakdown.Segment, SUM(CASE WHEN Period BETWEEN 200804 AND 200806 THEN number_terminations_salaried_controlled END) AS number_terminations_total_1 --Another Subselect FROM (SELECT Period, divisions.Segment, number_terminations_salaried_controlled = SUM(number_terminations_salaried_controlled)FROM RKTN_HCM_Reporting LEFT JOIN RKTN_CostCenters AS CostCenters ON Business_Unit = CostCenters.ID LEFT JOIN RKTN_Locations AS locations ON CostCenters.WorkSite = locations.WorkSite LEFT JOIN RKTN_Divisions AS divisions on CostCenters.Division = divisions.ID WHERE Period BETWEEN 200804 AND 200812 AND costcenters.Authorized_Plant IN (3211,3212,3215) AND costcenters.Division IN (10) AND divisions.Segment IN ('Consumer Packaging') GROUP BY Period, divisions.Segment) AS Monthly_Breakdown GROUP BY Monthly_Breakdown.Segment) AS WithPercentages) As PctMain --This Query is joined by Segment from the above Query but when returning by entire company there is nothing to join this outer query on. I only want the values summed and returned to the first query in the case of company totals..LEFT JOIN (Select Auth_Flag = 'Y', last_segment, sum(last_salaried_active_1) last_salaried_active_1, sum(last_hourly_active_1) last_hourly_active_1 FROM RKTN_Temp_Active GROUP BY last_segment) as testing ON testing.last_segment = PctMain.Segment ORDER By Segment |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-03-20 : 17:23:22
|
| format your query please. It looks a mess. Align your select, from , where etc clauses. |
 |
|
|
eric_ht
Starting Member
37 Posts |
Posted - 2009-03-21 : 16:43:24
|
| ok see it that looks better now... |
|
|
|
|
|
|
|
|
Left Join With Nothing to Join ON???