Debug on IF ELSE statement - SQL Server Forums

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)

Debug on IF ELSE statement

Author

Topic

sunegtheoverlord
Starting Member

5 Posts

Posted - 2010-12-08 : 09:45:24

Hello,

Could one of you please be so kind as to tell me what i'm doing wrong with this?

DECLARE @consultantref int
SET @consultantref = '541000019'


IF ( @consultantref = '541000019' )
BEGIN	
		

			(
				(	
					SELECT CallsPerMonth/0.6 AS CallsPerMonthKat
					FROM Calls
					WHERE [1stOfMonth] BETWEEN  (
														SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)) 
														)
												AND		(
														SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
														)

					AND ConsultantRef = @consultantref
				)


			 
				/* Divide by Number of Working days in previous month using function dbo.fnGetBusinessDaysInMonth */
				/ (select dbo.fnGetBusinessDaysInMonth((SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)))) 
					) 
					
				
		)
END
ELSE
BEGIN
		

		(
			SELECT CallsPerMonth
			FROM Calls
			WHERE [1stOfMonth] BETWEEN  (
												SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)) 
												)
										AND		(
												SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
												)

			AND ConsultantRef = @consultantref


		 
			/* Divide by Number of Working days in previous month using function dbo.fnGetBusinessDaysInMonth */
			/ (select dbo.fnGetBusinessDaysInMonth((SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)))) )
	
		)
END

I keep getting :-

Msg 170, Level 15, State 1, Line 26
Line 26: Incorrect syntax near '/'.
Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near ')'.

and can't work out why...

TimSman
Posting Yak Master

127 Posts

Posted - 2010-12-08 : 09:57:07

Does this work?


DECLARE @consultantref int
SET @consultantref = '541000019'


IF ( @consultantref = '541000019' )
BEGIN
	SELECT 
		(CallsPerMonth/0.6) / (select dbo.fnGetBusinessDaysInMonth((SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)))))  AS CallsPerMonthKat
	FROM 
		Calls
	WHERE 
		([1stOfMonth] BETWEEN  (SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))) AND (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))
		AND (ConsultantRef = @consultantref)
END
ELSE
BEGIN

SELECT 
	(CallsPerMonth) / (select dbo.fnGetBusinessDaysInMonth((SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))))) AS CallsPerMonthKat
FROM 
	Calls
WHERE 
	([1stOfMonth] BETWEEN  (SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))) AND (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))
	AND (ConsultantRef = @consultantref)

END

sunegtheoverlord
Starting Member

5 Posts

Posted - 2010-12-08 : 10:00:23

yes,

guess you just can't do it my way.

Thanks.

sunegtheoverlord
Starting Member

5 Posts

Posted - 2010-12-08 : 11:43:15

Hello again,

I've come a cropper again with this statement. The code i submitted first is actually part of a larger SELECT Statement
i.e. SELECT a = (my code), b = (my second code) etc

When i put the code into this larger structure i get :
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'IF'.
Msg 170, Level 15, State 1, Line 55
Line 55: Incorrect syntax near ')'.

DECLARE @consultantref int
SET @consultantref = '541000005'

SELECT DailyAverageCalls = (
IF ( @consultantref = '541000019' )
BEGIN
	SELECT 
		(CallsPerMonth/0.6) 
		
		/* Divide by Number of Working days in previous month using function dbo.fnGetBusinessDaysInMonth */		
		/	(select dbo.fnGetBusinessDaysInMonth((SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)))))  
		AS CallsPerMonthKat

	FROM Calls

	WHERE 
		(
			[1stOfMonth] BETWEEN	(
									SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))
									) 
						AND			(
									SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
									)
		)
		AND 
		(
			ConsultantRef = @consultantref
		)
END
ELSE
BEGIN

SELECT 
	(CallsPerMonth) 
	/* Divide by Number of Working days in previous month using function dbo.fnGetBusinessDaysInMonth */	
	/ (select dbo.fnGetBusinessDaysInMonth((SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))))) 

AS CallsPerMonth

FROM Calls
	WHERE 
		(
			[1stOfMonth] BETWEEN	(
									SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))
									) 
						AND			(
									SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
									)
		)
		AND 
		(
			ConsultantRef = @consultantref
		)

END )

can you help me with this?

Kristen
Test

22859 Posts

Posted - 2010-12-08 : 11:50:59

You can't use IF within a SELECT statement. You need to use CASE WHEN ... THEN ... ELSE ... END syntax instead

Kristen
Test

22859 Posts

Posted - 2010-12-08 : 11:52:25

Or instead of


SELECT @Variable =
IF xxx
   SELECT yyy
ELSE
   SELECT yyy
END

you could use


IF xxx
   SELECT @Variable = ...
ELSE
   SELECT @Variable = ...
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources