co_update_seasonal_address

This SP is used to flip a customer's Seasonal Address. See Seasonal Address Update Process for details on how this SP works.

Usage

This SP can be set up as a SQL Job to run on the first day of every month.

Code

The following example shows sample code for this stored procedure:

/****** Object:  StoredProcedure [dbo].[co_update_seasonal_address]    Script Date: 05/05/2009 
 
10:26:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[co_update_seasonal_address]
@entity_key uniqueidentifier=NULL
AS
SET nocount ON
 
 
declare @cstkey uniqueidentifier
declare @newcxakey uniqueidentifier
declare @current_month_normalized integer
 
 
-- someones season is not primary and now should be primary
 
declare set_new_primary_address_cursor cursor FOR 
SELECT 	cst_key,cxa_key 
FROM 	co_customer_x_address 
JOIN co_customer ON cxa_cst_key=cst_key 
WHERE 
	month(getdate())>=month(cxa_seasonal_from_date) 
AND month(getdate())<=month(cxa_seasonal_through_date) 
AND cxa_key<>cst_cxa_key
UNION
SELECT 	cst_key,cxa_key 
FROM
	co_customer_x_address 
JOIN co_customer ON cxa_cst_key=cst_key 
WHERE 
	month(cxa_seasonal_through_date) < month(cxa_seasonal_from_date) 
AND month(getdate()) <= month(cxa_seasonal_from_date) 
AND month(getdate()) + 12 <= (month(cxa_seasonal_through_date)+12) 
AND cxa_key<>cst_cxa_key
 
UNION
SELECT 	cst_key,cxa_key 
FROM 	co_customer_x_address 
JOIN co_customer 
ON cxa_cst_key=cst_key 
WHERE 
	month(cxa_seasonal_through_date) < month(cxa_seasonal_from_date) 
AND month(getdate()) >= month(cxa_seasonal_from_date) 
AND month(getdate()) <= (month(cxa_seasonal_through_date)+12) 
AND cxa_key<>cst_cxa_key
 
open set_new_primary_address_cursor
fetch next FROM set_new_primary_address_cursor INTO @cstkey,@newcxakey
 
 
while @@fetch_status=0
begin
UPDATE co_customer SET cst_old_cxa_key=cst_cxa_key, cst_cxa_key=@newcxakey WHERE cst_key=@cstkey
      fetch next FROM set_new_primary_address_cursor INTO @cstkey,@newcxakey
end
 
 
close set_new_primary_address_cursor
deallocate set_new_primary_address_cursor
 
 
 
-- someones season is not primary and now should be primary
 
declare set_back_primary_address_cursor cursor FOR 
--select cst_key from co_customer_x_address join co_customer on cxa_cst_key=cst_key where 
 
getdate()>cxa_seasonal_through_date AND cxa_key=cst_cxa_key
 
SELECT 	cst_key 
FROM 	co_customer_x_address 
JOIN co_customer 
ON cxa_cst_key=cst_key 
WHERE 
	month(cxa_seasonal_through_date) > month(cxa_seasonal_from_date) 
AND month(getdate())>month(cxa_seasonal_through_date) 
AND cxa_key=cst_cxa_key
UNION
SELECT 	cst_key 
FROM 	co_customer_x_address 
JOIN co_customer 
ON cxa_cst_key=cst_key 
WHERE 
	month(cxa_seasonal_through_date) < month(cxa_seasonal_from_date) 
AND month(getdate()) > month(cxa_seasonal_through_date) 
AND month(getdate()) < month(cxa_seasonal_from_date) 
AND cxa_key=cst_cxa_key
UNION
SELECT 	cst_key 
FROM 	co_customer_x_address 
JOIN co_customer 
ON cxa_cst_key=cst_key 
WHERE 
	month(cxa_seasonal_through_date) = month(cxa_seasonal_from_date) 
AND month(getdate()) > month(cxa_seasonal_through_date) 
AND cxa_key=cst_cxa_key
 
 
 
open set_back_primary_address_cursor
fetch next FROM set_back_primary_address_cursor INTO @cstkey
 
while @@fetch_status=0
begin
UPDATE co_customer SET cst_cxa_key=cst_old_cxa_key WHERE cst_key=@cstkey AND cst_old_cxa_key IS 
 
NOT NULL
      fetch next FROM set_back_primary_address_cursor INTO @cstkey
end
 
close set_back_primary_address_cursor
deallocate set_back_primary_address_cursor