?
Solved

If else in store procedure

Posted on 2008-11-09
8
Medium Priority
?
425 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:sassy168
  • 3
  • 3
  • 2
8 Comments
 
LVL 39

Expert Comment

by:appari
ID: 22919333
did you check the value of @isHoliday ?
if it is 0 it goes to if, not into else part
0
 
LVL 39

Accepted Solution

by:
appari earned 1000 total points
ID: 22919344
may be you need to change the condition
IF @isWeekday=1 or @isHoliday =0

to

IF @isWeekday=1 and @isHoliday =0
0
 
LVL 11

Expert Comment

by:Louis01
ID: 22919422
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sassy168
ID: 22919465
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
 
LVL 11

Expert Comment

by:Louis01
ID: 22919491

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
 

Author Comment

by:sassy168
ID: 22919517
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
 
LVL 11

Assisted Solution

by:Louis01
Louis01 earned 1000 total points
ID: 22919538
Then appari's suggestion is the right one. (Just make sure isWeekday and isHoliday cannot be NULL)
0
 

Author Closing Comment

by:sassy168
ID: 31514939
thank you!
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question