The purpose of this stored procedure is to merge dbo.receipts (new receipts), tblStoreData (historical data), and source_mealconnect tables into receipts_vw table (converted from view – necessary for speed).
Once the merge is completed, clean-up steps are run:
Pre-step remove all records within last 6 months where [comment] = ‘Missing Contributor id record for YearMonth’
- For last 6 months, get contributor, year month, and a count from existing data
- Get all contributors from mappings.fresh_alliance_localization_tracker
and cross-join year month for last 6 months with 0 as rowCnt - Union items a and b (above)
- roll up rowcounts and then any with a 0 (zero) rowcnt is left
- align fields
- insert into staging.contributor_vw
SQL Code:
-- =======================================================
-- Create Stored Procedure Template for Azure SQL Database
-- =======================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE staging.update_receipt_vw
/*
Created By John Horton LogicIntelligence
Created On 11/15/2024
Purpose Union/Append tables dbo.receipt, [raw_freshalliance].[ms_access_tblStoreData] - with referencecode (receiptRef) as null
, [staging].[source_mealconnect]. Then we replace Agency for NULL or 0 values with data from [mappings].[P2_Receipt_Warehouse_to_AgencyId_Mapping]
for warehouseId's
Updated on 12/19/2024
Purpose to optimize, changed from view to stored procedure
only updating last 6 months of data
*/
AS
BEGIN
DECLARE @MyTableVar TABLE (
[Action] varchar(50)
,receiptid BIGINT
,DataSourceid int
);
MERGE staging.receipt_vw as tgt
USING (
SELECT [receiptId]
,[referenceCode]
,[contributorId]
,[contributorAddressId]
,[contributorContactId]
,[warehouseId]
,[systemEntryOriginId]
,[donationTypeId]
,[systemReceiptTypeId]
,[isDelivery]
,[originationId]
,[originCity]
,[comment]
,[pickupDate]
,[approved]
,[isRetailPickup]
,[isFoodDrive]
,[confirmedDate]
,[submissionDate]
,[modifiedOn]
,[inactiveOn]
,[isInactive]
,[isConfirmed]
,[systemReceivedDate]
,[systemReceived]
,[agencyId]
,[donorInvoiceDate]
,[isBeingConfirmed]
,[confirmingStart]
,[confirmingEnd]
,[isBeingConvertedToOrder]
,[convertingToOrderStart]
,[convertingToOrderEnd]
,[dataSourceId]
,[RowCreateDateTime]
FROM [dbo].[receipt]
) as src
ON tgt.receiptid = src.receiptid
WHEN MATCHED AND src.[modifiedOn] >= dateadd(month,-3,GetDate())
THEN
UPDATE
SET [referenceCode] = src.[referenceCode]
,[contributorId] = src.[contributorId]
,[contributorAddressId] = src.[contributorAddressId]
,[contributorContactId] = src.[contributorContactId]
,[warehouseId] = src.[warehouseId]
,[systemEntryOriginId] = src.[systemEntryOriginId]
,[donationTypeId] = src.[donationTypeId]
,[systemReceiptTypeId] = src.[systemReceiptTypeId]
,[isDelivery] = src.[isDelivery]
,[originationId] = src.[originationId]
,[originCity] = src.[originCity]
,[comment] = src.[comment]
,[pickupDate] = src.[pickupDate]
,[approved] = src.[approved]
,[isRetailPickup] = src.[isRetailPickup]
,[isFoodDrive] = src.[isFoodDrive]
,[confirmedDate] = src.[confirmedDate]
,[submissionDate] = src.[submissionDate]
,[modifiedOn] = src.[modifiedOn]
,[inactiveOn] = src.[inactiveOn]
,[isInactive] = src.[isInactive]
,[isConfirmed] = src.[isConfirmed]
,[systemReceivedDate] = src.[systemReceivedDate]
,[systemReceived] = src.[systemReceived]
,[agencyId] = src.[agencyId]
,[donorInvoiceDate] = src.[donorInvoiceDate]
,[isBeingConfirmed] = src.[isBeingConfirmed]
,[confirmingStart] = src.[confirmingStart]
,[confirmingEnd] = src.[confirmingEnd]
,[isBeingConvertedToOrder] = src.[isBeingConvertedToOrder]
,[convertingToOrderStart] = src.[convertingToOrderStart]
,[convertingToOrderEnd] = src.[convertingToOrderEnd]
,[dataSourceId] = src.[dataSourceId]
,[RowCreateDateTime] = src.[RowCreateDateTime]
WHEN NOT MATCHED
THEN
INSERT
(
[receiptId]
,[referenceCode]
,[contributorId]
,[contributorAddressId]
,[contributorContactId]
,[warehouseId]
,[systemEntryOriginId]
,[donationTypeId]
,[systemReceiptTypeId]
,[isDelivery]
,[originationId]
,[originCity]
,[comment]
,[pickupDate]
,[approved]
,[isRetailPickup]
,[isFoodDrive]
,[confirmedDate]
,[submissionDate]
,[modifiedOn]
,[inactiveOn]
,[isInactive]
,[isConfirmed]
,[systemReceivedDate]
,[systemReceived]
,[agencyId]
,[donorInvoiceDate]
,[isBeingConfirmed]
,[confirmingStart]
,[confirmingEnd]
,[isBeingConvertedToOrder]
,[convertingToOrderStart]
,[convertingToOrderEnd]
,[dataSourceId]
,[RowCreateDateTime]
)
VALUES
(src.[receiptId]
, src.[referenceCode]
, src.[contributorId]
, src.[contributorAddressId]
, src.[contributorContactId]
, src.[warehouseId]
, src.[systemEntryOriginId]
, src.[donationTypeId]
, src.[systemReceiptTypeId]
, src.[isDelivery]
, src.[originationId]
, src.[originCity]
, src.[comment]
, src.[pickupDate]
, src.[approved]
, src.[isRetailPickup]
, src.[isFoodDrive]
, src.[confirmedDate]
, src.[submissionDate]
, src.[modifiedOn]
, src.[inactiveOn]
, src.[isInactive]
, src.[isConfirmed]
, src.[systemReceivedDate]
, src.[systemReceived]
, src.[agencyId]
, src.[donorInvoiceDate]
, src.[isBeingConfirmed]
, src.[confirmingStart]
, src.[confirmingEnd]
, src.[isBeingConvertedToOrder]
, src.[convertingToOrderStart]
, src.[convertingToOrderEnd]
, src.[dataSourceId]
, src.[RowCreateDateTime]
)
OUTPUT
$action as outputType
,inserted.receiptid
,inserted.DataSourceid
INTO @MyTableVar
;
-- [raw_freshalliance].[ms_access_tblStoreData] - Just adding new id's
MERGE staging.receipt_vw as tgt
USING (
SELECT tsd.[UniqueID] as [receiptId]
,COALESCE([ReceiptRef],'Unknown') as [referenceCode]
,COALESCE(cvw.contributorId,0) as [contributorId]
,COALESCE(cvw.[mainAddressId],0) as [contributorAddressId]
,COALESCE(cvw.[mainContactId],0) as [contributorContactId]
,0 as [warehouseId]
,0 as [systemEntryOriginId]
,COALESCE(cvw.[defaultdonationTypeId],0) as [donationTypeId]
,0 as [systemReceiptTypeId]
,0 as [isDelivery]
,COALESCE(cvw.[defaultoriginationId],0) as [originationId]
,NULL as [originCity]
,NULL as [comment]
,tsd.[pickupDate]
,1 as [approved]
,COALESCE(cvw.[isRetailPickup],0) as [isRetailPickup]
,COALESCE(cvw.[isFoodDrive],0) as [isFoodDrive]
,NULL as [confirmedDate]
,NULL as [submissionDate]
,NULL as [modifiedOn]
,NULL as [inactiveOn]
,COALESCE(cvw.[isInactive],1) as [isInactive]
,1 as [isConfirmed]
,NULL as [systemReceivedDate]
,NULL as [systemReceived]
,a.agencyid as [agencyId]
,NULL as [donorInvoiceDate]
,0 as [isBeingConfirmed]
,NULL as [confirmingStart]
,NULL as [confirmingEnd]
,0 as [isBeingConvertedToOrder]
,NULL as [convertingToOrderStart]
,NULL as [convertingToOrderEnd]
,6 as [dataSourceId]
,GetDate() as [RowCreateDateTime]
FROM [raw_freshalliance].[ms_access_tblStoreData] tsd
LEFT OUTER JOIN staging.contributor_vw cvw
ON tsd.DonorRef = cvw.referenceCode
LEFT OUTER JOIN dbo.agency a
ON tsd.AgencyRef = a.referenceCode
LEFT OUTER JOIN dbo.receipt r
ON r.receiptId = tsd.UniqueID
WHERE tsd.ReceiptRef is NULL
AND cvw.referenceCode IS NOT NULL
AND r.receiptId IS NULL
) as src
ON tgt.receiptid = src.receiptid
WHEN NOT MATCHED
THEN
INSERT
(
[receiptId]
,[referenceCode]
,[contributorId]
,[contributorAddressId]
,[contributorContactId]
,[warehouseId]
,[systemEntryOriginId]
,[donationTypeId]
,[systemReceiptTypeId]
,[isDelivery]
,[originationId]
,[originCity]
,[comment]
,[pickupDate]
,[approved]
,[isRetailPickup]
,[isFoodDrive]
,[confirmedDate]
,[submissionDate]
,[modifiedOn]
,[inactiveOn]
,[isInactive]
,[isConfirmed]
,[systemReceivedDate]
,[systemReceived]
,[agencyId]
,[donorInvoiceDate]
,[isBeingConfirmed]
,[confirmingStart]
,[confirmingEnd]
,[isBeingConvertedToOrder]
,[convertingToOrderStart]
,[convertingToOrderEnd]
,[dataSourceId]
,[RowCreateDateTime]
)
VALUES
(src.[receiptId]
, src.[referenceCode]
, src.[contributorId]
, src.[contributorAddressId]
, src.[contributorContactId]
, src.[warehouseId]
, src.[systemEntryOriginId]
, src.[donationTypeId]
, src.[systemReceiptTypeId]
, src.[isDelivery]
, src.[originationId]
, src.[originCity]
, src.[comment]
, src.[pickupDate]
, src.[approved]
, src.[isRetailPickup]
, src.[isFoodDrive]
, src.[confirmedDate]
, src.[submissionDate]
, src.[modifiedOn]
, src.[inactiveOn]
, src.[isInactive]
, src.[isConfirmed]
, src.[systemReceivedDate]
, src.[systemReceived]
, src.[agencyId]
, src.[donorInvoiceDate]
, src.[isBeingConfirmed]
, src.[confirmingStart]
, src.[confirmingEnd]
, src.[isBeingConvertedToOrder]
, src.[convertingToOrderStart]
, src.[convertingToOrderEnd]
, src.[dataSourceId]
, src.[RowCreateDateTime]
)
OUTPUT
$action as outputType
,inserted.receiptid
,inserted.DataSourceid
INTO @MyTableVar
;
-- [staging].[source_mealconnect]
MERGE staging.receipt_vw as tgt
USING (
SELECT CAST(CONCAT('99',mc.id,mc.itemId) as bigint) as [receiptId]
,'Unknown' as [referenceCode]
,COALESCE(cvw.contributorId,0) as [contributorId]
,COALESCE(cvw.[mainAddressId],0) as [contributorAddressId]
,COALESCE(cvw.[mainContactId],0) as [contributorContactId]
,0 as [warehouseId]
,0 as [systemEntryOriginId]
,COALESCE(cvw.[defaultdonationTypeId],0) as [donationTypeId]
,0 as [systemReceiptTypeId]
,0 as [isDelivery]
,COALESCE(cvw.[defaultoriginationId],0) as [originationId]
,NULL as [originCity]
,NULL as [comment]
,mc.postingDate as [pickupDate]
,1 as [approved]
,COALESCE(cvw.[isRetailPickup],0) as [isRetailPickup]
,COALESCE(cvw.[isFoodDrive],0) as [isFoodDrive]
,NULL as [confirmedDate]
,NULL as [submissionDate]
,NULL as [modifiedOn]
,NULL as [inactiveOn]
,COALESCE(cvw.[isInactive],1) as [isInactive]
,1 as [isConfirmed]
,NULL as [systemReceivedDate]
,NULL as [systemReceived]
,a.agencyid as [agencyId]
,NULL as [donorInvoiceDate]
,0 as [isBeingConfirmed]
,NULL as [confirmingStart]
,NULL as [confirmingEnd]
,0 as [isBeingConvertedToOrder]
,NULL as [convertingToOrderStart]
,NULL as [convertingToOrderEnd]
,7 as [dataSourceId]
,mc.postingDate as [RowCreateDateTime]
FROM (
SELECT DISTINCT *
FROM [staging].[source_mealconnect]
) mc
LEFT OUTER JOIN staging.contributor_vw cvw
ON mc.donorCode = cvw.referenceCode
LEFT OUTER JOIN dbo.agency a
ON mc.agencyCode = a.referenceCode
WHERE cvw.referenceCode IS NOT NULL
) as src
ON tgt.receiptid = src.receiptid
WHEN MATCHED AND src.[PickupDate] >= dateadd(month,-3,GetDate()) AND tgt.datasourceid = src.datasourceid
THEN
UPDATE
SET [referenceCode] = src.[referenceCode]
,[contributorId] = src.[contributorId]
,[contributorAddressId] = src.[contributorAddressId]
,[contributorContactId] = src.[contributorContactId]
,[warehouseId] = src.[warehouseId]
,[systemEntryOriginId] = src.[systemEntryOriginId]
,[donationTypeId] = src.[donationTypeId]
,[systemReceiptTypeId] = src.[systemReceiptTypeId]
,[isDelivery] = src.[isDelivery]
,[originationId] = src.[originationId]
,[originCity] = src.[originCity]
,[comment] = src.[comment]
,[pickupDate] = src.[pickupDate]
,[approved] = src.[approved]
,[isRetailPickup] = src.[isRetailPickup]
,[isFoodDrive] = src.[isFoodDrive]
,[confirmedDate] = src.[confirmedDate]
,[submissionDate] = src.[submissionDate]
,[modifiedOn] = src.[modifiedOn]
,[inactiveOn] = src.[inactiveOn]
,[isInactive] = src.[isInactive]
,[isConfirmed] = src.[isConfirmed]
,[systemReceivedDate] = src.[systemReceivedDate]
,[systemReceived] = src.[systemReceived]
,[agencyId] = src.[agencyId]
,[donorInvoiceDate] = src.[donorInvoiceDate]
,[isBeingConfirmed] = src.[isBeingConfirmed]
,[confirmingStart] = src.[confirmingStart]
,[confirmingEnd] = src.[confirmingEnd]
,[isBeingConvertedToOrder] = src.[isBeingConvertedToOrder]
,[convertingToOrderStart] = src.[convertingToOrderStart]
,[convertingToOrderEnd] = src.[convertingToOrderEnd]
,[dataSourceId] = src.[dataSourceId]
,[RowCreateDateTime] = src.[RowCreateDateTime]
WHEN NOT MATCHED
THEN
INSERT
(
[receiptId]
,[referenceCode]
,[contributorId]
,[contributorAddressId]
,[contributorContactId]
,[warehouseId]
,[systemEntryOriginId]
,[donationTypeId]
,[systemReceiptTypeId]
,[isDelivery]
,[originationId]
,[originCity]
,[comment]
,[pickupDate]
,[approved]
,[isRetailPickup]
,[isFoodDrive]
,[confirmedDate]
,[submissionDate]
,[modifiedOn]
,[inactiveOn]
,[isInactive]
,[isConfirmed]
,[systemReceivedDate]
,[systemReceived]
,[agencyId]
,[donorInvoiceDate]
,[isBeingConfirmed]
,[confirmingStart]
,[confirmingEnd]
,[isBeingConvertedToOrder]
,[convertingToOrderStart]
,[convertingToOrderEnd]
,[dataSourceId]
,[RowCreateDateTime]
)
VALUES
(src.[receiptId]
, src.[referenceCode]
, src.[contributorId]
, src.[contributorAddressId]
, src.[contributorContactId]
, src.[warehouseId]
, src.[systemEntryOriginId]
, src.[donationTypeId]
, src.[systemReceiptTypeId]
, src.[isDelivery]
, src.[originationId]
, src.[originCity]
, src.[comment]
, src.[pickupDate]
, src.[approved]
, src.[isRetailPickup]
, src.[isFoodDrive]
, src.[confirmedDate]
, src.[submissionDate]
, src.[modifiedOn]
, src.[inactiveOn]
, src.[isInactive]
, src.[isConfirmed]
, src.[systemReceivedDate]
, src.[systemReceived]
, src.[agencyId]
, src.[donorInvoiceDate]
, src.[isBeingConfirmed]
, src.[confirmingStart]
, src.[confirmingEnd]
, src.[isBeingConvertedToOrder]
, src.[convertingToOrderStart]
, src.[convertingToOrderEnd]
, src.[dataSourceId]
, src.[RowCreateDateTime]
)
OUTPUT
$action as outputType
,inserted.receiptid
,inserted.DataSourceid
INTO @MyTableVar
;
/* Add Missing Contributor id record for each YearMonth
Pre-step remove all records within last 6 months where [comment] = 'Missing Contributor id record for YearMonth'
a) For last 6 months, get contributor, year month, and a count from existing data
b) Then, get all contributors from mappings.fresh_alliance_localization_tracker
and cross-join year month for last 6 months with 0 as rowCnt
c) Union a and b
d) roll up rowcounts and then any with a 0 (zero) rowcnt is left
e) align fields
f) insert into staging.contributor_vw
*/
DELETE rdvw
FROM staging.receiptDetail_vw rdvw
JOIN staging.receipt_vw rvw
ON rdvw.receiptid = rvw.receiptid
WHERE [comment] = 'Missing Contributor id record for YearMonth'
AND PickupDate >= DATEADD(Month,-6,GetDate())
DELETE
FROM staging.receipt_vw
WHERE [comment] = 'Missing Contributor id record for YearMonth'
AND PickupDate >= DATEADD(Month,-6,GetDate())
IF OBJECT_ID('tempdb..#Staging_receipt_vw') IS NOT NULL DROP TABLE #Staging_receipt_vw
;WITH contYearMonth as
(
SELECT
contributorid
,FORMAT(pickupDate,'yyyyMM') as pickupYearMonthNumber
,COUNT(ReceiptID) as rowCnt
FROM staging.receipt_vw rvw
WHERE pickupDate >= DATEADD(MONTH,-6,GETDATE())
GROUP BY
contributorid
,FORMAT(pickupDate,'yyyyMM')
), crossYearMonth as
(
SELECT DISTINCT
--lt.Donor_Ref
--,lt.Donor_Name
cvw.contributorid
,ym.YearMonth
,rowCnt = 0
FROM mappings.fresh_alliance_localization_tracker lt
LEFT OUTER JOIN staging.contributor_vw cvw
ON cvw.referenceCode = lt.Donor_Ref
CROSS JOIN
(
SELECT DISTINCT
FORMAT(pickupDate,'yyyyMM') YearMonth
FROM staging.receipt_vw rvw
WHERE pickupDate >= DATEADD(MONTH,-6,GETDATE())
) ym
), UnionALL as
(
SELECT * FROM contYearMonth
UNION ALL
SELECT * FROM crossYearMonth
)
, FindZerorecords as (
SELECT
contributorid
,pickupYearMonthNumber
,SUM(rowCnt) as rowCnt
FROM UnionALL
Group BY
contributorid
,pickupYearMonthNumber
HAVING
Sum(rowCnt) = 0
)
SELECT
try_CAST(Concat(fzr.pickupYearMonthNumber,fzr.contributorid) as bigint) as [receiptId]
,'Unknown' as [referenceCode]
,COALESCE(cvw.contributorId,0) as [contributorId]
,COALESCE(cvw.[mainAddressId],0) as [contributorAddressId]
,COALESCE(cvw.[mainContactId],0) as [contributorContactId]
,0 as [warehouseId]
,0 as [systemEntryOriginId]
,COALESCE(cvw.[defaultdonationTypeId],0) as [donationTypeId]
,0 as [systemReceiptTypeId]
,CAST(0 as bit) as [isDelivery]
,COALESCE(cvw.[defaultoriginationId],0) as [originationId]
,CAST(NULL as varchar(100)) as [originCity]
,'Missing Contributor id record for YearMonth' as [comment]
,DATEFROMPARTS(left(fzr.pickupYearMonthNumber,4),right(pickupYearMonthNumber,2),'01') as PickupDate
,CAST(0 as bit) as [approved]
,CAST(COALESCE(cvw.[isRetailPickup],0) as bit) as [isRetailPickup]
,CAST(COALESCE(cvw.[isFoodDrive],0) as bit) as [isFoodDrive]
,CAST(NULL as DATE) as [confirmedDate]
,DATEFROMPARTS(left(fzr.pickupYearMonthNumber,4),right(pickupYearMonthNumber,2),'01') as [submissionDate]
,CAST(NULL as DATE) as [modifiedOn]
,CAST(NULL as DATE)as [inactiveOn]
,CAST(COALESCE(cvw.[isInactive],1) as bit) as [isInactive]
,CAST(0 as bit) as [isConfirmed]
,CAST(NULL as DATE) as [systemReceivedDate]
,CAST(NULL as bit) as [systemReceived]
,CAST(NULL as int) as [agencyId]
,CAST(NULL as DATE) as [donorInvoiceDate]
,CAST(0 as bit) as [isBeingConfirmed]
,CAST(NULL as DATETIME) as [confirmingStart]
,CAST(NULL as DATETIME) as [confirmingEnd]
,CAST(0 as bit) as [isBeingConvertedToOrder]
,CAST(NULL as DATETIME) as [convertingToOrderStart]
,CAST(NULL as DATETIME) as [convertingToOrderEnd]
,CAST(0 as int) as [dataSourceId]
,GetDate() as [RowCreateDateTime]
INTO #Staging_receipt_vw --staging.receipt_vw
FROM FindZerorecords fzr
LEFT OUTER JOIN staging.contributor_vw cvw
ON fzr.contributorid = cvw.contributorid
WHERE fzr.contributorid IS NOT NULL
INSERT INTO staging.receipt_vw
(
[receiptId]
,[referenceCode]
,[contributorId]
,[contributorAddressId]
,[contributorContactId]
,[warehouseId]
,[systemEntryOriginId]
,[donationTypeId]
,[systemReceiptTypeId]
,[isDelivery]
,[originationId]
,[originCity]
,[comment]
,[pickupDate]
,[approved]
,[isRetailPickup]
,[isFoodDrive]
,[confirmedDate]
,[submissionDate]
,[modifiedOn]
,[inactiveOn]
,[isInactive]
,[isConfirmed]
,[systemReceivedDate]
,[systemReceived]
,[agencyId]
,[donorInvoiceDate]
,[isBeingConfirmed]
,[confirmingStart]
,[confirmingEnd]
,[isBeingConvertedToOrder]
,[convertingToOrderStart]
,[convertingToOrderEnd]
,[dataSourceId]
,[RowCreateDateTime]
)
SELECT
[receiptId]
,[referenceCode]
,[contributorId]
,[contributorAddressId]
,[contributorContactId]
,[warehouseId]
,[systemEntryOriginId]
,[donationTypeId]
,[systemReceiptTypeId]
,[isDelivery]
,[originationId]
,[originCity]
,[comment]
,[pickupDate]
,[approved]
,[isRetailPickup]
,[isFoodDrive]
,[confirmedDate]
,[submissionDate]
,[modifiedOn]
,[inactiveOn]
,[isInactive]
,[isConfirmed]
,[systemReceivedDate]
,[systemReceived]
,[agencyId]
,[donorInvoiceDate]
,[isBeingConfirmed]
,[confirmingStart]
,[confirmingEnd]
,[isBeingConvertedToOrder]
,[convertingToOrderStart]
,[convertingToOrderEnd]
,[dataSourceId]
,[RowCreateDateTime]
FROM #Staging_receipt_vw
INSERT INTO @MyTableVar
([Action],receiptid,DataSourceid)
SELECT
'INSERT' as outputType
,receiptid
,DataSourceid
FROM #Staging_receipt_vw
INSERT INTO [log].data_event
([origin], event_type, [description],run_id)
SELECT
ds.[name] as [origin]
,[Action] as event_type
,CONCAT(Count(*),' rows ',[action],'ED to staging.receipt_vw') as [description]
,NULL as [run_id]
FROM @MyTableVar mt
LEFT OUTER JOIN [dbo].[dataSource] ds
ON ds.dataSourceid = mt.dataSourceid
GROUP BY
ds.[name]
,[Action]
Exec [staging].[update_receiptDetail_vw]
IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test
/* Mark PRE Receipt records with Duplicates and other issues*/
;WITH find_dups as
(
SELECT DISTINCT
agencyid
,contributorid
,pickupdate
,rank_basic = row_number() OVER(PARTITION BY rvw.agencyid, rvw.contributorid, rvw.pickupDate ORDER BY rvw.agencyid, rvw.contributorid, rvw.pickupDate)
FROM staging.receipt_vw rvw
LEFT OUTER JOIN [dbo].[systemReceiptType] srt
on rvw.systemReceiptTypeId = srt.systemReceiptTypeid
WHERE rvw.pickupDate >= DATEADD(MONTH,-6,GETDATE())
AND lower(srt.[name]) = 'prereceipt'
), get_distinct_dups as
(
SELECT
agencyid
,contributorid
,pickupdate
from find_dups
WHERE rank_basic > 1
), add_receiptid as
(
SELECT
rvw.receiptId
,rvw.agencyid
,rvw.contributorId
,rvw.pickupDate
FROM staging.receipt_vw rvw
--INNER JOIN get_distinct_dups ddup
-- ON rvw.agencyId = ddup.agencyId
-- AND rvw.contributorId = ddup.contributorId
-- AND rvw.pickupDate = ddup.pickupDate
LEFT OUTER JOIN [dbo].[systemReceiptType] srt
on rvw.systemReceiptTypeId = srt.systemReceiptTypeid
WHERE rvw.pickupDate >= DATEADD(MONTH,-6,GETDATE())
AND lower(srt.[name]) = 'prereceipt'
), Add_RDetails as
(
SELECT
ar.receiptId
,ar.agencyId
,ar.contributorId
,ar.pickupDate
,STRING_AGG(CAST(rdvw.productId as varchar(50)) ,', ') WITHIN GROUP(ORDER BY rdvw.productId ASC) as AggProductIDs
,SUM(rdvw.requestedWeight) as totalRequestedWeight
, rwTooLarge
FROM add_receiptid ar
LEFT JOIN (
SELECT
receiptid
,productid
,SUM(requestedWeight) as requestedWeight
,iif(sum(requestedWeight) > 1500,1,0) as rwTooLarge
FROM staging.receiptDetail_vw
GROUP BY
receiptid
,productid
) rdvw
ON ar.receiptId = rdvw.receiptId
WHERE rdvw.productId IS NOT NULL
GROUP BY
ar.receiptId
,ar.agencyId
,ar.contributorId
,ar.pickupDate
,rwTooLarge
), ranking as
(
SELECT
receiptid
,agencyid
,contributorid
,pickupDate
,AggProductIDs
,totalRequestedWeight
,GroupCount = DENSE_RANK() OVER(ORDER BY pickupDate, agencyid, contributorid)
,COUNT(*) OVER(PARTITION BY agencyid, contributorid, pickupDate) as dup_count
,ROW_NUMBER() OVER(PARTITION BY agencyid, contributorid, pickupDate ORDER BY receiptID ASC) as BasicDuplicateRow
,COUNT(*) OVER(PARTITION BY agencyid, contributorid, pickupDate, totalRequestedWeight) as NonProduct_dup_count
,ROW_NUMBER() OVER(PARTITION BY agencyid, contributorid, pickupDate, totalRequestedWeight ORDER BY receiptID ASC) as NonProductDuplicateRow
,COUNT(*) OVER(PARTITION BY agencyid, contributorid, pickupDate, AggProductIDs, totalRequestedWeight) as Fully_dup_count
,ROW_NUMBER() OVER(PARTITION BY agencyid, contributorid, pickupDate, AggProductIDs, totalRequestedWeight ORDER BY receiptID DESC) as FullyDuplicateRow
,COUNT(*) OVER(PARTITION BY agencyid, pickupDate, totalRequestedWeight) as nonContributorDuplicate
,COUNT(*) OVER(PARTITION BY agencyid, contributorid, totalRequestedWeight) as nonPickupDateDuplicate
,rwTooLarge
FROM Add_RDetails ard
)
SELECT
*
, CASE
WHEN totalRequestedWeight = 0 THEN 'Total Received Weight = 0; Color = Yellow'
WHEN rwTooLarge = 1 THEN 'Individual Product Received Weight > 1500; Color = Red'
WHEN dup_count = FullyDuplicateRow AND dup_count > 1 THEN 'Fully Duplicate Primary; Color = Green'
WHEN dup_count = Fully_dup_count AND FullyDuplicateRow < Fully_dup_count AND dup_count > 1 THEN 'Fully Duplicate Alternate; Color = Red'
WHEN dup_count = NonProduct_dup_count AND NonProduct_dup_count = NonProductDuplicateRow AND dup_count > 1 THEN 'Non Product Duplicate Primary; Color = Green'
WHEN dup_count = NonProduct_dup_count AND NonProduct_dup_count > NonProductDuplicateRow AND dup_count > 1 THEN 'Non Product Duplicate Alternate; Color = Red'
WHEN dup_count > 1 THEN 'Basic Duplicate; Color = Yellow'
WHEN nonContributorDuplicate > 1 THEN 'Non Contributor Duplicate; COLOR = Yellow'
WHEN nonPickupDateDuplicate > 1 THEN 'Non PickupDate Duplicate; Color = Yellow'
ELSE NULL
END as [Status]
, CASE WHEN dup_count > 1 AND TotalRequestedWeight > 0 THEN 1 ELSE 0 END as DUPS
INTO #Test
FROM ranking
ORDER BY
GroupCount
,agencyid
,contributorid
,pickupDate
,receiptid
,AggProductIDs
,totalRequestedWeight
SELECT [status], * FROM #TEST
ORDER BY
GroupCount
,agencyid
,contributorid
,pickupDate
,receiptid
,AggProductIDs
,totalRequestedWeight
-- Reset Status and Dups
UPDATE [staging].[receipt_vw]
SET [Status] = NULL
,Dups = 0
-- Update Status and Dups
UPDATE rvw
SET [Status] = T.[Status]
,Dups = T.DUPS
FROM [staging].[receipt_vw] rvw
INNER JOIN #Test T
ON rvw.receiptId = T.receiptId
END
GO