Need any help anyone can give.

[DarkWorlds]

First let me describe what im doing, I have to create a report from a single sql query. While im learning I do need to get further help.

These are the Columns and what they do, and how it should lay out.

Column 1 = number

Column 2 = number

Column 3 = 6 digit number (id#)

Column 4 = String (Name)

Column 5 = String

Column 6 = True/False

Column 8 = Number (max sum)

Column 9 = Number $$$$$ (sum of apr-dec months)

Column 10 = Number (sum apr-dec months)



Some of these are going to take calculations and others calls to databases. I would show the entire diagram but the company would be angry and I don’t have access to them anyways. Its all behind lines and will stay that way.

Here is what I have this far.

SELECT E.AGN_TERR_AGTMGR_CD AS TDL, E.AGN_AGENCY_MGR_CD AS MDL, Ov.AgtNum AS AGENT#, CASE WHEN Ov.[FIRST NM] IS NULL

THEN Ov.[LAST NM] ELSE Ov.[FIRST NM] + ' ' + Ov.[LAST NM] END AS 'AGT NAME', Ov.[AF AGT SIZE] AS 'RFG Category', EA.[VARIABLE LICENSE]

FROM RFG_Over36M_Mod AS Ov

INNER JOIN E7T_TOT_AGENT AS E ON

Ov.AgtNum = AGN_AGENT_NBR

INNER JOIN LIST_DATA AS LI ON

Ov.AgtNum = NBA_AGENT_NBR

LEFT JOIN EA_RFG EA ON

Ov.AgtNum = '0'+EA.AGENT#

WHERE (E.AGN_SUPV_REGION_CD = '016') AND (E.AGN_AGENT_TYPE_CD IN ('41', '13')) AND (LI.NBA_SUB_PROD_NBR = '000')

UNION

SELECT E.AGN_TERR_AGTMGR_CD AS TDL, E.AGN_AGENCY_MGR_CD AS MDL, Un.AgtNum AS AGENT#, CASE WHEN Un.[FIRST NM] IS NULL

THEN Un.[LAST NM] ELSE Un.[FIRST NM] + ' ' + Un.[LAST NM] END AS 'AGT NAME', 'RFG Category' = 'Under', EA.[VARIABLE LICENSE]

FROM RFG_Under36M AS Un

INNER JOIN E7T_TOT_AGENT AS E ON

Un.AgtNum = AGN_AGENT_NBR

INNER JOIN LIST_DATA AS LI ON

Un.AgtNum = NBA_AGENT_NBR

LEFT JOIN EA_RFG EA ON

Un.AgtNum = '0'+EA.AGENT#

WHERE (E.AGN_SUPV_REGION_CD = '016') AND (E.AGN_AGENT_TYPE_CD IN ('41', '13')) AND (LI.NBA_SUB_PROD_NBR = '000')

ORDER BY AGENT#

This takes care of the first 6 columns, the problem starts here. I cant keep putting each statement into the union clause. Because its starting to get slow, I would love to take out the EA.[VARIABLE LICENSE] and out it out of the issue. But basically everything will double check against an agent number. The agent numbers are check against this (E.AGN_SUPV_REGION_CD = '016') AND (E.AGN_AGENT_TYPE_CD IN ('41', '13')) AND (LI.NBA_SUB_PROD_NBR = '000') as you can see above.

How can I append to this with more querys, im lost. To keep putting the requirements in the union seems dumb(and taking time from the cpu). Is there any way around this. I know im not being that detailed but if you ask questions all try my best. I don’t know to much about the system my self, and the union, and join’s are new to me.

Such as this takes care of 1 of the requirements but I dont know how to attach it to this, and there will be more like this.


SELECT NBA_AGENT_NBR, NBA_ACCTG_DT, SUM(NBA_CMTD_PREM_AMT) AS TOTAL

FROM LIST_DATA

WHERE (NBA_ACCTG_DT =

(SELECT MAX(NBA_ACCTG_DT)

FROM LIST_60_DATA)) AND (NBA_SUB_PROD_NBR = '000') AND (NBA_CTGY_OF_INT_ID IN (61644, 61607, 61621, 61625))

GROUP BY NBA_AGENT_NBR, NBA_ACCTG_DT

ORDER BY NBA_AGENT_NBR


But it has to be done all in one go all in one postup. If anyone has any suggestion, or maybe a way to completely redo this let me know

Thanks for the help.

 

[SQLSister]

I would consider taking each chunk of data into a temp table or table variable and do any calculations you can on the temp table instead of the main table. Then I would perform the union on the various temp tables rather than the base tables.

Plus make sure that you have the right indexes onthe tables.

Questions about posting. See faq183-874