WITH [_Target] ( [_ResultID], [PayeeID_], [AttributeID], [LineID], [CategoryID], [Periods], [Value_]) AS
(
SELECT [_ResultID],
[PayeeID_],
[AttributeID],
[LineID],
[CategoryID],
[Periods],
[Value_]
FROM [_Result1218]
WHERE ((((
[_Result1218].[Periods]='2019, Week 11')
OR (
[_Result1218].[Periods]='2019, Week 12')
OR (
[_Result1218].[Periods]='2019, Week 13')
OR (
[_Result1218].[Periods]='2019, Week 14')
OR (
[_Result1218].[Periods]='2019, Week 15')
OR (
[_Result1218].[Periods]='2019, Week 16')
OR (
[_Result1218].[Periods]='2019, Week 17')
OR (
[_Result1218].[Periods]='2019, Week 18')
OR (
[_Result1218].[Periods]='2019, Week 19')
OR (
[_Result1218].[Periods]='2019, Week 20')
OR (
[_Result1218].[Periods]='2019, Week 21')
OR (
[_Result1218].[Periods]='2019, Week 22')
OR (
[_Result1218].[Periods]='2019, Week 23')
OR (
[_Result1218].[Periods]='2019, Week 24')
OR (
[_Result1218].[Periods]='2019, Week 25')
OR (
[_Result1218].[Periods]='2019, Week 26')
OR (
[_Result1218].[Periods]='2019, Week 27')
OR (
[_Result1218].[Periods]='2019, Week 28')
OR (
[_Result1218].[Periods]='2019, Week 29')
OR (
[_Result1218].[Periods]='2019, Week 30')
OR (
[_Result1218].[Periods]='2019, Week 31')
OR (
[_Result1218].[Periods]='2019, Week 32')
OR (
[_Result1218].[Periods]='2019, Week 33')
OR (
[_Result1218].[Periods]='2019, Week 34')
OR (
[_Result1218].[Periods]='2019, Week 35')
OR (
[_Result1218].[Periods]='2019, Week 36')
OR (
[_Result1218].[Periods]='2019, Week 37')
OR (
[_Result1218].[Periods]='2019, Week 38')
OR (
[_Result1218].[Periods]='2019, Week 39')
OR (
[_Result1218].[Periods]='2019, Week 40')
OR (
[_Result1218].[Periods]='2019, Week 41')
OR (
[_Result1218].[Periods]='2019, Week 42')
OR (
[_Result1218].[Periods]='2019, Week 43')
OR (
[_Result1218].[Periods]='2019, Week 44')
OR (
[_Result1218].[Periods]='2019, Week 45')
OR (
[_Result1218].[Periods]='2019, Week 46')
OR (
[_Result1218].[Periods]='2019, Week 47')
OR (
[_Result1218].[Periods]='2019, Week 48')
OR (
[_Result1218].[Periods]='2019, Week 49')
OR (
[_Result1218].[Periods]='2019, Week 50')
OR (
[_Result1218].[Periods]='2019, Week 51')
OR (
[_Result1218].[Periods]='2019, Week 52')))))
MERGE [_Target] AS [_Result1218]
using (
SELECT Cast(0 AS INT) [_ResultID],
[_Port777].[_Column1] [PayeeID_],
[_LookupAlias5].[_Column42] [AttributeID],
[_Port777].[_Column3] [LineID],
[_Port777].[_Column2] [CategoryID],
[_Time].[Name_] [Periods],
Sum([_LookupAlias3].[_Column32]) [Value_]
FROM (
SELECT [_Port777].[Periods] [_Column0],
[_Port777].[PayeeID_] [_Column1],
[_Port777].[CategoryID] [_Column2],
[_Port777].[LineID] [_Column3],
[_TableAlias1].[DataStream] [_Column4],
[_TableAlias1].[CommonComponent] [_Column5],
[_TableAlias1].[AccumulationType] [_Column6],
[_TableAlias2].[TransType] [_Column7]
FROM (
SELECT [_Result1217].[PayeeID_] [PayeeID_],
[_Result1217].[AttributeID] [AttributeID],
[_Result1217].[LineID] [LineID],
[_Result1217].[CategoryID] [CategoryID],
[_Result1217].[Periods] [Periods],
[_Result1217].[Value_] [Value_]
FROM [_Result1217] [_Result1217]) [_Port777]
INNER JOIN [AttributesTable] [_TableAlias1]
ON ((
[_TableAlias1].[AttributeID]=[_Port777].[AttributeID]))
INNER JOIN [dtMasterData] [_TableAlias2]
ON ((
[_TableAlias2].[LineID]=[_Port777].[LineID]))) [_Port777]
INNER JOIN
(
SELECT [_LookupAlias0].[TimeID_] [_Column8],
[_LookupAlias0].[Name_] [_Column9],
[_LookupAlias0].[Ending_] [_Column10]
FROM [Time_] [_LookupAlias0]) [_LookupAlias0]
ON (((
[_LookupAlias0].[_Column8]='T001')
AND (
[_LookupAlias0].[_Column9]=[_Port777].[_Column0])))
INNER JOIN
(
SELECT [_LookupAlias1].[PayeeID] [_Column11],
[_LookupAlias1].[Start_Date] [_Column12],
[_LookupAlias1].[End_Date] [_Column13],
[_LookupAlias1].[LocationID] [_Column14],
[_LookupAlias1].[CompPlanID] [_Column15]
FROM [EffectivePayee] [_LookupAlias1]) [_LookupAlias1]
ON (((
[_LookupAlias1].[_Column11]=[_Port777].[_Column1])
AND (
[_LookupAlias1].[_Column12]<=[_LookupAlias0].[_Column10])
AND (
[_LookupAlias1].[_Column13]>=[_LookupAlias0].[_Column10])))
INNER JOIN
(
SELECT [_LookupAlias2].[LocationID] [_Column16],
[_LookupAlias2].[Periods] [_Column17],
[_LookupAlias2].[Value_] [_Column18],
[_TableAlias0].[NumericType] [_Column19],
[_TableAlias0].[CommonComponent] [_Column20],
[_TableAlias0].[AccumulationType] [_Column21],
[_TableAlias0].[DataStream] [_Column22]
FROM (
SELECT [_Port589].[LocationID] [LocationID],
[_Port589].[AttributeID] [AttributeID],
[_Port589].[Periods] [Periods],
[_Port589].[Value_] [Value_]
FROM (
SELECT [_Port207].[LocationID] [LocationID],
[_Port207].[AttributeID] [AttributeID],
[_Port207].[Periods] [Periods],
[_Port207].[Value_] [Value_]
FROM (
SELECT [_Result323].[LocationID] [LocationID],
[_Result323].[AttributeID] [AttributeID],
[_Result323].[Periods] [Periods],
[_Result323].[Value_] [Value_]
FROM [_Result323] [_Result323]) [_Port207]) [_Port589]) [_LookupAlias2]
INNER JOIN [AttributesTable] [_TableAlias0]
ON ((
[_TableAlias0].[AttributeID]=[_LookupAlias2].[AttributeID]))) [_LookupAlias2]
ON (((
[_LookupAlias2].[_Column16]=[_LookupAlias1].[_Column14])
AND (
[_LookupAlias2].[_Column17]=[_Port777].[_Column0])
AND (
[_LookupAlias2].[_Column19]='ATTAIN %')
AND (
[_LookupAlias2].[_Column20]='NETSALES')
AND (
[_LookupAlias2].[_Column21]='QTD')
AND (
[_LookupAlias2].[_Column22]=[_Port777].[_Column4])))
INNER JOIN
(
SELECT [_LookupAlias3].[CompPlan] [_Column23],
[_LookupAlias3].[Component] [_Column24],
[_LookupAlias3].[Category] [_Column25],
[_LookupAlias3].[StartDate] [_Column26],
[_LookupAlias3].[EndDate] [_Column27],
[_LookupAlias3].[TierMin] [_Column28],
[_LookupAlias3].[TierMax] [_Column29],
[_LookupAlias3].[TierType] [_Column30],
[_LookupAlias3].[DataStream] [_Column31],
[_LookupAlias3].[Rate] [_Column32]
FROM [RatesByCategory] [_LookupAlias3]) [_LookupAlias3]
ON (((
[_LookupAlias3].[_Column23]=[_LookupAlias1].[_Column15])
AND (
[_LookupAlias3].[_Column24]=[_Port777].[_Column5])
AND (
[_LookupAlias3].[_Column25]=[_Port777].[_Column2])
AND (
[_LookupAlias3].[_Column26]<=[_LookupAlias0].[_Column10])
AND (
[_LookupAlias3].[_Column27]>=[_LookupAlias0].[_Column10])
AND (
[_LookupAlias3].[_Column28]<=[_LookupAlias2].[_Column18])
AND (
[_LookupAlias3].[_Column29]>[_LookupAlias2].[_Column18])
AND (
[_LookupAlias3].[_Column30]='RETROACTIVE')
AND ((
[_LookupAlias3].[_Column23]<>'CNH')
OR (
[_LookupAlias3].[_Column23] IS NULL))
AND ((
[_LookupAlias3].[_Column23]<>'CSM')
OR (
[_LookupAlias3].[_Column23] IS NULL))
AND ((
[_LookupAlias3].[_Column23]<>'DSM')
OR (
[_LookupAlias3].[_Column23] IS NULL))
AND ((
[_LookupAlias3].[_Column23]<>'DHV')
OR (
[_LookupAlias3].[_Column23] IS NULL))
AND ((
[_LookupAlias3].[_Column23]<>'DXL')
OR (
[_LookupAlias3].[_Column23] IS NULL))
AND ((
[_LookupAlias3].[_Column23]<>'CXL')
OR (
[_LookupAlias3].[_Column23] IS NULL))
AND ((
[_LookupAlias3].[_Column23]<>'CNM')
OR (
[_LookupAlias3].[_Column23] IS NULL))))
INNER JOIN
(
SELECT [_LookupAlias4].[NodeID] [_Column33],
[_LookupAlias4].[StartDate] [_Column34],
[_LookupAlias4].[EndDate] [_Column35],
[_LookupAlias4].[Incoming_DataStream] [_Column36],
[_LookupAlias4].[Node_DataStream] [_Column37]
FROM [DataStreamDirection] [_LookupAlias4]) [_LookupAlias4]
ON (((
[_LookupAlias4].[_Column33]='RATES')
AND (
[_LookupAlias4].[_Column34]<=[_LookupAlias0].[_Column10])
AND (
[_LookupAlias4].[_Column35]>=[_LookupAlias0].[_Column10])
AND (
[_LookupAlias4].[_Column36]=[_Port777].[_Column4])
AND (
[_LookupAlias4].[_Column37]=[_LookupAlias3].[_Column31])))
INNER JOIN
(
SELECT [_LookupAlias5].[NumericType] [_Column38],
[_LookupAlias5].[CommonComponent] [_Column39],
[_LookupAlias5].[AccumulationType] [_Column40],
[_LookupAlias5].[DataStream] [_Column41],
[_LookupAlias5].[AttributeID] [_Column42]
FROM [AttributesTable] [_LookupAlias5]) [_LookupAlias5]
ON (((
[_LookupAlias5].[_Column38]='COMMRATE')
AND (
[_LookupAlias5].[_Column39]=[_Port777].[_Column5])
AND (
[_LookupAlias5].[_Column40]=[_Port777].[_Column6])
AND (
[_LookupAlias5].[_Column41]=[_Port777].[_Column4])))
INNER JOIN [_Period0] [_Time]
ON ((
[_Time].[Name_]=[_Port777].[_Column0])
AND (
[_Time].[Ending_]>='2019-04-14 00:00:00.000')
AND (
[_Time].[Ending_]<='2020-02-01 00:00:00.000'))
WHERE (((
[_Port777].[_Column7]='S'))
AND ((
[_Port777].[_Column0]='2019, Week 11')
OR (
[_Port777].[_Column0]='2019, Week 12')
OR (
[_Port777].[_Column0]='2019, Week 13')
OR (
[_Port777].[_Column0]='2019, Week 14')
OR (
[_Port777].[_Column0]='2019, Week 15')
OR (
[_Port777].[_Column0]='2019, Week 16')
OR (
[_Port777].[_Column0]='2019, Week 17')
OR (
[_Port777].[_Column0]='2019, Week 18')
OR (
[_Port777].[_Column0]='2019, Week 19')
OR (
[_Port777].[_Column0]='2019, Week 20')
OR (
[_Port777].[_Column0]='2019, Week 21')
OR (
[_Port777].[_Column0]='2019, Week 22')
OR (
[_Port777].[_Column0]='2019, Week 23')
OR (
[_Port777].[_Column0]='2019, Week 24')
OR (
[_Port777].[_Column0]='2019, Week 25')
OR (
[_Port777].[_Column0]='2019, Week 26')
OR (
[_Port777].[_Column0]='2019, Week 27')
OR (
[_Port777].[_Column0]='2019, Week 28')
OR (
[_Port777].[_Column0]='2019, Week 29')
OR (
[_Port777].[_Column0]='2019, Week 30')
OR (
[_Port777].[_Column0]='2019, Week 31')
OR (
[_Port777].[_Column0]='2019, Week 32')
OR (
[_Port777].[_Column0]='2019, Week 33')
OR (
[_Port777].[_Column0]='2019, Week 34')
OR (
[_Port777].[_Column0]='2019, Week 35')
OR (
[_Port777].[_Column0]='2019, Week 36')
OR (
[_Port777].[_Column0]='2019, Week 37')
OR (
[_Port777].[_Column0]='2019, Week 38')
OR (
[_Port777].[_Column0]='2019, Week 39')
OR (
[_Port777].[_Column0]='2019, Week 40')
OR (
[_Port777].[_Column0]='2019, Week 41')
OR (
[_Port777].[_Column0]='2019, Week 42')
OR (
[_Port777].[_Column0]='2019, Week 43')
OR (
[_Port777].[_Column0]='2019, Week 44')
OR (
[_Port777].[_Column0]='2019, Week 45')
OR (
[_Port777].[_Column0]='2019, Week 46')
OR (
[_Port777].[_Column0]='2019, Week 47')
OR (
[_Port777].[_Column0]='2019, Week 48')
OR (
[_Port777].[_Column0]='2019, Week 49')
OR (
[_Port777].[_Column0]='2019, Week 50')
OR (
[_Port777].[_Column0]='2019, Week 51')
OR (
[_Port777].[_Column0]='2019, Week 52')))
GROUP BY [_Port777].[_Column1],
[_LookupAlias5].[_Column42],
[_Port777].[_Column3],
[_Port777].[_Column2],
[_Time].[Name_]
HAVING ((
Sum([_LookupAlias3].[_Column32]) IS NOT NULL))) AS [SOURCE]
ON (
[SOURCE].[PayeeID_] = [_Result1218].[PayeeID_]
AND [SOURCE].[AttributeID] = [_Result1218].[AttributeID]
AND [SOURCE].[LineID] = [_Result1218].[LineID]
AND [SOURCE].[CategoryID] = [_Result1218].[CategoryID]
AND [SOURCE].[Periods] = [_Result1218].[Periods])
WHEN matched
AND EXISTS
(
SELECT [SOURCE].[Value_]
EXCEPT
SELECT [_Result1218].[Value_])
AND (((
[_Result1218].[Periods]='2019, Week 11')
OR (
[_Result1218].[Periods]='2019, Week 12')
OR (
[_Result1218].[Periods]='2019, Week 13')
OR (
[_Result1218].[Periods]='2019, Week 14')
OR (
[_Result1218].[Periods]='2019, Week 15')
OR (
[_Result1218].[Periods]='2019, Week 16')
OR (
[_Result1218].[Periods]='2019, Week 17')
OR (
[_Result1218].[Periods]='2019, Week 18')
OR (
[_Result1218].[Periods]='2019, Week 19')
OR (
[_Result1218].[Periods]='2019, Week 20')
OR (
[_Result1218].[Periods]='2019, Week 21')
OR (
[_Result1218].[Periods]='2019, Week 22')
OR (
[_Result1218].[Periods]='2019, Week 23')
OR (
[_Result1218].[Periods]='2019, Week 24')
OR (
[_Result1218].[Periods]='2019, Week 25')
OR (
[_Result1218].[Periods]='2019, Week 26')
OR (
[_Result1218].[Periods]='2019, Week 27')
OR (
[_Result1218].[Periods]='2019, Week 28')
OR (
[_Result1218].[Periods]='2019, Week 29')
OR (
[_Result1218].[Periods]='2019, Week 30')
OR (
[_Result1218].[Periods]='2019, Week 31')
OR (
[_Result1218].[Periods]='2019, Week 32')
OR (
[_Result1218].[Periods]='2019, Week 33')
OR (
[_Result1218].[Periods]='2019, Week 34')
OR (
[_Result1218].[Periods]='2019, Week 35')
OR (
[_Result1218].[Periods]='2019, Week 36')
OR (
[_Result1218].[Periods]='2019, Week 37')
OR (
[_Result1218].[Periods]='2019, Week 38')
OR (
[_Result1218].[Periods]='2019, Week 39')
OR (
[_Result1218].[Periods]='2019, Week 40')
OR (
[_Result1218].[Periods]='2019, Week 41')
OR (
[_Result1218].[Periods]='2019, Week 42')
OR (
[_Result1218].[Periods]='2019, Week 43')
OR (
[_Result1218].[Periods]='2019, Week 44')
OR (
[_Result1218].[Periods]='2019, Week 45')
OR (
[_Result1218].[Periods]='2019, Week 46')
OR (
[_Result1218].[Periods]='2019, Week 47')
OR (
[_Result1218].[Periods]='2019, Week 48')
OR (
[_Result1218].[Periods]='2019, Week 49')
OR (
[_Result1218].[Periods]='2019, Week 50')
OR (
[_Result1218].[Periods]='2019, Week 51')
OR (
[_Result1218].[Periods]='2019, Week 52'))) THEN
UPDATE
SET [Value_] = [SOURCE].[Value_]
WHEN NOT matched BY source
AND (((
[_Result1218].[Periods]='2019, Week 11')
OR (
[_Result1218].[Periods]='2019, Week 12')
OR (
[_Result1218].[Periods]='2019, Week 13')
OR (
[_Result1218].[Periods]='2019, Week 14')
OR (
[_Result1218].[Periods]='2019, Week 15')
OR (
[_Result1218].[Periods]='2019, Week 16')
OR (
[_Result1218].[Periods]='2019, Week 17')
OR (
[_Result1218].[Periods]='2019, Week 18')
OR (
[_Result1218].[Periods]='2019, Week 19')
OR (
[_Result1218].[Periods]='2019, Week 20')
OR (
[_Result1218].[Periods]='2019, Week 21')
OR (
[_Result1218].[Periods]='2019, Week 22')
OR (
[_Result1218].[Periods]='2019, Week 23')
OR (
[_Result1218].[Periods]='2019, Week 24')
OR (
[_Result1218].[Periods]='2019, Week 25')
OR (
[_Result1218].[Periods]='2019, Week 26')
OR (
[_Result1218].[Periods]='2019, Week 27')
OR (
[_Result1218].[Periods]='2019, Week 28')
OR (
[_Result1218].[Periods]='2019, Week 29')
OR (
[_Result1218].[Periods]='2019, Week 30')
OR (
[_Result1218].[Periods]='2019, Week 31')
OR (
[_Result1218].[Periods]='2019, Week 32')
OR (
[_Result1218].[Periods]='2019, Week 33')
OR (
[_Result1218].[Periods]='2019, Week 34')
OR (
[_Result1218].[Periods]='2019, Week 35')
OR (
[_Result1218].[Periods]='2019, Week 36')
OR (
[_Result1218].[Periods]='2019, Week 37')
OR (
[_Result1218].[Periods]='2019, Week 38')
OR (
[_Result1218].[Periods]='2019, Week 39')
OR (
[_Result1218].[Periods]='2019, Week 40')
OR (
[_Result1218].[Periods]='2019, Week 41')
OR (
[_Result1218].[Periods]='2019, Week 42')
OR (
[_Result1218].[Periods]='2019, Week 43')
OR (
[_Result1218].[Periods]='2019, Week 44')
OR (
[_Result1218].[Periods]='2019, Week 45')
OR (
[_Result1218].[Periods]='2019, Week 46')
OR (
[_Result1218].[Periods]='2019, Week 47')
OR (
[_Result1218].[Periods]='2019, Week 48')
OR (
[_Result1218].[Periods]='2019, Week 49')
OR (
[_Result1218].[Periods]='2019, Week 50')
OR (
[_Result1218].[Periods]='2019, Week 51')
OR (
[_Result1218].[Periods]='2019, Week 52'))) THEN
DELETE
WHEN NOT matched BY target THEN
INSERT
(
[_ResultID],
[PayeeID_],
[AttributeID],
[LineID],
[CategoryID],
[Periods],
[Value_]
)
VALUES
(
0,
[SOURCE].[PayeeID_],
[SOURCE].[AttributeID],
[SOURCE].[LineID],
[SOURCE].[CategoryID],
[SOURCE].[Periods],
[SOURCE].[Value_]
);