SQL Server Performance

Adding Then condition in CASE Condition

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

  1. rahils573 New Member

    Hello Everybody ,
    I want to introduce a variable where I can add DATEADD(hh, DATEDIFF (HH , @StartDate, @EndDate ), @DateDiff ) to a variable in my CASE Statement .So finally I have a single variable where all the DATEADD are add to it.How do I do that .Please guide.



    Rahil

    Set @StartDate='08:00 AM'
    Set @EndDate='05:00 PM'
    Set @DateDiff= 0
    Set @DateDiff1= DATEADD(hh, DATEDIFF (HH , @StartDate, @EndDate ), @DateDiff )
    Set @DateDiff2= DATEDIFF (HH,@StartDate,@EndDate) + @DateDiff1
    Set @time= datetime
    Set @time1=Datetime
    SELECT
    CASE
    WHEN @MyaspDate>@EndDate THEN DATEADD(hh, DATEDIFF (HH , @StartDate, @EndDate ), @DateDiff )
    -- Want to Set @time = DATEADD(hh, DATEDIFF (HH , @StartDate, @EndDate ), @DateDiff )
    ---How do I do that?

    WHEN @MyaspDate>@StartDate and @MyaspDate<@EndDate THEN DATEDIFF (HH,@StartDate,@EndDate) + @DateDiff1
    -- Want to Set @time1 = DATEADD(hh, DATEDIFF (HH , @StartDate, @EndDate ), @DateDiff )
    ---How do I do that?

    WHEN @MyaspDate<@StartDate THEN DATEDIFF(HH, @StartDate,@EndDate) + @DateDiff2
    ---Want to Set @time2=DATEDIFF(HH, @StartDate,@EndDate) + @DateDiff2
    ---How do I do that???
    ELSE 0
    END AS "Time Category"
    FROM ScheduleItems
    ORDER BY ScheduleItemID
  2. Chappy New Member

    Can you elaborate on what it is you want? I think your question is a bit unclear
  3. rahils573 New Member


    I want to add all the Then condition of the CASE statement .How do I do that.

    Please guide..

    Rahil




    quote:Originally posted by rahils573

    Hello Everybody ,
    I want to introduce a variable where I can add DATEADD(hh, DATEDIFF (HH , @StartDate, @EndDate ), @DateDiff ) to a variable in my CASE Statement .So finally I have a single variable where all the DATEADD are add to it.How do I do that .Please guide.



    Rahil

    Set @StartDate='08:00 AM'
    Set @EndDate='05:00 PM'
    Set @DateDiff= 0
    Set @DateDiff1= DATEADD(hh, DATEDIFF (HH , @StartDate, @EndDate ), @DateDiff )
    Set @DateDiff2= DATEDIFF (HH,@StartDate,@EndDate) + @DateDiff1
    Set @time= datetime
    Set @time1=Datetime
    SELECT
    CASE
    WHEN @MyaspDate>@EndDate THEN DATEADD(hh, DATEDIFF (HH , @StartDate, @EndDate ), @DateDiff )
    -- Want to Set @time = DATEADD(hh, DATEDIFF (HH , @StartDate, @EndDate ), @DateDiff )
    ---How do I do that?

    WHEN @MyaspDate>@StartDate and @MyaspDate<@EndDate THEN DATEDIFF (HH,@StartDate,@EndDate) + @DateDiff1
    -- Want to Set @time1 = DATEADD(hh, DATEDIFF (HH , @StartDate, @EndDate ), @DateDiff )
    ---How do I do that?

    WHEN @MyaspDate<@StartDate THEN DATEDIFF(HH, @StartDate,@EndDate) + @DateDiff2
    ---Want to Set @time2=DATEDIFF(HH, @StartDate,@EndDate) + @DateDiff2
    ---How do I do that???
    ELSE 0
    END AS "Time Category"
    FROM ScheduleItems
    ORDER BY ScheduleItemID

  4. Adriaan New Member

    I think you're confusing the SELECT CASE syntax you find in many programming languages, with the SELECT and CASE constructs that you have in T-SQL.

    The syntax you're looking for is probably this:

    SELECT @time =
    CASE WHEN @MyaspDate>@EndDate THEN DATEADD(hh, DATEDIFF (HH , @StartDate, @EndDate ), @DateDiff )
    WHEN ................... THEN ..................
    WHEN ................... THEN ..................
    WHEN ................... THEN ..................
    END

    Also, it may well be possible to do the CASE resolution inside the DATEADD and DATEDIFF functions. Depends on what you find easier to review and maintain; I don't think it will make a lot of difference regarding execution time.
  5. FrankKalis Moderator

    I think Adriaan is right on track, at least from what I have understood from your narrative.
    And if not, I don't think we'll get any much further unless you don't give an example of what you want. That is the input data and the expected output.


    --
    --Frank
    http://www.insidesql.de
  6. Twan New Member

    Hi ya,

    you can put a case statement within a sum()... so sum( case ... end )

    but a sum expects a numeric field so you can't sum dates or chars in this way...

    Cheers
    Twan
  7. ChrisFretwell New Member

    What are you using @myaspdate for? What are you getting with the select on the table???

    What data type is time category? Is it an integer with the number of hours different? Is it a date? or ???

    You are mixing dateadd with datediff for you case statement results.



Share This Page