January 20, 2017 at 4:06 pm
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 in advance
Zo
January 20, 2017 at 6:21 pm
It REALLY helps us help you to provide a create table script, some INSERT statements for sample data and your expected outputs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 21, 2017 at 12:36 pm
Like Eirikur said, we need something to work with. You've had several posts where we keep asking for the same thing. Please take the time to read the link in my signature to get a better understanding of what we need to better answer your questions and why we need it.
January 22, 2017 at 11:22 am
This was removed by the editor as SPAM
January 23, 2017 at 11:51 am
Zososql - Friday, January 20, 2017 4:06 PMdatediff(dd,CASE stats.abbrev WHEN 'A' THEN CONVERT(VARCHAR, eventscheduled, 111) END, CASE stats.abbrev WHEN 'B' THEN CONVERT(VARCHAR, eventscheduled, 111) END)
The above function will always return NULL. When stat.abbrev = 'A', then stats.abbrev cannot = 'B', so the second CASE expression will return the (unspecified) default value of NULL. When stats.abbrev = 'B' then stats.abbrev cannot = 'A', so the first CASE expression will return the (unspecified) default value of NULL. (Both will return NULL when stats.abbrev is neither 'A' nor 'B'.)
When troubleshooting complex expressions like this, you should always take them apart to make sure that you are getting the values that you expect for subcomponents of those expressions.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply