{"id":3794,"date":"2026-04-24T17:32:07","date_gmt":"2026-04-24T17:32:07","guid":{"rendered":"https:\/\/jpbydesign.com\/?p=3794"},"modified":"2026-04-24T22:05:21","modified_gmt":"2026-04-24T22:05:21","slug":"sql-example-stored-procedure-update_receipt_vw","status":"publish","type":"post","link":"https:\/\/jpbydesign.com\/index.php\/2026\/04\/24\/sql-example-stored-procedure-update_receipt_vw\/","title":{"rendered":"SQL example Stored Procedure update_receipt_vw"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">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 &#8211; necessary for speed).  <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Once the merge is completed, clean-up steps are run:<br>Pre-step remove all records within last 6 months where [comment] = &#8216;Missing Contributor id record for YearMonth&#8217;<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li class=\"\">For last 6 months, get contributor, year month, and a count from existing data<\/li>\n\n\n\n<li class=\"\">Get all contributors from mappings.fresh_alliance_localization_tracker<br>and cross-join year month for last 6 months with 0 as rowCnt<\/li>\n\n\n\n<li class=\"\">Union items a and b (above)<\/li>\n\n\n\n<li class=\"\">roll up rowcounts and then any with a 0 (zero) rowcnt is left<\/li>\n\n\n\n<li class=\"\">align fields<\/li>\n\n\n\n<li class=\"\">insert into staging.contributor_vw<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img data-recalc-dims=\"1\" decoding=\"async\" width=\"1024\" height=\"526\" data-attachment-id=\"3791\" data-permalink=\"https:\/\/jpbydesign.com\/sql-seeklogo-webp\/\" data-orig-file=\"https:\/\/i0.wp.com\/jpbydesign.com\/wp-content\/uploads\/2026\/04\/sql-seeklogo.webp?fit=2000%2C1027&amp;ssl=1\" data-orig-size=\"2000,1027\" data-comments-opened=\"1\" data-image-title=\"sql-seeklogo.webp\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/i0.wp.com\/jpbydesign.com\/wp-content\/uploads\/2026\/04\/sql-seeklogo.webp?fit=1024%2C526&amp;ssl=1\" loading=\"lazy\" src=\"https:\/\/i0.wp.com\/jpbydesign.com\/wp-content\/uploads\/2026\/04\/sql-seeklogo.webp?resize=1024%2C526&#038;ssl=1\" alt=\"\" class=\"wp-image-3791\" style=\"aspect-ratio:1.9467727422733085;width:238px;height:auto\" srcset=\"https:\/\/i0.wp.com\/jpbydesign.com\/wp-content\/uploads\/2026\/04\/sql-seeklogo.webp?resize=1024%2C526&amp;ssl=1 1024w, https:\/\/i0.wp.com\/jpbydesign.com\/wp-content\/uploads\/2026\/04\/sql-seeklogo.webp?resize=300%2C154&amp;ssl=1 300w, https:\/\/i0.wp.com\/jpbydesign.com\/wp-content\/uploads\/2026\/04\/sql-seeklogo.webp?resize=768%2C394&amp;ssl=1 768w, https:\/\/i0.wp.com\/jpbydesign.com\/wp-content\/uploads\/2026\/04\/sql-seeklogo.webp?resize=1536%2C789&amp;ssl=1 1536w, https:\/\/i0.wp.com\/jpbydesign.com\/wp-content\/uploads\/2026\/04\/sql-seeklogo.webp?w=2000&amp;ssl=1 2000w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">SQL Code:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\n-- =======================================================\n-- Create Stored Procedure Template for Azure SQL Database\n-- =======================================================\nSET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n\nCREATE PROCEDURE staging.update_receipt_vw\n\/* \nCreated By\tJohn Horton\tLogicIntelligence\nCreated On\t11\/15\/2024\nPurpose\t\tUnion\/Append tables dbo.receipt, &#91;raw_freshalliance].&#91;ms_access_tblStoreData] - with referencecode (receiptRef) as null \n\t\t\t\t, &#91;staging].&#91;source_mealconnect].  Then we replace Agency for NULL or 0 values with data from &#91;mappings].&#91;P2_Receipt_Warehouse_to_AgencyId_Mapping] \n\t\t\t\tfor warehouseId's\nUpdated on\t12\/19\/2024\nPurpose\t\tto optimize, changed from view to stored procedure\n\t\t\t\t\t\tonly updating last 6 months of data\n*\/\n\nAS\nBEGIN\n\n\nDECLARE @MyTableVar TABLE (\n    &#91;Action] varchar(50)\n\t,receiptid BIGINT\n\t,DataSourceid int\n);\n\n\nMERGE  staging.receipt_vw as tgt\nUSING (\n\t\tSELECT  &#91;receiptId]\n\t\t\t  ,&#91;referenceCode]\n\t\t\t  ,&#91;contributorId]\n\t\t\t  ,&#91;contributorAddressId]\n\t\t\t  ,&#91;contributorContactId]\n\t\t\t  ,&#91;warehouseId]\n\t\t\t  ,&#91;systemEntryOriginId]\n\t\t\t  ,&#91;donationTypeId]\n\t\t\t  ,&#91;systemReceiptTypeId]\n\t\t\t  ,&#91;isDelivery]\n\t\t\t  ,&#91;originationId]\n\t\t\t  ,&#91;originCity]\n\t\t\t  ,&#91;comment]\n\t\t\t  ,&#91;pickupDate]\n\t\t\t  ,&#91;approved]\n\t\t\t  ,&#91;isRetailPickup]\n\t\t\t  ,&#91;isFoodDrive]\n\t\t\t  ,&#91;confirmedDate]\n\t\t\t  ,&#91;submissionDate]\n\t\t\t  ,&#91;modifiedOn]\n\t\t\t  ,&#91;inactiveOn]\n\t\t\t  ,&#91;isInactive]\n\t\t\t  ,&#91;isConfirmed]\n\t\t\t  ,&#91;systemReceivedDate]\n\t\t\t  ,&#91;systemReceived]\n\t\t\t  ,&#91;agencyId]\n\t\t\t  ,&#91;donorInvoiceDate]\n\t\t\t  ,&#91;isBeingConfirmed]\n\t\t\t  ,&#91;confirmingStart]\n\t\t\t  ,&#91;confirmingEnd]\n\t\t\t  ,&#91;isBeingConvertedToOrder]\n\t\t\t  ,&#91;convertingToOrderStart]\n\t\t\t  ,&#91;convertingToOrderEnd]\n\t\t\t  ,&#91;dataSourceId]\n\t\t\t  ,&#91;RowCreateDateTime]\n\t\t  FROM &#91;dbo].&#91;receipt]\n) as src\n\tON tgt.receiptid = src.receiptid\nWHEN MATCHED AND src.&#91;modifiedOn] >= dateadd(month,-3,GetDate())\n\tTHEN \n\t\tUPDATE\n\t\tSET &#91;referenceCode] = src.&#91;referenceCode]\n\t\t\t,&#91;contributorId] = src.&#91;contributorId]\n\t\t\t,&#91;contributorAddressId] = src.&#91;contributorAddressId]\n\t\t\t,&#91;contributorContactId] = src.&#91;contributorContactId]\n\t\t\t,&#91;warehouseId] = src.&#91;warehouseId]\n\t\t\t,&#91;systemEntryOriginId] = src.&#91;systemEntryOriginId]\n\t\t\t,&#91;donationTypeId] = src.&#91;donationTypeId]\n\t\t\t,&#91;systemReceiptTypeId] = src.&#91;systemReceiptTypeId]\n\t\t\t,&#91;isDelivery] = src.&#91;isDelivery]\n\t\t\t,&#91;originationId] = src.&#91;originationId]\n\t\t\t,&#91;originCity] = src.&#91;originCity]\n\t\t\t,&#91;comment] = src.&#91;comment]\n\t\t\t,&#91;pickupDate] = src.&#91;pickupDate]\n\t\t\t,&#91;approved] = src.&#91;approved]\n\t\t\t,&#91;isRetailPickup] = src.&#91;isRetailPickup]\n\t\t\t,&#91;isFoodDrive] = src.&#91;isFoodDrive]\n\t\t\t,&#91;confirmedDate] = src.&#91;confirmedDate]\n\t\t\t,&#91;submissionDate] = src.&#91;submissionDate]\n\t\t\t,&#91;modifiedOn] = src.&#91;modifiedOn]\n\t\t\t,&#91;inactiveOn] = src.&#91;inactiveOn]\n\t\t\t,&#91;isInactive] = src.&#91;isInactive]\n\t\t\t,&#91;isConfirmed] = src.&#91;isConfirmed]\n\t\t\t,&#91;systemReceivedDate] = src.&#91;systemReceivedDate]\n\t\t\t,&#91;systemReceived] = src.&#91;systemReceived]\n\t\t\t,&#91;agencyId] = src.&#91;agencyId]\n\t\t\t,&#91;donorInvoiceDate] = src.&#91;donorInvoiceDate]\n\t\t\t,&#91;isBeingConfirmed] = src.&#91;isBeingConfirmed]\n\t\t\t,&#91;confirmingStart] = src.&#91;confirmingStart]\n\t\t\t,&#91;confirmingEnd] = src.&#91;confirmingEnd]\n\t\t\t,&#91;isBeingConvertedToOrder] = src.&#91;isBeingConvertedToOrder]\n\t\t\t,&#91;convertingToOrderStart] = src.&#91;convertingToOrderStart]\n\t\t\t,&#91;convertingToOrderEnd] = src.&#91;convertingToOrderEnd]\n\t\t\t,&#91;dataSourceId] = src.&#91;dataSourceId]\n\t\t\t,&#91;RowCreateDateTime] = src.&#91;RowCreateDateTime]\n\nWHEN NOT MATCHED\n\tTHEN\n\t\tINSERT\n\t\t\t(\n\t\t\t&#91;receiptId]\n\t\t\t,&#91;referenceCode]\n\t\t\t,&#91;contributorId]\n\t\t\t,&#91;contributorAddressId]\n\t\t\t,&#91;contributorContactId]\n\t\t\t,&#91;warehouseId]\n\t\t\t,&#91;systemEntryOriginId]\n\t\t\t,&#91;donationTypeId]\n\t\t\t,&#91;systemReceiptTypeId]\n\t\t\t,&#91;isDelivery]\n\t\t\t,&#91;originationId]\n\t\t\t,&#91;originCity]\n\t\t\t,&#91;comment]\n\t\t\t,&#91;pickupDate]\n\t\t\t,&#91;approved]\n\t\t\t,&#91;isRetailPickup]\n\t\t\t,&#91;isFoodDrive]\n\t\t\t,&#91;confirmedDate]\n\t\t\t,&#91;submissionDate]\n\t\t\t,&#91;modifiedOn]\n\t\t\t,&#91;inactiveOn]\n\t\t\t,&#91;isInactive]\n\t\t\t,&#91;isConfirmed]\n\t\t\t,&#91;systemReceivedDate]\n\t\t\t,&#91;systemReceived]\n\t\t\t,&#91;agencyId]\n\t\t\t,&#91;donorInvoiceDate]\n\t\t\t,&#91;isBeingConfirmed]\n\t\t\t,&#91;confirmingStart]\n\t\t\t,&#91;confirmingEnd]\n\t\t\t,&#91;isBeingConvertedToOrder]\n\t\t\t,&#91;convertingToOrderStart]\n\t\t\t,&#91;convertingToOrderEnd]\n\t\t\t,&#91;dataSourceId]\n\t\t\t,&#91;RowCreateDateTime]\n\t\t\t)\n\t\t\tVALUES\n\t\t\t(src.&#91;receiptId]\n\t\t\t, src.&#91;referenceCode]\n\t\t\t, src.&#91;contributorId]\n\t\t\t, src.&#91;contributorAddressId]\n\t\t\t, src.&#91;contributorContactId]\n\t\t\t, src.&#91;warehouseId]\n\t\t\t, src.&#91;systemEntryOriginId]\n\t\t\t, src.&#91;donationTypeId]\n\t\t\t, src.&#91;systemReceiptTypeId]\n\t\t\t, src.&#91;isDelivery]\n\t\t\t, src.&#91;originationId]\n\t\t\t, src.&#91;originCity]\n\t\t\t, src.&#91;comment]\n\t\t\t, src.&#91;pickupDate]\n\t\t\t, src.&#91;approved]\n\t\t\t, src.&#91;isRetailPickup]\n\t\t\t, src.&#91;isFoodDrive]\n\t\t\t, src.&#91;confirmedDate]\n\t\t\t, src.&#91;submissionDate]\n\t\t\t, src.&#91;modifiedOn]\n\t\t\t, src.&#91;inactiveOn]\n\t\t\t, src.&#91;isInactive]\n\t\t\t, src.&#91;isConfirmed]\n\t\t\t, src.&#91;systemReceivedDate]\n\t\t\t, src.&#91;systemReceived]\n\t\t\t, src.&#91;agencyId]\n\t\t\t, src.&#91;donorInvoiceDate]\n\t\t\t, src.&#91;isBeingConfirmed]\n\t\t\t, src.&#91;confirmingStart]\n\t\t\t, src.&#91;confirmingEnd]\n\t\t\t, src.&#91;isBeingConvertedToOrder]\n\t\t\t, src.&#91;convertingToOrderStart]\n\t\t\t, src.&#91;convertingToOrderEnd]\n\t\t\t, src.&#91;dataSourceId]\n\t\t\t, src.&#91;RowCreateDateTime]\n\t\t\t)\nOUTPUT\n\t$action as outputType\n\t,inserted.receiptid\n\t,inserted.DataSourceid\nINTO @MyTableVar \n;\n\n-- &#91;raw_freshalliance].&#91;ms_access_tblStoreData]  - Just adding new id's\nMERGE  staging.receipt_vw as tgt\nUSING (\n\t\t\t\tSELECT  tsd.&#91;UniqueID] as &#91;receiptId]\n\t\t\t  ,COALESCE(&#91;ReceiptRef],'Unknown') as &#91;referenceCode]\n\t\t\t  ,COALESCE(cvw.contributorId,0) as &#91;contributorId]\n\t\t\t  ,COALESCE(cvw.&#91;mainAddressId],0) as &#91;contributorAddressId]\n\t\t\t  ,COALESCE(cvw.&#91;mainContactId],0) as &#91;contributorContactId]\n\t\t\t  ,0 as &#91;warehouseId]\n\t\t\t  ,0 as &#91;systemEntryOriginId] \n\t\t\t  ,COALESCE(cvw.&#91;defaultdonationTypeId],0) as &#91;donationTypeId] \n\t\t\t  ,0 as &#91;systemReceiptTypeId] \n\t\t\t  ,0 as &#91;isDelivery]\n\t\t\t  ,COALESCE(cvw.&#91;defaultoriginationId],0) as &#91;originationId]\n\t\t\t  ,NULL as &#91;originCity]\n\t\t\t  ,NULL as &#91;comment]\n\t\t\t  ,tsd.&#91;pickupDate]\n\t\t\t  ,1 as &#91;approved]\n\t\t\t  ,COALESCE(cvw.&#91;isRetailPickup],0) as &#91;isRetailPickup]\n\t\t\t  ,COALESCE(cvw.&#91;isFoodDrive],0) as &#91;isFoodDrive]\n\t\t\t  ,NULL as &#91;confirmedDate]\n\t\t\t  ,NULL as &#91;submissionDate]\n\t\t\t  ,NULL as &#91;modifiedOn]\n\t\t\t  ,NULL as &#91;inactiveOn]\n\t\t\t  ,COALESCE(cvw.&#91;isInactive],1) as &#91;isInactive]\n\t\t\t  ,1 as &#91;isConfirmed]\n\t\t\t  ,NULL as &#91;systemReceivedDate]\n\t\t\t  ,NULL as &#91;systemReceived]\n\t\t\t  ,a.agencyid as &#91;agencyId]\n\t\t\t  ,NULL as &#91;donorInvoiceDate]\n\t\t\t  ,0 as &#91;isBeingConfirmed]\n\t\t\t  ,NULL as &#91;confirmingStart]\n\t\t\t  ,NULL as &#91;confirmingEnd]\n\t\t\t  ,0 as &#91;isBeingConvertedToOrder]\n\t\t\t  ,NULL as &#91;convertingToOrderStart]\n\t\t\t  ,NULL as &#91;convertingToOrderEnd]\n\t\t\t  ,6 as &#91;dataSourceId]\n\t\t\t  ,GetDate() as &#91;RowCreateDateTime]\n\t\t  FROM &#91;raw_freshalliance].&#91;ms_access_tblStoreData] tsd\n\t\t  LEFT OUTER JOIN staging.contributor_vw cvw\n\t\t\tON tsd.DonorRef = cvw.referenceCode\n\t\t  LEFT OUTER JOIN dbo.agency a\n\t\t\tON tsd.AgencyRef = a.referenceCode\n\t\t  LEFT OUTER JOIN dbo.receipt r\n\t\t\tON r.receiptId = tsd.UniqueID\n\t\t  WHERE tsd.ReceiptRef is NULL\n\t\t  AND cvw.referenceCode IS NOT NULL\n\t\t  AND r.receiptId IS NULL\n) as src\n\tON tgt.receiptid = src.receiptid\n\nWHEN NOT MATCHED\n\tTHEN\n\t\tINSERT\n\t\t\t(\n\t\t\t&#91;receiptId]\n\t\t\t,&#91;referenceCode]\n\t\t\t,&#91;contributorId]\n\t\t\t,&#91;contributorAddressId]\n\t\t\t,&#91;contributorContactId]\n\t\t\t,&#91;warehouseId]\n\t\t\t,&#91;systemEntryOriginId]\n\t\t\t,&#91;donationTypeId]\n\t\t\t,&#91;systemReceiptTypeId]\n\t\t\t,&#91;isDelivery]\n\t\t\t,&#91;originationId]\n\t\t\t,&#91;originCity]\n\t\t\t,&#91;comment]\n\t\t\t,&#91;pickupDate]\n\t\t\t,&#91;approved]\n\t\t\t,&#91;isRetailPickup]\n\t\t\t,&#91;isFoodDrive]\n\t\t\t,&#91;confirmedDate]\n\t\t\t,&#91;submissionDate]\n\t\t\t,&#91;modifiedOn]\n\t\t\t,&#91;inactiveOn]\n\t\t\t,&#91;isInactive]\n\t\t\t,&#91;isConfirmed]\n\t\t\t,&#91;systemReceivedDate]\n\t\t\t,&#91;systemReceived]\n\t\t\t,&#91;agencyId]\n\t\t\t,&#91;donorInvoiceDate]\n\t\t\t,&#91;isBeingConfirmed]\n\t\t\t,&#91;confirmingStart]\n\t\t\t,&#91;confirmingEnd]\n\t\t\t,&#91;isBeingConvertedToOrder]\n\t\t\t,&#91;convertingToOrderStart]\n\t\t\t,&#91;convertingToOrderEnd]\n\t\t\t,&#91;dataSourceId]\n\t\t\t,&#91;RowCreateDateTime]\n\t\t\t)\n\t\t\tVALUES\n\t\t\t(src.&#91;receiptId]\n\t\t\t, src.&#91;referenceCode]\n\t\t\t, src.&#91;contributorId]\n\t\t\t, src.&#91;contributorAddressId]\n\t\t\t, src.&#91;contributorContactId]\n\t\t\t, src.&#91;warehouseId]\n\t\t\t, src.&#91;systemEntryOriginId]\n\t\t\t, src.&#91;donationTypeId]\n\t\t\t, src.&#91;systemReceiptTypeId]\n\t\t\t, src.&#91;isDelivery]\n\t\t\t, src.&#91;originationId]\n\t\t\t, src.&#91;originCity]\n\t\t\t, src.&#91;comment]\n\t\t\t, src.&#91;pickupDate]\n\t\t\t, src.&#91;approved]\n\t\t\t, src.&#91;isRetailPickup]\n\t\t\t, src.&#91;isFoodDrive]\n\t\t\t, src.&#91;confirmedDate]\n\t\t\t, src.&#91;submissionDate]\n\t\t\t, src.&#91;modifiedOn]\n\t\t\t, src.&#91;inactiveOn]\n\t\t\t, src.&#91;isInactive]\n\t\t\t, src.&#91;isConfirmed]\n\t\t\t, src.&#91;systemReceivedDate]\n\t\t\t, src.&#91;systemReceived]\n\t\t\t, src.&#91;agencyId]\n\t\t\t, src.&#91;donorInvoiceDate]\n\t\t\t, src.&#91;isBeingConfirmed]\n\t\t\t, src.&#91;confirmingStart]\n\t\t\t, src.&#91;confirmingEnd]\n\t\t\t, src.&#91;isBeingConvertedToOrder]\n\t\t\t, src.&#91;convertingToOrderStart]\n\t\t\t, src.&#91;convertingToOrderEnd]\n\t\t\t, src.&#91;dataSourceId]\n\t\t\t, src.&#91;RowCreateDateTime]\n\t\t\t)\nOUTPUT\n\t$action as outputType\n\t,inserted.receiptid\n\t,inserted.DataSourceid\nINTO @MyTableVar \n;\n\n-- &#91;staging].&#91;source_mealconnect] \nMERGE  staging.receipt_vw as tgt\nUSING (\n\t\t  SELECT  CAST(CONCAT('99',mc.id,mc.itemId) as bigint) as &#91;receiptId]\n\t\t\t  ,'Unknown' as &#91;referenceCode]\n\t\t\t  ,COALESCE(cvw.contributorId,0) as &#91;contributorId]\n\t\t\t  ,COALESCE(cvw.&#91;mainAddressId],0) as &#91;contributorAddressId]\n\t\t\t  ,COALESCE(cvw.&#91;mainContactId],0) as &#91;contributorContactId]\n\t\t\t  ,0 as &#91;warehouseId]\n\t\t\t  ,0 as &#91;systemEntryOriginId] \n\t\t\t  ,COALESCE(cvw.&#91;defaultdonationTypeId],0) as &#91;donationTypeId] \n\t\t\t  ,0 as &#91;systemReceiptTypeId] \n\t\t\t  ,0 as &#91;isDelivery]\n\t\t\t  ,COALESCE(cvw.&#91;defaultoriginationId],0) as &#91;originationId]\n\t\t\t  ,NULL as &#91;originCity]\n\t\t\t  ,NULL as &#91;comment]\n\t\t\t  ,mc.postingDate as &#91;pickupDate]\n\t\t\t  ,1 as &#91;approved]\n\t\t\t  ,COALESCE(cvw.&#91;isRetailPickup],0) as &#91;isRetailPickup]\n\t\t\t  ,COALESCE(cvw.&#91;isFoodDrive],0) as &#91;isFoodDrive]\n\t\t\t  ,NULL as &#91;confirmedDate]\n\t\t\t  ,NULL as &#91;submissionDate]\n\t\t\t  ,NULL as &#91;modifiedOn]\n\t\t\t  ,NULL as &#91;inactiveOn]\n\t\t\t  ,COALESCE(cvw.&#91;isInactive],1) as &#91;isInactive]\n\t\t\t  ,1 as &#91;isConfirmed]\n\t\t\t  ,NULL as &#91;systemReceivedDate]\n\t\t\t  ,NULL as &#91;systemReceived]\n\t\t\t  ,a.agencyid as &#91;agencyId]\n\t\t\t  ,NULL as &#91;donorInvoiceDate]\n\t\t\t  ,0 as &#91;isBeingConfirmed]\n\t\t\t  ,NULL as &#91;confirmingStart]\n\t\t\t  ,NULL as &#91;confirmingEnd]\n\t\t\t  ,0 as &#91;isBeingConvertedToOrder]\n\t\t\t  ,NULL as &#91;convertingToOrderStart]\n\t\t\t  ,NULL as &#91;convertingToOrderEnd]\n\t\t\t  ,7 as &#91;dataSourceId]\n\t\t\t  ,mc.postingDate as &#91;RowCreateDateTime]\n\t\t  FROM (\n\t\t\t\tSELECT DISTINCT *\n\t\t\t\tFROM &#91;staging].&#91;source_mealconnect] \n\t\t\t\t) mc\n\t\t  LEFT OUTER JOIN staging.contributor_vw cvw\n\t\t\tON mc.donorCode = cvw.referenceCode\n\t\t  LEFT OUTER JOIN dbo.agency a\n\t\t\tON mc.agencyCode = a.referenceCode\n\t\t  WHERE cvw.referenceCode IS NOT NULL\n) as src\n\tON tgt.receiptid = src.receiptid\nWHEN MATCHED AND src.&#91;PickupDate] >= dateadd(month,-3,GetDate()) AND tgt.datasourceid = src.datasourceid\n\tTHEN \n\t\tUPDATE\n\t\tSET &#91;referenceCode] = src.&#91;referenceCode]\n\t\t\t,&#91;contributorId] = src.&#91;contributorId]\n\t\t\t,&#91;contributorAddressId] = src.&#91;contributorAddressId]\n\t\t\t,&#91;contributorContactId] = src.&#91;contributorContactId]\n\t\t\t,&#91;warehouseId] = src.&#91;warehouseId]\n\t\t\t,&#91;systemEntryOriginId] = src.&#91;systemEntryOriginId]\n\t\t\t,&#91;donationTypeId] = src.&#91;donationTypeId]\n\t\t\t,&#91;systemReceiptTypeId] = src.&#91;systemReceiptTypeId]\n\t\t\t,&#91;isDelivery] = src.&#91;isDelivery]\n\t\t\t,&#91;originationId] = src.&#91;originationId]\n\t\t\t,&#91;originCity] = src.&#91;originCity]\n\t\t\t,&#91;comment] = src.&#91;comment]\n\t\t\t,&#91;pickupDate] = src.&#91;pickupDate]\n\t\t\t,&#91;approved] = src.&#91;approved]\n\t\t\t,&#91;isRetailPickup] = src.&#91;isRetailPickup]\n\t\t\t,&#91;isFoodDrive] = src.&#91;isFoodDrive]\n\t\t\t,&#91;confirmedDate] = src.&#91;confirmedDate]\n\t\t\t,&#91;submissionDate] = src.&#91;submissionDate]\n\t\t\t,&#91;modifiedOn] = src.&#91;modifiedOn]\n\t\t\t,&#91;inactiveOn] = src.&#91;inactiveOn]\n\t\t\t,&#91;isInactive] = src.&#91;isInactive]\n\t\t\t,&#91;isConfirmed] = src.&#91;isConfirmed]\n\t\t\t,&#91;systemReceivedDate] = src.&#91;systemReceivedDate]\n\t\t\t,&#91;systemReceived] = src.&#91;systemReceived]\n\t\t\t,&#91;agencyId] = src.&#91;agencyId]\n\t\t\t,&#91;donorInvoiceDate] = src.&#91;donorInvoiceDate]\n\t\t\t,&#91;isBeingConfirmed] = src.&#91;isBeingConfirmed]\n\t\t\t,&#91;confirmingStart] = src.&#91;confirmingStart]\n\t\t\t,&#91;confirmingEnd] = src.&#91;confirmingEnd]\n\t\t\t,&#91;isBeingConvertedToOrder] = src.&#91;isBeingConvertedToOrder]\n\t\t\t,&#91;convertingToOrderStart] = src.&#91;convertingToOrderStart]\n\t\t\t,&#91;convertingToOrderEnd] = src.&#91;convertingToOrderEnd]\n\t\t\t,&#91;dataSourceId] = src.&#91;dataSourceId]\n\t\t\t,&#91;RowCreateDateTime] = src.&#91;RowCreateDateTime]\n\nWHEN NOT MATCHED\n\tTHEN\n\t\tINSERT\n\t\t\t(\n\t\t\t&#91;receiptId]\n\t\t\t,&#91;referenceCode]\n\t\t\t,&#91;contributorId]\n\t\t\t,&#91;contributorAddressId]\n\t\t\t,&#91;contributorContactId]\n\t\t\t,&#91;warehouseId]\n\t\t\t,&#91;systemEntryOriginId]\n\t\t\t,&#91;donationTypeId]\n\t\t\t,&#91;systemReceiptTypeId]\n\t\t\t,&#91;isDelivery]\n\t\t\t,&#91;originationId]\n\t\t\t,&#91;originCity]\n\t\t\t,&#91;comment]\n\t\t\t,&#91;pickupDate]\n\t\t\t,&#91;approved]\n\t\t\t,&#91;isRetailPickup]\n\t\t\t,&#91;isFoodDrive]\n\t\t\t,&#91;confirmedDate]\n\t\t\t,&#91;submissionDate]\n\t\t\t,&#91;modifiedOn]\n\t\t\t,&#91;inactiveOn]\n\t\t\t,&#91;isInactive]\n\t\t\t,&#91;isConfirmed]\n\t\t\t,&#91;systemReceivedDate]\n\t\t\t,&#91;systemReceived]\n\t\t\t,&#91;agencyId]\n\t\t\t,&#91;donorInvoiceDate]\n\t\t\t,&#91;isBeingConfirmed]\n\t\t\t,&#91;confirmingStart]\n\t\t\t,&#91;confirmingEnd]\n\t\t\t,&#91;isBeingConvertedToOrder]\n\t\t\t,&#91;convertingToOrderStart]\n\t\t\t,&#91;convertingToOrderEnd]\n\t\t\t,&#91;dataSourceId]\n\t\t\t,&#91;RowCreateDateTime]\n\t\t\t)\n\t\t\tVALUES\n\t\t\t(src.&#91;receiptId]\n\t\t\t, src.&#91;referenceCode]\n\t\t\t, src.&#91;contributorId]\n\t\t\t, src.&#91;contributorAddressId]\n\t\t\t, src.&#91;contributorContactId]\n\t\t\t, src.&#91;warehouseId]\n\t\t\t, src.&#91;systemEntryOriginId]\n\t\t\t, src.&#91;donationTypeId]\n\t\t\t, src.&#91;systemReceiptTypeId]\n\t\t\t, src.&#91;isDelivery]\n\t\t\t, src.&#91;originationId]\n\t\t\t, src.&#91;originCity]\n\t\t\t, src.&#91;comment]\n\t\t\t, src.&#91;pickupDate]\n\t\t\t, src.&#91;approved]\n\t\t\t, src.&#91;isRetailPickup]\n\t\t\t, src.&#91;isFoodDrive]\n\t\t\t, src.&#91;confirmedDate]\n\t\t\t, src.&#91;submissionDate]\n\t\t\t, src.&#91;modifiedOn]\n\t\t\t, src.&#91;inactiveOn]\n\t\t\t, src.&#91;isInactive]\n\t\t\t, src.&#91;isConfirmed]\n\t\t\t, src.&#91;systemReceivedDate]\n\t\t\t, src.&#91;systemReceived]\n\t\t\t, src.&#91;agencyId]\n\t\t\t, src.&#91;donorInvoiceDate]\n\t\t\t, src.&#91;isBeingConfirmed]\n\t\t\t, src.&#91;confirmingStart]\n\t\t\t, src.&#91;confirmingEnd]\n\t\t\t, src.&#91;isBeingConvertedToOrder]\n\t\t\t, src.&#91;convertingToOrderStart]\n\t\t\t, src.&#91;convertingToOrderEnd]\n\t\t\t, src.&#91;dataSourceId]\n\t\t\t, src.&#91;RowCreateDateTime]\n\t\t\t)\nOUTPUT\n\t$action as outputType\n\t,inserted.receiptid\n\t,inserted.DataSourceid\nINTO @MyTableVar \n;\n\n\/*  Add Missing Contributor id record for each YearMonth\n\tPre-step remove all records within last 6 months where &#91;comment] = 'Missing Contributor id record for YearMonth'\n\ta) For last 6 months, get contributor, year month, and a count from existing data\n\tb) Then, get all contributors from mappings.fresh_alliance_localization_tracker \n\t\t\tand cross-join year month for last 6 months with 0 as rowCnt\n\tc)\tUnion a and b\n\td) roll up rowcounts and then any with a 0 (zero) rowcnt is left\n\te) align fields\n\tf) insert into staging.contributor_vw\n*\/\nDELETE rdvw\nFROM staging.receiptDetail_vw rdvw\nJOIN staging.receipt_vw rvw\n\tON rdvw.receiptid = rvw.receiptid\nWHERE &#91;comment] = 'Missing Contributor id record for YearMonth'\nAND PickupDate >= DATEADD(Month,-6,GetDate())\n\nDELETE\nFROM staging.receipt_vw\nWHERE &#91;comment] = 'Missing Contributor id record for YearMonth'\nAND PickupDate >= DATEADD(Month,-6,GetDate())\n\nIF OBJECT_ID('tempdb..#Staging_receipt_vw') IS NOT NULL DROP TABLE #Staging_receipt_vw\n;WITH contYearMonth as\n(\n\tSELECT \n\t\tcontributorid\n\t\t,FORMAT(pickupDate,'yyyyMM') as pickupYearMonthNumber\n\t\t,COUNT(ReceiptID) as rowCnt\n\tFROM staging.receipt_vw rvw\n\tWHERE pickupDate >= DATEADD(MONTH,-6,GETDATE())\n\tGROUP BY \n\t\tcontributorid\n\t\t,FORMAT(pickupDate,'yyyyMM') \n), crossYearMonth as \n(\n\t\tSELECT DISTINCT\n\t\t\t--lt.Donor_Ref\n\t\t\t--,lt.Donor_Name\n\t\t\tcvw.contributorid\n\t\t\t,ym.YearMonth\n\t\t\t,rowCnt = 0\n\t\tFROM mappings.fresh_alliance_localization_tracker lt\n\t\tLEFT OUTER JOIN staging.contributor_vw cvw\n\t\t\tON cvw.referenceCode = lt.Donor_Ref\n\t\tCROSS JOIN \n\t\t\t\t(\n\t\t\t\tSELECT DISTINCT\n\t\t\t\t\tFORMAT(pickupDate,'yyyyMM') YearMonth\n\t\t\t\tFROM staging.receipt_vw rvw\n\t\t\t\tWHERE pickupDate >= DATEADD(MONTH,-6,GETDATE())\n\t\t\t\t) ym \n\n), UnionALL as\n(\n\tSELECT * FROM contYearMonth\n\tUNION ALL\n\tSELECT * FROM crossYearMonth\n)\n, FindZerorecords as (\n\tSELECT\n\t\tcontributorid\n\t\t,pickupYearMonthNumber\n\t\t,SUM(rowCnt) as rowCnt\n\tFROM UnionALL\n\tGroup BY\n\t\tcontributorid\n\t\t,pickupYearMonthNumber\n\tHAVING \n\t\tSum(rowCnt) = 0\n)\nSELECT \n\t\t\t\ttry_CAST(Concat(fzr.pickupYearMonthNumber,fzr.contributorid) as bigint) as &#91;receiptId]\n\t\t\t  ,'Unknown' as &#91;referenceCode]\n\t\t\t  ,COALESCE(cvw.contributorId,0) as &#91;contributorId]\n\t\t\t  ,COALESCE(cvw.&#91;mainAddressId],0) as &#91;contributorAddressId]\n\t\t\t  ,COALESCE(cvw.&#91;mainContactId],0) as &#91;contributorContactId]\n\t\t\t  ,0 as &#91;warehouseId]\n\t\t\t  ,0 as &#91;systemEntryOriginId] \n\t\t\t  ,COALESCE(cvw.&#91;defaultdonationTypeId],0) as &#91;donationTypeId] \n\t\t\t  ,0 as &#91;systemReceiptTypeId] \n\t\t\t  ,CAST(0 as bit) as &#91;isDelivery]\n\t\t\t  ,COALESCE(cvw.&#91;defaultoriginationId],0) as &#91;originationId]\n\t\t\t  ,CAST(NULL as varchar(100)) as &#91;originCity]\n\t\t\t  ,'Missing Contributor id record for YearMonth' as &#91;comment]\n\t\t\t  ,DATEFROMPARTS(left(fzr.pickupYearMonthNumber,4),right(pickupYearMonthNumber,2),'01') as PickupDate \n\t\t\t  ,CAST(0 as bit) as &#91;approved]\n\t\t\t  ,CAST(COALESCE(cvw.&#91;isRetailPickup],0) as bit) as &#91;isRetailPickup]\n\t\t\t  ,CAST(COALESCE(cvw.&#91;isFoodDrive],0) as bit) as &#91;isFoodDrive]\n\t\t\t  ,CAST(NULL as DATE) as &#91;confirmedDate]\n\t\t\t  ,DATEFROMPARTS(left(fzr.pickupYearMonthNumber,4),right(pickupYearMonthNumber,2),'01') as &#91;submissionDate]\n\t\t\t  ,CAST(NULL as DATE) as &#91;modifiedOn]\n\t\t\t  ,CAST(NULL as DATE)as &#91;inactiveOn]\n\t\t\t  ,CAST(COALESCE(cvw.&#91;isInactive],1) as bit) as &#91;isInactive]\n\t\t\t  ,CAST(0 as bit) as &#91;isConfirmed]\n\t\t\t  ,CAST(NULL as DATE) as &#91;systemReceivedDate]\n\t\t\t  ,CAST(NULL as bit) as &#91;systemReceived]\n\t\t\t  ,CAST(NULL as int) as &#91;agencyId]\n\t\t\t  ,CAST(NULL as DATE) as &#91;donorInvoiceDate]\n\t\t\t  ,CAST(0 as bit)  as &#91;isBeingConfirmed]\n\t\t\t  ,CAST(NULL as DATETIME) as &#91;confirmingStart]\n\t\t\t  ,CAST(NULL as DATETIME) as &#91;confirmingEnd]\n\t\t\t  ,CAST(0 as bit)  as &#91;isBeingConvertedToOrder]\n\t\t\t  ,CAST(NULL as DATETIME) as &#91;convertingToOrderStart]\n\t\t\t  ,CAST(NULL as DATETIME) as &#91;convertingToOrderEnd]\n\t\t\t  ,CAST(0 as int) as &#91;dataSourceId]\n\t\t\t  ,GetDate() as &#91;RowCreateDateTime]\nINTO #Staging_receipt_vw --staging.receipt_vw\nFROM FindZerorecords fzr\nLEFT OUTER JOIN staging.contributor_vw cvw\n\tON fzr.contributorid = cvw.contributorid\nWHERE fzr.contributorid IS NOT NULL\n\n\nINSERT INTO staging.receipt_vw\n(\n&#91;receiptId]\n      ,&#91;referenceCode]\n      ,&#91;contributorId]\n      ,&#91;contributorAddressId]\n      ,&#91;contributorContactId]\n      ,&#91;warehouseId]\n      ,&#91;systemEntryOriginId]\n      ,&#91;donationTypeId]\n      ,&#91;systemReceiptTypeId]\n      ,&#91;isDelivery]\n      ,&#91;originationId]\n      ,&#91;originCity]\n      ,&#91;comment]\n      ,&#91;pickupDate]\n      ,&#91;approved]\n      ,&#91;isRetailPickup]\n      ,&#91;isFoodDrive]\n      ,&#91;confirmedDate]\n      ,&#91;submissionDate]\n      ,&#91;modifiedOn]\n      ,&#91;inactiveOn]\n      ,&#91;isInactive]\n      ,&#91;isConfirmed]\n      ,&#91;systemReceivedDate]\n      ,&#91;systemReceived]\n      ,&#91;agencyId]\n      ,&#91;donorInvoiceDate]\n      ,&#91;isBeingConfirmed]\n      ,&#91;confirmingStart]\n      ,&#91;confirmingEnd]\n      ,&#91;isBeingConvertedToOrder]\n      ,&#91;convertingToOrderStart]\n      ,&#91;convertingToOrderEnd]\n      ,&#91;dataSourceId]\n      ,&#91;RowCreateDateTime]\n)\nSELECT \n&#91;receiptId]\n      ,&#91;referenceCode]\n      ,&#91;contributorId]\n      ,&#91;contributorAddressId]\n      ,&#91;contributorContactId]\n      ,&#91;warehouseId]\n      ,&#91;systemEntryOriginId]\n      ,&#91;donationTypeId]\n      ,&#91;systemReceiptTypeId]\n      ,&#91;isDelivery]\n      ,&#91;originationId]\n      ,&#91;originCity]\n      ,&#91;comment]\n      ,&#91;pickupDate]\n      ,&#91;approved]\n      ,&#91;isRetailPickup]\n      ,&#91;isFoodDrive]\n      ,&#91;confirmedDate]\n      ,&#91;submissionDate]\n      ,&#91;modifiedOn]\n      ,&#91;inactiveOn]\n      ,&#91;isInactive]\n      ,&#91;isConfirmed]\n      ,&#91;systemReceivedDate]\n      ,&#91;systemReceived]\n      ,&#91;agencyId]\n      ,&#91;donorInvoiceDate]\n      ,&#91;isBeingConfirmed]\n      ,&#91;confirmingStart]\n      ,&#91;confirmingEnd]\n      ,&#91;isBeingConvertedToOrder]\n      ,&#91;convertingToOrderStart]\n      ,&#91;convertingToOrderEnd]\n      ,&#91;dataSourceId]\n      ,&#91;RowCreateDateTime]\nFROM #Staging_receipt_vw \n\nINSERT INTO @MyTableVar \n(&#91;Action],receiptid,DataSourceid)\nSELECT \n\t'INSERT' as outputType\n\t,receiptid\n\t,DataSourceid\nFROM #Staging_receipt_vw\n\n\n\nINSERT INTO &#91;log].data_event\n(&#91;origin], event_type, &#91;description],run_id)\nSELECT \n\tds.&#91;name] as &#91;origin]\n\t,&#91;Action] as event_type\n\t,CONCAT(Count(*),' rows ',&#91;action],'ED to staging.receipt_vw') as &#91;description]\n\t,NULL as &#91;run_id]\nFROM @MyTableVar mt\nLEFT OUTER JOIN &#91;dbo].&#91;dataSource] ds\n\tON ds.dataSourceid = mt.dataSourceid\nGROUP BY \n\tds.&#91;name]\n\t,&#91;Action]\n\nExec &#91;staging].&#91;update_receiptDetail_vw]\n\n\nIF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test\n\/* Mark PRE Receipt records with Duplicates and other issues*\/\n;WITH find_dups as \n(\n\tSELECT DISTINCT\n\t\tagencyid\n\t\t,contributorid\n\t\t,pickupdate\n\t\t,rank_basic = row_number() OVER(PARTITION BY rvw.agencyid, rvw.contributorid, rvw.pickupDate ORDER BY rvw.agencyid, rvw.contributorid, rvw.pickupDate)\n\tFROM staging.receipt_vw rvw\n\tLEFT OUTER JOIN &#91;dbo].&#91;systemReceiptType] srt\n\t\ton rvw.systemReceiptTypeId = srt.systemReceiptTypeid\n\tWHERE rvw.pickupDate >= DATEADD(MONTH,-6,GETDATE())\n\tAND lower(srt.&#91;name]) = 'prereceipt'\n), get_distinct_dups as \n(\n\tSELECT \n\t\t\tagencyid\n\t\t\t,contributorid\n\t\t\t,pickupdate\n\tfrom find_dups\n\tWHERE rank_basic > 1\n), add_receiptid as \n(\n\tSELECT \n\t\trvw.receiptId\n\t\t,rvw.agencyid\n\t\t,rvw.contributorId\n\t\t,rvw.pickupDate\n\tFROM staging.receipt_vw rvw\n\t--INNER JOIN get_distinct_dups ddup\n\t--\tON rvw.agencyId = ddup.agencyId\n\t--\tAND rvw.contributorId = ddup.contributorId\n\t--\tAND rvw.pickupDate = ddup.pickupDate\n\t\tLEFT OUTER JOIN &#91;dbo].&#91;systemReceiptType] srt\n\t\ton rvw.systemReceiptTypeId = srt.systemReceiptTypeid\n\tWHERE rvw.pickupDate >= DATEADD(MONTH,-6,GETDATE())\n\tAND lower(srt.&#91;name]) = 'prereceipt'\n), Add_RDetails as \n(\n\tSELECT \n\t\tar.receiptId\n\t\t,ar.agencyId\n\t\t,ar.contributorId\n\t\t,ar.pickupDate\n\t\t,STRING_AGG(CAST(rdvw.productId as varchar(50)) ,', ') WITHIN GROUP(ORDER BY rdvw.productId ASC) as AggProductIDs\n\t\t,SUM(rdvw.requestedWeight) as totalRequestedWeight\n\t\t, rwTooLarge\n\tFROM add_receiptid ar\n\tLEFT JOIN (\n\t\tSELECT\n\t\t\treceiptid\n\t\t\t,productid\n\t\t\t,SUM(requestedWeight) as requestedWeight\n\t\t\t,iif(sum(requestedWeight) > 1500,1,0) as rwTooLarge\n\t\tFROM staging.receiptDetail_vw \n\t\tGROUP BY\n\t\t\treceiptid\n\t\t\t,productid\t\t\n\t\t) rdvw\n\t\tON ar.receiptId = rdvw.receiptId\n\tWHERE rdvw.productId IS NOT NULL\n\tGROUP BY \n\t\tar.receiptId\n\t\t,ar.agencyId\n\t\t,ar.contributorId\n\t\t,ar.pickupDate\n\t\t,rwTooLarge\n), ranking as \n(\n\tSELECT \n\t\treceiptid\n\t\t,agencyid\n\t\t,contributorid\n\t\t,pickupDate\n\t\t,AggProductIDs\n\t\t,totalRequestedWeight\n\t\t,GroupCount = DENSE_RANK() OVER(ORDER BY pickupDate, agencyid, contributorid)\n\t\t,COUNT(*) OVER(PARTITION BY agencyid, contributorid, pickupDate) as dup_count\n\t\t,ROW_NUMBER() OVER(PARTITION BY agencyid, contributorid, pickupDate ORDER BY receiptID ASC)  as BasicDuplicateRow\n\t\t,COUNT(*) OVER(PARTITION BY agencyid, contributorid, pickupDate, totalRequestedWeight) as NonProduct_dup_count\n\t\t,ROW_NUMBER() OVER(PARTITION BY agencyid, contributorid, pickupDate, totalRequestedWeight ORDER BY receiptID ASC)  as NonProductDuplicateRow\n\t\t,COUNT(*) OVER(PARTITION BY agencyid, contributorid, pickupDate, AggProductIDs, totalRequestedWeight) as Fully_dup_count\n\t\t,ROW_NUMBER() OVER(PARTITION BY agencyid, contributorid, pickupDate, AggProductIDs, totalRequestedWeight ORDER BY receiptID DESC)  as FullyDuplicateRow\n\t\t,COUNT(*) OVER(PARTITION BY agencyid, pickupDate, totalRequestedWeight) as nonContributorDuplicate\n\t\t,COUNT(*) OVER(PARTITION BY agencyid, contributorid, totalRequestedWeight) as nonPickupDateDuplicate\n\t\t,rwTooLarge\n\tFROM Add_RDetails ard\n\n)\nSELECT \n\t*\n\t, CASE \n\t\tWHEN totalRequestedWeight = 0 THEN 'Total Received Weight = 0; Color = Yellow'\n\t\tWHEN rwTooLarge = 1 THEN 'Individual Product Received Weight > 1500; Color = Red'\n\t\tWHEN dup_count = FullyDuplicateRow AND dup_count > 1 THEN 'Fully Duplicate Primary; Color = Green'\n\t\tWHEN dup_count = Fully_dup_count AND FullyDuplicateRow &lt; Fully_dup_count AND dup_count > 1 THEN 'Fully Duplicate Alternate; Color = Red'\n\t\tWHEN dup_count = NonProduct_dup_count AND NonProduct_dup_count = NonProductDuplicateRow AND dup_count > 1 THEN 'Non Product Duplicate Primary; Color = Green'\n\t\tWHEN dup_count = NonProduct_dup_count AND NonProduct_dup_count > NonProductDuplicateRow AND dup_count > 1 THEN 'Non Product Duplicate Alternate; Color = Red'\n\t\tWHEN dup_count > 1 THEN 'Basic Duplicate; Color = Yellow'\n\t\tWHEN nonContributorDuplicate > 1 THEN 'Non Contributor Duplicate; COLOR = Yellow'\n\t\tWHEN nonPickupDateDuplicate > 1 THEN 'Non PickupDate Duplicate; Color = Yellow'\n\t\tELSE NULL\n\t\tEND as &#91;Status]\t\t\n\t, CASE WHEN dup_count > 1 AND TotalRequestedWeight > 0 THEN 1 ELSE 0 END as DUPS\nINTO #Test\nFROM ranking\n\tORDER BY \n\t\tGroupCount\t\n\t\t,agencyid\n\t\t,contributorid\n\t\t,pickupDate\n\t\t,receiptid\n\t\t,AggProductIDs\n\t\t,totalRequestedWeight\n\nSELECT &#91;status], * FROM #TEST\n\tORDER BY \n\t\tGroupCount\t\n\t\t,agencyid\n\t\t,contributorid\n\t\t,pickupDate\n\t\t,receiptid\n\t\t,AggProductIDs\n\t\t,totalRequestedWeight\n\n-- Reset Status and Dups\nUPDATE &#91;staging].&#91;receipt_vw]\nSET &#91;Status] = NULL\n\t,Dups = 0\n\n-- Update Status and Dups\nUPDATE rvw\nSET &#91;Status] = T.&#91;Status]\n\t,Dups = T.DUPS\nFROM &#91;staging].&#91;receipt_vw] rvw\nINNER JOIN #Test T\n\tON rvw.receiptId = T.receiptId\n\n\nEND\nGO<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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 &#8211; necessary for speed). Once the merge is completed, clean-up steps are run:Pre-step remove all records within last 6 months where [comment] = &#8216;Missing Contributor id record for YearMonth&#8217; SQL Code:<\/p>\n","protected":false},"author":1,"featured_media":3791,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"nf_dc_page":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[4,89],"tags":[47,101],"class_list":["post-3794","post","type-post","status-publish","format-standard","has-post-thumbnail","category-database","category-professional-portfolio","tag-sql","tag-sql-code","czr-hentry"],"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p7Ze6V-Zc","jetpack-related-posts":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/jpbydesign.com\/wp-content\/uploads\/2026\/04\/sql-seeklogo.webp?fit=2000%2C1027&ssl=1","_links":{"self":[{"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/posts\/3794","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/comments?post=3794"}],"version-history":[{"count":2,"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/posts\/3794\/revisions"}],"predecessor-version":[{"id":3796,"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/posts\/3794\/revisions\/3796"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/media\/3791"}],"wp:attachment":[{"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/media?parent=3794"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/categories?post=3794"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/tags?post=3794"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}