SQL CURSOR

好久没有写SQL CURSOR了，语法的有点生疏了。今天写了个玩玩。呵呵！该功能是计算客户的丢失和恢复分析信息的。

/*
Definition:
Customer had sales before, but there is no sales in the last six month.
e.g: 
Before or in June has sales
From July to Dec – no sales
Customer lost in Jan
*/

/*
Following is the Solution:
*/

/*  Create a new CUSTOMER_STATUS to save the analysis data
USE [DW]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CUSTOMER_STATUS](
	[CUSTOMER_NAME] [nchar](100) NOT NULL,
	[CUSTOMER_ID] [int] NOT NULL,
	[YEAR] [nchar](4) NOT NULL,
	[MONTH] [nchar](2) NOT NULL,
	[STATUS] [nchar](20) NOT NULL,
	[INVOICE_DATE] [date] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CUSTOMER_STATUS] ADD  CONSTRAINT [DF_CUSTOMER_STATUS_YEAR]  DEFAULT ('') FOR [YEAR]
GO

ALTER TABLE [dbo].[CUSTOMER_STATUS] ADD  CONSTRAINT [DF_CUSTOMER_STATUS_MONTH]  DEFAULT ('') FOR [MONTH]
GO
*/

--Use the Cursor to get the target data


use DW

--测试的时候需要把表里的数据删除了
truncate table CUSTOMER_STATUS

declare @CUSTOMER_ID int
declare @CUSTOMER_NAME char(100)
declare @INVOICE_DATE date
--记录游标里的下一条记录
declare @CUSTOMER_ID_NEXT int
declare @CUSTOMER_NAME_NEXT char(100)
declare @INVOICE_DATE_NEXT date

--记录丢失客户的那个的月份
declare @INVOICE_DATE_LOST date

--记录循环次数
declare @LOOP_COUNT INT
SET @LOOP_COUNT=0

declare @sql char(5000)

declare table_customer_cursor cursor for 
select c.[CUSTOMER_ID],c.CUSTOMER_NAME,h.[INVOICE_DATE]
--COUNT(*) TransTimes 
from [dbo].[VSC_BI_CUSTOMER] c,[dbo].[VSC_BI_INV_HEADERS] h
where c.[CUSTOMER_ID]=h.[CUSTOMER_ID] and h.[INVOICE_DATE]>'2012-03-31'
group by c.[CUSTOMER_ID],c.CUSTOMER_NAME,h.[INVOICE_DATE]
--having count(*)>=1
order by c.[CUSTOMER_ID],c.CUSTOMER_NAME,h.[INVOICE_DATE]

open table_customer_cursor

fetch next from table_customer_cursor into @CUSTOMER_ID,@CUSTOMER_NAME,@INVOICE_DATE


WHILE @@FETCH_STATUS = 0 
BEGIN
	--第一进入循环的时候，@CUSTOMER_ID_NEXT是空的，这个时候直接用上面这个语句取到的值fetch next from table_customer_cursor into @CUSTOMER_ID,@CUSTOMER_NAME,@INVOICE_DATE
	if @CUSTOMER_ID_NEXT<>''
		BEGIN
			set @CUSTOMER_ID=@CUSTOMER_ID_NEXT
			set @CUSTOMER_NAME = @CUSTOMER_NAME_NEXT
			set @INVOICE_DATE = @INVOICE_DATE_NEXT
		END

	SET @LOOP_COUNT=@LOOP_COUNT+1
	print  @LOOP_COUNT
	--第一条记录插入到[dbo].[CUSTOMER_STATUS]表里，而且STATUS=NEW
	IF @LOOP_COUNT=1
		BEGIN
			--set @sql='insert into CUSTOMER_STATUS(CUSTOMER_ID,CUSTOMER_NAME,STATUS,INVOICE_DATE) values('+@CUSTOMER_ID+','+''''+@CUSTOMER_NAME+''''+','+'NEW'+','+@INVOICE_DATE+')' 
			--print @sql
			--exec(@sql)
			insert into CUSTOMER_STATUS(CUSTOMER_ID,CUSTOMER_NAME,STATUS,INVOICE_DATE)values(+@CUSTOMER_ID,@CUSTOMER_NAME,'NEW',@INVOICE_DATE) 
		END
	ELSE --不是一个客户的第一条记录
		--set @sql='delete from  '+@tblname
		--exec(@sql)
		print @CUSTOMER_ID 
		print @CUSTOMER_NAME 
		print @INVOICE_DATE

		fetch next from table_customer_cursor into @CUSTOMER_ID_NEXT,@CUSTOMER_NAME_NEXT,@INVOICE_DATE_NEXT
		--set @CUSTOMER_ID_NEXT = @CUSTOMER_ID
		--set @CUSTOMER_NAME_NEXT = @CUSTOMER_NAME
		--set @INVOICE_DATE_NEXT = @INVOICE_DATE

		print '显示下一行的记录 '
		print @CUSTOMER_ID_NEXT
		print @CUSTOMER_NAME_NEXT
		print @INVOICE_DATE_NEXT

			--计算逻辑如下：
			--如果相邻的两条记录是一个相同的客户，则需要比较两条记录的时间间隔，
			--1，如果时间间隔大于6个月，则表示这个客户在上一个时间的基础上加6个月份丢失的，这个时候需要把该客户在那个丢失的月份插入到CUSTOMER_STATUS表里
			--同时还要把此期间的下区间记录插入到CUSTOMER_STATUS表，表示此客户在此条记录的月份RECOVERY
			--2, 如果时间间隔小于6个月在插入此记录到CUSTOMER_STATUS表里，同时STATUS是NORMAL
			--总上所述，除了需要把循环的表记录都要插入CUSTOMER_STATUS表里外，还要插入丢失的那个月份的信息

			--如果一个客户循环到最后一条记录，此时要判断该记录距离getdate的时间是否大于6个月，如果是则是丢失LOST(这个时候需要再插入一条丢失的记录，
			--即最后一次交易的时间加6个月)，否则是正常NORMAL

			--Notes:如果客户只有一次交易，则需要特别的考虑，这个时候需要插入两条记录，一条是NEW，一条是丢失的记录
		IF (@CUSTOMER_ID=@CUSTOMER_ID_NEXT and @CUSTOMER_NAME=@CUSTOMER_NAME_NEXT)  --表示还是同一个客户
			--BEGIN
				--@INVOICE_DATE_NEXT-@INVOICE_DATE>180
				if DATEDIFF(day,@INVOICE_DATE,@INVOICE_DATE_NEXT)>180
					begin
						--set @INVOICE_DATE_LOST=@INVOICE_DATE+180
						set @INVOICE_DATE_LOST=dateadd(day,180,@INVOICE_DATE)
						--插入客户丢失的月份信息
						insert into CUSTOMER_STATUS(CUSTOMER_ID,CUSTOMER_NAME,STATUS,INVOICE_DATE)values(+@CUSTOMER_ID_NEXT,@CUSTOMER_NAME_NEXT,'LOST',@INVOICE_DATE_LOST)
						--插入恢复的月份信息
						insert into CUSTOMER_STATUS(CUSTOMER_ID,CUSTOMER_NAME,STATUS,INVOICE_DATE)values(+@CUSTOMER_ID_NEXT,@CUSTOMER_NAME_NEXT,'RECOVERY',@INVOICE_DATE_NEXT)	
					end	
				else
					insert into CUSTOMER_STATUS(CUSTOMER_ID,CUSTOMER_NAME,STATUS,INVOICE_DATE)values(+@CUSTOMER_ID_NEXT,@CUSTOMER_NAME_NEXT,'NORMAL',@INVOICE_DATE_NEXT)	
						
		ELSE  --不是同一个客户
			BEGIN
				SET @LOOP_COUNT=0  --是为了把每个客户的第一次交易时，他们会是我们的新客
				--此时客户循环到最后一条记录，此时要判断该记录距离getdate的时间是否大于6个月，如果是则是丢失LOST(这个时候需要再插入一条丢失的记录，
			    --即最后一次交易的时间加6个月)，否则是正常NORMAL
				if DATEDIFF(day,@INVOICE_DATE,getdate())>180
					BEGIN
						insert into CUSTOMER_STATUS(CUSTOMER_ID,CUSTOMER_NAME,STATUS,INVOICE_DATE)values(+@CUSTOMER_ID,@CUSTOMER_NAME,'LOST',dateadd(day,180,@INVOICE_DATE))
						--update CUSTOMER_STATUS set STATUS='LOST' where CUSTOMER_ID=@CUSTOMER_ID and CUSTOMER_NAME=@CUSTOMER_NAME and INVOICE_DATE=dateadd(day,180,@INVOICE_DATE)
					END
			END
END

close table_customer_cursor
deallocate table_customer_cursor

--更新下YEAR和MONTH,其实BI里可以直接用INOVICE_DATE就可以关联到YEAR，MONTH，QUARTER您自己看吧，需要就加上。
update CUSTOMER_STATUS set YEAR=YEAR(INVOICE_DATE),MONTH=MONTH(INVOICE_DATE)



/* 验证用的SQL
truncate table  CUSTOMER_STATUS

select * from CUSTOMER_STATUS 
where 1=1
--AND status='RECOVERY' 
AND CUSTOMER_ID=3698
order by customEr_id ,invoice_date
*/