SQL Server Performance

While and IF Else Condition-Problem

Discussion in 'General Developer Questions' started by rahils573, Sep 8, 2004.

  1. rahils573 New Member

    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
  2. Chappy New Member

    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




  3. satya Moderator

  4. FrankKalis Moderator

    quote:Originally posted by Chappy

    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

    Not only that, I'm sure your problem isn't that tough. The time you've spent now starting new threads could have been better spent on finding a solution.

    --
    --Frank
    http://www.insidesql.de
  5. ChrisFretwell New Member

    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.
  6. tdong New Member

    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
  7. ChrisFretwell New Member

    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.
  8. Chappy New Member

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

Share This Page