I am able to compare two dates in my recordset and subtract one from the other (eventadded and eventscheduled are datetime data types), and assign the difference as aftersked alias:

,(datediff(dd, Eventadded, CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END) -(datediff(wk, Eventadded, CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END) * 2) -(case when datepart(dw, Eventadded) in (1) then 1 else 0 end) +(case when datepart(dw, CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END) in (1) then 1 else 0 end) ) as aftersked

..but when i compare two other conditions for days difference, the below always returns 0 , but I know there is a value for most rows for the B valued container (in which abbrev is a varchar datatype) :

,(datediff(dd,CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END, CASE stats.abbrev WHEN 'B' THEN CONVERT(VARCHAR, eventscheduled, 111) END) -(datediff(wk, CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END, CASE stats.abbrev WHEN 'B' THEN CONVERT(VARCHAR, eventscheduled, 111) END) * 2) -(case when datepart(dw, CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END) in (1) then 1 else 0 end) +(case when datepart(dw, CASE stats.abbrev WHEN 'B' THEN CONVERT(VARCHAR, eventscheduled, 111) END) in (1) then 1 else 0 end) ) as afterA

I can't figure out why the second CASE WHEN isnt' producing the output. Thanks for all help
Qwest

I think relevant column value is leading to the problem. You should put Else clause to your Case statement, for also showing some output in case that there is no MATCHING values.

,(datediff(dd,CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END, CASE stats.abbrev WHEN 'B' THEN CONVERT(VARCHAR, eventscheduled, 111) END) -(datediff(wk, CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END, CASE stats.abbrev WHEN 'B' THEN CONVERT(VARCHAR, eventscheduled, 111) END) * 2) -(case when datepart(dw, CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END) in (1) then 1 else 0 end) +(case when datepart(dw, CASE stats.abbrev WHEN 'B' THEN CONVERT(VARCHAR, eventscheduled, 111) END) in (1) then 1 else 0 end) ) as afterA

Yes, understand that using ELSE is good practice in CASE WHEN ; my other alternative is to create a FUNCTION and call the function for this first parameter of the datediff and datepart above (noted with the
boldunderline) . In my first example whereas I have Eventadded as the first parameter in these, Eventadded is a datetime datatype field in the main table.
Qwest

You have two conditions in your statement. Please try to simplify it and note that value of either stats.abbrev or eventscheduled, is affecting the result.

I need to find a way to use a derived column for that expression i have highlighted in my last post. stats.abbrev is a field that is already sitting in my statistics table.
Thanks

If ELSE argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.

None

0 Points

29 Posts

## case when statement not displaying derived column data

Jan 20, 2017 11:26 PM|qwester|LINK

I am able to compare two dates in my recordset and subtract one from the other (eventadded and eventscheduled are datetime data types), and assign the difference as aftersked alias:

,(datediff(dd, Eventadded, CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END)

-(datediff(wk, Eventadded, CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END) * 2)

-(case when datepart(dw, Eventadded) in (1) then 1 else 0 end)

+(case when datepart(dw, CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END) in (1) then 1 else 0 end)

) as aftersked

..but when i compare two other conditions for days difference, the below always returns 0 , but I know there is a value for most rows for the B valued container (in which abbrev is a varchar datatype) :

,(datediff(dd,CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END, CASE stats.abbrev WHEN 'B' THEN CONVERT(VARCHAR, eventscheduled, 111) END)

-(datediff(wk, CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END, CASE stats.abbrev WHEN 'B' THEN CONVERT(VARCHAR, eventscheduled, 111) END) * 2)

-(case when datepart(dw, CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END) in (1) then 1 else 0 end)

+(case when datepart(dw, CASE stats.abbrev WHEN 'B' THEN CONVERT(VARCHAR, eventscheduled, 111) END) in (1) then 1 else 0 end)

) as afterA

I can't figure out why the second CASE WHEN isnt' producing the output.

Thanks for all help

Qwest

Contributor

6492 Points

5846 Posts

## Re: case when statement not displaying derived column data

Jan 21, 2017 02:54 AM|wmec|LINK

Hi,

I think relevant column value is leading to the problem. You should put Else clause to your Case statement, for also showing some output in case that there is no MATCHING values.

Peter

None

0 Points

29 Posts

## Re: case when statement not displaying derived column data

Jan 24, 2017 09:51 PM|qwester|LINK

,(datediff(dd,

CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END, CASE stats.abbrev WHEN 'B' THEN CONVERT(VARCHAR, eventscheduled, 111) END)-(datediff(wk,

CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END, CASE stats.abbrev WHEN 'B' THEN CONVERT(VARCHAR, eventscheduled, 111) END) * 2)-(case when datepart(dw,

CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END)in (1) then 1 else 0 end)+(case when datepart(dw, CASE stats.abbrev WHEN 'B' THEN CONVERT(VARCHAR, eventscheduled, 111) END) in (1) then 1 else 0 end)

) as afterA

Yes, understand that using ELSE is good practice in CASE WHEN ; my other alternative is to create a FUNCTION and call the function for this first parameter of the datediff and datepart above (noted with the

boldunderline) . In my first example whereas I have Eventadded as the first parameter in these, Eventadded is a datetime datatype field in the main table.Qwest

Contributor

6492 Points

5846 Posts

## Re: case when statement not displaying derived column data

Jan 25, 2017 05:04 AM|wmec|LINK

You have two conditions in your statement. Please try to simplify it and note that value of either stats.abbrev or eventscheduled, is affecting the result.

Peter

None

0 Points

29 Posts

## Re: case when statement not displaying derived column data

Jan 25, 2017 04:56 PM|qwester|LINK

I need to find a way to use a derived column for that expression i have highlighted in my last post.

stats.abbrevis a field that is already sitting in my statistics table.Thanks

Qwest

All-Star

17652 Points

3510 Posts

## Re: case when statement not displaying derived column data

Feb 07, 2017 08:25 AM|Chris Zhao|LINK

Hi Qwester,

If ELSE argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.

reference: https://msdn.microsoft.com/en-us/library/ms181765.aspx

Best Regards,

Chris