How to write this query simplerer

1

DROP TABLE IF EXISTS #tmp_EK_KondData;

declare @dtStart datetime
set @dtStart= '01.11.2014'

select KO.KO_ID,
KO.DV_ID,
KO.AK_ID,
KO.KVG_ID,
KO.FO_ID,
KO.VER_ID,
KO.KOA_ID,
KOA.KOA_NAME,
KO.KOE_ID,
KOE.KOE_NAME,
KO.KO_WERT,
CASE
WHEN KO1.KO_WERT IS NULL
THEN ISNULL(KO2.KO_WERT,0)
ELSE KO1.KO_WERT
END AS BEREINIGTE_EK,
CASE
WHEN KO3.KO_WERT IS NULL
THEN ISNULL(KO4.KO_WERT,0)
ELSE KO3.KO_WERT
END AS VK_MANDANT,
KO.KO_GUELTIG_AB,
KO.KO_GUELTIG_BIS,
KO.KO_ANGELEGT_AM,
KO.KO_MODIFIZIERT_AM,
KO.KO_ANGELEGT_VON,
KO.KO_MODIFIZIERT_VON,
KO.STA_ID,
case
WHEN KO.KOA_ID = 1
THEN 'NORMALE EK'
WHEN KO.KOA_ID = 13 AND KO.FO_ID IS NULL
THEN 'SOKO EK ASSETKLASSE'
WHEN KO.KOA_ID = 13 AND KO.FO_ID IS NOT NULL
THEN 'SOKO EK FONDS'
ELSE 'UPS'
END AS KOND_INFO
INTO #tmp_EK_KondData
FROM STAMM.dbo.KONDITION KO
LEFT OUTER JOIN STAMM.dbo.KONDITION KO1
ON KO.AK_ID = KO1.AK_ID
AND KO.DV_ID = KO1.DV_ID
AND ISNULL(KO.KVG_ID,0) = ISNULL(KO1.KVG_ID,0)
AND KO.PAG_ID = KO1.PAG_ID
AND KO1.KOA_ID = 9
AND @dtStart BETWEEN KO1.KO_GUELTIG_AB
AND KO1.KO_GUELTIG_BIS
AND KO1.STA_ID BETWEEN 100 AND 199
LEFT OUTER JOIN STAMM.dbo.KONDITION KO2
ON KO.AK_ID = KO2.AK_ID
AND ISNULL(KO.KVG_ID,0) = ISNULL(KO2.KVG_ID,0)
AND KO.PAG_ID = KO2.PAG_ID
AND KO2.KOA_ID = 9
AND KO2.DV_ID IS NULL
AND @dtStart BETWEEN KO2.KO_GUELTIG_AB
AND KO2.KO_GUELTIG_BIS
AND KO2.STA_ID BETWEEN 100 AND 199
LEFT OUTER JOIN STAMM.dbo.KONDITION KO3
ON KO.AK_ID = KO3.AK_ID
AND KO.DV_ID = KO3.DV_ID
AND ISNULL(KO.KVG_ID,0) = ISNULL(KO3.KVG_ID,0)
AND KO.PAG_ID = KO3.PAG_ID
AND KO3.KOA_ID = 2
AND @dtStart BETWEEN KO3.KO_GUELTIG_AB
AND KO3.KO_GUELTIG_BIS
AND KO3.STA_ID BETWEEN 100 AND 199
LEFT OUTER JOIN STAMM.dbo.KONDITION KO4
ON KO.AK_ID = KO4.AK_ID
AND ISNULL(KO.KVG_ID,0) = ISNULL(KO4.KVG_ID,0)
AND KO.PAG_ID = KO4.PAG_ID
AND KO4.KOA_ID = 2
AND KO4.DV_ID IS NULL
AND @dtStart BETWEEN KO4.KO_GUELTIG_AB
AND KO4.KO_GUELTIG_BIS
AND KO4.STA_ID BETWEEN 100 AND 199
INNER JOIN STAMM.dbo.KONDITION_ART KOA
ON KO.KOA_ID = KOA.KOA_ID
INNER JOIN STAMM.dbo.KONDITION_EINHEIT KOE
ON KO.KOE_ID = KOE.KOE_ID
WHERE KO.KOA_ID IN (1,13)
AND KO.PAG_ID = 2
and ( KO.KO_ANGELEGT_AM >= @dtStart OR
KO.KO_MODIFIZIERT_AM >= @dtStart
)
and @dtStart between KO.KO_GUELTIG_AB and KO.KO_GUELTIG_BIS
and KO.STA_ID between 100 and 199

-- UNTERTEILUNG IN SOKO UND NORMALE EK, WEGEN LEFT OUTER JOINS

