July 4, 2017 at 10:02 pm
Hi
I am trying to add in the case statement below
if sn_status <> 'COMP' and sn_type='De-Activated' then 'NOT COMP'
Could you tell me how I could incorporate that?
Thanks
Dani
,case
when sum(case when sn_status='COMP' and sn_type='De-Activated' then 1 else 0 end)>0
then str(datediff(day
,min(case
when (sn_type='De-Activated'
and sn_status='COMP'
)
or (sn_type='Re-Activattion'
and sn_status='N-CO'
)
then created_date
else null
end
)
,min(case
when (sn_type='De-Activated'
and sn_status='COMP'
)
or (sn_type='Re-Activattion'
and sn_status='COMP'
)
then completed_date
else null
end
)
)
)
else 'NO DEACT'
end
as comp_deactivated
July 5, 2017 at 5:50 am
case
when (sn_type='De-Activated' and sn_status='COMP')
or (sn_type='Re-Activattion' and sn_status='N-CO')
then created_date
WHEN sn_status <> 'COMP' and sn_type='De-Activated' then 'NOT COMP'
else null
end
John
July 5, 2017 at 6:01 am
John Mitchell-245523 - Wednesday, July 5, 2017 5:50 AMcase
when (sn_type='De-Activated' and sn_status='COMP')
or (sn_type='Re-Activattion' and sn_status='N-CO')
then created_date
WHEN sn_status <> 'COMP' and sn_type='De-Activated' then 'NOT COMP'
else null
endThanks John but that will provide me with a new column
I am trying to use the logic you wrote but in the provided sttament.
So it's one column.
July 5, 2017 at 7:25 am
danijel.bozic87 - Tuesday, July 4, 2017 10:02 PMHi
I am trying to add in the case statement below
if sn_status <> 'COMP' and sn_type='De-Activated' then 'NOT COMP'
Could you tell me how I could incorporate that?
Thanks
Dani
,case
when sum(case when sn_status='COMP' and sn_type='De-Activated' then 1 else 0 end)>0
then str(datediff(day
,min(case
when (sn_type='De-Activated'
and sn_status='COMP'
)
or (sn_type='Re-Activattion'
and sn_status='N-CO'
)
then created_date
else null
end
)
,min(case
when (sn_type='De-Activated'
and sn_status='COMP'
)
or (sn_type='Re-Activattion'
and sn_status='COMP'
)
then completed_date
else null
end
)
)
)
else 'NO DEACT'
end
as comp_deactivated
Give us a CREATE table statement, some INSERTs to populate it, and your expected output and we can provide you with working code in a jiffy. Without those we are doing nothing more than trying to guess at what you need - and most of us won't waste our time with guesses.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 5, 2017 at 5:01 pm
TheSQLGuru - Wednesday, July 5, 2017 7:25 AMdanijel.bozic87 - Tuesday, July 4, 2017 10:02 PMHi
I am trying to add in the case statement below
if sn_status <> 'COMP' and sn_type='De-Activated' then 'NOT COMP'
Could you tell me how I could incorporate that?
Thanks
Dani
,case
when sum(case when sn_status='COMP' and sn_type='De-Activated' then 1 else 0 end)>0
then str(datediff(day
,min(case
when (sn_type='De-Activated'
and sn_status='COMP'
)
or (sn_type='Re-Activattion'
and sn_status='N-CO'
)
then created_date
else null
end
)
,min(case
when (sn_type='De-Activated'
and sn_status='COMP'
)
or (sn_type='Re-Activattion'
and sn_status='COMP'
)
then completed_date
else null
end
)
)
)
else 'NO DEACT'
end
as comp_deactivatedGive us a CREATE table statement, some INSERTs to populate it, and your expected output and we can provide you with working code in a jiffy. Without those we are doing nothing more than trying to guess at what you need - and most of us won't waste our time with guesses.
Got it
Thanks, I will keep that in mind next time i post a question.
By the way
This was the solution
when sum(case when SN_TYPE='Re-energisation' then 1 else 0 end)>0
then 'NOT COMP'
else 'NO RE-ACT'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply