Incremental Counter for Complex Data Comparison

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Incremental Counter for Complex Data Comparison

Author

Topic

lueylump
Starting Member

25 Posts

Posted - 2010-12-07 : 13:40:29

I have two situations in which I need to compare the same donor to see if they gave last year and this year. If affirmative a Returning Donor counter is incremented. Also if they did not give last year but gave this year and are not a new donor, A Lapsed Donor Counter is incremented.

The database makes this type of query more complex than it should be but I am getting syntax errors within a case statement. Any help you can provide is greatly appreciated (see code below):

Go

Declare
@Previous_Start_Date date,
@Previous_End_Date date,
@Current_Start_Date date,
@Current_End_Date date,
@Return_Count int,
@Lapse_Count int

Set @Return_Count = 0
Set @Lapse_Count = 0
Set @Previous_Start_Date = '20080701'
Set @Previous_End_Date = '20080630 23:59:59'
Set @Current_Start_Date = '20090701'
Set @Current_End_Date = '20100630 23:59:59'

SELECT @Return_Count, @Lapse_Count,
CASE H.Process_Date
WHEN H.Process_Date >= @Previous_Start_Date
AND H.Process_Date <= @Previous_End_Date
AND SUM(F.Allocation_Amt) > 0 AND
WHEN H.Process_Date >= @Current_Start_Date
AND H.Process_Date <= @Current_End_Date
AND SUM(F.Allocation_Amt) > 0
THEN Set @Return_Count = @Return_Count +1
Else WHEN H.Process_Date >= @Previous_Start_Date
AND H.Process_Date <= @Previous_End_Date
AND SUM(F.Allocation_Amt) = 0 AND
WHEN H.Process_Date >= @Current_Start_Date
AND H.Process_Date <= @Current_End_Date
AND SUM(F.Allocation_Amt) > 0
THEN Set @Lapse_Count = @Lapse_Count +1
END)
FROM Per_Group_Loc A
INNER JOIN Payment_Device B ON B.Payor_Code = A.Group_Code
INNER JOIN Payment_Device_History C ON C.Pay_Device_ID = B.Pay_Device_ID
INNER JOIN Payment D ON D.Payment_ID = C.Payment_ID
INNER JOIN Payment_Allocation F ON F.Payment_ID = D.Payment_ID
INNER JOIN Payment_Batch H ON H.Batch_ID = D.Batch_ID
INNER JOIN LookUpValues E ON E.LUV_ID = A.Group_Type_Code
INNER JOIN Org_Addresses G ON G.Org_Addr_ID = A.Primary_Mail_Addr_ID
WHERE F.Allocation_Amt > 0 AND
A.Create_Date < @Current_Start_Date AND
F.Fund_ID NOT IN('92F34AC0-C5CE-454D-A553-3BB6C6AA8E65','A2222598-D018-4F58-A68B-604E4D292A57',
'9D91E0EF-6773-4E47-9573-6901136B2FFF','84F2C1BF-2ACE-4959-8FB1-76BE75518FE7','8DD4BAFF-FF3A-420C-933A-7810F2EDC296',
'D1393F1C-4A44-4E3D-9820-7A57EF2C0771','F51C37E6-927E-4F1F-B900-87EBC4417209','CBF486D6-D184-4890-8F2F-DB8AEDF8DA81',
'75FED8FF-E146-4B0C-B7EC-516D38AADE74')

Also if their is a better way to effect this type of logic, I am open to any and all suggestions.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-12-07 : 14:09:04

try below

CASE H.Process_Date
WHEN H.Process_Date >= @Previous_Start_Date

should become

CASE
WHEN H.Process_Date >= @Previous_Start_Date

and follow the END with AS "YOURCOLUMNNAME"

also the END) might need to be END...I can't see the missing "(" which normally ")" needs...but which END does not automatically be followed by.

(Your code could be laid out more readable...have a look at the CODE TAGS feature for this forum)

lueylump
Starting Member

25 Posts

Posted - 2010-12-07 : 14:24:05

thank you, I will giv it a try.

lueylump
Starting Member

25 Posts

Posted - 2010-12-07 : 16:29:52

Let's try this again with Code tags this time (thanks for the heads up). Below are the syntax errors I am receiving as well as the code. If anyone has any ideas about fixing this problem they would be greatly appreciated.

Error Messages:

Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'Set'.
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'Else'.
Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'END'.

Source Code:

Go 

Declare @Previous_Start_Date date,
		@Previous_End_Date date,
		@Current_Start_Date date,
		@Current_End_Date date,
		@Return_Count int,
		@Lapse_Count int

Set @Return_Count = 0
Set @Lapse_Count = 0
Set @Previous_Start_Date = '20080701'
Set @Previous_End_Date = '20080630 23:59:59' 
Set @Current_Start_Date = '20090701'
Set @Current_End_Date = '20100630 23:59:59' 

SELECT Count(A.Group_Formal_Name), @Return_Count, @Lapse_Count,
CASE WHEN H.Process_Date between @Previous_Start_Date and @Current_End_Date THEN +
	CASE WHEN H.Process_Date between @Previous_Start_Date and @Previous_End_Date 
	 AND SUM(F.Allocation_Amt) > 0 THEN +
  		CASE WHEN H.Process_Date between @Current_Start_Date and @Current_End_Date
		 AND SUM(F.Allocation_Amt) > 0 THEN 
			Set @Return_Count = @Return_Count + 1
		END
	Else H.Process_Date between @Current_Start_Date and @Current_End_Date
		 AND SUM(F.Allocation_Amt) > 0 THEN 
			Set @Return_Count = @Return_Count + 1
	END	
END

FROM Per_Group_Loc A 
INNER JOIN Payment_Device B ON B.Payor_Code = A.Group_Code 
INNER JOIN Payment_Device_History C ON C.Pay_Device_ID = B.Pay_Device_ID 
INNER JOIN Payment D ON D.Payment_ID = C.Payment_ID 
INNER JOIN Payment_Allocation F ON F.Payment_ID = D.Payment_ID 
INNER JOIN Payment_Batch H ON H.Batch_ID = D.Batch_ID
INNER JOIN LookUpValues E ON E.LUV_ID = A.Group_Type_Code
INNER JOIN Org_Addresses G ON G.Org_Addr_ID = A.Primary_Mail_Addr_ID

WHERE F.Allocation_Amt > 0 AND
A.Create_Date < @Current_Start_Date AND
F.Fund_ID NOT IN('92F34AC0-C5CE-454D-A553-3BB6C6AA8E65','A2222598-D018-4F58-A68B-604E4D292A57',
'9D91E0EF-6773-4E47-9573-6901136B2FFF','84F2C1BF-2ACE-4959-8FB1-76BE75518FE7','8DD4BAFF-FF3A-420C-933A-7810F2EDC296',
'D1393F1C-4A44-4E3D-9820-7A57EF2C0771','F51C37E6-927E-4F1F-B900-87EBC4417209','CBF486D6-D184-4890-8F2F-DB8AEDF8DA81',
'75FED8FF-E146-4B0C-B7EC-516D38AADE74')

GROUP BY A.Group_Formal_Name
ORDER BY A.Group_Formal_Name

Kristen
Test

22859 Posts

Posted - 2010-12-08 : 03:51:40

You cannot SELECT values and SET variables in the same statement.

You can use SELECT if ALL the elements are assignments, or ALL are values that are Selected (displayed) ... but not a mix I'm afraid.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-12-08 : 04:05:48

basically you need to go look at the CASE construct again.

CASE
WHEN x = y then resulta
WHEN x = y+1 then resultb
WHEN x = y+2 then resultc
ELSE resultd
END as "result_meaningful_name"

or

CASE X
WHEN y then resulta
WHEN y+1 then resultb
WHEN y+2 then resultc
ELSE resultd
END as "result_meaningful_name"

note resulta-d all need to be the same datatype

I don't get your + clauses. are you using line continuations? or looking to use AND or use the literal "+" as the result?
I've altered your CASE code to be more in line with the syntax demands but...I was guessing at soem of the output requirements so answer the questions above and you may be able to resolve this.


CASE 
WHEN H.Process_Date between @Previous_Start_Date and @Current_End_Date THEN "+"
WHEN H.Process_Date between @Previous_Start_Date and @Previous_End_Date AND SUM(F.Allocation_Amt) > 0 THEN "+"
WHEN H.Process_Date between @Current_Start_Date and @Current_End_Date AND SUM(F.Allocation_Amt) > 0 THEN @Return_Count = @Return_Count + 1
WHEN H.Process_Date between @Current_Start_Date and @Current_End_Date AND SUM(F.Allocation_Amt) > 0 THEN @Return_Count = @Return_Count + 1
ELSE nothing
END

Kristen
Test

22859 Posts

Posted - 2010-12-08 : 04:11:33

WHEN H.Process_Date between @Current_Start_Date and @Current_End_Date AND SUM(F.Allocation_Amt) > 0 THEN @Return_Count = @Return_Count + 1

that assignment is not allowed in a SELECT statement is it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources