While and IF Else Condition-Problem

Hello Everybody,<br />I have created a Temporary table in which I am creating two cursors to walk through the table and while condition to populate data. Inside the while condition I am using if statements.My problem is that my if condition are not working properly .<br /><br />See below..<br /><br />If((@day=@ScheduleItemDay)and(@MyaspDate&gt;@EndDate))<br /><br /><br />Select @MyaspDate as [Today's Date]<br />Select @ScheduleItemDay as [Schedule]<br />Select @Day as [day]<br />BEGIN<br />Set @count=@count+DATEDIFF (hh , @StartDate, @EndDate ) <br /> Select @Count as [Count1]<br /> <br /> <br />END<br />---ELSE<br />if(@MyaspDate&gt;@StartDate and @MyaspDate&lt;@EndDate and @ScheduleItemDay=@day)<br />BEGIN<br />Set @count= @count+DATEDIFF (hh,@StartDate,@MyaspDate)<br /> Select @Count as [Count2]<br />END <br /><br /><br /><br />Even if @day not equal to @ScheduleItemDay the condition inside the if statement is executed.. Is it due to the while condition which nests the if condition or is it something else... I have pasted the whole code(containing the temp table,cursors, if condition ) for your reference...<br /><br />Please guide.<br /><br />Rahil<br /><br /><br /><br /><br /><br /><br /><br /><br />CREATE TABLE #TempInteractions (<br /> TempInteractionID bigint,<br /> TempClientID int,<br /> TempInteractionStatusID int,<br /> TempCreateDate datetime,<br /> Statusvarchar(20),<br /> TimeOpenSec int <br /> <br />) <br /><br />----Insert into #TempInteractions values from Interactions,Clients, Schedules, ScheduleItems<br />INSERT into #TempInteractions (TempInteractionID, TempClientID, TempInteractionStatusID,TempCreateDate,Status,TimeOpenSec)<br />SELECT TOP 1 i.InteractionID,i.ClientID,i.InteractionStatusID,i.CreateDate,'N/A',0<br /> FROM Interactions i<br />INNER JOIN Clients c<br />ON i.ClientID= c.ClientId<br />INNER JOIN Schedules s<br />ON c.ScheduleID= s.ScheduleID<br />INNER JOIN ScheduleItems si<br />ON s.ScheduleID=si.ScheduleID<br /><br />Select * from #TempInteractions<br /><br />----Declare variables<br />DECLARE @TempInteractionID bigint<br />DECLARE @TempClientID int<br />DECLARE @TempInteractionStatusID int<br />DECLARE @TempCreateDate datetime<br />DECLARE @Status varchar(20)<br />DECLARE @TimeOpenSec int<br />DECLARE @Counter datetime<br /><br /> DECLARE @ScheduleItemID int<br />DECLARE @ScheduleID int<br />DECLARE @ScheduleItemDay int<br />DECLARE @StartDate datetime<br />DECLARE@EndDate datetime<br />DECLARE @DateDiff datetime<br />DECLARE @DateDiff1 datetime<br />DECLARE @DateDiff2 datetime <br />DECLARE @time varchar(50)<br />DECLARE @timeStartdate varchar(50)<br />DECLARE @timeEnddate varchar(50)<br />DECLARE @day int<br />DECLARE @count int<br />declare @mygetdate datetime<br />Declare @time1 datetime<br /><br /><br />----Set @DateDiff= 0<br />----Set @DateDiff1= DATEADD(hh, DATEDIFF (hh , @StartDate, @EndDate ), @DateDiff ) <br />-----Set @DateDiff2= DATEDIFF (hh,@StartDate,@EndDate) + @DateDiff1 <br /><br /><br /><br />DECLARE @MyaspDate datetime /*the .aspx page when it loads*/<br />SET @MyaspDate =getdate()<br />SELECT @MyaspDate as[MyaspDate]<br />SELECT DATEPART(weekday, @MyaspDate) as MyaspDay----- Finds Day of the week for TempCreateDate <br />----Convert @myaspDate datetime variable into int<br /><br /><br /><br />set @mygetdate=getdate()<br />Select @mygetdate as [Mygetdate]<br /><br /><br />DECLARE @myday datetime<br />SELECT @myday= TempCreateDate FROM #TempInteractions <br />SELECT DATEPART(weekday, @myday) as TempCreateDay----- Finds Day of the week for TempCreateDate <br />Set @day=DATEPART(weekday,@myday) <br /><br />set @count=0<br />select @count as [InitCount]<br /><br /><br />---- Cursor for #TempInteractions <br />DECLARE CursorInteractions CURSOR FAST_FORWARD READ_ONLY FOR<br />SELECT TempInteractionID, TempClientID, TempInteractionStatusID, TempCreateDate, Status, TimeOpenSec FROM<br /> #TempInteractions<br /><br /> ---- Open CursorInteractions<br />OPEN CursorInteractions<br />--- Store values <br />FETCH NEXT FROM CursorInteractions INTO @TempInteractionID,@TempClientID,@TempInteractionStatusID,@TempCreateDate, @Status,@TimeOpenSec<br />---fetch_status for CursorInteractions<br />while @@fetch_status=0<br />begin<br />---- begin for @@fetch_status of CursorInteractions<br /><br /><br /><br /> Select @myday= TempCreateDate FROM #TempInteractions <br /> <br />---- CONVERT Function to truncate @myday<br /> SELECT CONVERT(VARCHAR,@myday,1) as [Mydaydate]<br />---- Store the truncate value in @time <br /> Set @time=CONVERT(VARCHAR,@myday,1)<br />while @myday &lt;= @mygetdate<br /><br />---- Begin of @myday &lt;=getdate()<br />begin<br />Select @myday as [myDate]<br /> <br />------ INSERT INTO [datewalk]([datevalue])<br /> -----select @myday <br /><br />----1) find the day of the week of @mydate<br />----2) in your case statement compare day(@mydate) to scheduleitemday <br /> <br />--- Cursor for ScheduleItems <br /> DECLARE CursorTest CURSOR FAST_FORWARD READ_ONLY FOR<br /> SELECT st.ScheduleItemID, st.ScheduleID,st.ScheduleItemDay,st.StartDate,st.EndDate<br /> FROM ScheduleItems st <br /> --- Open Cursor for ScheduleItems<br /> OPEN CursorTest<br /> FETCH NEXT FROM CursorTest INTO @ScheduleItemID,@ScheduleID,@ScheduleItemDay,@StartDate,@EndDate<br /> while @@fetch_status=0<br /> begin<br /> ---- CONVERT Function to truncate @StartDate , time only<br /> SELECT CONVERT(VARCHAR,@StartDate,10<img src='/community/emoticons/emotion-11.gif' alt='8)' /> as [StartTime]<br /> Set @timeStartdate=CONVERT(VARCHAR,@StartDate,10<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /> Set @StartDate= CONVERT(datetime,@time+ ' ' + @timeStartdate) <br /> ---- Select @StartDate= @time+ ' ' + @timeStartdate <br /> select @StartDate as [StartDate]<br /> ----Convert VARCHAR to int<br /> <br /><br /> <br /> <br /> ----- CONVERT Function to truncate @EndDate , time only <br /> SELECT CONVERT(VARCHAR,@EndDate,10<img src='/community/emoticons/emotion-11.gif' alt='8)' />as [EndTime]<br /> Set @timeEndDate=CONVERT(VARCHAR,@EndDate,10<img src='/community/emoticons/emotion-11.gif' alt='8)' /> <br /> Set @EndDate=CONVERT(datetime,@time+ ' ' + @timeEndDate)<br /> ---- Select @time+ ' ' + @timeEndDate <br /> Select @EndDate as [End Date]<br /> ----Convert VARCHAR to int<br /> <br /><br />/*----SELECT */<br />If((@day=@ScheduleItemDay)and(@MyaspDate&gt;@EndDate))<br /><br /><br />Select @MyaspDate as [Today's Date]<br />Select @ScheduleItemDay as [Schedule]<br />Select @Day as [day]<br />BEGIN<br />Set @count=@count+DATEDIFF (hh , @StartDate, @EndDate ) <br /> Select @Count as [Count1]<br /> <br /> <br />END<br />---ELSE<br />if(@MyaspDate&gt;@StartDate and @MyaspDate&lt;@EndDate and @ScheduleItemDay=@day)<br />BEGIN<br />Set @count= @count+DATEDIFF (hh,@StartDate,@MyaspDate)<br /> Select @Count as [Count2]<br />END <br />----if (@MyaspDate&lt;@StartDate and @ScheduleItemDay=@day)<br />---BEGIN<br />---Set @count= @count+DATEDIFF(hh, @StartDate,@EndDate)<br /> -- Select @Count as [Count3] <br />---END <br /><br /><br /><br /> FETCH NEXT FROM CursorTest INTO @ScheduleItemID,@ScheduleID,@ScheduleItemDay,@StartDate,@EndDate<br /><br /> end<br />--- End of CursorTest<br /> close CursorTest<br /> deallocate CursorTest <br /><br /><br /> --- SET Status= 'Green' <br /> <br /> --- Update Status ,TimeOpenSec<br /> <br /> select @myday = dateadd(dd,1,@myday)<br />end<br />---End of while @myday&lt;=getdate()<br />Select @count as COUNT<br />Select @count as TotalCount<br /><br /> UPDATE #TempInteractions<br /> SET TimeOpenSec=@count<br /><br /><br />FETCH NEXT FROM CursorInteractions INTO @TempInteractionID,@TempClientID,@TempInteractionStatusID,@TempCreateDate, @Status,@TimeOpenSec<br />-----End of CursorInteractions and Close CursorInteractions<br />end<br /><br />CLOSE CursorInteractions<br /><br />DEALLOCATE CursorInteractions<br /><br />SELECT * FROM #TempInteractions<br /><br /><br /><br />DROP TABLE #TempInteractions<br /><br />GO

Once again you fail to explain what it is youre trying to do.
Your SQL is not self explanatory - we do not know your database, we dont know what youre trying to acheive.

Maybe try replying to your previous threads instead of starting a new one, its kinda tiresome

And I'll add my 2 cents (canadian, so only about 1.5 cents US)

Its very rare to need a cursor in sql anymore. There are almost always far more effecient ways of doing things. Sometimes its better to redesign from scratch than try to modify existing code.

Hi
Try to use CASE ...... WHEN i don't use if statement at all and SQL2000 doesn't seem to support it

May the best cheaters win

I use if stetements with no problem in sql 2000 for control of flow. You dont use them in select, thats where you use the case, but if's for control of flow work fine.

yeah, not sure what youre doing wrong. I dont know how id cope if IF didnt work !