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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

appariCommented:
did you check the value of @isHoliday ?
if it is 0 it goes to if, not into else part
0
appariCommented:
may be you need to change the condition
IF @isWeekday=1 or @isHoliday =0

to

IF @isWeekday=1 and @isHoliday =0
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
Louis01Commented:
Then appari's suggestion is the right one. (Just make sure isWeekday and isHoliday cannot be NULL)
0
sassy168Author Commented:
thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.