Skip to content

Instantly share code, notes, and snippets.

@mwpastore

mwpastore/ExampleLoader.sql

Last active December 3, 2024 22:38
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save mwpastore/f4af94d08e50f90a13e8d821d8b17904 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save mwpastore/f4af94d08e50f90a13e8d821d8b17904 to your computer and use it in GitHub Desktop.
Download ZIP
Update n2-reporting ETL process
Raw
Item.sql
CREATE TABLE [dimension].[Item]
(
[ItemKey] BIGINT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
[SequenceNumber] BIGINT NOT NULL,
[ItemId] UNIQUEIDENTIFIER NOT NULL,
[HistoricalUpc] CHAR(13) NOT NULL,
[HistoricalMfgCode] NVARCHAR(5) NOT NULL,
[HistoricalType] INT NOT NULL,
[HistoricalIsWeighed] BIT NOT NULL,
[HistoricalDescription] NVARCHAR(50) NOT NULL,
[HistoricalLongDescription] NVARCHAR(50) NOT NULL,
[HistoricalPack] DECIMAL(8,2) NOT NULL,
[HistoricalItemCode] NVARCHAR(50) NOT NULL,
[HistoricalSize] DECIMAL(8,2) NOT NULL,
[HistoricalUnitOfMeasure] NVARCHAR(2) NOT NULL,
[HistoricalDateAuthorized] DATE NOT NULL,
[HistoricalCutDate] DATE NOT NULL,
[HistoricalBrand] NVARCHAR(MAX) NOT NULL,
[VendorId] UNIQUEIDENTIFIER NULL,
[HistoricalVendorName] NVARCHAR(50) NOT NULL,
[HistoricalVendorShortCode] NVARCHAR(10) NOT NULL,
[CategoryId] UNIQUEIDENTIFIER NULL,
[HistoricalCategoryName] NVARCHAR(50) NOT NULL,
[HistoricalCategoryShortCode] NVARCHAR(10) NOT NULL,
[HistoricalLeftPaddedCategoryShortCode] NVARCHAR(7) NOT NULL,
[DepartmentGroupId] UNIQUEIDENTIFIER NULL,
[HistoricalDepartmentGroupName] NVARCHAR(50) NOT NULL,
[HistoricalDepartmentGroupShortCode] NVARCHAR(50) NOT NULL,
[FamilyGroupId] UNIQUEIDENTIFIER NULL,
[HistoricalFamilyGroupName] NVARCHAR(50) NOT NULL,
[GlAccountId] UNIQUEIDENTIFIER NULL,
[HistoricalGlAccountName] NVARCHAR(50) NOT NULL,
[HistoricalGlAccountShortCode] NVARCHAR(10) NOT NULL,
[HistoricalHashKey] VARBINARY(20) NOT NULL,
[CurrentUpc] CHAR(13) NOT NULL,
[CurrentMfgCode] NVARCHAR(5) NOT NULL,
[CurrentType] INT NOT NULL,
[CurrentIsWeighed] BIT NOT NULL,
[CurrentDescription] NVARCHAR(50) NOT NULL,
[CurrentLongDescription] NVARCHAR(50) NOT NULL,
[CurrentPack] DECIMAL(8,2) NOT NULL,
[CurrentItemCode] NVARCHAR(50) NOT NULL,
[CurrentSize] DECIMAL(8,2) NOT NULL,
[CurrentUnitOfMeasure] NVARCHAR(2) NOT NULL,
[CurrentDateAuthorized] DATE NOT NULL,
[CurrentCutDate] DATE NOT NULL,
[CurrentBrand] NVARCHAR(MAX) NOT NULL,
[CurrentVendorName] NVARCHAR(50) NOT NULL,
[CurrentVendorShortCode] NVARCHAR(50) NOT NULL,
[CurrentCategoryName] NVARCHAR(50) NOT NULL,
[CurrentCategoryShortCode] NVARCHAR(10) NOT NULL,
[CurrentLeftPaddedCategoryShortCode] NVARCHAR(7) NOT NULL,
[CurrentDepartmentGroupName] NVARCHAR(50) NOT NULL,
[CurrentDepartmentGroupShortCode] NVARCHAR(50) NOT NULL,
[CurrentFamilyGroupName] NVARCHAR(50) NOT NULL,
[CurrentGlAccountName] NVARCHAR(50) NOT NULL,
[CurrentGlAccountShortCode] NVARCHAR(10) NOT NULL,
[IsRowCurrent] BIT NULL,
)
GO
CREATE UNIQUE NONCLUSTERED INDEX [CK_Item_Finder]
ON [dimension].[Item]
(
[HistoricalHashKey],
[ItemId]
)
INCLUDE
(
[ItemKey],
[IsRowCurrent],
[SequenceNumber]
)
GO
-- For Category Analysis report
CREATE NONCLUSTERED INDEX [IX_Item_Filter_Upc]
ON [dimension].[Item]
(
[CurrentUpc],
[CurrentMfgCode]
)
-- N.B. We basically need to fetch the whole row after this lookup, so there's
-- no need to INCLUDE any specific columns in the index
GO
CREATE NONCLUSTERED INDEX [IX_Item_Filter_Category]
ON [dimension].[Item]
(
[CurrentCategoryName],
[CurrentCategoryShortCode]
)
GO
CREATE NONCLUSTERED INDEX [IX_Item_Filter_DepartmentGroup]
ON [dimension].[Item]
(
[CurrentDepartmentGroupName],
[CurrentDepartmentGroupShortCode]
)
GO
CREATE NONCLUSTERED INDEX [IX_Item_Filter_Vendor]
ON [dimension].[Item]
(
[CurrentVendorName],
[CurrentVendorShortCode]
)
GO
Raw
ItemAisledFactlessFacts.sql
--
-- any changes here *MUST* be replicated to switch_staging/tables/ItemAisledFactlessFacts
--
CREATE TABLE [fact].[ItemAisledFactlessFacts]
(
INDEX [CCI_ItemAisledFactlessFacts] CLUSTERED COLUMNSTORE
ON [ItemAisledFactlessFactsPartitionScheme]([BusinessDay]),
[BusinessDay] DATE NOT NULL,
[LocationKey] BIGINT NOT NULL,
[ItemKey] BIGINT NOT NULL,
CONSTRAINT [FK_ItemAisledFactlessFacts_BusinessDay] FOREIGN KEY ([BusinessDay]) REFERENCES [dimension].[Date]([BusinessDay]),
CONSTRAINT [FK_ItemAisledFactlessFacts_LocationKey] FOREIGN KEY ([LocationKey]) REFERENCES [dimension].[Location]([LocationKey]),
CONSTRAINT [FK_ItemAisledFactlessFacts_ItemKey] FOREIGN KEY ([ItemKey]) REFERENCES [dimension].[Item]([ItemKey]),
CONSTRAINT [CK_ItemAisledFactlessFacts] UNIQUE NONCLUSTERED
(
[BusinessDay],
[LocationKey],
[ItemKey]
),
)
ON [ItemAisledFactlessFactsPartitionScheme]([BusinessDay])
Raw
MovementFacts.sql
--
-- any changes here *MUST* be replicated to switch_staging/tables/MovementFacts
--
CREATE TABLE [fact].[MovementFacts]
(
INDEX [CCI_MovementFacts] CLUSTERED COLUMNSTORE
ON [MovementFactsPartitionScheme]([BusinessDay]),
[BusinessDay] DATE NOT NULL,
[ItemKey] BIGINT NOT NULL,
[LocationKey] BIGINT NOT NULL,
[TotalQuantity] INT NOT NULL,
[TotalWeight] DECIMAL(12,4) NOT NULL,
[TotalCost] DECIMAL(12,4) NOT NULL,
[TotalRetail] DECIMAL(8,2) NOT NULL,
[WeekMargin] DECIMAL(6,2) NOT NULL, -- Can these actually be sum'd/avg'd/etc?
CONSTRAINT [FK_MovementFacts_BusinessDay] FOREIGN KEY ([BusinessDay]) REFERENCES [dimension].[Date]([BusinessDay]),
CONSTRAINT [FK_MovementFacts_ItemKey] FOREIGN KEY ([ItemKey]) REFERENCES [dimension].[Item]([ItemKey]),
CONSTRAINT [FK_MovementFacts_LocationKey] FOREIGN KEY ([LocationKey]) REFERENCES [dimension].[Location]([LocationKey]),
CONSTRAINT [CK_MovementFacts] UNIQUE NONCLUSTERED
(
[BusinessDay],
[ItemKey],
[LocationKey]
),
)
ON [MovementFactsPartitionScheme]([BusinessDay])
Raw
MovementItemAggregateView.sql
CREATE VIEW [etl].[MovementItemAggregateView] AS
WITH Base AS (
SELECT
mia.[BusinessDay],
mia.[IntId],
mia.[Item_id] AS ItemId,
mia.[Upc],
COALESCE(mia.[ItemType], -1) AS ItemType,
COALESCE(i.[Description], 'N/A') AS [Description],
COALESCE(i.[LongDescription], 'N/A') AS LongDescription,
COALESCE(mia.[Pack], -1) AS Pack,
COALESCE(mia.[VItem], 'QQ') AS ItemCode,
COALESCE(mia.[Size], -1) AS [Size],
COALESCE(mia.[UnitOfMeasureShortCode], 'QQ') AS UnitOfMeasure,
COALESCE(mia.[DateAuthorized], i.[DateAuthorized], '1900-01-01') AS DateAuthorized,
COALESCE(mia.[CutDate], i.[CutDate], '9999-12-31') AS CutDate,
COALESCE(i.[Brand], 'N/A') AS Brand,
mia.[VendorId],
COALESCE(mia.[VendorName], 'N/A') AS VendorName,
COALESCE(v.[ShortCode], 'N/A') AS VendorShortCode,
mia.[CategoryId],
COALESCE(mia.[CategoryName], 'N/A') AS CategoryName,
COALESCE(mia.[CategoryShortCode], 'N/A') AS CategoryShortCode,
mia.[DepartmentGroupId],
COALESCE(dg.[Name], 'N/A') AS DepartmentGroupName,
COALESCE(dg.[ShortCode], 'N/A') AS DepartmentGroupShortCode,
mia.[FamilyGroupId],
COALESCE(fg.[Name], 'N/A') AS FamilyGroupName,
i.[GlAccount_id] AS GlAccountId,
COALESCE(gl.[Name], 'N/A') AS GlAccountName,
COALESCE(gl.[ShortCode], 'N/A') AS GlAccountShortCode,
mia.[Location_id] AS LocationId,
COALESCE(mia.[LocationName], 'N/A') AS LocationName,
COALESCE(mia.[LocationShortCode], 'N/A') AS LocationShortCode,
mia.[IsAuthorized],
mia.[TotalQuantity],
mia.[TotalWeight],
mia.[TotalCost],
mia.[TotalRetail],
mia.[WeekMargin]
FROM [Host].[dbo].[MovementItemAggregate] AS mia
--INNER JOIN [Host].[dbo].[Location] AS l
-- ON l.[Id] = mia.[Location_id]
INNER JOIN [Host].[dbo].[Item] AS i
ON i.[Id] = mia.[Item_id]
--LEFT JOIN [Host].[dbo].[Category] AS c
-- ON c.[Id] = mia.[CategoryId]
LEFT JOIN [Host].[dbo].[DepartmentGroup] AS dg
ON dg.[Id] = mia.[DepartmentGroupId]
LEFT JOIN [Host].[dbo].[FamilyGroup] AS fg
ON fg.[Id] = mia.[FamilyGroupId]
LEFT JOIN [Host].[dbo].[GlAccount] AS gl
ON gl.[Id] = i.[GlAccount_id]
LEFT JOIN [Host].[dbo].[Vendor] AS v
ON v.[Id] = mia.[VendorId]
)
SELECT *,
CONVERT(VARBINARY(20), HASHBYTES('SHA1', CONCAT(
[ItemId], '|',
[VendorId], '|',
[CategoryId], '|',
[DepartmentGroupId], '|',
[FamilyGroupId], '|',
[GlAccountId], '|',
[Upc], '|',
[ItemType], '|',
[Description], '|',
[LongDescription], '|',
[Pack], '|',
[ItemCode], '|',
[Size], '|',
[UnitOfMeasure], '|',
[DateAuthorized], '|',
[CutDate], '|',
[Brand], '|',
[VendorName], '|',
[VendorShortCode], '|',
[CategoryName], '|',
[CategoryShortCode], '|',
[DepartmentGroupName], '|',
[DepartmentGroupShortCode], '|',
[FamilyGroupName], '|',
[GlAccountName], '|',
[GlAccountShortCode]
))) AS ItemHashKey,
CONVERT(VARBINARY(20), HASHBYTES('SHA1', CONCAT(
[LocationName], '|',
[LocationShortCode]
))) AS LocationHashKey
FROM Base
Raw
PopulateCategoryAnalysisFacts.sql
CREATE PROCEDURE [etl].[PopulateCategoryAnalysisFacts]
@fromBusinessDay DATE,
@toBusinessDay DATE = @fromBusinessDay,
@factSchemaName NVARCHAR(128),
@appendOnly BIT = 0
AS
BEGIN
-- Build Fact Source
BEGIN
-- Load a temp table instead of using a CTE so predicate(s) push
-- down to the view
SELECT
BusinessDay,
ItemHashKey,
LocationHashKey,
CAST(MAX(CAST(IsAuthorized AS INT)) AS BIT) AS IsAuthorized,
SUM(TotalQuantity) AS TotalQuantity,
SUM(TotalWeight) AS TotalWeight,
SUM(TotalCost) AS TotalCost,
SUM(TotalRetail) AS TotalRetail,
SUM(WeekMargin) AS WeekMargin
INTO #IntermediateFactsSource
FROM [etl].[MovementItemAggregateView]
WHERE BusinessDay BETWEEN @fromBusinessDay AND @toBusinessDay
GROUP BY BusinessDay, ItemHashKey, LocationHashKey;
SELECT
BusinessDay,
id.ItemKey,
l.LocationKey,
CAST(MAX(CAST(IsAuthorized AS INT)) AS BIT) AS IsAuthorized,
SUM(TotalQuantity) AS TotalQuantity,
SUM(TotalWeight) AS TotalWeight,
SUM(TotalCost) AS TotalCost,
SUM(TotalRetail) AS TotalRetail,
SUM(WeekMargin) AS WeekMargin,
CAST(CASE
WHEN SUM(TotalQuantity) <> 0
OR SUM(TotalWeight) <> 0
OR SUM(TotalCost) <> 0
OR SUM(TotalRetail) <> 0
OR SUM(WeekMargin) <> 0
THEN 1
ELSE 0
END AS BIT) AS HasMovement
INTO #FactsSource
FROM #IntermediateFactsSource
INNER JOIN [dimension].[Item] AS id
ON id.[HistoricalHashKey] = ItemHashKey
INNER JOIN (
SELECT MAX([LocationKey]) AS LocationKey, [HistoricalHashKey]
FROM [dimension].[Location]
GROUP BY [HistoricalHashKey]
) AS l
ON l.[HistoricalHashKey] = LocationHashKey
GROUP BY
BusinessDay,
id.ItemKey,
l.LocationKey;
END
BEGIN -- Add new Movement Facts
DECLARE @movementFactsSql NVARCHAR(MAX);
SET @movementFactsSql = N'
MERGE ' + QUOTENAME(@factSchemaName) + N'.[MovementFacts] WITH (TABLOCK) AS target
USING (
SELECT *
FROM #FactsSource
WHERE HasMovement = 1
) AS source
ON target.BusinessDay = source.BusinessDay
AND target.ItemKey = source.ItemKey
AND target.LocationKey = source.LocationKey
WHEN MATCHED AND @appendOnly <> 1 THEN
UPDATE SET
target.TotalQuantity = source.TotalQuantity,
target.TotalWeight = source.TotalWeight,
target.TotalCost = source.TotalCost,
target.TotalRetail = source.TotalRetail,
target.WeekMargin = source.WeekMargin
WHEN NOT MATCHED BY TARGET THEN
INSERT (BusinessDay, ItemKey, LocationKey,
TotalQuantity, TotalWeight, TotalCost, TotalRetail, WeekMargin)
VALUES (source.BusinessDay, source.ItemKey, source.LocationKey,
source.TotalQuantity, source.TotalWeight, source.TotalCost, source.TotalRetail, source.WeekMargin);
';
EXEC sp_executesql @movementFactsSql, N'@appendOnly BIT', @appendOnly;
IF @appendOnly <> 1
BEGIN
SET @movementFactsSql = N'
DELETE target WITH (TABLOCK)
FROM ' + QUOTENAME(@factSchemaName) + N'.[MovementFacts] AS target
WHERE target.BusinessDay BETWEEN @fromBusinessDay AND @toBusinessDay AND NOT EXISTS (
SELECT 1
FROM #FactsSource AS source
WHERE target.BusinessDay = source.BusinessDay
AND target.ItemKey = source.ItemKey
AND target.LocationKey = source.LocationKey
AND source.HasMovement = 1
);
';
EXEC sp_executesql @movementFactsSql, N'@fromBusinessDay DATE, @toBusinessDay DATE', @fromBusinessDay, @toBusinessDay;
END
END
BEGIN -- Add Factless Facts for the day
DECLARE @factlessFactsSql NVARCHAR(MAX);
SET @factlessFactsSql = N'
INSERT INTO ' + QUOTENAME(@factSchemaName) + N'.[ItemAisledFactlessFacts] WITH (TABLOCK)
(
BusinessDay,
ItemKey,
LocationKey
)
SELECT
source.BusinessDay,
source.ItemKey,
source.LocationKey
FROM #FactsSource AS source
WHERE (IsAuthorized = 1 OR HasMovement = 1)
AND NOT EXISTS (
SELECT 1
FROM ' + QUOTENAME(@factSchemaName) + N'.[ItemAisledFactlessFacts] AS target
WHERE target.BusinessDay = source.BusinessDay
AND target.ItemKey = source.ItemKey
AND target.LocationKey = source.LocationKey
);
';
EXEC (@factlessFactsSql);
IF @appendOnly <> 1
BEGIN
SET @factlessFactsSql = N'
DELETE target WITH (TABLOCK)
FROM ' + QUOTENAME(@factSchemaName) + N'.[ItemAisledFactlessFacts] AS target
WHERE target.BusinessDay BETWEEN @fromBusinessDay AND @toBusinessDay AND NOT EXISTS (
SELECT 1
FROM #FactsSource AS source
WHERE target.BusinessDay = source.BusinessDay
AND target.ItemKey = source.ItemKey
AND target.LocationKey = source.LocationKey
AND (source.IsAuthorized = 1 OR source.HasMovement = 1)
);
';
EXEC sp_executesql @factlessFactsSql, N'@fromBusinessDay DATE, @toBusinessDay DATE', @fromBusinessDay, @toBusinessDay;
END
END
END
Raw
PopulateItem.sql
CREATE PROCEDURE [etl].[PopulateItem]
@fromBusinessDay DATE,
@toBusinessDay DATE = @fromBusinessDay,
@updateCurrent BIT
AS
BEGIN
-- Load a temp table instead of using a CTE so predicate(s) push
-- down to the view
SELECT
[BusinessDay],
[IntId] AS [SequenceNumber],
[ItemId],
[Upc],
SUBSTRING([Upc], 4, 5) AS [MfgCode],
[ItemType] AS [Type],
CASE WHEN [ItemType] IN (2, 3) THEN 1 ELSE 0 END AS [IsWeighed],
[Description],
[LongDescription],
[Pack],
[ItemCode],
[Size],
[UnitOfMeasure],
[DateAuthorized],
[CutDate],
[Brand],
[VendorId],
[VendorName],
[VendorShortCode],
[CategoryId],
[CategoryName],
[CategoryShortCode],
RIGHT('00000000' + [CategoryShortCode], 7) AS [LeftPaddedCategoryShortCode],
[DepartmentGroupId],
[DepartmentGroupName],
[DepartmentGroupShortCode],
[FamilyGroupId],
[FamilyGroupName],
[GlAccountId],
[GlAccountName],
[GlAccountShortCode],
[ItemHashKey] AS HashKey,
ROW_NUMBER() OVER (PARTITION BY [ItemHashKey] ORDER BY [IntId] DESC) AS Rank
INTO #ItemsSource
FROM [etl].[MovementItemAggregateView]
WHERE [BusinessDay] BETWEEN @fromBusinessDay AND @toBusinessDay;
INSERT INTO [dimension].[Item] WITH (TABLOCK)
(
[SequenceNumber],
[HistoricalHashKey],
[IsRowCurrent],
[CategoryId],
[DepartmentGroupId],
[FamilyGroupId],
[GlAccountId],
[ItemId],
[VendorId],
[HistoricalBrand],
[HistoricalCategoryName],
[HistoricalCategoryShortCode],
[HistoricalCutDate],
[HistoricalDateAuthorized],
[HistoricalDepartmentGroupName],
[HistoricalDepartmentGroupShortCode],
[HistoricalDescription],
[HistoricalFamilyGroupName],
[HistoricalGlAccountName],
[HistoricalGlAccountShortCode],
[HistoricalIsWeighed],
[HistoricalItemCode],
[HistoricalLeftPaddedCategoryShortCode],
[HistoricalLongDescription],
[HistoricalMfgCode],
[HistoricalPack],
[HistoricalSize],
[HistoricalType],
[HistoricalUnitOfMeasure],
[HistoricalUpc],
[HistoricalVendorName],
[HistoricalVendorShortCode],
[CurrentBrand],
[CurrentCategoryName],
[CurrentCategoryShortCode],
[CurrentCutDate],
[CurrentDateAuthorized],
[CurrentDepartmentGroupName],
[CurrentDepartmentGroupShortCode],
[CurrentDescription],
[CurrentFamilyGroupName],
[CurrentGlAccountName],
[CurrentGlAccountShortCode],
[CurrentIsWeighed],
[CurrentItemCode],
[CurrentLeftPaddedCategoryShortCode],
[CurrentLongDescription],
[CurrentMfgCode],
[CurrentPack],
[CurrentSize],
[CurrentType],
[CurrentUnitOfMeasure],
[CurrentUpc],
[CurrentVendorName],
[CurrentVendorShortCode]
)
SELECT
li.[SequenceNumber],
li.[HashKey],
NULL, -- IsRowCurrent
li.[CategoryId],
li.[DepartmentGroupId],
li.[FamilyGroupId],
li.[GlAccountId],
li.[ItemId],
li.[VendorId],
li.[Brand],
li.[CategoryName],
li.[CategoryShortCode],
li.[CutDate],
li.[DateAuthorized],
li.[DepartmentGroupName],
li.[DepartmentGroupShortCode],
li.[Description],
li.[FamilyGroupName],
li.[GlAccountName],
li.[GlAccountShortCode],
li.[IsWeighed],
li.[ItemCode],
li.[LeftPaddedCategoryShortCode],
li.[LongDescription],
li.[MfgCode],
li.[Pack],
li.[Size],
li.[Type],
li.[UnitOfMeasure],
li.[Upc],
li.[VendorName],
li.[VendorShortCode],
li.[Brand],
li.[CategoryName],
li.[CategoryShortCode],
li.[CutDate],
li.[DateAuthorized],
li.[DepartmentGroupName],
li.[DepartmentGroupShortCode],
li.[Description],
li.[FamilyGroupName],
li.[GlAccountName],
li.[GlAccountShortCode],
li.[IsWeighed],
li.[ItemCode],
li.[LeftPaddedCategoryShortCode],
li.[LongDescription],
li.[MfgCode],
li.[Pack],
li.[Size],
li.[Type],
li.[UnitOfMeasure],
li.[Upc],
li.[VendorName],
li.[VendorShortCode]
FROM #ItemsSource li
WHERE li.[Rank] = 1
AND NOT EXISTS (
SELECT 1
FROM [dimension].[Item] di
WHERE di.[HistoricalHashKey] = li.[HashKey]
);
UPDATE di WITH (TABLOCK)
SET di.[SequenceNumber] = li.[SequenceNumber]
FROM [dimension].[Item] di
INNER JOIN #ItemsSource li ON li.[HashKey] = di.[HistoricalHashKey]
WHERE li.[Rank] = 1 AND li.[SequenceNumber] > di.[SequenceNumber];
IF @updateCurrent <> 1
RETURN;
SELECT
[ItemKey],
[ItemId],
[HistoricalUpc] AS [CurrentUpc],
[HistoricalMfgCode] AS [CurrentMfgCode],
[HistoricalType] AS [CurrentType],
[HistoricalIsWeighed] AS [CurrentIsWeighed],
[HistoricalDescription] AS [CurrentDescription],
[HistoricalLongDescription] AS [CurrentLongDescription],
[HistoricalPack] AS [CurrentPack],
[HistoricalItemCode] AS [CurrentItemCode],
[HistoricalSize] AS [CurrentSize],
[HistoricalUnitOfMeasure] AS [CurrentUnitOfMeasure],
[HistoricalDateAuthorized] AS [CurrentDateAuthorized],
[HistoricalCutDate] AS [CurrentCutDate],
[HistoricalBrand] AS [CurrentBrand],
[HistoricalVendorName] AS [CurrentVendorName],
[HistoricalVendorShortCode] AS [CurrentVendorShortCode],
[HistoricalCategoryName] AS [CurrentCategoryName],
[HistoricalCategoryShortCode] AS [CurrentCategoryShortCode],
[HistoricalLeftPaddedCategoryShortCode] AS [CurrentLeftPaddedCategoryShortCode],
[HistoricalDepartmentGroupName] AS [CurrentDepartmentGroupName],
[HistoricalDepartmentGroupShortCode] AS [CurrentDepartmentGroupShortCode],
[HistoricalFamilyGroupName] AS [CurrentFamilyGroupName],
[HistoricalGlAccountName] AS [CurrentGlAccountName],
[HistoricalGlAccountShortCode] AS [CurrentGlAccountShortCode]
INTO #CurrentItems
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY [ItemId] ORDER BY [SequenceNumber] DESC) AS Rank
FROM [dimension].[Item]
) AS RankedCurrentItems
WHERE [Rank] = 1;
UPDATE di WITH (TABLOCK)
SET
di.CurrentUpc = ci.CurrentUpc,
di.CurrentMfgCode = ci.CurrentMfgCode,
di.CurrentType = ci.CurrentType,
di.CurrentIsWeighed = ci.CurrentIsWeighed,
di.CurrentDescription = ci.CurrentDescription,
di.CurrentLongDescription = ci.CurrentLongDescription,
di.CurrentPack = ci.CurrentPack,
di.CurrentItemCode = ci.CurrentItemCode,
di.CurrentSize = ci.CurrentSize,
di.CurrentUnitOfMeasure = ci.CurrentUnitOfMeasure,
di.CurrentDateAuthorized = ci.CurrentDateAuthorized,
di.CurrentCutDate = ci.CurrentCutDate,
di.CurrentBrand = ci.CurrentBrand,
di.CurrentCategoryName = ci.CurrentCategoryName,
di.CurrentCategoryShortCode = ci.CurrentCategoryShortCode,
di.CurrentLeftPaddedCategoryShortCode = ci.CurrentLeftPaddedCategoryShortCode,
di.CurrentDepartmentGroupName = ci.CurrentDepartmentGroupName,
di.CurrentDepartmentGroupShortCode = ci.CurrentDepartmentGroupShortCode,
di.CurrentFamilyGroupName = ci.CurrentFamilyGroupName,
di.CurrentGlAccountName = ci.CurrentGlAccountName,
di.CurrentGlAccountShortCode = ci.CurrentGlAccountShortCode,
di.IsRowCurrent = (CASE WHEN ci.[ItemKey] = di.[ItemKey] THEN 1 ELSE 0 END)
FROM [dimension].[Item] di
INNER JOIN #CurrentItems ci ON ci.[ItemId] = di.[ItemId];
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment