If else in store procedure

hi experts, i have the following sp...the problem is that its not going into else. Today is a weekend and it did not go to else...i checked and isWeekday =0.

please help! thankis
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_UpdateTotals]
AS
BEGIN
 
 
Declare @isWeekday  tinyint
Declare @isHoliday  tinyint
Declare @WeekendTotalActual int
Declare @WeekdayTotalActual int
Declare @WeekdayTotalTarget int
Declare @WeekdayHolidayTotalActual int
Declare @WeekendHolidayTotalActual int
 
--Find out if today is weekend or holiday (True =1, False =0)
set @isWeekday =(select isWeekday  from Corp_calendar T where dt = CONVERT(VARCHAR(10),GETDATE(),101))  
set @isHoliday =  (select isHoliday  from Corp_calendar T where dt = CONVERT(VARCHAR(10),GETDATE(),101)) 
 
--Check to see if today is a weekday and not a holiday:
 
IF @isWeekday=1 or @isHoliday =0 
 BEGIN
		--This updates DAILY totals
        UPDATE TB_Cummulative 
        SET 
				TB_Cummulative.Cummulative_actual = D.ActualTotal, 
				TB_Cummulative.Cummulative_target = D.TargetTotal
				FROM Auto_Daily_Total D 
        WHERE 
				TB_Cummulative.Run_Date = convert(varchar(10),getdate(),101)  and TB_Cummulative.Board_ID = D.Board_ID	
				and TB_Cummulative.isWeekday = D.isWeekday and TB_Cummulative.isHoliday = D.isHoliday
		
		--This updates MTD totals
		UPDATE TB_Cummulative 
        SET
				TotalActual = D.TotalActual, 
				TotalTarget = D.TotalTarget 
				FROM TB_Cummulative T 
				INNER JOIN Auto_Monthly_Actual_Total D ON T.Board_ID = D.Board_ID
		WHERE 
				T.Run_Date = convert(datetime,CONVERT(VARCHAR(10),GETDATE(),101) ,101) 
				AND D.MONTH=MONTH(GETDATE()) AND D.YEAR = YEAR(GETDATE())
		
 
 END
 
ELSE
 BEGIN
		--Update Daily Totals on weekends. We set the target to 0.
        UPDATE TB_Cummulative 
        SET 
                        TB_Cummulative.Cummulative_actual = D.ActualTotal, 
                        TB_Cummulative.Cummulative_target = 0
                        FROM Auto_Daily_Total D
        WHERE 
                        TB_Cummulative.Run_Date = convert(varchar(10),getdate(),101)  and TB_Cummulative.Board_ID = D.Board_ID
						and TB_Cummulative.isWeekday = D.isWeekday and TB_Cummulative.isHoliday = D.isHoliday
 
		--THis updates MTD on weekends.
       UPDATE TB_Cummulative 
        SET
				TotalActual = D.TotalActual, 
				TotalTarget = D.TotalTarget 
				FROM TB_Cummulative T 
				INNER JOIN Auto_Monthly_Actual_Total D ON T.Board_ID = D.Board_ID
		WHERE 
				T.Run_Date = convert(datetime,CONVERT(VARCHAR(10),GETDATE(),101) ,101) 
				AND D.MONTH=MONTH(GETDATE()) AND D.YEAR = YEAR(GETDATE())
		
  END       
END

Open in new window

sassy168Asked:
Who is Participating?
 
appariConnect With a Mentor Commented:
may be you need to change the condition
IF @isWeekday=1 or @isHoliday =0

to

IF @isWeekday=1 and @isHoliday =0
0
 
appariCommented:
did you check the value of @isHoliday ?
if it is 0 it goes to if, not into else part
0
 
Louis01Commented:
I'd suggest you change lines 18 & 19 as follows:
 
set @isWeekday =(select IsNull(isWeekday, 0) as isWeekday from Corp_calendar T where dt = CONVERT(VARCHAR(10),GETDATE(),101))  
set @isHoliday =  (select IsNull(isHoliday, 0) as isHoliday from Corp_calendar T where dt = CONVERT(VARCHAR(10),GETDATE(),101))

 
And line 23 to:
IF @isWeekday = 1 and @isHoliday <> 1  
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
sassy168Author Commented:
well there is 4 different possiblilities:

weekday and not holiday
weekday and holiday
weekend and not holiday
weekend and holiday

so i guess if i want to check tomorrorw, it would be weekday and not holiday like you mentioned and i should do more if statements for the other conditions?

IF @isWeekday=1 and @isHoliday =0 (this would be tomorrow)
IF @isWeekday=1 and  @isHoliday =1
IF @isWeekday=0 or @isHoliday =1
IF @isWeekday=0 or @isHoliday =0
another question: should i do multiple ifs instead of If else?

 
 





0
 
Louis01Commented:

DECLARE @ActionType varchar(20);
DECLARE @isWeekday  tinyint
DECLARE @isHoliday  tinyint
 
SET @isWeekday =(select IsNull(isWeekday, 0) as isWeekday from Corp_calendar T where dt = CONVERT(VARCHAR(10),GETDATE(),101))  
SET @isHoliday =  (select IsNull(isHoliday, 0) as isHoliday from Corp_calendar T where dt = CONVERT(VARCHAR(10),GETDATE(),101)) 
 
IF (@isWeekday=1 and @isHoliday=0) SET @ActionType = 'Normal Weekday'
IF (@isWeekday=1 and @isHoliday=1) SET @ActionType = 'Weekday Holiday'
IF (@isWeekday=0 and @isHoliday=1) SET @ActionType = 'Weekend Holiday'
IF (@isWeekday=0 and @isHoliday=0) SET @ActionType = 'Normal Weekend'
 
IF @ActionType = 'Normal Weekday'
BEGIN
END
 
IF @ActionType = 'Weekday Holiday'
BEGIN
END
 
IF @ActionType = 'Weekend Holiday'
BEGIN
END
 
IF @ActionType = 'Normal Weekend'
BEGIN
END

Open in new window

0
 
sassy168Author Commented:
i just checked my logic...basically everythinjg besides the normal weekday falls into the else. I believe can just use if weekday =1 and isHoliday <> 1 for the if part and then let everything else go to else.
0
 
Louis01Connect With a Mentor Commented:
Then appari's suggestion is the right one. (Just make sure isWeekday and isHoliday cannot be NULL)
0
 
sassy168Author Commented:
thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.