-- Declare the variables to store the values returned by FETCH.
DECLARE @ClientID char(14), 
	@ClaimDate datetime, 
	@Product char(5), 
	@Product_count int, 
	@ClientID_cur char(14), 
	@ClaimDate_cur datetime,
	@exist_ClientID char(14)

set @ClientID = 'start'
set @Product_count = 0

DECLARE Claim_cursor CURSOR FOR
	--get unique ClientIDs for 2001
	select cc.ClientID,
		cc.ClaimDate
	from ClaimCandidate2001 cc
	where cc.ClientID not in (select ClientID from ClaimClientIDsExceed)
	order by mc.ClientID

OPEN Claim_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement. 

FETCH NEXT FROM Claim_cursor
INTO @ClientID_cur, @ClaimDate_cur

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
	--if 14 unique Product exceeded in 180 days then check to skip to next next record
	if @ClientID_cur <> @ClientID or @Product_count < 15
	begin
		--reset variables
		set @Product_count = 0
		set @ClientID = @ClientID_cur
		set @ClaimDate = @ClaimDate_cur
	
		--Get count of unique Product for 180 day period
		select @Product_count = count(distinct pc.Product)
		from ClaimCandidate2001 pc
		where  pc.ClientID = @ClientID
			and pc.ClaimDate between @ClaimDate and dateadd(d,180,@ClaimDate)
		--check to see if unique Product exceed 14
		if @Product_count > 14
		begin
			set @exist_ClientID = 'reset'
			--check to see if the ClientID already exists
			select @exist_ClientID = isnull(ClientID, 'no') 
			from ClaimClientIDsExceed
			where ClientID like @ClientID

			if @exist_ClientID in ('no', 'reset')
			begin
				--put into list of ClientIDs that exceed 14 for 180 day period
				insert into ClaimClientIDsExceed
				(ClientID)
				values(@ClientID)
			end 
		end
	end

   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM Claim_cursor
   INTO @ClientID_cur, @ClaimDate_cur--, @Product
END

CLOSE Claim_cursor
DEALLOCATE Claim_cursor"

quote:

---

I am trying to identify and insert clients who exceed 14 claims into a table.

---