CODEWITHSUNDEEP
sqlsql-server
Simos Fasouliotis
Simos Fasouliotis

Reputation: 1390

MSSQL Case Where Clause

i have the following query.

    SELECT DISTINCT datepart(day, eventdetails_datetime) as dates , bu_events.event_daysbeforeType
FROM "bu_events" 

INNER JOIN "bu_eventdetails" ON "bu_eventdetails"."event_id" = "bu_events"."event_id"

WHERE "bu_events"."is_active" = 1 
AND "bu_events"."is_deleted" =0 
AND "bu_eventdetails"."is_active" = 1 
AND "bu_eventdetails"."is_deleted" =0 
AND "bu_events"."service_id" = '31' 
AND (DATEDIFF(Minute, BU_EventDetails.eventDetails_datetime, '2017-07-12 10:33:19') <=0 or coalesce(event_always_available,0) = 1 ) 
AND 

(DATEDIFF(d, '2017-07-12 10:33:19',BU_EventDetails.eventDetails_datetime) >= coalesce(BU_Events.event_daysbefore, 0) or coalesce(event_always_available,0) = 1 ) 


AND ( select max(availabletable_amount) 
    from bu_availabletable 
    inner join bu_eventdetails on bu_eventdetails.eventdetail_id = bu_availabletable.eventdetail_id 
    inner join bu_eventpricegroups on bu_eventpricegroups.event_id = bu_eventdetails.event_id 
    where bu_availabletable.eventdetail_id = bu_eventdetails.eventdetail_id 
    and bu_eventpricegroups.eventpricegroup_always_available = 0 ) > 0 
    AND datepart(Month,eventdetails_datetime) = 07 
    AND datediff(d,getdate(),eventdetails_datetime)>=0

What i'm trying to do, is if bu_events.event_daysbeforeType is 1 then (DATEDIFF(d, '2017-07-12 10:33:19',BU_EventDetails.eventDetails_datetime) >= coalesce(BU_Events.event_daysbefore, 0) or coalesce(event_always_available,0) = 1 ) and if it's 2 then (DATEDIFF(hour, '2017-07-12 10:33:19',BU_EventDetails.eventDetails_datetime) >= coalesce(BU_Events.event_daysbefore, 0) or coalesce(event_always_available,0) = 1 )

So depending on bu_events.event_daysbeforeType i want to datediff with D or HOUR.

What i've tried so far but not working is this: 

SELECT DISTINCT datepart(day, eventdetails_datetime) as dates , bu_events.event_daysbeforeType
FROM "bu_events" 

INNER JOIN "bu_eventdetails" ON "bu_eventdetails"."event_id" = "bu_events"."event_id"

WHERE "bu_events"."is_active" = 1 
AND "bu_events"."is_deleted" =0 
AND "bu_eventdetails"."is_active" = 1 
AND "bu_eventdetails"."is_deleted" =0 
AND "bu_events"."service_id" = '31' 
AND (DATEDIFF(Minute, BU_EventDetails.eventDetails_datetime, '2017-07-12 10:33:19') <=0 or coalesce(event_always_available,0) = 1 ) 
AND 

CASE 
WHEN bu_events.event_daysbeforeType = 1 THEN
(DATEDIFF(d, '2017-07-12 10:33:19',BU_EventDetails.eventDetails_datetime) >= coalesce(BU_Events.event_daysbefore, 0) or coalesce(event_always_available,0) = 1 )  
ELSE 
(DATEDIFF(hour, '2017-07-12 10:33:19',BU_EventDetails.eventDetails_datetime) >= coalesce(BU_Events.event_daysbefore, 0) or coalesce(event_always_available,0) = 1 )  
END

AND ( select max(availabletable_amount) 
    from bu_availabletable 
    inner join bu_eventdetails on bu_eventdetails.eventdetail_id = bu_availabletable.eventdetail_id 
    inner join bu_eventpricegroups on bu_eventpricegroups.event_id = bu_eventdetails.event_id 
    where bu_availabletable.eventdetail_id = bu_eventdetails.eventdetail_id 
    and bu_eventpricegroups.eventpricegroup_always_available = 0 ) > 0 
    AND datepart(Month,eventdetails_datetime) = 07 
    AND datediff(d,getdate(),eventdetails_datetime)>=0

Any ideas what i'm doing wrong ?

Upvotes: 0

Views: 97

Answers (2)

Serg
Serg

Reputation: 22811

CASE is for computing the value and as SQL has no logical (boolean) values it shouldn't be mixed up with logical expressions but a computed value is a valid argument of logical expression. Try 

AND (CASE WHEN bu_events.event_daysbeforeType = 1 
      THEN (DATEDIFF(d, '2017-07-12 10:33:19',BU_EventDetails.eventDetails_datetime) 
      ELSE DATEDIFF(hour, '2017-07-12 10:33:19',BU_EventDetails.eventDetails_datetime) 
    END >= coalesce(BU_Events.event_daysbefore, 0) or coalesce(event_always_available,0) = 1 
)

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

You can't use case like you this. case is an expression, and you try to use it as flow control.
From MSDN:

The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures. For a list of control-of-flow methods, see Control-of-Flow Language (Transact-SQL).

Instead of using case, do this:

AND 
(
    (
        bu_events.event_daysbeforeType = 1 
        AND (DATEDIFF(d, '2017-07-12 10:33:19',BU_EventDetails.eventDetails_datetime) >= coalesce(BU_Events.event_daysbefore, 0) or coalesce(event_always_available,0) = 1 )  
    )
    OR 
    (
        bu_events.event_daysbeforeType <> 1 
        AND (DATEDIFF(hour, '2017-07-12 10:33:19',BU_EventDetails.eventDetails_datetime) >= coalesce(BU_Events.event_daysbefore, 0) or coalesce(event_always_available,0) = 1 )  
    )

)

Upvotes: 2

Related Questions