SELECT
tmp1.DV_ID,
tmp1.VER_ID,
tmp1.AK_ID,
'' AS AK_CODE,
KVG.KVG_ID,
KVG.KVG_CODE,
KVG.KVG_NAME,
FO.FO_ID,
FO.FO_WKN,
FO.FO_BEZEICHNUNG,
tmp1.KOA_ID,
tmp1.KOA_NAME,
tmp1.KOE_ID,
tmp1.KOE_NAME,
tmp1.KO_WERT,
tmp1.BEREINIGTE_EK,
tmp1.VK_MANDANT,
tmp1.KO_GUELTIG_AB,
tmp1.KO_GUELTIG_BIS,
tmp1.KO_ANGELEGT_AM,
tmp1.KO_MODIFIZIERT_AM,
tmp1.KO_ANGELEGT_VON,
tmp1.KO_MODIFIZIERT_VON,
tmp1.KOND_INFO
FROM #tmp_EK_KondData tmp1
INNER JOIN STAMM.dbo.FONDS FO
ON tmp1.FO_ID = FO.FO_ID
INNER JOIN STAMM.dbo.KVG KVG
ON FO.KVG_ID = KVG.KVG_ID
WHERE tmp1.KOA_ID = 13
UNION ALL
SELECT
tmp1.DV_ID,
tmp1.VER_ID,
tmp1.AK_ID,
AKL.AK_CODE,
KVG.KVG_ID,
KVG.KVG_CODE,
KVG.KVG_NAME,
NULL AS FOND_NR,
'' AS FOND_WKN,
'' AS FOND_BEZEICHNUNG,
tmp1.KOA_ID,
tmp1.KOA_NAME,
tmp1.KOE_ID,
tmp1.KOE_NAME,
tmp1.KO_WERT,
tmp1.BEREINIGTE_EK,
tmp1.VK_MANDANT,
tmp1.KO_GUELTIG_AB,
tmp1.KO_GUELTIG_BIS,
tmp1.KO_ANGELEGT_AM,
tmp1.KO_MODIFIZIERT_AM,
tmp1.KO_ANGELEGT_VON,
tmp1.KO_MODIFIZIERT_VON,
tmp1.KOND_INFO
FROM #tmp_EK_KondData tmp1
INNER JOIN STAMM.dbo.ASSETKLASSE AKL
ON tmp1.AK_ID = AKL.AK_ID
INNER JOIN STAMM.dbo.ASSETKLASSE_FONDS_ZUORD AFZ
ON AKL.AK_ID = AFZ.AK_ID
AND @dtStart BETWEEN AFZ.AFZ_GUELTIG_AB
AND AFZ.AFZ_GUELTIG_BIS
AND AFZ.AFZ_AKTIV = 1
INNER JOIN STAMM.dbo.FONDS FO
ON AFZ.FO_ID = FO.FO_ID
INNER JOIN STAMM.dbo.KVG KVG
ON FO.KVG_ID = KVG.KVG_ID
WHERE tmp1.KOA_ID = 13
GROUP BY
tmp1.DV_ID,
tmp1.VER_ID,
tmp1.AK_ID,
AKL.AK_CODE,
KVG.KVG_ID,
KVG.KVG_CODE,
KVG.KVG_NAME,
tmp1.KOA_ID,
tmp1.KOA_NAME,
tmp1.KOE_ID,
tmp1.KOE_NAME,
tmp1.KO_WERT,
tmp1.BEREINIGTE_EK,
tmp1.VK_MANDANT,
tmp1.KO_GUELTIG_AB,
tmp1.KO_GUELTIG_BIS,
tmp1.KO_ANGELEGT_AM,
tmp1.KO_MODIFIZIERT_AM,
tmp1.KO_ANGELEGT_VON,
tmp1.KO_MODIFIZIERT_VON,
tmp1.KOND_INFO
UNION ALL
SELECT
tmp1.DV_ID,
tmp1.VER_ID,
tmp1.AK_ID,
AKL.AK_CODE,
KVG.KVG_ID,
KVG.KVG_CODE,
KVG.KVG_NAME,
NULL AS FOND_NR,
'' AS FOND_WKN,
'' AS FOND_BEZEICHNUNG,
tmp1.KOA_ID,
tmp1.KOA_NAME,
tmp1.KOE_ID,
tmp1.KOE_NAME,
tmp1.KO_WERT,
tmp1.BEREINIGTE_EK,
tmp1.VK_MANDANT,
tmp1.KO_GUELTIG_AB,
tmp1.KO_GUELTIG_BIS,
tmp1.KO_ANGELEGT_AM,
tmp1.KO_MODIFIZIERT_AM,
tmp1.KO_ANGELEGT_VON,
tmp1.KO_MODIFIZIERT_VON,
tmp1.KOND_INFO
FROM #tmp_EK_KondData tmp1
INNER JOIN STAMM.dbo.ASSETKLASSE AKL
ON tmp1.AK_ID = AKL.AK_ID
INNER JOIN STAMM.dbo.ASSETKLASSE_FONDS_ZUORD AFZ
ON AKL.AK_ID = AFZ.AK_ID
AND @dtStart BETWEEN AFZ.AFZ_GUELTIG_AB
AND AFZ.AFZ_GUELTIG_BIS
AND AFZ.AFZ_AKTIV = 1
INNER JOIN STAMM.dbo.FONDS FO
ON AFZ.FO_ID = FO.FO_ID
INNER JOIN STAMM.dbo.KVG KVG
ON FO.KVG_ID = KVG.KVG_ID
WHERE tmp1.KOA_ID = 1
GROUP BY
tmp1.DV_ID,
tmp1.VER_ID,
tmp1.AK_ID,
AKL.AK_CODE,
KVG.KVG_ID,
KVG.KVG_CODE,
KVG.KVG_NAME,
tmp1.KOA_ID,
tmp1.KOA_NAME,
tmp1.KOE_ID,
tmp1.KOE_NAME,
tmp1.KO_WERT,
tmp1.BEREINIGTE_EK,
tmp1.VK_MANDANT,
tmp1.KO_GUELTIG_AB,
tmp1.KO_GUELTIG_BIS,
tmp1.KO_ANGELEGT_AM,
tmp1.KO_MODIFIZIERT_AM,
tmp1.KO_ANGELEGT_VON,
tmp1.KO_MODIFIZIERT_VON,
tmp1.KOND_INFO

2

I see 2 queries, but the second has a union all in it. When you say simpler, do you mean because it's so long? We don't know what the schema looks like, what the execution plan is or what the real issue is

3

I mean beacause it is too complex, how can i send the excecution plan to you?

4

so, how do you want to simplify it?? We don't know your schema, so we really don't know what it's supposed to do