November 17, 2011 at 1:30 pm
I'm having an issue with a stored procedure that queries a table valued function which uses a number of table variables. When SQL Server is recycled the stored procedure doesn't complete (I can also recompile the table value function to make it perform poorly also). If I run the stored procedure and its parameters through the database tuning advisor it doesn't recommend any indexes. However, when I execute the stored procedure again afterwards it returns within 2 minutes. I've looked at a profiler trace while the DTA was running but nothing was done, that I can see, other than the create\drop of hypothetical indexes. What may I be overlooking?
November 17, 2011 at 1:44 pm
Review the spid doing the initial call after reboot, and review its wait stats. Find out WHY that first one is taking so long, you'll have a better idea.
My guess is the cache isn't loaded yet after the reboot so you're running into a ton of PageLatch_IO with a query that's poorly optimized.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 17, 2011 at 2:09 pm
It's pretty usual for queries of many sorts to run slower after a reboot, because of caching issues for both data and execution plans.
This sounds, however, like a good case for code optimization. Can you post the code and the table definitions it impacts? If so, performance improvements can probably be suggested.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 17, 2011 at 2:13 pm
Here is the stored proc:
CREATEPROCEDURE [dbo].[usp_rpt_InflMon]
@OrganizationID Int=NULL,
@PlantID Int=NULL,
@CommodityManagerGroupID Int=NULL,
@CommodityManagerSubGroupID Int=NULL,
@CommLdrUserID Int=NULL,
@CommodityManagerUserID Int=NULL,
@SupplierCode Varchar(50)=NULL,
@SupplierName Varchar(50)=NULL,
@ContractNumber Varchar(50)=NULL,
@BaseYear Int,
@CurrSnapshotID Int,
@TrackedForInflation BIT = NULL, --SSR 80863 Change
@MaterialCostAgreement BIT = NULL --SSR 81055 Change
AS
SET NOCOUNT ON
DECLARE @AOPSnapshotID Int
DECLARE @JanPeriod Datetime
DECLARE @DecPeriod Datetime
DECLARE @CheckSupplierCount Int
IF NOT @SupplierCode IS NULL SET @SupplierCode = '%' + @SupplierCode + '%'
IF NOT @SupplierName IS NULL SET @SupplierName = '%' + @SupplierName + '%'
IF NOT @ContractNumber IS NULL SET @ContractNumber = '%' + @ContractNumber + '%'
-- SSR 81911 changes Begin
SET @CheckSupplierCount = 0
IF NOT @SupplierCode IS NULL SET @CheckSupplierCount = (SELECT COUNT(SupplierCode) FROM tSupplier WITH (NOLOCK) WHERE suppliercode LIKE @SupplierCode)
-- SSR 81911 changes End
SELECT @AOPSnapshotID = SnapshotID FROM tSnapshot WITH (NOLOCK) WHERE tSnapshot.Official = 1 AND tSnapshot.SnapshotTypeID = 1 AND tSnapshot.SnapshotYear = @BaseYear
SELECT @JanPeriod = Convert(Datetime, Convert(Varchar(4), @BaseYear) + '-01-01' ),
@DecPeriod = Convert(Datetime, Convert(Varchar(4), @BaseYear) + '-12-01' )
IF @CheckSupplierCount = 1 -- SSR 81911 Change
BEGIN-- SSR 81911 Change
SELECT
RepRowDesc,
ISNULL(pDec,0) AS pDec,
ISNULL(pAnn,0) AS pAnn,
ISNULL(Jan,0) AS Jan,
ISNULL(Feb,0) AS Feb,
ISNULL(Mar,0) AS Mar,
ISNULL(Apr,0) AS Apr,
ISNULL(May,0) AS May,
ISNULL(Jun,0) AS Jun,
ISNULL(Jul,0) AS Jul,
ISNULL(Aug,0) AS Aug,
ISNULL(Sep,0) AS Sep,
ISNULL(Oct,0) AS Oct,
ISNULL(Nov,0) AS Nov,
ISNULL([Dec],0) AS [Dec],
ISNULL(ExchangeRateDate,'') AS ExchangeRateDate, --SSR 80705 Change - Add a column for ExchangeRateDate
ISNULL(SupplierName,'') AS SupplierName ---SSR 81911 Change - Add a column to get Supplier Name for given Supplier Code.
FROM
( --SSR - 80698 Changes Begin
SELECT 1 AS RepRowID, 'AOP Volume' AS RepRowDesc
UNION ALL
SELECT 2, 'AOP Spend'
UNION ALL
SELECT 3, 'AOP Annual FC'
UNION ALL
SELECT 4, 'AOP Annual FX'
UNION ALL
SELECT 5, 'AOP Annual FO'
UNION ALL
SELECT 6, 'AOP Annual FR'
UNION ALL
SELECT 7, 'AOP Fiscal FC'
UNION ALL
SELECT 8, 'AOP Fiscal FX'
UNION ALL
SELECT 9, 'AOP Fiscal FO'
UNION ALL
SELECT 10, 'AOP Fiscal FR'
UNION ALL
SELECT 11, 'Curr Volume'
UNION ALL
SELECT 12, 'Curr Spend'
UNION ALL
SELECT 13, 'Curr Annual FC'
UNION ALL
SELECT 14, 'Curr Annual FX'
UNION ALL
SELECT 15, 'Curr Annual FO'
UNION ALL
SELECT 16, 'Curr Annual FR'
UNION ALL
SELECT 21, 'Backdated MPCAs Curr Annual'
UNION ALL
SELECT 17, 'Curr Fiscal FC'
UNION ALL
SELECT 18, 'Curr Fiscal FX'
UNION ALL
SELECT 19, 'Curr Fiscal FO'
UNION ALL
SELECT 20, 'Curr Fiscal FR'
--SSR - 82281 Changes Begin
UNION ALL
SELECT 22, 'Backdated MPCAs Curr Fiscal'
--SSR - 82281 Changes End
) AS RepRow --SSR - 80698 Changes End
LEFT OUTER JOIN
(
SELECT
RepRowID,
SUM(pDec) as pDec,
SUM(pAnn) as pAnn,
SUM(Jan) as Jan,
SUM(Feb) as Feb,
SUM(Mar) as Mar,
SUM(Apr) as Apr,
SUM(May) as May,
SUM(Jun) as Jun,
SUM(Jul) as Jul,
SUM(Aug) as Aug,
SUM(Sep) as Sep,
SUM(Oct) as Oct,
SUM(Nov) as Nov,
SUM([Dec]) as [Dec],
ExchangeRateDate, --SSR 80705 Change
SupplierName ---SSR 81911 Change - Add a column to get Supplier Name for given Supplier Code.
FROM
(
SELECT DISTINCT
ContractID
FROM
--SSR 82210 -Changes Begin
-- tSnapshotSummary WITH (NOLOCK)
-- WHERE
-- SnapshotID in (@AOPSnapshotID, @CurrSnapshotID) AND
-- Period BETWEEN @JanPeriod AND @DecPeriod
((SELECT DISTINCT ContractID FROMtSnapshotsummary WHERESnapshotID in (@AOPSnapshotID, @CurrSnapshotID))
UNION
(SELECT DISTINCT ContractIDFROMtHistorysummary WHEREPeriod >= Convert(Datetime, Convert(Varchar(4), (@BaseYear-1)) + '-12-01' )))AS Contracts
--SSR 82210 -Changes End
) AS SnapshotContract
INNER JOIN
tContract WITH (NOLOCK) ON SnapshotContract.ContractID = tContract.ContractID
INNER JOIN
tSupplier WITH (NOLOCK) ON tSupplier.SupplierID = tContract.SupplierID
INNER JOIN
tPlant WITH (NOLOCK) ON tContract.PlantID = tPlant.PlantID
LEFT OUTER JOIN
tSupplierOrganization WITH (NOLOCK) on tSupplier.SupplierID = tSupplierOrganization.SupplierID AND tPlant.OrganizationID = tSupplierOrganization.OrganizationID
INNER JOIN
tOrganization WITH (NOLOCK) ON tSupplierOrganization.OrganizationID = tOrganization.OrganizationID
LEFT OUTER JOIN
tCommodityManagerSubGroup WITH (NOLOCK) ON tSupplier.CommodityManagerSubGroupID = tCommodityManagerSubGroup.CommodityManagerSubGroupID
LEFT OUTER JOIN
tCommodityManagerGroup WITH (NOLOCK) ON tCommodityManagerSubGroup.CommodityManagerGroupID = tCommodityManagerGroup.CommodityManagerGroupID
LEFT OUTER JOIN
(
SELECT CommodityManagerSubGroupID, UserID AS CommLdrUserID
FROM tCommoditySubGroupTeamMember WITH (NOLOCK)
WHERE (CommodityTeamMemberTypeID = 2)
) AS CommLdr ON tSupplier.CommodityManagerSubGroupID = CommLdr.CommodityManagerSubGroupID
LEFT OUTER JOIN
(
SELECT
[CommodityManagerSubGroupID],
[tCommodityTeamMemberType].OrganizationID,
tCommoditySubGroupTeamMember.[UserID] as SubRegionalBuyerUserID
FROM
[tCommoditySubGroupTeamMember] WITH (NOLOCK)
INNER JOIN
[tCommodityTeamMemberType] ON [tCommoditySubGroupTeamMember].CommodityTeamMemberTypeID = tCommodityTeamMemberType.CommodityTeamMemberTypeID
WHERE
[tCommoditySubGroupTeamMember].[CommodityTeamMemberTypeID] in(4,5)
) AS SubRegionalBuyer
ON tCommodityManagerSubGroup.CommodityManagerSubGroupID = SubRegionalBuyer.CommodityManagerSubGroupID
AND tSupplierOrganization.OrganizationID = SubRegionalBuyer.OrganizationID
LEFT OUTER JOIN
(
SELECT
RepRowID,
ContractID,
pDec,
pAnn,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
[Dec],
IsNull(ExchangeRateDate,'') as ExchangeRateDate --SSR 80705 Change
FROM
dbo.fnInflationMonitorData(@AOPSnapshotID,'A',@BaseYear)
UNION ALL
SELECT
RepRowID,
ContractID,
pDec,
pAnn,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
[Dec],
IsNull(ExchangeRateDate,'') as ExchangeRateDate --SSR 80705 Change
FROM
dbo.fnInflationMonitorData(@CurrSnapshotID,'C',@BaseYear)
) AS RptData ON SnapshotContract.ContractID = RptData.ContractID
WHERE
(tSupplierOrganization.OrganizationID = @OrganizationID or @OrganizationID IS NULL)
AND (tContract.PlantID = @PlantID OR @PlantID IS NULL)
AND (tCommodityManagerSubGroup.CommodityManagerGroupID = @CommodityManagerGroupID OR @CommodityManagerGroupID IS NULL)
AND (tSupplier.CommodityManagerSubGroupID = @CommodityManagerSubGroupID OR @CommodityManagerSubGroupID IS NULL)
AND (CommLdr.CommLdrUserID = @CommLdrUserID OR @CommLdrUserID IS NULL)
AND (SubRegionalBuyer.SubRegionalBuyerUserID = @CommodityManagerUserID OR @CommodityManagerUserID IS NULL)
AND (tSupplier.SupplierCode Like @SupplierCode OR @SupplierCode IS NULL)
AND (tSupplier.SupplierName Like @SupplierName OR @SupplierName IS NULL)
AND (tContract.ContractNumber Like @ContractNumber OR @ContractNumber IS NULL)
AND (tSupplier.InflationMonitorTracked = @TrackedForInflation OR @TrackedForInflation IS NULL) --SSR 80863 Change - Filter the Records using InflactionMonitorTracked
AND (tSupplier.MaterialCostAgreeementExist = @MaterialCostAgreement OR @MaterialCostAgreement IS NULL) --SSR 81055 Change
ANDtsupplier.IMTSupplier <> 1 --SSR 82026 Change
GROUP BY
RepRowID,ExchangeRateDate,SupplierName
) AS RepRowData ON RepRow.RepRowID = RepRowData.RepRowID
--ORDER BY
--RepRow.RepRowID
-- SSR 81911 Changes Begin
END
ELSE
BEGIN
-- SSR 81911 Changes End
SELECT
RepRowDesc,
ISNULL(pDec,0) AS pDec,
ISNULL(pAnn,0) AS pAnn,
ISNULL(Jan,0) AS Jan,
ISNULL(Feb,0) AS Feb,
ISNULL(Mar,0) AS Mar,
ISNULL(Apr,0) AS Apr,
ISNULL(May,0) AS May,
ISNULL(Jun,0) AS Jun,
ISNULL(Jul,0) AS Jul,
ISNULL(Aug,0) AS Aug,
ISNULL(Sep,0) AS Sep,
ISNULL(Oct,0) AS Oct,
ISNULL(Nov,0) AS Nov,
ISNULL([Dec],0) AS [Dec],
ISNULL(ExchangeRateDate,'') AS ExchangeRateDate, --SSR 80705 Change - Add a column for ExchangeRateDate
'' AS SupplierName ---SSR 81911 Change
FROM
( --SSR - 80698 Changes Begin
SELECT 1 AS RepRowID, 'AOP Volume' AS RepRowDesc
UNION ALL
SELECT 2, 'AOP Spend'
UNION ALL
SELECT 3, 'AOP Annual FC'
UNION ALL
SELECT 4, 'AOP Annual FX'
UNION ALL
SELECT 5, 'AOP Annual FO'
UNION ALL
SELECT 6, 'AOP Annual FR'
UNION ALL
SELECT 7, 'AOP Fiscal FC'
UNION ALL
SELECT 8, 'AOP Fiscal FX'
UNION ALL
SELECT 9, 'AOP Fiscal FO'
UNION ALL
SELECT 10, 'AOP Fiscal FR'
UNION ALL
SELECT 11, 'Curr Volume'
UNION ALL
SELECT 12, 'Curr Spend'
UNION ALL
SELECT 13, 'Curr Annual FC'
UNION ALL
SELECT 14, 'Curr Annual FX'
UNION ALL
SELECT 15, 'Curr Annual FO'
UNION ALL
SELECT 16, 'Curr Annual FR'
UNION ALL
SELECT 21, 'Backdated MPCAs Curr Annual'
UNION ALL
SELECT 17, 'Curr Fiscal FC'
UNION ALL
SELECT 18, 'Curr Fiscal FX'
UNION ALL
SELECT 19, 'Curr Fiscal FO'
UNION ALL
SELECT 20, 'Curr Fiscal FR'
--SSR - 82281 Changes Begin
UNION ALL
SELECT 22, 'Backdated MPCAs Curr Fiscal'
--SSR - 82281 Changes End
) AS RepRow --SSR - 80698 Changes End
LEFT OUTER JOIN
(
SELECT
RepRowID,
SUM(pDec) as pDec,
SUM(pAnn) as pAnn,
SUM(Jan) as Jan,
SUM(Feb) as Feb,
SUM(Mar) as Mar,
SUM(Apr) as Apr,
SUM(May) as May,
SUM(Jun) as Jun,
SUM(Jul) as Jul,
SUM(Aug) as Aug,
SUM(Sep) as Sep,
SUM(Oct) as Oct,
SUM(Nov) as Nov,
SUM([Dec]) as [Dec],
ExchangeRateDate --SSR 80705 Change
FROM
(
SELECT DISTINCT
ContractID
FROM
--SSR 82210 -Changes Begin
-- tSnapshotSummary WITH (NOLOCK)
-- WHERE
-- SnapshotID in (@AOPSnapshotID, @CurrSnapshotID) AND
-- Period BETWEEN @JanPeriod AND @DecPeriod
((SELECT DISTINCT ContractID FROMtSnapshotsummary WHERESnapshotID in (@AOPSnapshotID, @CurrSnapshotID))
UNION
(SELECT DISTINCT ContractIDFROMtHistorysummary WHEREPeriod >= Convert(Datetime, Convert(Varchar(4), (@BaseYear-1)) + '-12-01' )))AS Contracts
--SSR 82210 -Changes End
) AS SnapshotContract
INNER JOIN
tContract WITH (NOLOCK) ON SnapshotContract.ContractID = tContract.ContractID
INNER JOIN
tSupplier WITH (NOLOCK) ON tSupplier.SupplierID = tContract.SupplierID
INNER JOIN
tPlant WITH (NOLOCK) ON tContract.PlantID = tPlant.PlantID
LEFT OUTER JOIN
tSupplierOrganization WITH (NOLOCK) on tSupplier.SupplierID = tSupplierOrganization.SupplierID AND tPlant.OrganizationID = tSupplierOrganization.OrganizationID
INNER JOIN
tOrganization WITH (NOLOCK) ON tSupplierOrganization.OrganizationID = tOrganization.OrganizationID
LEFT OUTER JOIN
tCommodityManagerSubGroup WITH (NOLOCK) ON tSupplier.CommodityManagerSubGroupID = tCommodityManagerSubGroup.CommodityManagerSubGroupID
LEFT OUTER JOIN
tCommodityManagerGroup WITH (NOLOCK) ON tCommodityManagerSubGroup.CommodityManagerGroupID = tCommodityManagerGroup.CommodityManagerGroupID
LEFT OUTER JOIN
(
SELECT CommodityManagerSubGroupID, UserID AS CommLdrUserID
FROM tCommoditySubGroupTeamMember WITH (NOLOCK)
WHERE (CommodityTeamMemberTypeID = 2)
) AS CommLdr ON tSupplier.CommodityManagerSubGroupID = CommLdr.CommodityManagerSubGroupID
LEFT OUTER JOIN
(
SELECT
[CommodityManagerSubGroupID],
[tCommodityTeamMemberType].OrganizationID,
tCommoditySubGroupTeamMember.[UserID] as SubRegionalBuyerUserID
FROM
[tCommoditySubGroupTeamMember] WITH (NOLOCK)
INNER JOIN
[tCommodityTeamMemberType] ON [tCommoditySubGroupTeamMember].CommodityTeamMemberTypeID = tCommodityTeamMemberType.CommodityTeamMemberTypeID
WHERE
[tCommoditySubGroupTeamMember].[CommodityTeamMemberTypeID] in(4,5)
) AS SubRegionalBuyer
ON tCommodityManagerSubGroup.CommodityManagerSubGroupID = SubRegionalBuyer.CommodityManagerSubGroupID
AND tSupplierOrganization.OrganizationID = SubRegionalBuyer.OrganizationID
LEFT OUTER JOIN
(
SELECT
RepRowID,
ContractID,
pDec,
pAnn,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
[Dec],
IsNull(ExchangeRateDate,'') as ExchangeRateDate --SSR 80705 Change
FROM
dbo.fnInflationMonitorData(@AOPSnapshotID,'A',@BaseYear)
UNION ALL
SELECT
RepRowID,
ContractID,
pDec,
pAnn,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
[Dec],
IsNull(ExchangeRateDate,'') as ExchangeRateDate --SSR 80705 Change
FROM
dbo.fnInflationMonitorData(@CurrSnapshotID,'C',@BaseYear)
) AS RptData ON SnapshotContract.ContractID = RptData.ContractID
WHERE
(tSupplierOrganization.OrganizationID = @OrganizationID or @OrganizationID IS NULL)
AND (tContract.PlantID = @PlantID OR @PlantID IS NULL)
AND (tCommodityManagerSubGroup.CommodityManagerGroupID = @CommodityManagerGroupID OR @CommodityManagerGroupID IS NULL)
AND (tSupplier.CommodityManagerSubGroupID = @CommodityManagerSubGroupID OR @CommodityManagerSubGroupID IS NULL)
AND (CommLdr.CommLdrUserID = @CommLdrUserID OR @CommLdrUserID IS NULL)
AND (SubRegionalBuyer.SubRegionalBuyerUserID = @CommodityManagerUserID OR @CommodityManagerUserID IS NULL)
AND (tSupplier.SupplierCode Like @SupplierCode OR @SupplierCode IS NULL)
AND (tSupplier.SupplierName Like @SupplierName OR @SupplierName IS NULL)
AND (tContract.ContractNumber Like @ContractNumber OR @ContractNumber IS NULL)
AND (tSupplier.InflationMonitorTracked = @TrackedForInflation OR @TrackedForInflation IS NULL) --SSR 80863 Change - Filter the Records using InflactionMonitorTracked
AND (tSupplier.MaterialCostAgreeementExist = @MaterialCostAgreement OR @MaterialCostAgreement IS NULL) --SSR 81055 Change
ANDtsupplier.IMTSupplier <> 1 --SSR 82026 Change
GROUP BY
RepRowID,ExchangeRateDate
) AS RepRowData ON RepRow.RepRowID = RepRowData.RepRowID
--ORDER BY
--RepRow.RepRowID
END --SSR 81911 Change.
November 17, 2011 at 2:15 pm
And the table valued function:
CREATE FUNCTION [dbo].[fnInflationMonitorData]
(
@SnapshotID int,
@SnapKind char(1),
@BaseYear int
)
RETURNS
@RepData TABLE
(
RepRowID int,
ContractID int,
pDec Decimal(20,5),
pAnn Decimal(20,5),
Jan Decimal(20,5),
Feb Decimal(20,5),
Mar Decimal(20,5),
Apr Decimal(20,5),
May Decimal(20,5),
Jun Decimal(20,5),
Jul Decimal(20,5),
Aug Decimal(20,5),
Sep Decimal(20,5),
Oct Decimal(20,5),
Nov Decimal(20,5),
[Dec] Decimal(20,5),
ExchangeRateDate Datetime--SSR 80705 Change - Insert a new column for ExchangeRateDate
)
AS
BEGIN
DECLARE @RepRowSeed int
DECLARE @PriorDecPeriod Datetime
DECLARE @CurrJanPeriod Datetime
DECLARE @CurrDecPeriod Datetime
--SSR 80705 -Changes Begin
--Get value for ExchangeRateDate
Declare @ExchangeRateDate Datetime
SELECT @ExchangeRateDate = EffectiveRateDate FROM tSnapshot WITH (NOLOCK) WHERE SnapshotID = @SnapshotID
--SSR 80705 Changes End
DECLARE @SnapshotContract as Table(ContractID int NOT NULL, PRIMARY KEY (ContractID))
SELECT @PriorDecPeriod = Convert(Datetime, Convert(Varchar(4), (@BaseYear-1)) + '-12-01' ),
@CurrJanPeriod = Convert(Datetime, Convert(Varchar(4), @BaseYear) + '-01-01' ),
@CurrDecPeriod = Convert(Datetime, Convert(Varchar(4), @BaseYear) + '-12-01' )
IF @SnapKind='A' SET @RepRowSeed=1
IF @SnapKind='C' SET @RepRowSeed=11 --SSR-80698 Change
INSERT INTO @SnapshotContract
SELECT DISTINCT
ContractID
FROM
--SSR 82210 -Changes Begin
--
--
--
-- tSnapshotSummary WITH (NOLOCK)
--
--
--
--WHERE
--
--
--
-- SnapshotID = @SnapshotID AND
--
--
--
-- Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
((SELECT DISTINCT ContractID FROMtSnapshotsummary WHERESnapshotID = @SnapshotID)
UNION
(SELECT DISTINCT ContractIDFROMtHistorysummary WHEREPeriod >= @PriorDecPeriod))AS Contracts
DECLARE@snapflag AS INT
IF EXISTS(SELECT 1 FROMtSnapshotsummary WHERE SnapshotID = @SnapshotID AND Period = @PriorDecPeriod)
BEGIN
SET @snapflag = 1
END
ELSE
BEGIN
SET @snapflag = 0
END
--SSR 82210 -Changes End
INSERT INTO @RepData
SELECT
@RepRowSeed,
SnapshotContract.ContractID,
pDec,
pAnn,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
[Dec],
@ExchangeRateDate--SSR 80705 Change - Insert Value for ExchangeRateDate
FROM
@SnapshotContract as SnapshotContract
LEFT OUTER JOIN
(
SELECT
SnapshotContract.[ContractID],
--SSR 82025 -Changes Begin
-- Change the logic for AOP section, If AOP records exists for prior Dec period then use it otherwise use history summary data.
-- But for Current section should be the same.
--CASE WHEN NOT tHistorySummary.MER IS NULL THEN tHistorySummary.MER ELSE tSnapshotSummary.TotalMER END as pDec,
--CASE WHEN NOT tHistorySummary.MER IS NULL THEN tHistorySummary.EAR ELSE tSnapshotSummary.TotalEAR END as pAnn
--SSR 82210 -Changes Begin
CASE WHEN@SnapKind = 'A' AND @RepRowSeed = 1 AND @snapflag = 1 THEN
--CASE WHEN NOTtSnapshotSummary.TotalMER IS NULL THENtSnapshotSummary.TotalMER
--
--ELSEtHistorySummary.MER
--
--END
tSnapshotSummary.TotalMER
ELSE
--CASE WHEN NOTtHistorySummary.MER IS NULL THENtHistorySummary.MER
--
--ELSEtSnapshotSummary.TotalMER
--
--END
tHistorySummary.MER
END ASpDec,
CASE WHEN@SnapKind = 'A' AND @RepRowSeed = 1 AND @snapflag = 1 THEN
--CASE WHEN NOTtSnapshotSummary.TotalMER IS NULL THENtSnapshotSummary.TotalEAR
--
--ELSEtHistorySummary.EAR
--
--END
tSnapshotSummary.TotalEAR
ELSE
--CASE WHEN NOTtHistorySummary.MER IS NULL THENtHistorySummary.EAR
--
--ELSEtSnapshotSummary.TotalEAR
--
--END
tHistorySummary.EAR
END AS pAnn
--SSR 82210 -Changes End
--SSR 82025 -Changes End
FROM
@SnapshotContract as SnapshotContract
LEFT OUTER JOIN
tHistorySummary WITH (NOLOCK) ON SnapshotContract.ContractID = tHistorySummary.ContractID AND tHistorySummary.Period = @PriorDecPeriod
LEFT OUTER JOIN
tSnapshotSummary WITH (NOLOCK) ON SnapshotContract.ContractID = tSnapshotSummary.ContractID AND tSnapshotSummary.SnapshotID = @SnapshotID AND tSnapshotSummary.Period = @PriorDecPeriod
) AS VolLastYear ON SnapshotContract.ContractID = VolLastYear.ContractID
LEFT OUTER JOIN
(
SELECT
ContractID,
SUM((ContractMER)*(1-ABS(SIGN(DatePart(mm,Period)-1)))) AS Jan,
SUM((ContractMER)*(1-ABS(SIGN(DatePart(mm,Period)-2)))) AS Feb,
SUM((ContractMER)*(1-ABS(SIGN(DatePart(mm,Period)-3)))) AS Mar,
SUM((ContractMER)*(1-ABS(SIGN(DatePart(mm,Period)-4)))) AS Apr,
SUM((ContractMER)*(1-ABS(SIGN(DatePart(mm,Period)-5)))) AS May,
SUM((ContractMER)*(1-ABS(SIGN(DatePart(mm,Period)-6)))) AS Jun,
SUM((ContractMER)*(1-ABS(SIGN(DatePart(mm,Period)-7)))) AS Jul,
SUM((ContractMER)*(1-ABS(SIGN(DatePart(mm,Period)-8)))) AS Aug,
SUM((ContractMER)*(1-ABS(SIGN(DatePart(mm,Period)-9)))) AS Sep,
SUM((ContractMER)*(1-ABS(SIGN(DatePart(mm,Period)-10)))) AS Oct,
SUM((ContractMER)*(1-ABS(SIGN(DatePart(mm,Period)-11)))) AS Nov,
SUM((ContractMER)*(1-ABS(SIGN(DatePart(mm,Period)-12)))) AS [Dec]
FROM
(
SELECT
SnapshotContract.ContractID,
ContractPeriod.Period,
CASE WHEN NOT tSnapshotSummary.TotalMER IS NULL OR @SnapKind='A' THEN tSnapshotSummary.TotalMER ELSE tHistorySummary.MER END AS ContractMER
FROM
@SnapshotContract as SnapshotContract
CROSS JOIN
(
SELECT @CurrJanPeriod AS Period
UNION ALL
SELECT DateAdd(month, 1, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 2, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 3, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 4, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 5, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 6, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 7, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 8, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 9, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 10, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 11, @CurrJanPeriod)
) AS ContractPeriod
LEFT OUTER JOIN
tHistorySummary WITH (NOLOCK) ON SnapshotContract.ContractID = tHistorySummary.ContractID AND ContractPeriod.Period = tHistorySummary.Period
LEFT OUTER JOIN
tSnapshotSummary WITH (NOLOCK) ON tSnapshotSummary.SnapshotID = @SnapshotID AND SnapshotContract.ContractID = tSnapshotSummary.ContractID AND ContractPeriod.Period = tSnapshotSummary.Period
) AS VolThisYearByPeriod
GROUP BY
ContractID
) AS VolThisYear ON SnapshotContract.ContractID = VolThisYear.ContractID
INSERT INTO @RepData
SELECT
(@RepRowSeed+1),
SnapshotContract.ContractID,
pDec,
pAnn,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
[Dec],
@ExchangeRateDate--SSR 80705 Change - Insert Value for ExchangeRateDate
FROM
@SnapshotContract as SnapshotContract
LEFT OUTER JOIN
(
SELECT
SnapshotContract.[ContractID],
--SSR 82025 -Changes Begin
-- Change the logic for AOP section, If AOP records exists for prior Dec period then use it otherwise use history summary data.
-- But for Current section should be the same.
--CASE WHEN NOT tHistorySummary.TotalMERSpend IS NULL THEN Case IsNull(tHistorySummary.ExchangeRate,0) when 0 then 0 else (tHistorySummary.TotalMERSpend/tHistorySummary.ExchangeRate) End ELSE Case IsNull(tSnapshotSummary.ExchangeRate,0) when 0 then 0 else (tSnapshotSummary.TotalMERSpend/tSnapshotSummary.ExchangeRate) End END as pDec,
--CASE WHEN NOT tHistorySummary.TotalMERSpend IS NULL THEN Case IsNull(tHistorySummary.ExchangeRate,0) when 0 then 0 else (tHistorySummary.TotalEARSpend/tHistorySummary.ExchangeRate) End ELSE Case IsNull(tSnapshotSummary.ExchangeRate,0) when 0 then 0 else (tSnapshotSummary.TotalEARSpend/tSnapshotSummary.ExchangeRate) End END as pAnn
--SSR 82210 -Changes Begin
CASE WHEN@SnapKind = 'A' AND @RepRowSeed = 1 AND @snapflag = 1 THEN
--CASE WHEN NOTtSnapshotSummary.TotalMERSpend IS NULL THEN
CASEISNULL(tSnapshotSummary.ExchangeRate,0) WHEN 0 THEN 0
ELSE(tSnapshotSummary.TotalMERSpend/tSnapshotSummary.ExchangeRate)
END
--ELSE
--
--CASEISNULL(tHistorySummary.ExchangeRate,0) WHEN 0 THEN 0
--
--ELSE(tHistorySummary.TotalMERSpend/tHistorySummary.ExchangeRate)
--
--END
--
--END
ELSE
--CASE WHEN NOTtHistorySummary.TotalMERSpend IS NULL THEN
CASEISNULL(tHistorySummary.ExchangeRate,0) WHEN 0 THEN 0
ELSE(tHistorySummary.TotalMERSpend/tHistorySummary.ExchangeRate)
END
--ELSE
--
--CASEISNULL(tSnapshotSummary.ExchangeRate,0) WHEN 0 THEN 0
--
--ELSE(tSnapshotSummary.TotalMERSpend/tSnapshotSummary.ExchangeRate)
--
--END
--
--END
END AS pDec,
CASE WHEN@SnapKind = 'A' AND @RepRowSeed = 1 AND @snapflag = 1 THEN
--CASE WHEN NOTtSnapshotSummary.TotalMERSpend IS NULL THEN
CASEISNULL(tSnapshotSummary.ExchangeRate,0) WHEN 0 THEN 0
ELSE(tSnapshotSummary.TotalEARSpend/tSnapshotSummary.ExchangeRate)
END
--ELSE
--
--CASEISNULL(tHistorySummary.ExchangeRate,0) WHEN 0 THEN 0
--
--ELSE(tHistorySummary.TotalEARSpend/tHistorySummary.ExchangeRate)
--
--END
--
--END
ELSE
--CASE WHEN NOTtHistorySummary.TotalMERSpend IS NULL THEN
CASEISNULL(tHistorySummary.ExchangeRate,0) WHEN 0 THEN 0
ELSE(tHistorySummary.TotalEARSpend/tHistorySummary.ExchangeRate)
END
--ELSE
--
--CASEISNULL(tSnapshotSummary.ExchangeRate,0) WHEN 0 THEN 0
--
--ELSE(tSnapshotSummary.TotalEARSpend/tSnapshotSummary.ExchangeRate)
--
--END
--
--END
END AS pAnn
--SSR 82210 -Changes End
--SSR 82025 -Changes End
FROM
@SnapshotContract as SnapshotContract
LEFT OUTER JOIN
tHistorySummary WITH (NOLOCK) ON SnapshotContract.ContractID = tHistorySummary.ContractID AND tHistorySummary.Period = @PriorDecPeriod
LEFT OUTER JOIN
tSnapshotSummary WITH (NOLOCK) ON SnapshotContract.ContractID = tSnapshotSummary.ContractID AND tSnapshotSummary.SnapshotID = @SnapshotID AND tSnapshotSummary.Period = @PriorDecPeriod
) AS SpendLastYear ON SnapshotContract.ContractID = SpendLastYear.ContractID
LEFT OUTER JOIN
(
SELECT
ContractID,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-1)))) AS Jan,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-2)))) AS Feb,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-3)))) AS Mar,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-4)))) AS Apr,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-5)))) AS May,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-6)))) AS Jun,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-7)))) AS Jul,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-8)))) AS Aug,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-9)))) AS Sep,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-10)))) AS Oct,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-11)))) AS Nov,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-12)))) AS [Dec]
FROM
(
SELECT
SnapshotContract.ContractID,
ContractPeriod.Period,
CASE WHEN NOT tSnapshotSummary.TotalMERSpend IS NULL OR @SnapKind='A' THEN Case IsNull(tSnapshotSummary.ExchangeRate,0) when 0 then 0 else (tSnapshotSummary.TotalMERSpend/tSnapshotSummary.ExchangeRate) End ELSE Case IsNull(tHistorySummary.ExchangeRate,0) when 0 then 0 else (tHistorySummary.TotalMERSpend/tHistorySummary.ExchangeRate) End END AS ContractMERSpend
FROM
@SnapshotContract as SnapshotContract
CROSS JOIN
(
SELECT @CurrJanPeriod AS Period
UNION ALL
SELECT DateAdd(month, 1, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 2, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 3, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 4, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 5, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 6, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 7, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 8, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 9, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 10, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 11, @CurrJanPeriod)
) AS ContractPeriod
LEFT OUTER JOIN
tHistorySummary WITH (NOLOCK) ON SnapshotContract.ContractID = tHistorySummary.ContractID AND ContractPeriod.Period = tHistorySummary.Period
LEFT OUTER JOIN
tSnapshotSummary WITH (NOLOCK) ON tSnapshotSummary.SnapshotID = @SnapshotID AND SnapshotContract.ContractID = tSnapshotSummary.ContractID AND ContractPeriod.Period = tSnapshotSummary.Period
) AS SpendThisYearByPeriod
GROUP BY
ContractID
) AS SpendThisYear ON SnapshotContract.ContractID = SpendThisYear.ContractID
INSERT INTO @RepData
SELECT
RepRowID,
ContractID,
NULL AS pDec,
NULL AS pAnn,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-1)))) AS Jan,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-2)))) AS Feb,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-3)))) AS Mar,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-4)))) AS Apr,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-5)))) AS May,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-6)))) AS Jun,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-7)))) AS Jul,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-8)))) AS Aug,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-9)))) AS Sep,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-10)))) AS Oct,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-11)))) AS Nov,
SUM((ContractMERSpend)*(1-ABS(SIGN(DatePart(mm,Period)-12)))) AS [Dec],
@ExchangeRateDate--SSR 80705 Change - Insert Value for ExchangeRateDate
FROM
(
SELECT
RepRow.RepRowID,
HistSnapData.ContractID,
HistSnapData.Period,
CASE WHEN NOT snapMERSpend IS NULL OR @SnapKind='A' THEN Case IsNull(snapExchangeRate,0) when 0 then 0 else ((snapMERSpend * (snapDriverPercent/100))/snapExchangeRate) End ELSE Case IsNull(histExchangeRate,0) when 0 then 0 else (((histMERSpend/((100 + histFC + histFX + histFO + histFR)/100)) * (histDriverPercent / 100)) / histExchangeRate) End END AS ContractMERSpend
FROM
(
SELECT (@RepRowSeed+2) AS RepRowID
UNION ALL
SELECT (@RepRowSeed+3)
UNION ALL
SELECT (@RepRowSeed+4)
UNION ALL
SELECT (@RepRowSeed+5)
) AS RepRow
CROSS JOIN
(
SELECT
SnapshotContract.ContractID,
ContractPeriod.Period,
tempSnapshotSummary.snapMERSpend,
tempSnapshotSummary.ExchangeRate AS snapExchangeRate,
tHistorySummary.TotalMERSpend AS histMERSpend,
tHistorySummary.FCDriverPercent AS histFC,
tHistorySummary.FXDriverPercent AS histFX,
tHistorySummary.FODriverPercent AS histFO,
tHistorySummary.FRDriverPercent AS histFR,
tHistorySummary.ExchangeRate AS histExchangeRate
FROM
@SnapshotContract as SnapshotContract
CROSS JOIN
(
SELECT @CurrJanPeriod AS Period
UNION ALL
SELECT DateAdd(month, 1, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 2, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 3, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 4, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 5, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 6, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 7, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 8, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 9, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 10, @CurrJanPeriod)
UNION ALL
SELECT DateAdd(month, 11, @CurrJanPeriod)
) AS ContractPeriod
LEFT OUTER JOIN
tHistorySummary WITH (NOLOCK) ON SnapshotContract.ContractID = tHistorySummary.ContractID AND ContractPeriod.Period = tHistorySummary.Period
LEFT OUTER JOIN
(
SELECT
ContractSnapSpend.ContractID,
ContractSnapSpend.Period,
ContractSnapSpend.snapMERSpend,
tSnapshotSummary.ExchangeRate
FROM
(
SELECT
tPart.ContractID,
--SSR-80690 Changes Start
tsp.Period,
SUM((PreviousPrice/CASE ISNULL(tsp.PricePerQty,0) WHEN 0 THEN 1 ELSE tsp.PricePerQty END) *
(MER/(CASE ISNULL(tsp.ConversionFactor,0) WHEN 0 THEN 1 ELSE tsp.ConversionFactor END /
CASE ISNULL(tsp.ConversionFactorBase,0) WHEN 0 THEN 1 ELSE tsp.ConversionFactorBase END)))
AS snapMERSpend
FROM
tSnapshotPart tsp WITH (NOLOCK)
--SSR-80690 Changes End
INNER JOIN
tPart WITH (NOLOCK) ON tsp.PartID = tPart.PartID
WHERE
tsp.SnapshotID = @SnapshotID AND
tsp.Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
--AND
--tPart.Revision = tPart.PlantRevisionLevel
GROUP BY
tPart.ContractID,
tsp.Period
) AS ContractSnapSpend
INNER JOIN
tSnapshotSummary WITH (NOLOCK) ON ContractSnapSpend.ContractID = tSnapshotSummary.ContractID AND ContractSnapSpend.Period = tSnapshotSummary.Period
WHERE
tSnapshotSummary.SnapshotID = @SnapshotID
) AS tempSnapshotSummary ON SnapshotContract.ContractID = tempSnapshotSummary.ContractID AND ContractPeriod.Period = tempSnapshotSummary.Period
) AS HistSnapData
LEFT OUTER JOIN
(
SELECT
(@RepRowSeed+2) AS RepRowID,
ContractID,
Period,
FCDriverPercent AS SnapDriverPercent
FROM
tSnapshotSummary WITH (NOLOCK)
WHERE
SnapshotID = @SnapshotID AND
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
(@RepRowSeed+3) AS RepRowID,
ContractID,
Period,
FXDriverPercent
FROM
tSnapshotSummary WITH (NOLOCK)
WHERE
SnapshotID = @SnapshotID AND
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
(@RepRowSeed+4) AS RepRowID,
ContractID,
Period,
FODriverPercent
FROM
tSnapshotSummary WITH (NOLOCK)
WHERE
SnapshotID = @SnapshotID AND
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
(@RepRowSeed+5) AS RepRowID,
ContractID,
Period,
FRDriverPercent
FROM
tSnapshotSummary WITH (NOLOCK)
WHERE
SnapshotID = @SnapshotID AND
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
) AS snapDriverPercent ON RepRow.RepRowID = snapDriverPercent.RepRowID AND HistSnapData.ContractID = snapDriverPercent.ContractID AND HistSnapData.Period = snapDriverPercent.Period
LEFT OUTER JOIN
(
SELECT
(@RepRowSeed+2) AS RepRowID,
SnapshotContract.ContractID,
Period,
FCDriverPercent AS histDriverPercent
FROM
@SnapshotContract as SnapshotContract
INNER JOIN
tHistorySummary WITH (NOLOCK) ON SnapshotContract.ContractID = tHistorySummary.ContractID
WHERE
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
(@RepRowSeed+3) AS RepRowID,
SnapshotContract.ContractID,
Period,
FXDriverPercent
FROM
@SnapshotContract as SnapshotContract
INNER JOIN
tHistorySummary WITH (NOLOCK) ON SnapshotContract.ContractID = tHistorySummary.ContractID
WHERE
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
(@RepRowSeed+4) AS RepRowID,
SnapshotContract.ContractID,
Period,
FODriverPercent
FROM
@SnapshotContract as SnapshotContract
INNER JOIN
tHistorySummary WITH (NOLOCK) ON SnapshotContract.ContractID = tHistorySummary.ContractID
WHERE
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
(@RepRowSeed+5) AS RepRowID,
SnapshotContract.ContractID,
Period,
FRDriverPercent
FROM
@SnapshotContract as SnapshotContract
INNER JOIN
tHistorySummary WITH (NOLOCK) ON SnapshotContract.ContractID = tHistorySummary.ContractID
WHERE
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
) AS histDriverPercent ON RepRow.RepRowID = histDriverPercent.RepRowID AND HistSnapData.ContractID = histDriverPercent.ContractID AND HistSnapData.Period = histDriverPercent.Period
) AS ContractSpend
GROUP BY
ContractID,
RepRowID
--SSR-80698 Changes Begin
--To Retrieve and insert the AOP and Current Fiscal Values
INSERT INTO @RepData
SELECT
RepRowID,
ContractID,
NULL AS pDec,
NULL AS pAnn,
SUM((ContractMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-1)))) AS Jan,
SUM((ContractMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-2)))) AS Feb,
SUM((ContractMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-3)))) AS Mar,
SUM((ContractMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-4)))) AS Apr,
SUM((ContractMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-5)))) AS May,
SUM((ContractMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-6)))) AS Jun,
SUM((ContractMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-7)))) AS Jul,
SUM((ContractMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-8)))) AS Aug,
SUM((ContractMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-9)))) AS Sep,
SUM((ContractMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-10)))) AS Oct,
SUM((ContractMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-11)))) AS Nov,
SUM((ContractMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-12)))) AS [Dec],
@ExchangeRateDate--SSR 80705 Change - Insert Value for ExchangeRateDate
FROM
(
SELECT
RepRow.RepRowID,
HistSnapData.ContractID,
HistSnapData.Period,
--replace CASE WHEN NOT snapMERSpend IS NULL OR @SnapKind='A'
--replace THEN (snapMERSpend * (snapFIPercent*(snapDriverPercent/Case IsNull((snapFC+snapFX+snapFO+snapFR),0) When 0 Then 1 Else (snapFC+snapFX+snapFO+snapFR) End/100)))/snapExchangeRate
--replace ELSE ((histMERSpend/((100 + histFC + histFX + histFO + histFR)/100)) *
--replace ((((histTotalROYMERSpend-histOriginalROYMERSpend)/Case IsNull(histOriginalEARSpend,0) When 0 Then 1 Else histOriginalEARSpend End)*100)*
--replace (histDriverPercent/Case IsNull((histFC + histFX + histFO + histFR),0) When 0 Then 1 Else (histFC + histFX + histFO + histFR) End/100)))
--replace / Case IsNull(histExchangeRate,0) When 0 Then 1 Else histExchangeRate End END AS ContractMERSpend
CASE WHEN NOT snapFI IS NULL OR @SnapKind='A'
THEN (snapFI * (snapDriverPercent/Case IsNull((snapFC+snapFX+snapFO+snapFR),0) When 0 Then 1 Else (snapFC+snapFX+snapFO+snapFR) End))/snapExchangeRate
ELSE ((histTotalROYMERSpend-histOriginalROYMERSpend) * ((histDriverPercent/Case IsNull((histFC + histFX + histFO + histFR),0) When 0 Then 1 Else (histFC + histFX + histFO + histFR) End)))
/ Case IsNull(histExchangeRate,0) When 0 Then 1 Else histExchangeRate End END AS ContractMERSpend
FROM
(
SELECT (@RepRowSeed+6) AS RepRowID
UNION ALL
SELECT (@RepRowSeed+7)
UNION ALL
SELECT (@RepRowSeed+8)
UNION ALL
SELECT (@RepRowSeed+9)
) AS RepRow
CROSS JOIN
(
SELECT
SnapshotContract.ContractID,
ContractPeriod.Period,
tempSnapshotSummary.snapMERSpend,
tempSnapshotSummary.ExchangeRate AS snapExchangeRate,
tempSnapshotSummary.FCDriverPercent AS snapFC,
tempSnapshotSummary.FXDriverPercent AS snapFX,
tempSnapshotSummary.FODriverPercent AS snapFO,
tempSnapshotSummary.FRDriverPercent AS snapFR,
--insertline
tempSnapshotSummary.FiscalImpact AS snapFI,
--endinsert
tempSnapshotSummary.FiscalImpactPercent AS snapFIPercent,
tHistorySummary.TotalMERSpend AS histMERSpend,
tHistorySummary.FCDriverPercent AS histFC,
tHistorySummary.FXDriverPercent AS histFX,
tHistorySummary.FODriverPercent AS histFO,
tHistorySummary.FRDriverPercent AS histFR,
tHistorySummary.ExchangeRate AS histExchangeRate,
tHIstorySummary.TotalROYMERSpend AS histTotalROYMERSpend,
tHistorySummary.TotalEARSpend/((100+ tHistorySummary.FCDriverPercent+tHistorySummary.FXDriverPercent+
tHistorySummary.FODriverPercent + tHistorySummary.FRDriverPercent)/100) AS histOriginalEARSpend,
tHistorySummary.TotalROYMERSpend/((100+ tHistorySummary.FCDriverPercent+tHistorySummary.FXDriverPercent+
tHistorySummary.FODriverPercent + tHistorySummary.FRDriverPercent)/100) AS histOriginalROYMERSpend
FROM
@SnapshotContract AS SnapshotContract
CROSS JOIN
(
SELECT @CurrJanPeriod AS Period
UNION ALL
SELECT DATEADD(MONTH, 1, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 2, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 3, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 4, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 5, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 6, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 7, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 8, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 9, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 10, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 11, @CurrJanPeriod)
) AS ContractPeriod
LEFT OUTER JOIN
tHistorySummary WITH (NOLOCK) ON SnapshotContract.ContractID = tHistorySummary.ContractID
AND ContractPeriod.Period = tHistorySummary.Period
LEFT OUTER JOIN
(
SELECT
ContractSnapSpend.ContractID,
ContractSnapSpend.Period,
ContractSnapSpend.snapMERSpend,
tSnapshotSummary.ExchangeRate,
--insertline
tSnapshotSummary.FiscalImpact,
--endinsert
tSnapshotSummary.FiscalImpactPercent,
tSnapshotSummary.FCDriverPercent,
tSnapshotSummary.FXDriverPercent,
tSnapshotSummary.FODriverPercent,
tSnapshotSummary.FRDriverPercent
FROM
(
SELECT
tPart.ContractID,
tsp.Period,
--SSR-80690 Changes Begin
SUM((PreviousPrice/CASE ISNULL(tsp.PricePerQty,0) WHEN 0 THEN 1 ELSE tsp.PricePerQty END) *
(MER/(CASE ISNULL(tsp.ConversionFactor,0) WHEN 0 THEN 1
ELSE tsp.ConversionFactor END / CASE ISNULL(tsp.ConversionFactorBase,0)
WHEN 0 THEN 1 ELSE tsp.ConversionFactorBase END))) AS snapMERSpend
FROM
tSnapshotPart tsp WITH (NOLOCK)
--SSR-80690 Changes End
INNER JOIN
tPart WITH (NOLOCK) ON tsp.PartID = tPart.PartID
WHERE
tsp.SnapshotID = @SnapshotID AND
tsp.Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
--AND
--tPart.Revision = tPart.PlantRevisionLevel
GROUP BY
tPart.ContractID,
tsp.Period
) AS ContractSnapSpend
INNER JOIN
tSnapshotSummary WITH (NOLOCK) ON ContractSnapSpend.ContractID = tSnapshotSummary.ContractID
AND ContractSnapSpend.Period = tSnapshotSummary.Period
WHERE
tSnapshotSummary.SnapshotID = @SnapshotID
) AS tempSnapshotSummary ON SnapshotContract.ContractID = tempSnapshotSummary.ContractID
AND ContractPeriod.Period = tempSnapshotSummary.Period
) AS HistSnapData
LEFT OUTER JOIN
(
SELECT
(@RepRowSeed+6) AS RepRowID,
ContractID,
Period,
FCDriverPercent AS SnapDriverPercent
FROM
tSnapshotSummary WITH (NOLOCK)
WHERE
SnapshotID = @SnapshotID AND
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
(@RepRowSeed+7) AS RepRowID,
ContractID,
Period,
FXDriverPercent
FROM
tSnapshotSummary WITH (NOLOCK)
WHERE
SnapshotID = @SnapshotID AND
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
(@RepRowSeed+8) AS RepRowID,
ContractID,
Period,
FODriverPercent
FROM
tSnapshotSummary WITH (NOLOCK)
WHERE
SnapshotID = @SnapshotID AND
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
(@RepRowSeed+9) AS RepRowID,
ContractID,
Period,
FRDriverPercent
FROM
tSnapshotSummary WITH (NOLOCK)
WHERE
SnapshotID = @SnapshotID AND
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
) AS snapDriverPercent ON RepRow.RepRowID = snapDriverPercent.RepRowID AND HistSnapData.ContractID = snapDriverPercent.ContractID
AND HistSnapData.Period = snapDriverPercent.Period
LEFT OUTER JOIN
(
SELECT
(@RepRowSeed+6) AS RepRowID,
SnapshotContract.ContractID,
Period,
FCDriverPercent AS histDriverPercent
FROM
@SnapshotContract AS SnapshotContract
INNER JOIN
tHistorySummary WITH (NOLOCK) ON SnapshotContract.ContractID = tHistorySummary.ContractID
WHERE
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
(@RepRowSeed+7) AS RepRowID,
SnapshotContract.ContractID,
Period,
FXDriverPercent
FROM
@SnapshotContract AS SnapshotContract
INNER JOIN
tHistorySummary WITH (NOLOCK) ON SnapshotContract.ContractID = tHistorySummary.ContractID
WHERE
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
(@RepRowSeed+8) AS RepRowID,
SnapshotContract.ContractID,
Period,
FODriverPercent
FROM
@SnapshotContract AS SnapshotContract
INNER JOIN
tHistorySummary WITH (NOLOCK) ON SnapshotContract.ContractID = tHistorySummary.ContractID
WHERE
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
(@RepRowSeed+9) AS RepRowID,
SnapshotContract.ContractID,
Period,
FRDriverPercent
FROM
@SnapshotContract as SnapshotContract
INNER JOIN
tHistorySummary WITH (NOLOCK) ON SnapshotContract.ContractID = tHistorySummary.ContractID
WHERE
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
) AS histDriverPercent ON RepRow.RepRowID = histDriverPercent.RepRowID AND HistSnapData.ContractID = histDriverPercent.ContractID
AND HistSnapData.Period = histDriverPercent.Period
) AS ContractSpend
GROUP BY
ContractID,
RepRowID
--SSR-80698 Changes End
--SSR 82281 Changes Begin
--Curr Annual for BackDated Update.
IF @SnapKind='C'
BEGIN
INSERT INTO @RepData
SELECT
RepRowID,
ContractID,
NULL AS pDec,
NULL AS pAnn,
SUM((TotalMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-1)))) AS Jan,
SUM((TotalMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-2)))) AS Feb,
SUM((TotalMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-3)))) AS Mar,
SUM((TotalMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-4)))) AS Apr,
SUM((TotalMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-5)))) AS May,
SUM((TotalMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-6)))) AS Jun,
SUM((TotalMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-7)))) AS Jul,
SUM((TotalMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-8)))) AS Aug,
SUM((TotalMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-9)))) AS Sep,
SUM((TotalMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-10)))) AS Oct,
SUM((TotalMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-11)))) AS Nov,
SUM((TotalMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-12)))) AS [Dec],
@ExchangeRateDate--SSR 80705 Change - Insert Value for ExchangeRateDate
FROM
(
SELECT
RepRow.RepRowID,
ContractSpend.ContractID,
ContractSpend.Period,
ContractSpend.TotalMerSpend,
ContractSpend.BackDatedUpdate
FROM
(
SELECT (@RepRowSeed+10) AS RepRowID
) AS RepRow
CROSS JOIN
(
SELECT
SnapshotContract.ContractID,
ContractPeriod.Period,
tHistorySummary.TotalMERSpend AS histMERSpend,
tHistorySummary.FCDriverPercent AS histFC,
tHistorySummary.FXDriverPercent AS histFX,
tHistorySummary.FODriverPercent AS histFO,
tHistorySummary.FRDriverPercent AS histFR,
tHistorySummary.ExchangeRate AS histExchangeRate,
tHIstorySummary.TotalROYMERSpend AS histTotalROYMERSpend,
tHistorySummary.TotalMERSpend/((100+ tHistorySummary.FCDriverPercent+tHistorySummary.FXDriverPercent+
tHistorySummary.FODriverPercent + tHistorySummary.FRDriverPercent)/100)* ((tHistorySummary.FCDriverPercent+tHistorySummary.FXDriverPercent+
tHistorySummary.FODriverPercent + tHistorySummary.FRDriverPercent)/100) AS TotalMERSpend ,
tHistorySummary.BackDatedUpdate
FROM
@SnapshotContract AS SnapshotContract
CROSS JOIN
(
SELECT @CurrJanPeriod AS Period
UNION ALL
SELECT DATEADD(MONTH, 1, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 2, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 3, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 4, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 5, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 6, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 7, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 8, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 9, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 10, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 11, @CurrJanPeriod)
) AS ContractPeriod
LEFT OUTER JOIN
tHistorySummary WITH (NOLOCK) ON SnapshotContract.ContractID = tHistorySummary.ContractID
AND ContractPeriod.Period = tHistorySummary.Period
) AS ContractSpend WHERE ContractSpend.BackDatedUpdate = 1
) AS TotalHistMerSpendBackDated
GROUP BY
ContractID,
RepRowID
--Curr Fiscal for BackDated MPCA
INSERT INTO @RepData
SELECT
RepRowID,
ContractID,
NULL AS pDec,
NULL AS pAnn,
SUM((TotalROYMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-1)))) AS Jan,
SUM((TotalROYMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-2)))) AS Feb,
SUM((TotalROYMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-3)))) AS Mar,
SUM((TotalROYMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-4)))) AS Apr,
SUM((TotalROYMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-5)))) AS May,
SUM((TotalROYMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-6)))) AS Jun,
SUM((TotalROYMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-7)))) AS Jul,
SUM((TotalROYMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-8)))) AS Aug,
SUM((TotalROYMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-9)))) AS Sep,
SUM((TotalROYMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-10)))) AS Oct,
SUM((TotalROYMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-11)))) AS Nov,
SUM((TotalROYMERSpend)*(1-ABS(SIGN(DATEPART(mm,Period)-12)))) AS [Dec],
@ExchangeRateDate--SSR 80705 Change - Insert Value for ExchangeRateDate
FROM
(
SELECT
RepRow.RepRowID,
ContractSpend.ContractID,
ContractSpend.Period,
ContractSpend.TotalROYMERSpend,
ContractSpend.BackDatedUpdate
FROM
(
SELECT (@RepRowSeed+11) AS RepRowID
) AS RepRow
CROSS JOIN
(
SELECT
SnapshotContract.ContractID,
ContractPeriod.Period,
tHistorySummary.TotalMERSpend AS histMERSpend,
tHistorySummary.FCDriverPercent AS histFC,
tHistorySummary.FXDriverPercent AS histFX,
tHistorySummary.FODriverPercent AS histFO,
tHistorySummary.FRDriverPercent AS histFR,
tHistorySummary.ExchangeRate AS histExchangeRate,
tHIstorySummary.TotalROYMERSpend AS histTotalROYMERSpend,
tHistorySummary.TotalROYMERSpend/((100+ tHistorySummary.FCDriverPercent+tHistorySummary.FXDriverPercent+
tHistorySummary.FODriverPercent + tHistorySummary.FRDriverPercent)/100)* ((tHistorySummary.FCDriverPercent+tHistorySummary.FXDriverPercent+
tHistorySummary.FODriverPercent + tHistorySummary.FRDriverPercent)/100) AS TotalROYMERSpend ,
tHistorySummary.BackDatedUpdate
FROM
@SnapshotContract AS SnapshotContract
CROSS JOIN
(
SELECT @CurrJanPeriod AS Period
UNION ALL
SELECT DATEADD(MONTH, 1, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 2, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 3, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 4, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 5, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 6, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 7, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 8, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 9, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 10, @CurrJanPeriod)
UNION ALL
SELECT DATEADD(MONTH, 11, @CurrJanPeriod)
) AS ContractPeriod
LEFT OUTER JOIN
tHistorySummary WITH (NOLOCK) ON SnapshotContract.ContractID = tHistorySummary.ContractID
AND ContractPeriod.Period = tHistorySummary.Period
) AS ContractSpend WHERE ContractSpend.BackDatedUpdate = 1
) AS TotalHistRoyMerSpendBackDated
GROUP BY
ContractID,
RepRowID
END
RETURN
END
November 17, 2011 at 2:32 pm
AGH, my eyes! ... and formatting is eaten.
Can you edit your posts and wrap your code with the 'ifCode' shortcuts you see on the left? The one you want is code="sql".
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 17, 2011 at 4:01 pm
Evil Kraig F (11/17/2011)
AGH, my eyes! ... and formatting is eaten.Can you edit your posts and wrap your code with the 'ifCode' shortcuts you see on the left? The one you want is code="sql".
What? You don't have 2 hours to format this??? You're losing your way!
CREATE PROCEDURE [dbo].[usp_rpt_InflMon]
@OrganizationID Int = NULL
, @PlantID Int = NULL
, @CommodityManagerGroupID Int = NULL
, @CommodityManagerSubGroupID Int = NULL
, @CommLdrUserID Int = NULL
, @CommodityManagerUserID Int = NULL
, @SupplierCode Varchar(50) = NULL
, @SupplierName Varchar(50) = NULL
, @ContractNumber Varchar(50) = NULL
, @BaseYear Int
, @CurrSnapshotID Int
, @TrackedForInflation BIT = NULL
, --SSR 80863 Change
@MaterialCostAgreement BIT = NULL --SSR 81055 Change
AS
SET NOCOUNT ON
DECLARE @AOPSnapshotID Int
DECLARE @JanPeriod Datetime
DECLARE @DecPeriod Datetime
DECLARE @CheckSupplierCount Int
IF NOT @SupplierCode IS NULL
SET @SupplierCode = '%' + @SupplierCode + '%'
IF NOT @SupplierName IS NULL
SET @SupplierName = '%' + @SupplierName + '%'
IF NOT @ContractNumber IS NULL
SET @ContractNumber = '%' + @ContractNumber + '%'
-- SSR 81911 changes Begin
SET @CheckSupplierCount = 0
IF NOT @SupplierCode IS NULL
SET @CheckSupplierCount = (
SELECT
COUNT(SupplierCode)
FROM
tSupplier WITH ( NOLOCK )
WHERE
suppliercode LIKE @SupplierCode
)
-- SSR 81911 changes End
SELECT
@AOPSnapshotID = SnapshotID
FROM
tSnapshot WITH ( NOLOCK )
WHERE
tSnapshot.Official = 1
AND tSnapshot.SnapshotTypeID = 1
AND tSnapshot.SnapshotYear = @BaseYear
SELECT
@JanPeriod = Convert(Datetime , Convert(Varchar(4) , @BaseYear)
+ '-01-01')
, @DecPeriod = Convert(Datetime , Convert(Varchar(4) , @BaseYear)
+ '-12-01')
IF @CheckSupplierCount = 1 -- SSR 81911 Change
BEGIN -- SSR 81911 Change
SELECT
RepRowDesc
, ISNULL(pDec , 0) AS pDec
, ISNULL(pAnn , 0) AS pAnn
, ISNULL(Jan , 0) AS Jan
, ISNULL(Feb , 0) AS Feb
, ISNULL(Mar , 0) AS Mar
, ISNULL(Apr , 0) AS Apr
, ISNULL(May , 0) AS May
, ISNULL(Jun , 0) AS Jun
, ISNULL(Jul , 0) AS Jul
, ISNULL(Aug , 0) AS Aug
, ISNULL(Sep , 0) AS Sep
, ISNULL(Oct , 0) AS Oct
, ISNULL(Nov , 0) AS Nov
, ISNULL([Dec] , 0) AS [Dec]
, ISNULL(ExchangeRateDate , '') AS ExchangeRateDate
, --SSR 80705 Change - Add a column for ExchangeRateDate
ISNULL(SupplierName , '') AS SupplierName ---SSR 81911 Change - Add a column to get Supplier Name for given Supplier Code.
FROM
(
--SSR - 80698 Changes Begin
SELECT
1 AS RepRowID
, 'AOP Volume' AS RepRowDesc
UNION ALL
SELECT
2
, 'AOP Spend'
UNION ALL
SELECT
3
, 'AOP Annual FC'
UNION ALL
SELECT
4
, 'AOP Annual FX'
UNION ALL
SELECT
5
, 'AOP Annual FO'
UNION ALL
SELECT
6
, 'AOP Annual FR'
UNION ALL
SELECT
7
, 'AOP Fiscal FC'
UNION ALL
SELECT
8
, 'AOP Fiscal FX'
UNION ALL
SELECT
9
, 'AOP Fiscal FO'
UNION ALL
SELECT
10
, 'AOP Fiscal FR'
UNION ALL
SELECT
11
, 'Curr Volume'
UNION ALL
SELECT
12
, 'Curr Spend'
UNION ALL
SELECT
13
, 'Curr Annual FC'
UNION ALL
SELECT
14
, 'Curr Annual FX'
UNION ALL
SELECT
15
, 'Curr Annual FO'
UNION ALL
SELECT
16
, 'Curr Annual FR'
UNION ALL
SELECT
21
, 'Backdated MPCAs Curr Annual'
UNION ALL
SELECT
17
, 'Curr Fiscal FC'
UNION ALL
SELECT
18
, 'Curr Fiscal FX'
UNION ALL
SELECT
19
, 'Curr Fiscal FO'
UNION ALL
SELECT
20
, 'Curr Fiscal FR'
--SSR - 82281 Changes Begin
UNION ALL
SELECT
22
, 'Backdated MPCAs Curr Fiscal'
--SSR - 82281 Changes End
) AS RepRow --SSR - 80698 Changes End
LEFT OUTER JOIN (
SELECT
RepRowID
, SUM(pDec) as pDec
, SUM(pAnn) as pAnn
, SUM(Jan) as Jan
, SUM(Feb) as Feb
, SUM(Mar) as Mar
, SUM(Apr) as Apr
, SUM(May) as May
, SUM(Jun) as Jun
, SUM(Jul) as Jul
, SUM(Aug) as Aug
, SUM(Sep) as Sep
, SUM(Oct) as Oct
, SUM(Nov) as Nov
, SUM([Dec]) as [Dec]
, ExchangeRateDate
, --SSR 80705 Change
SupplierName ---SSR 81911 Change - Add a column to get Supplier Name for given Supplier Code.
FROM
(
SELECT DISTINCT
ContractID
FROM
--SSR 82210 -Changes Begin
-- tSnapshotSummary WITH (NOLOCK)
-- WHERE
-- SnapshotID in (@AOPSnapshotID, @CurrSnapshotID) AND
-- Period BETWEEN @JanPeriod AND @DecPeriod
(
(SELECT DISTINCT
ContractID
FROM
tSnapshotsummary
WHERE
SnapshotID in ( @AOPSnapshotID ,
@CurrSnapshotID ))
UNION
(
SELECT DISTINCT
ContractID
FROM
tHistorysummary
WHERE
Period >= Convert(Datetime , Convert(Varchar(4) , ( @BaseYear - 1 ))
+ '-12-01')
)
) AS Contracts
--SSR 82210 -Changes End
) AS SnapshotContract
INNER JOIN tContract WITH ( NOLOCK )
ON SnapshotContract.ContractID = tContract.ContractID
INNER JOIN tSupplier WITH ( NOLOCK )
ON tSupplier.SupplierID = tContract.SupplierID
INNER JOIN tPlant WITH ( NOLOCK )
ON tContract.PlantID = tPlant.PlantID
LEFT OUTER JOIN tSupplierOrganization WITH ( NOLOCK )
on tSupplier.SupplierID = tSupplierOrganization.SupplierID
AND tPlant.OrganizationID = tSupplierOrganization.OrganizationID
INNER JOIN tOrganization WITH ( NOLOCK )
ON tSupplierOrganization.OrganizationID = tOrganization.OrganizationID
LEFT OUTER JOIN tCommodityManagerSubGroup
WITH ( NOLOCK )
ON tSupplier.CommodityManagerSubGroupID = tCommodityManagerSubGroup.CommodityManagerSubGroupID
LEFT OUTER JOIN tCommodityManagerGroup
WITH ( NOLOCK )
ON tCommodityManagerSubGroup.CommodityManagerGroupID = tCommodityManagerGroup.CommodityManagerGroupID
LEFT OUTER JOIN (
SELECT
CommodityManagerSubGroupID
, UserID AS CommLdrUserID
FROM
tCommoditySubGroupTeamMember
WITH ( NOLOCK )
WHERE
( CommodityTeamMemberTypeID = 2 )
) AS CommLdr
ON tSupplier.CommodityManagerSubGroupID = CommLdr.CommodityManagerSubGroupID
LEFT OUTER JOIN (
SELECT
[CommodityManagerSubGroupID]
, [tCommodityTeamMemberType].OrganizationID
, tCommoditySubGroupTeamMember.[UserID] as SubRegionalBuyerUserID
FROM
[tCommoditySubGroupTeamMember]
WITH ( NOLOCK )
INNER JOIN [tCommodityTeamMemberType]
ON [tCommoditySubGroupTeamMember].CommodityTeamMemberTypeID = tCommodityTeamMemberType.CommodityTeamMemberTypeID
WHERE
[tCommoditySubGroupTeamMember].[CommodityTeamMemberTypeID] in (
4 , 5 )
) AS SubRegionalBuyer
ON tCommodityManagerSubGroup.CommodityManagerSubGroupID = SubRegionalBuyer.CommodityManagerSubGroupID
AND tSupplierOrganization.OrganizationID = SubRegionalBuyer.OrganizationID
LEFT OUTER JOIN (
SELECT
RepRowID
, ContractID
, pDec
, pAnn
, Jan
, Feb
, Mar
, Apr
, May
, Jun
, Jul
, Aug
, Sep
, Oct
, Nov
, [Dec]
, IsNull(ExchangeRateDate , '') as ExchangeRateDate --SSR 80705 Change
FROM
dbo.fnInflationMonitorData(@AOPSnapshotID , 'A' , @BaseYear)
UNION ALL
SELECT
RepRowID
, ContractID
, pDec
, pAnn
, Jan
, Feb
, Mar
, Apr
, May
, Jun
, Jul
, Aug
, Sep
, Oct
, Nov
, [Dec]
, IsNull(ExchangeRateDate , '') as ExchangeRateDate --SSR 80705 Change
FROM
dbo.fnInflationMonitorData(@CurrSnapshotID , 'C' , @BaseYear)
) AS RptData
ON SnapshotContract.ContractID = RptData.ContractID
WHERE
(
tSupplierOrganization.OrganizationID = @OrganizationID
or @OrganizationID IS NULL
)
AND (
tContract.PlantID = @PlantID
OR @PlantID IS NULL
)
AND (
tCommodityManagerSubGroup.CommodityManagerGroupID = @CommodityManagerGroupID
OR @CommodityManagerGroupID IS NULL
)
AND (
tSupplier.CommodityManagerSubGroupID = @CommodityManagerSubGroupID
OR @CommodityManagerSubGroupID IS NULL
)
AND (
CommLdr.CommLdrUserID = @CommLdrUserID
OR @CommLdrUserID IS NULL
)
AND (
SubRegionalBuyer.SubRegionalBuyerUserID = @CommodityManagerUserID
OR @CommodityManagerUserID IS NULL
)
AND (
tSupplier.SupplierCode Like @SupplierCode
OR @SupplierCode IS NULL
)
AND (
tSupplier.SupplierName Like @SupplierName
OR @SupplierName IS NULL
)
AND (
tContract.ContractNumber Like @ContractNumber
OR @ContractNumber IS NULL
)
AND (
tSupplier.InflationMonitorTracked = @TrackedForInflation
OR @TrackedForInflation IS NULL
) --SSR 80863 Change - Filter the Records using InflactionMonitorTracked
AND (
tSupplier.MaterialCostAgreeementExist = @MaterialCostAgreement
OR @MaterialCostAgreement IS NULL
) --SSR 81055 Change
AND tsupplier.IMTSupplier <> 1 --SSR 82026 Change
GROUP BY
RepRowID
, ExchangeRateDate
, SupplierName
) AS RepRowData
ON RepRow.RepRowID = RepRowData.RepRowID
-- ORDER BY
-- RepRow.RepRowID
-- SSR 81911 Changes Begin
END
ELSE
BEGIN
-- SSR 81911 Changes End
SELECT
RepRowDesc
, ISNULL(pDec , 0) AS pDec
, ISNULL(pAnn , 0) AS pAnn
, ISNULL(Jan , 0) AS Jan
, ISNULL(Feb , 0) AS Feb
, ISNULL(Mar , 0) AS Mar
, ISNULL(Apr , 0) AS Apr
, ISNULL(May , 0) AS May
, ISNULL(Jun , 0) AS Jun
, ISNULL(Jul , 0) AS Jul
, ISNULL(Aug , 0) AS Aug
, ISNULL(Sep , 0) AS Sep
, ISNULL(Oct , 0) AS Oct
, ISNULL(Nov , 0) AS Nov
, ISNULL([Dec] , 0) AS [Dec]
, ISNULL(ExchangeRateDate , '') AS ExchangeRateDate
, --SSR 80705 Change - Add a column for ExchangeRateDate
'' AS SupplierName ---SSR 81911 Change
FROM
(
--SSR - 80698 Changes Begin
SELECT
1 AS RepRowID
, 'AOP Volume' AS RepRowDesc
UNION ALL
SELECT
2
, 'AOP Spend'
UNION ALL
SELECT
3
, 'AOP Annual FC'
UNION ALL
SELECT
4
, 'AOP Annual FX'
UNION ALL
SELECT
5
, 'AOP Annual FO'
UNION ALL
SELECT
6
, 'AOP Annual FR'
UNION ALL
SELECT
7
, 'AOP Fiscal FC'
UNION ALL
SELECT
8
, 'AOP Fiscal FX'
UNION ALL
SELECT
9
, 'AOP Fiscal FO'
UNION ALL
SELECT
10
, 'AOP Fiscal FR'
UNION ALL
SELECT
11
, 'Curr Volume'
UNION ALL
SELECT
12
, 'Curr Spend'
UNION ALL
SELECT
13
, 'Curr Annual FC'
UNION ALL
SELECT
14
, 'Curr Annual FX'
UNION ALL
SELECT
15
, 'Curr Annual FO'
UNION ALL
SELECT
16
, 'Curr Annual FR'
UNION ALL
SELECT
21
, 'Backdated MPCAs Curr Annual'
UNION ALL
SELECT
17
, 'Curr Fiscal FC'
UNION ALL
SELECT
18
, 'Curr Fiscal FX'
UNION ALL
SELECT
19
, 'Curr Fiscal FO'
UNION ALL
SELECT
20
, 'Curr Fiscal FR'
--SSR - 82281 Changes Begin
UNION ALL
SELECT
22
, 'Backdated MPCAs Curr Fiscal'
--SSR - 82281 Changes End
) AS RepRow --SSR - 80698 Changes End
LEFT OUTER JOIN (
SELECT
RepRowID
, SUM(pDec) as pDec
, SUM(pAnn) as pAnn
, SUM(Jan) as Jan
, SUM(Feb) as Feb
, SUM(Mar) as Mar
, SUM(Apr) as Apr
, SUM(May) as May
, SUM(Jun) as Jun
, SUM(Jul) as Jul
, SUM(Aug) as Aug
, SUM(Sep) as Sep
, SUM(Oct) as Oct
, SUM(Nov) as Nov
, SUM([Dec]) as [Dec]
, ExchangeRateDate --SSR 80705 Change
FROM
(
SELECT DISTINCT
ContractID
FROM
--SSR 82210 -Changes Begin
-- tSnapshotSummary WITH (NOLOCK)
-- WHERE
-- SnapshotID in (@AOPSnapshotID, @CurrSnapshotID) AND
-- Period BETWEEN @JanPeriod AND @DecPeriod
(
(SELECT DISTINCT
ContractID
FROM
tSnapshotsummary
WHERE
SnapshotID in ( @AOPSnapshotID ,
@CurrSnapshotID ))
UNION
(
SELECT DISTINCT
ContractID
FROM
tHistorysummary
WHERE
Period >= Convert(Datetime , Convert(Varchar(4) , ( @BaseYear - 1 ))
+ '-12-01')
)
) AS Contracts
--SSR 82210 -Changes End
) AS SnapshotContract
INNER JOIN tContract WITH ( NOLOCK )
ON SnapshotContract.ContractID = tContract.ContractID
INNER JOIN tSupplier WITH ( NOLOCK )
ON tSupplier.SupplierID = tContract.SupplierID
INNER JOIN tPlant WITH ( NOLOCK )
ON tContract.PlantID = tPlant.PlantID
LEFT OUTER JOIN tSupplierOrganization WITH ( NOLOCK )
on tSupplier.SupplierID = tSupplierOrganization.SupplierID
AND tPlant.OrganizationID = tSupplierOrganization.OrganizationID
INNER JOIN tOrganization WITH ( NOLOCK )
ON tSupplierOrganization.OrganizationID = tOrganization.OrganizationID
LEFT OUTER JOIN tCommodityManagerSubGroup
WITH ( NOLOCK )
ON tSupplier.CommodityManagerSubGroupID = tCommodityManagerSubGroup.CommodityManagerSubGroupID
LEFT OUTER JOIN tCommodityManagerGroup
WITH ( NOLOCK )
ON tCommodityManagerSubGroup.CommodityManagerGroupID = tCommodityManagerGroup.CommodityManagerGroupID
LEFT OUTER JOIN (
SELECT
CommodityManagerSubGroupID
, UserID AS CommLdrUserID
FROM
tCommoditySubGroupTeamMember
WITH ( NOLOCK )
WHERE
( CommodityTeamMemberTypeID = 2 )
) AS CommLdr
ON tSupplier.CommodityManagerSubGroupID = CommLdr.CommodityManagerSubGroupID
LEFT OUTER JOIN (
SELECT
[CommodityManagerSubGroupID]
, [tCommodityTeamMemberType].OrganizationID
, tCommoditySubGroupTeamMember.[UserID] as SubRegionalBuyerUserID
FROM
[tCommoditySubGroupTeamMember]
WITH ( NOLOCK )
INNER JOIN [tCommodityTeamMemberType]
ON [tCommoditySubGroupTeamMember].CommodityTeamMemberTypeID = tCommodityTeamMemberType.CommodityTeamMemberTypeID
WHERE
[tCommoditySubGroupTeamMember].[CommodityTeamMemberTypeID] in (
4 , 5 )
) AS SubRegionalBuyer
ON tCommodityManagerSubGroup.CommodityManagerSubGroupID = SubRegionalBuyer.CommodityManagerSubGroupID
AND tSupplierOrganization.OrganizationID = SubRegionalBuyer.OrganizationID
LEFT OUTER JOIN (
SELECT
RepRowID
, ContractID
, pDec
, pAnn
, Jan
, Feb
, Mar
, Apr
, May
, Jun
, Jul
, Aug
, Sep
, Oct
, Nov
, [Dec]
, IsNull(ExchangeRateDate , '') as ExchangeRateDate --SSR 80705 Change
FROM
dbo.fnInflationMonitorData(@AOPSnapshotID , 'A' , @BaseYear)
UNION ALL
SELECT
RepRowID
, ContractID
, pDec
, pAnn
, Jan
, Feb
, Mar
, Apr
, May
, Jun
, Jul
, Aug
, Sep
, Oct
, Nov
, [Dec]
, IsNull(ExchangeRateDate , '') as ExchangeRateDate --SSR 80705 Change
FROM
dbo.fnInflationMonitorData(@CurrSnapshotID , 'C' , @BaseYear)
) AS RptData
ON SnapshotContract.ContractID = RptData.ContractID
WHERE
(
tSupplierOrganization.OrganizationID = @OrganizationID
or @OrganizationID IS NULL
)
AND (
tContract.PlantID = @PlantID
OR @PlantID IS NULL
)
AND (
tCommodityManagerSubGroup.CommodityManagerGroupID = @CommodityManagerGroupID
OR @CommodityManagerGroupID IS NULL
)
AND (
tSupplier.CommodityManagerSubGroupID = @CommodityManagerSubGroupID
OR @CommodityManagerSubGroupID IS NULL
)
AND (
CommLdr.CommLdrUserID = @CommLdrUserID
OR @CommLdrUserID IS NULL
)
AND (
SubRegionalBuyer.SubRegionalBuyerUserID = @CommodityManagerUserID
OR @CommodityManagerUserID IS NULL
)
AND (
tSupplier.SupplierCode Like @SupplierCode
OR @SupplierCode IS NULL
)
AND (
tSupplier.SupplierName Like @SupplierName
OR @SupplierName IS NULL
)
AND (
tContract.ContractNumber Like @ContractNumber
OR @ContractNumber IS NULL
)
AND (
tSupplier.InflationMonitorTracked = @TrackedForInflation
OR @TrackedForInflation IS NULL
) --SSR 80863 Change - Filter the Records using InflactionMonitorTracked
AND (
tSupplier.MaterialCostAgreeementExist = @MaterialCostAgreement
OR @MaterialCostAgreement IS NULL
) --SSR 81055 Change
AND tsupplier.IMTSupplier <> 1 --SSR 82026 Change
GROUP BY
RepRowID
, ExchangeRateDate
) AS RepRowData
ON RepRow.RepRowID = RepRowData.RepRowID
--ORDER BY
-- RepRow.RepRowID
END --SSR 81911 Change.
And
CREATE FUNCTION [dbo].[fnInflationMonitorData]
(
@SnapshotID int
, @SnapKind char(1)
, @BaseYear int
)
RETURNS @RepData TABLE
(
RepRowID int
, ContractID int
, pDec Decimal(20 , 5)
, pAnn Decimal(20 , 5)
, Jan Decimal(20 , 5)
, Feb Decimal(20 , 5)
, Mar Decimal(20 , 5)
, Apr Decimal(20 , 5)
, May Decimal(20 , 5)
, Jun Decimal(20 , 5)
, Jul Decimal(20 , 5)
, Aug Decimal(20 , 5)
, Sep Decimal(20 , 5)
, Oct Decimal(20 , 5)
, Nov Decimal(20 , 5)
, [Dec] Decimal(20 , 5)
, ExchangeRateDate Datetime--SSR 80705 Change - Insert a new column for ExchangeRateDate
)
AS BEGIN
DECLARE @RepRowSeed int
DECLARE @PriorDecPeriod Datetime
DECLARE @CurrJanPeriod Datetime
DECLARE @CurrDecPeriod Datetime
--SSR 80705 -Changes Begin
--Get value for ExchangeRateDate
Declare @ExchangeRateDate Datetime
SELECT
@ExchangeRateDate = EffectiveRateDate
FROM
tSnapshot WITH ( NOLOCK )
WHERE
SnapshotID = @SnapshotID
--SSR 80705 Changes End
DECLARE @SnapshotContract as Table
(
ContractID int NOT NULL
, PRIMARY KEY ( ContractID )
)
SELECT
@PriorDecPeriod = Convert(Datetime , Convert(Varchar(4) , ( @BaseYear - 1 ))
+ '-12-01')
, @CurrJanPeriod = Convert(Datetime , Convert(Varchar(4) , @BaseYear)
+ '-01-01')
, @CurrDecPeriod = Convert(Datetime , Convert(Varchar(4) , @BaseYear)
+ '-12-01')
IF @SnapKind = 'A'
SET @RepRowSeed = 1
IF @SnapKind = 'C'
SET @RepRowSeed = 11 --SSR-80698 Change
INSERT INTO
@SnapshotContract
SELECT DISTINCT
ContractID
FROM
--SSR 82210 -Changes Begin
--
--
--
-- tSnapshotSummary WITH (NOLOCK)
--
--
--
--WHERE
--
--
--
-- SnapshotID = @SnapshotID AND
--
--
--
-- Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
(
(SELECT DISTINCT
ContractID
FROM
tSnapshotsummary
WHERE
SnapshotID = @SnapshotID)
UNION
(
SELECT DISTINCT
ContractID
FROM
tHistorysummary
WHERE
Period >= @PriorDecPeriod
)
) AS Contracts
DECLARE @snapflag AS INT
IF EXISTS ( SELECT
1
FROM
tSnapshotsummary
WHERE
SnapshotID = @SnapshotID
AND Period = @PriorDecPeriod )
BEGIN
SET @snapflag = 1
END
ELSE
BEGIN
SET @snapflag = 0
END
--SSR 82210 -Changes End
INSERT INTO
@RepData
SELECT
@RepRowSeed
, SnapshotContract.ContractID
, pDec
, pAnn
, Jan
, Feb
, Mar
, Apr
, May
, Jun
, Jul
, Aug
, Sep
, Oct
, Nov
, [Dec]
, @ExchangeRateDate--SSR 80705 Change - Insert Value for ExchangeRateDate
FROM
@SnapshotContract as SnapshotContract
LEFT OUTER JOIN (
SELECT
SnapshotContract.[ContractID]
,
--SSR 82025 -Changes Begin
-- Change the logic for AOP section, If AOP records exists for prior Dec period then use it otherwise use history summary data.
-- But for Current section should be the same.
--CASE WHEN NOT tHistorySummary.MER IS NULL THEN tHistorySummary.MER ELSE tSnapshotSummary.TotalMER END as pDec,
--CASE WHEN NOT tHistorySummary.MER IS NULL THEN tHistorySummary.EAR ELSE tSnapshotSummary.TotalEAR END as pAnn
--SSR 82210 -Changes Begin
CASE WHEN @SnapKind = 'A'
AND @RepRowSeed = 1
AND @snapflag = 1
THEN
-- CASE WHEN NOT tSnapshotSummary.TotalMER IS NULL THEN tSnapshotSummary.TotalMER
--
-- ELSE tHistorySummary.MER
--
-- END
tSnapshotSummary.TotalMER
ELSE
-- CASE WHEN NOT tHistorySummary.MER IS NULL THEN tHistorySummary.MER
--
-- ELSE tSnapshotSummary.TotalMER
--
-- END
tHistorySummary.MER
END AS pDec
, CASE WHEN @SnapKind = 'A'
AND @RepRowSeed = 1
AND @snapflag = 1
THEN
-- CASE WHEN NOT tSnapshotSummary.TotalMER IS NULL THEN tSnapshotSummary.TotalEAR
--
-- ELSE tHistorySummary.EAR
--
-- END
tSnapshotSummary.TotalEAR
ELSE
-- CASE WHEN NOT tHistorySummary.MER IS NULL THEN tHistorySummary.EAR
--
-- ELSE tSnapshotSummary.TotalEAR
--
-- END
tHistorySummary.EAR
END AS pAnn
--SSR 82210 -Changes End
--SSR 82025 -Changes End
FROM
@SnapshotContract as SnapshotContract
LEFT OUTER JOIN tHistorySummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tHistorySummary.ContractID
AND tHistorySummary.Period = @PriorDecPeriod
LEFT OUTER JOIN tSnapshotSummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tSnapshotSummary.ContractID
AND tSnapshotSummary.SnapshotID = @SnapshotID
AND tSnapshotSummary.Period = @PriorDecPeriod
) AS VolLastYear
ON SnapshotContract.ContractID = VolLastYear.ContractID
LEFT OUTER JOIN (
SELECT
ContractID
, SUM(( ContractMER ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 1)) )) AS Jan
, SUM(( ContractMER ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 2)) )) AS Feb
, SUM(( ContractMER ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 3)) )) AS Mar
, SUM(( ContractMER ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 4)) )) AS Apr
, SUM(( ContractMER ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 5)) )) AS May
, SUM(( ContractMER ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 6)) )) AS Jun
, SUM(( ContractMER ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 7)) )) AS Jul
, SUM(( ContractMER ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 8)) )) AS Aug
, SUM(( ContractMER ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 9)) )) AS Sep
, SUM(( ContractMER ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 10)) )) AS Oct
, SUM(( ContractMER ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 11)) )) AS Nov
, SUM(( ContractMER ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 12)) )) AS [Dec]
FROM
(
SELECT
SnapshotContract.ContractID
, ContractPeriod.Period
, CASE WHEN NOT tSnapshotSummary.TotalMER IS NULL
OR @SnapKind = 'A'
THEN tSnapshotSummary.TotalMER
ELSE tHistorySummary.MER
END AS ContractMER
FROM
@SnapshotContract as SnapshotContract
CROSS JOIN (
SELECT
@CurrJanPeriod AS Period
UNION ALL
SELECT
DateAdd(month , 1 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 2 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 3 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 4 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 5 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 6 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 7 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 8 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 9 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 10 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 11 ,
@CurrJanPeriod)
) AS ContractPeriod
LEFT OUTER JOIN tHistorySummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tHistorySummary.ContractID
AND ContractPeriod.Period = tHistorySummary.Period
LEFT OUTER JOIN tSnapshotSummary WITH ( NOLOCK )
ON tSnapshotSummary.SnapshotID = @SnapshotID
AND SnapshotContract.ContractID = tSnapshotSummary.ContractID
AND ContractPeriod.Period = tSnapshotSummary.Period
) AS VolThisYearByPeriod
GROUP BY
ContractID
) AS VolThisYear
ON SnapshotContract.ContractID = VolThisYear.ContractID
INSERT INTO
@RepData
SELECT
( @RepRowSeed + 1 )
, SnapshotContract.ContractID
, pDec
, pAnn
, Jan
, Feb
, Mar
, Apr
, May
, Jun
, Jul
, Aug
, Sep
, Oct
, Nov
, [Dec]
, @ExchangeRateDate--SSR 80705 Change - Insert Value for ExchangeRateDate
FROM
@SnapshotContract as SnapshotContract
LEFT OUTER JOIN (
SELECT
SnapshotContract.[ContractID]
,
--SSR 82025 -Changes Begin
-- Change the logic for AOP section, If AOP records exists for prior Dec period then use it otherwise use history summary data.
-- But for Current section should be the same.
--CASE WHEN NOT tHistorySummary.TotalMERSpend IS NULL THEN Case IsNull(tHistorySummary.ExchangeRate,0) when 0 then 0 else (tHistorySummary.TotalMERSpend/tHistorySummary.ExchangeRate) End ELSE Case IsNull(tSnapshotSummary.ExchangeRate,0) when 0 then 0 else (tSnapshotSummary.TotalMERSpend/tSnapshotSummary.ExchangeRate) End END as pDec,
--CASE WHEN NOT tHistorySummary.TotalMERSpend IS NULL THEN Case IsNull(tHistorySummary.ExchangeRate,0) when 0 then 0 else (tHistorySummary.TotalEARSpend/tHistorySummary.ExchangeRate) End ELSE Case IsNull(tSnapshotSummary.ExchangeRate,0) when 0 then 0 else (tSnapshotSummary.TotalEARSpend/tSnapshotSummary.ExchangeRate) End END as pAnn
--SSR 82210 -Changes Begin
CASE WHEN @SnapKind = 'A'
AND @RepRowSeed = 1
AND @snapflag = 1 THEN
-- CASE WHEN NOT tSnapshotSummary.TotalMERSpend IS NULL THEN
CASE ISNULL(tSnapshotSummary.ExchangeRate ,
0)
WHEN 0 THEN 0
ELSE ( tSnapshotSummary.TotalMERSpend
/ tSnapshotSummary.ExchangeRate )
END
-- ELSE
--
-- CASE ISNULL(tHistorySummary.ExchangeRate,0) WHEN 0 THEN 0
--
-- ELSE (tHistorySummary.TotalMERSpend/tHistorySummary.ExchangeRate)
--
-- END
--
-- END
ELSE
-- CASE WHEN NOT tHistorySummary.TotalMERSpend IS NULL THEN
CASE ISNULL(tHistorySummary.ExchangeRate ,
0)
WHEN 0 THEN 0
ELSE ( tHistorySummary.TotalMERSpend
/ tHistorySummary.ExchangeRate )
END
-- ELSE
--
-- CASE ISNULL(tSnapshotSummary.ExchangeRate,0) WHEN 0 THEN 0
--
-- ELSE (tSnapshotSummary.TotalMERSpend/tSnapshotSummary.ExchangeRate)
--
-- END
--
-- END
END AS pDec
, CASE WHEN @SnapKind = 'A'
AND @RepRowSeed = 1
AND @snapflag = 1 THEN
-- CASE WHEN NOT tSnapshotSummary.TotalMERSpend IS NULL THEN
CASE ISNULL(tSnapshotSummary.ExchangeRate ,
0)
WHEN 0 THEN 0
ELSE ( tSnapshotSummary.TotalEARSpend
/ tSnapshotSummary.ExchangeRate )
END
-- ELSE
--
-- CASE ISNULL(tHistorySummary.ExchangeRate,0) WHEN 0 THEN 0
--
-- ELSE (tHistorySummary.TotalEARSpend/tHistorySummary.ExchangeRate)
--
-- END
--
-- END
ELSE
-- CASE WHEN NOT tHistorySummary.TotalMERSpend IS NULL THEN
CASE ISNULL(tHistorySummary.ExchangeRate ,
0)
WHEN 0 THEN 0
ELSE ( tHistorySummary.TotalEARSpend
/ tHistorySummary.ExchangeRate )
END
-- ELSE
--
-- CASE ISNULL(tSnapshotSummary.ExchangeRate,0) WHEN 0 THEN 0
--
-- ELSE (tSnapshotSummary.TotalEARSpend/tSnapshotSummary.ExchangeRate)
--
-- END
--
-- END
END AS pAnn
--SSR 82210 -Changes End
--SSR 82025 -Changes End
FROM
@SnapshotContract as SnapshotContract
LEFT OUTER JOIN tHistorySummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tHistorySummary.ContractID
AND tHistorySummary.Period = @PriorDecPeriod
LEFT OUTER JOIN tSnapshotSummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tSnapshotSummary.ContractID
AND tSnapshotSummary.SnapshotID = @SnapshotID
AND tSnapshotSummary.Period = @PriorDecPeriod
) AS SpendLastYear
ON SnapshotContract.ContractID = SpendLastYear.ContractID
LEFT OUTER JOIN (
SELECT
ContractID
, SUM(( ContractMERSpend ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 1)) )) AS Jan
, SUM(( ContractMERSpend ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 2)) )) AS Feb
, SUM(( ContractMERSpend ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 3)) )) AS Mar
, SUM(( ContractMERSpend ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 4)) )) AS Apr
, SUM(( ContractMERSpend ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 5)) )) AS May
, SUM(( ContractMERSpend ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 6)) )) AS Jun
, SUM(( ContractMERSpend ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 7)) )) AS Jul
, SUM(( ContractMERSpend ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 8)) )) AS Aug
, SUM(( ContractMERSpend ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 9)) )) AS Sep
, SUM(( ContractMERSpend ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 10)) )) AS Oct
, SUM(( ContractMERSpend ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 11)) )) AS Nov
, SUM(( ContractMERSpend ) * ( 1
- ABS(SIGN(DatePart(mm , Period) - 12)) )) AS [Dec]
FROM
(
SELECT
SnapshotContract.ContractID
, ContractPeriod.Period
, CASE WHEN NOT tSnapshotSummary.TotalMERSpend IS NULL
OR @SnapKind = 'A'
THEN Case IsNull(tSnapshotSummary.ExchangeRate ,
0)
when 0 then 0
else ( tSnapshotSummary.TotalMERSpend
/ tSnapshotSummary.ExchangeRate )
End
ELSE Case IsNull(tHistorySummary.ExchangeRate ,
0)
when 0 then 0
else ( tHistorySummary.TotalMERSpend
/ tHistorySummary.ExchangeRate )
End
END AS ContractMERSpend
FROM
@SnapshotContract as SnapshotContract
CROSS JOIN (
SELECT
@CurrJanPeriod AS Period
UNION ALL
SELECT
DateAdd(month , 1 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 2 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 3 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 4 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 5 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 6 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 7 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 8 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 9 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 10 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 11 ,
@CurrJanPeriod)
) AS ContractPeriod
LEFT OUTER JOIN tHistorySummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tHistorySummary.ContractID
AND ContractPeriod.Period = tHistorySummary.Period
LEFT OUTER JOIN tSnapshotSummary WITH ( NOLOCK )
ON tSnapshotSummary.SnapshotID = @SnapshotID
AND SnapshotContract.ContractID = tSnapshotSummary.ContractID
AND ContractPeriod.Period = tSnapshotSummary.Period
) AS SpendThisYearByPeriod
GROUP BY
ContractID
) AS SpendThisYear
ON SnapshotContract.ContractID = SpendThisYear.ContractID
INSERT INTO
@RepData
SELECT
RepRowID
, ContractID
, NULL AS pDec
, NULL AS pAnn
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DatePart(mm , Period)
- 1)) )) AS Jan
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DatePart(mm , Period)
- 2)) )) AS Feb
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DatePart(mm , Period)
- 3)) )) AS Mar
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DatePart(mm , Period)
- 4)) )) AS Apr
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DatePart(mm , Period)
- 5)) )) AS May
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DatePart(mm , Period)
- 6)) )) AS Jun
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DatePart(mm , Period)
- 7)) )) AS Jul
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DatePart(mm , Period)
- 8)) )) AS Aug
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DatePart(mm , Period)
- 9)) )) AS Sep
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DatePart(mm , Period)
- 10)) )) AS Oct
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DatePart(mm , Period)
- 11)) )) AS Nov
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DatePart(mm , Period)
- 12)) )) AS [Dec]
, @ExchangeRateDate--SSR 80705 Change - Insert Value for ExchangeRateDate
FROM
(
SELECT
RepRow.RepRowID
, HistSnapData.ContractID
, HistSnapData.Period
, CASE WHEN NOT snapMERSpend IS NULL
OR @SnapKind = 'A'
THEN Case IsNull(snapExchangeRate , 0)
when 0 then 0
else ( ( snapMERSpend * ( snapDriverPercent / 100 ) )
/ snapExchangeRate )
End
ELSE Case IsNull(histExchangeRate , 0)
when 0 then 0
else ( ( ( histMERSpend / ( ( 100 + histFC
+ histFX + histFO
+ histFR ) / 100 ) )
* ( histDriverPercent / 100 ) )
/ histExchangeRate )
End
END AS ContractMERSpend
FROM
(
SELECT
( @RepRowSeed + 2 ) AS RepRowID
UNION ALL
SELECT
( @RepRowSeed + 3 )
UNION ALL
SELECT
( @RepRowSeed + 4 )
UNION ALL
SELECT
( @RepRowSeed + 5 )
) AS RepRow
CROSS JOIN (
SELECT
SnapshotContract.ContractID
, ContractPeriod.Period
, tempSnapshotSummary.snapMERSpend
, tempSnapshotSummary.ExchangeRate AS snapExchangeRate
, tHistorySummary.TotalMERSpend AS histMERSpend
, tHistorySummary.FCDriverPercent AS histFC
, tHistorySummary.FXDriverPercent AS histFX
, tHistorySummary.FODriverPercent AS histFO
, tHistorySummary.FRDriverPercent AS histFR
, tHistorySummary.ExchangeRate AS histExchangeRate
FROM
@SnapshotContract as SnapshotContract
CROSS JOIN (
SELECT
@CurrJanPeriod AS Period
UNION ALL
SELECT
DateAdd(month , 1 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 2 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 3 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 4 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 5 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 6 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 7 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 8 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 9 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 10 ,
@CurrJanPeriod)
UNION ALL
SELECT
DateAdd(month , 11 ,
@CurrJanPeriod)
) AS ContractPeriod
LEFT OUTER JOIN tHistorySummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tHistorySummary.ContractID
AND ContractPeriod.Period = tHistorySummary.Period
LEFT OUTER JOIN (
SELECT
ContractSnapSpend.ContractID
, ContractSnapSpend.Period
, ContractSnapSpend.snapMERSpend
, tSnapshotSummary.ExchangeRate
FROM
(
SELECT
tPart.ContractID
,
--SSR-80690 Changes Start
tsp.Period
, SUM(( PreviousPrice / CASE ISNULL(tsp.PricePerQty , 0)
WHEN 0 THEN 1
ELSE tsp.PricePerQty
END )
* ( MER / ( CASE ISNULL(tsp.ConversionFactor , 0)
WHEN 0 THEN 1
ELSE tsp.ConversionFactor
END / CASE ISNULL(tsp.ConversionFactorBase , 0)
WHEN 0 THEN 1
ELSE tsp.ConversionFactorBase
END ) )) AS snapMERSpend
FROM
tSnapshotPart tsp WITH ( NOLOCK ) --SSR-80690 Changes End
INNER JOIN tPart WITH ( NOLOCK )
ON tsp.PartID = tPart.PartID
WHERE
tsp.SnapshotID = @SnapshotID
AND tsp.Period BETWEEN @CurrJanPeriod
AND @CurrDecPeriod
--AND
--tPart.Revision = tPart.PlantRevisionLevel
GROUP BY
tPart.ContractID
, tsp.Period
) AS ContractSnapSpend
INNER JOIN tSnapshotSummary
WITH ( NOLOCK )
ON ContractSnapSpend.ContractID = tSnapshotSummary.ContractID
AND ContractSnapSpend.Period = tSnapshotSummary.Period
WHERE
tSnapshotSummary.SnapshotID = @SnapshotID
) AS tempSnapshotSummary
ON SnapshotContract.ContractID = tempSnapshotSummary.ContractID
AND ContractPeriod.Period = tempSnapshotSummary.Period
) AS HistSnapData
LEFT OUTER JOIN (
SELECT
( @RepRowSeed + 2 ) AS RepRowID
, ContractID
, Period
, FCDriverPercent AS SnapDriverPercent
FROM
tSnapshotSummary WITH ( NOLOCK )
WHERE
SnapshotID = @SnapshotID
AND Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
( @RepRowSeed + 3 ) AS RepRowID
, ContractID
, Period
, FXDriverPercent
FROM
tSnapshotSummary WITH ( NOLOCK )
WHERE
SnapshotID = @SnapshotID
AND Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
( @RepRowSeed + 4 ) AS RepRowID
, ContractID
, Period
, FODriverPercent
FROM
tSnapshotSummary WITH ( NOLOCK )
WHERE
SnapshotID = @SnapshotID
AND Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
( @RepRowSeed + 5 ) AS RepRowID
, ContractID
, Period
, FRDriverPercent
FROM
tSnapshotSummary WITH ( NOLOCK )
WHERE
SnapshotID = @SnapshotID
AND Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
) AS snapDriverPercent
ON RepRow.RepRowID = snapDriverPercent.RepRowID
AND HistSnapData.ContractID = snapDriverPercent.ContractID
AND HistSnapData.Period = snapDriverPercent.Period
LEFT OUTER JOIN (
SELECT
( @RepRowSeed + 2 ) AS RepRowID
, SnapshotContract.ContractID
, Period
, FCDriverPercent AS histDriverPercent
FROM
@SnapshotContract as SnapshotContract
INNER JOIN tHistorySummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tHistorySummary.ContractID
WHERE
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
( @RepRowSeed + 3 ) AS RepRowID
, SnapshotContract.ContractID
, Period
, FXDriverPercent
FROM
@SnapshotContract as SnapshotContract
INNER JOIN tHistorySummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tHistorySummary.ContractID
WHERE
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
( @RepRowSeed + 4 ) AS RepRowID
, SnapshotContract.ContractID
, Period
, FODriverPercent
FROM
@SnapshotContract as SnapshotContract
INNER JOIN tHistorySummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tHistorySummary.ContractID
WHERE
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
( @RepRowSeed + 5 ) AS RepRowID
, SnapshotContract.ContractID
, Period
, FRDriverPercent
FROM
@SnapshotContract as SnapshotContract
INNER JOIN tHistorySummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tHistorySummary.ContractID
WHERE
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
) AS histDriverPercent
ON RepRow.RepRowID = histDriverPercent.RepRowID
AND HistSnapData.ContractID = histDriverPercent.ContractID
AND HistSnapData.Period = histDriverPercent.Period
) AS ContractSpend
GROUP BY
ContractID
, RepRowID
--SSR-80698 Changes Begin
--To Retrieve and insert the AOP and Current Fiscal Values
INSERT INTO
@RepData
SELECT
RepRowID
, ContractID
, NULL AS pDec
, NULL AS pAnn
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 1)) )) AS Jan
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 2)) )) AS Feb
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 3)) )) AS Mar
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 4)) )) AS Apr
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 5)) )) AS May
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 6)) )) AS Jun
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 7)) )) AS Jul
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 8)) )) AS Aug
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 9)) )) AS Sep
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 10)) )) AS Oct
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 11)) )) AS Nov
, SUM(( ContractMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 12)) )) AS [Dec]
, @ExchangeRateDate--SSR 80705 Change - Insert Value for ExchangeRateDate
FROM
(
SELECT
RepRow.RepRowID
, HistSnapData.ContractID
, HistSnapData.Period
,
--replace CASE WHEN NOT snapMERSpend IS NULL OR @SnapKind='A'
--replace THEN (snapMERSpend * (snapFIPercent*(snapDriverPercent/Case IsNull((snapFC+snapFX+snapFO+snapFR),0) When 0 Then 1 Else (snapFC+snapFX+snapFO+snapFR) End/100)))/snapExchangeRate
--replace ELSE ((histMERSpend/((100 + histFC + histFX + histFO + histFR)/100)) *
--replace ((((histTotalROYMERSpend-histOriginalROYMERSpend)/Case IsNull(histOriginalEARSpend,0) When 0 Then 1 Else histOriginalEARSpend End)*100)*
--replace (histDriverPercent/Case IsNull((histFC + histFX + histFO + histFR),0) When 0 Then 1 Else (histFC + histFX + histFO + histFR) End/100)))
--replace / Case IsNull(histExchangeRate,0) When 0 Then 1 Else histExchangeRate End END AS ContractMERSpend
CASE WHEN NOT snapFI IS NULL
OR @SnapKind = 'A'
THEN ( snapFI * ( snapDriverPercent
/ Case IsNull(( snapFC + snapFX
+ snapFO + snapFR ) , 0)
When 0 Then 1
Else ( snapFC + snapFX + snapFO
+ snapFR )
End ) ) / snapExchangeRate
ELSE ( ( histTotalROYMERSpend - histOriginalROYMERSpend )
* ( (histDriverPercent
/ Case IsNull(( histFC + histFX + histFO
+ histFR ) , 0)
When 0 Then 1
Else ( histFC + histFX + histFO + histFR )
End) ) ) / Case IsNull(histExchangeRate , 0)
When 0 Then 1
Else histExchangeRate
End
END AS ContractMERSpend
FROM
(
SELECT
( @RepRowSeed + 6 ) AS RepRowID
UNION ALL
SELECT
( @RepRowSeed + 7 )
UNION ALL
SELECT
( @RepRowSeed + 8 )
UNION ALL
SELECT
( @RepRowSeed + 9 )
) AS RepRow
CROSS JOIN (
SELECT
SnapshotContract.ContractID
, ContractPeriod.Period
, tempSnapshotSummary.snapMERSpend
, tempSnapshotSummary.ExchangeRate AS snapExchangeRate
, tempSnapshotSummary.FCDriverPercent AS snapFC
, tempSnapshotSummary.FXDriverPercent AS snapFX
, tempSnapshotSummary.FODriverPercent AS snapFO
, tempSnapshotSummary.FRDriverPercent AS snapFR
,
--insertline
tempSnapshotSummary.FiscalImpact AS snapFI
,
--endinsert
tempSnapshotSummary.FiscalImpactPercent AS snapFIPercent
, tHistorySummary.TotalMERSpend AS histMERSpend
, tHistorySummary.FCDriverPercent AS histFC
, tHistorySummary.FXDriverPercent AS histFX
, tHistorySummary.FODriverPercent AS histFO
, tHistorySummary.FRDriverPercent AS histFR
, tHistorySummary.ExchangeRate AS histExchangeRate
, tHIstorySummary.TotalROYMERSpend AS histTotalROYMERSpend
, tHistorySummary.TotalEARSpend / ( ( 100 + tHistorySummary.FCDriverPercent + tHistorySummary.FXDriverPercent + tHistorySummary.FODriverPercent + tHistorySummary.FRDriverPercent ) / 100 ) AS histOriginalEARSpend
, tHistorySummary.TotalROYMERSpend / ( ( 100 + tHistorySummary.FCDriverPercent + tHistorySummary.FXDriverPercent + tHistorySummary.FODriverPercent + tHistorySummary.FRDriverPercent ) / 100 ) AS histOriginalROYMERSpend
FROM
@SnapshotContract AS SnapshotContract
CROSS JOIN (
SELECT
@CurrJanPeriod AS Period
UNION ALL
SELECT
DATEADD(MONTH , 1 ,
@CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 2 ,
@CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 3 ,
@CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 4 ,
@CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 5 ,
@CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 6 ,
@CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 7 ,
@CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 8 ,
@CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 9 ,
@CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 10 ,
@CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 11 ,
@CurrJanPeriod)
) AS ContractPeriod
LEFT OUTER JOIN tHistorySummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tHistorySummary.ContractID
AND ContractPeriod.Period = tHistorySummary.Period
LEFT OUTER JOIN (
SELECT
ContractSnapSpend.ContractID
, ContractSnapSpend.Period
, ContractSnapSpend.snapMERSpend
, tSnapshotSummary.ExchangeRate
,
--insertline
tSnapshotSummary.FiscalImpact
,
--endinsert
tSnapshotSummary.FiscalImpactPercent
, tSnapshotSummary.FCDriverPercent
, tSnapshotSummary.FXDriverPercent
, tSnapshotSummary.FODriverPercent
, tSnapshotSummary.FRDriverPercent
FROM
(
SELECT
tPart.ContractID
, tsp.Period
,
--SSR-80690 Changes Begin
SUM(( PreviousPrice / CASE ISNULL(tsp.PricePerQty , 0)
WHEN 0 THEN 1
ELSE tsp.PricePerQty
END )
* ( MER / ( CASE ISNULL(tsp.ConversionFactor , 0)
WHEN 0 THEN 1
ELSE tsp.ConversionFactor
END / CASE ISNULL(tsp.ConversionFactorBase , 0)
WHEN 0 THEN 1
ELSE tsp.ConversionFactorBase
END ) )) AS snapMERSpend
FROM
tSnapshotPart tsp WITH ( NOLOCK ) --SSR-80690 Changes End
INNER JOIN tPart WITH ( NOLOCK )
ON tsp.PartID = tPart.PartID
WHERE
tsp.SnapshotID = @SnapshotID
AND tsp.Period BETWEEN @CurrJanPeriod
AND @CurrDecPeriod
--AND
--tPart.Revision = tPart.PlantRevisionLevel
GROUP BY
tPart.ContractID
, tsp.Period
) AS ContractSnapSpend
INNER JOIN tSnapshotSummary
WITH ( NOLOCK )
ON ContractSnapSpend.ContractID = tSnapshotSummary.ContractID
AND ContractSnapSpend.Period = tSnapshotSummary.Period
WHERE
tSnapshotSummary.SnapshotID = @SnapshotID
) AS tempSnapshotSummary
ON SnapshotContract.ContractID = tempSnapshotSummary.ContractID
AND ContractPeriod.Period = tempSnapshotSummary.Period
) AS HistSnapData
LEFT OUTER JOIN (
SELECT
( @RepRowSeed + 6 ) AS RepRowID
, ContractID
, Period
, FCDriverPercent AS SnapDriverPercent
FROM
tSnapshotSummary WITH ( NOLOCK )
WHERE
SnapshotID = @SnapshotID
AND Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
( @RepRowSeed + 7 ) AS RepRowID
, ContractID
, Period
, FXDriverPercent
FROM
tSnapshotSummary WITH ( NOLOCK )
WHERE
SnapshotID = @SnapshotID
AND Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
( @RepRowSeed + 8 ) AS RepRowID
, ContractID
, Period
, FODriverPercent
FROM
tSnapshotSummary WITH ( NOLOCK )
WHERE
SnapshotID = @SnapshotID
AND Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
( @RepRowSeed + 9 ) AS RepRowID
, ContractID
, Period
, FRDriverPercent
FROM
tSnapshotSummary WITH ( NOLOCK )
WHERE
SnapshotID = @SnapshotID
AND Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
) AS snapDriverPercent
ON RepRow.RepRowID = snapDriverPercent.RepRowID
AND HistSnapData.ContractID = snapDriverPercent.ContractID
AND HistSnapData.Period = snapDriverPercent.Period
LEFT OUTER JOIN (
SELECT
( @RepRowSeed + 6 ) AS RepRowID
, SnapshotContract.ContractID
, Period
, FCDriverPercent AS histDriverPercent
FROM
@SnapshotContract AS SnapshotContract
INNER JOIN tHistorySummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tHistorySummary.ContractID
WHERE
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
( @RepRowSeed + 7 ) AS RepRowID
, SnapshotContract.ContractID
, Period
, FXDriverPercent
FROM
@SnapshotContract AS SnapshotContract
INNER JOIN tHistorySummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tHistorySummary.ContractID
WHERE
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
( @RepRowSeed + 8 ) AS RepRowID
, SnapshotContract.ContractID
, Period
, FODriverPercent
FROM
@SnapshotContract AS SnapshotContract
INNER JOIN tHistorySummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tHistorySummary.ContractID
WHERE
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
UNION ALL
SELECT
( @RepRowSeed + 9 ) AS RepRowID
, SnapshotContract.ContractID
, Period
, FRDriverPercent
FROM
@SnapshotContract as SnapshotContract
INNER JOIN tHistorySummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tHistorySummary.ContractID
WHERE
Period BETWEEN @CurrJanPeriod AND @CurrDecPeriod
) AS histDriverPercent
ON RepRow.RepRowID = histDriverPercent.RepRowID
AND HistSnapData.ContractID = histDriverPercent.ContractID
AND HistSnapData.Period = histDriverPercent.Period
) AS ContractSpend
GROUP BY
ContractID
, RepRowID
--SSR-80698 Changes End
--SSR 82281 Changes Begin
--Curr Annual for BackDated Update.
IF @SnapKind = 'C'
BEGIN
INSERT INTO
@RepData
SELECT
RepRowID
, ContractID
, NULL AS pDec
, NULL AS pAnn
, SUM(( TotalMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 1)) )) AS Jan
, SUM(( TotalMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 2)) )) AS Feb
, SUM(( TotalMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 3)) )) AS Mar
, SUM(( TotalMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 4)) )) AS Apr
, SUM(( TotalMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 5)) )) AS May
, SUM(( TotalMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 6)) )) AS Jun
, SUM(( TotalMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 7)) )) AS Jul
, SUM(( TotalMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 8)) )) AS Aug
, SUM(( TotalMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 9)) )) AS Sep
, SUM(( TotalMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 10)) )) AS Oct
, SUM(( TotalMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 11)) )) AS Nov
, SUM(( TotalMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period)
- 12)) )) AS [Dec]
, @ExchangeRateDate--SSR 80705 Change - Insert Value for ExchangeRateDate
FROM
(
SELECT
RepRow.RepRowID
, ContractSpend.ContractID
, ContractSpend.Period
, ContractSpend.TotalMerSpend
, ContractSpend.BackDatedUpdate
FROM
(
SELECT
( @RepRowSeed + 10 ) AS RepRowID
) AS RepRow
CROSS JOIN (
SELECT
SnapshotContract.ContractID
, ContractPeriod.Period
, tHistorySummary.TotalMERSpend AS histMERSpend
, tHistorySummary.FCDriverPercent AS histFC
, tHistorySummary.FXDriverPercent AS histFX
, tHistorySummary.FODriverPercent AS histFO
, tHistorySummary.FRDriverPercent AS histFR
, tHistorySummary.ExchangeRate AS histExchangeRate
, tHIstorySummary.TotalROYMERSpend AS histTotalROYMERSpend
, tHistorySummary.TotalMERSpend
/ ( ( 100
+ tHistorySummary.FCDriverPercent
+ tHistorySummary.FXDriverPercent
+ tHistorySummary.FODriverPercent
+ tHistorySummary.FRDriverPercent )
/ 100 )
* ( ( tHistorySummary.FCDriverPercent
+ tHistorySummary.FXDriverPercent
+ tHistorySummary.FODriverPercent
+ tHistorySummary.FRDriverPercent )
/ 100 ) AS TotalMERSpend
, tHistorySummary.BackDatedUpdate
FROM
@SnapshotContract AS SnapshotContract
CROSS JOIN (
SELECT
@CurrJanPeriod AS Period
UNION ALL
SELECT
DATEADD(MONTH , 1 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 2 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 3 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 4 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 5 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 6 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 7 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 8 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 9 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 10 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 11 , @CurrJanPeriod)
) AS ContractPeriod
LEFT OUTER JOIN tHistorySummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tHistorySummary.ContractID
AND ContractPeriod.Period = tHistorySummary.Period
) AS ContractSpend
WHERE
ContractSpend.BackDatedUpdate = 1
) AS TotalHistMerSpendBackDated
GROUP BY
ContractID
, RepRowID
--Curr Fiscal for BackDated MPCA
INSERT INTO
@RepData
SELECT
RepRowID
, ContractID
, NULL AS pDec
, NULL AS pAnn
, SUM(( TotalROYMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period) - 1)) )) AS Jan
, SUM(( TotalROYMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period) - 2)) )) AS Feb
, SUM(( TotalROYMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period) - 3)) )) AS Mar
, SUM(( TotalROYMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period) - 4)) )) AS Apr
, SUM(( TotalROYMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period) - 5)) )) AS May
, SUM(( TotalROYMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period) - 6)) )) AS Jun
, SUM(( TotalROYMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period) - 7)) )) AS Jul
, SUM(( TotalROYMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period) - 8)) )) AS Aug
, SUM(( TotalROYMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period) - 9)) )) AS Sep
, SUM(( TotalROYMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period) - 10)) )) AS Oct
, SUM(( TotalROYMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period) - 11)) )) AS Nov
, SUM(( TotalROYMERSpend ) * ( 1 - ABS(SIGN(DATEPART(mm , Period) - 12)) )) AS [Dec]
, @ExchangeRateDate--SSR 80705 Change - Insert Value for ExchangeRateDate
FROM
(
SELECT
RepRow.RepRowID
, ContractSpend.ContractID
, ContractSpend.Period
, ContractSpend.TotalROYMERSpend
, ContractSpend.BackDatedUpdate
FROM
(
SELECT
( @RepRowSeed + 11 ) AS RepRowID
) AS RepRow
CROSS JOIN (
SELECT
SnapshotContract.ContractID
, ContractPeriod.Period
, tHistorySummary.TotalMERSpend AS histMERSpend
, tHistorySummary.FCDriverPercent AS histFC
, tHistorySummary.FXDriverPercent AS histFX
, tHistorySummary.FODriverPercent AS histFO
, tHistorySummary.FRDriverPercent AS histFR
, tHistorySummary.ExchangeRate AS histExchangeRate
, tHIstorySummary.TotalROYMERSpend AS histTotalROYMERSpend
, tHistorySummary.TotalROYMERSpend
/ ( ( 100
+ tHistorySummary.FCDriverPercent
+ tHistorySummary.FXDriverPercent
+ tHistorySummary.FODriverPercent
+ tHistorySummary.FRDriverPercent )
/ 100 )
* ( ( tHistorySummary.FCDriverPercent
+ tHistorySummary.FXDriverPercent
+ tHistorySummary.FODriverPercent
+ tHistorySummary.FRDriverPercent )
/ 100 ) AS TotalROYMERSpend
, tHistorySummary.BackDatedUpdate
FROM
@SnapshotContract AS SnapshotContract
CROSS JOIN (
SELECT
@CurrJanPeriod AS Period
UNION ALL
SELECT
DATEADD(MONTH , 1 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 2 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 3 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 4 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 5 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 6 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 7 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 8 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 9 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 10 , @CurrJanPeriod)
UNION ALL
SELECT
DATEADD(MONTH , 11 , @CurrJanPeriod)
) AS ContractPeriod
LEFT OUTER JOIN tHistorySummary WITH ( NOLOCK )
ON SnapshotContract.ContractID = tHistorySummary.ContractID
AND ContractPeriod.Period = tHistorySummary.Period
) AS ContractSpend
WHERE
ContractSpend.BackDatedUpdate = 1
) AS TotalHistRoyMerSpendBackDated
GROUP BY
ContractID
, RepRowID
END
RETURN
END
November 17, 2011 at 4:03 pm
Please upload the ACTUAL execution plan.
The bad one and the good one too.
I have a low fee for stuff like that. Normal time to optimize this is around 5+ days.
November 17, 2011 at 4:27 pm
Thanks Ninja, I was also hoping to shorten up the endless scroll when I came back to this though. 🙂
No wonder that thing takes two minutes, gyeah. Between the non-SARGable LIKE structures, the endless hierarchy of queries, the sub-function calls...
My guess is this thing is running on a monster of a server. You're waiting on the data cache to load when the server first reboots. Once it's all in memory you've got a fighting chance.
I'm afraid I'd have to agree with Ninja, this is too much to optimize online for free. I'd have to dig into data heuristics, algorithmic functional expectations and re-writes, data-flow trees and result expectations on the primary query... ugh, yeah, no. Though I see why you didn't want to dig into it deeply... :hehe:
In cases like this, I'd start with the primary culprits. Dig into the functions first, working your way out to the main query. Flatten out as many functions as you can into the primary query call as well, so that you don't have iterative requests.
The only way out of the 'startup slowdown' would be to reduce all the intermediary data this query is going to rely on, and get the memory allocation accurate at the beginning, which requires not forcing a timeout on the optimizer, which at a guess is occurring.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 17, 2011 at 4:34 pm
Ya in 5 days you get it working better or acceptable. You're not done!
We might be able to find low hanging fruits in the execution plan, but that's about as much as we can do with that monster(s).
November 18, 2011 at 6:14 am
I'm not even going to try to wade through all that.
Here's a question. How many rows are returned by that multi-statement table valued user defined function? I ask because, if you're using that in JOINs, WHERE clauses, or any other situation that calls for statistics AND you're returning more than a handful of rows, that is killing your performance, right there.
You need to break that stuff down and simplify it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 18, 2011 at 3:33 pm
I'm glad I can say I didn't write it. Yes, it's a mess. The function returns approximately 30,000 records. But why is it that after I run the database engine tuning advisor the stored procedure that calls this function returns in less than 2 minutes. If I recompile the function or recycle SQL Server it seems to stall and doesn't do anything? I've checked for hypothetical indexes and statistics and cleared out the trash that was left by the DETA but the problem still remains.
November 18, 2011 at 3:38 pm
bridgt01 (11/18/2011)
I'm glad I can say I didn't write it. Yes, it's a mess. The function returns approximately 30,000 records. But why is it that after I run the database engine tuning advisor the stored procedure that calls this function returns in less than 2 minutes. If I recompile the function or recycle SQL Server it seems to stall and doesn't do anything? I've checked for hypothetical indexes and statistics and cleared out the trash that was left by the DETA but the problem still remains.
Sounds like a bad plan is getting into the cache. Might be parameter sniffing, could be a few other things. Doing a full update on the statistics would probably help too.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 18, 2011 at 3:39 pm
bridgt01 (11/18/2011)
I'm glad I can say I didn't write it. Yes, it's a mess. The function returns approximately 30,000 records. But why is it that after I run the database engine tuning advisor the stored procedure that calls this function returns in less than 2 minutes. If I recompile the function or recycle SQL Server it seems to stall and doesn't do anything? I've checked for hypothetical indexes and statistics and cleared out the trash that was left by the DETA but the problem still remains.
You need to bite the bullet & and let it finish once to grad the ACTUAL execution plan.
Ideally we need both the good & bad plan.
We need to see the difference between the plans to have any chance of helping out.
November 18, 2011 at 3:41 pm
Evil Kraig F (11/18/2011)
bridgt01 (11/18/2011)
I'm glad I can say I didn't write it. Yes, it's a mess. The function returns approximately 30,000 records. But why is it that after I run the database engine tuning advisor the stored procedure that calls this function returns in less than 2 minutes. If I recompile the function or recycle SQL Server it seems to stall and doesn't do anything? I've checked for hypothetical indexes and statistics and cleared out the trash that was left by the DETA but the problem still remains.Sounds like a bad plan is getting into the cache. Might be parameter sniffing, could be a few other things. Doing a full update on the statistics would probably help too.
Might be BAD parameter sniffing (slight difference).
Update stats could be the solution only if the data in those tables is super volatile. We'd see signs of that in the plan.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply