merge case when

Question

merge case when

immaduddinahmed

Ten Centuries

Points: 1375
More actions
June 18, 2013 at 4:23 am

#276466

i have this syntax
case when convert(varchar(10),t.[Timein],108) >= convert(varchar(10),t4.ltime,108) and spendtime is not null then 'Late'
else '' end remarks
case when (540- Time_Minutes) >= 120 then 'HALF DAY'
else '' end HALFDAY
i want to merge this two syntax and show in one column name remarks
immad

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

I'm not sure that I understood what you want. If all you want is to concatenate the results of the results of both case statements into one column, then remove the name that you gave to the first case statement and add a plus sign between them:

select case when convert(varchar(10),t.[Timein],108) >= convert(varchar(10),t4.ltime,108) and spendtime is not null then 'Late'

else '' end

+ case when (540- Time_Minutes) >= 120 then 'HALF DAY'

else '' end as Remarks

from MyTable

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

immaduddinahmed Ten Centuries Points: 1375 More actions · Answer 2

yes this is what i want Thanks

but it gives me like this

Remarks

LateHALFDAY

i want this

late / halfday

when only halfday remarks show this Halfday

and when late and halfday remark show this LATE / HALFDAY

when only late remark show LATE

immad

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

immaduddinahmed (6/18/2013)
yes this is what i want Thanks
but it gives me like this
Remarks
LateHALFDAY
i want this
late / halfday
when only halfday remarks show this Halfday
and when late and halfday remark show this LATE / HALFDAY
when only late remark show LATE

That would require very few changes to code that Adi had provided you.

Did you try solving this yourself? If Yes,what did you come up with?

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

immaduddinahmed Ten Centuries Points: 1375 More actions · Answer 4

i use this

when convert(varchar(10),t.[Timein],108) >= convert(varchar(10),t4.ltime,108) and spendtime is not null then 'LATE -'

else '' end

+ case when (t4.minute- Time_Minutes) >= 120 then + '- HALF DAY'

i alse use this

when convert(varchar(10),t.[Timein],108) >= convert(varchar(10),t4.ltime,108) and spendtime is not null then 'LATE '

else '' end

+ case when (t4.minute- Time_Minutes) >= 120 then + 'HALF DAY'

else '' end

+ case when convert(varchar(10),t.[Timein],108) >= convert(varchar(10),t4.ltime,108) and spendtime is not null and

(t4.minute- Time_Minutes) >= 120 then + 'L / H'

but not good

immad

immaduddinahmed Ten Centuries Points: 1375 More actions · Answer 5

immaduddinahmed

Ten Centuries

Points: 1375

June 18, 2013 at 6:59 am

#1625007

Thanks i get it

immad

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 6

immaduddinahmed (6/18/2013)
Thanks i get it

Great. I am glad you got it yourself.

Anyways here is what I came up with

SELECTCASE

WHEN CONVERT(VARCHAR(10), t.[Timein], 108) >= CONVERT(VARCHAR(10), t4.ltime, 108) AND spendtime IS NOT NULL AND ( 540 - Time_Minutes) >= 120 THEN 'LATE/HALF DAY'

WHEN CONVERT(VARCHAR(10), t.[Timein], 108) >= CONVERT(VARCHAR(10), t4.ltime, 108) AND spendtime IS NOT NULL THEN 'LATE'

WHEN ( 540 - Time_Minutes) >= 120 THEN 'HALF DAY'

ELSE ''

END AS Remarks

FROM MyTable

You can post your solution and use whichever you feel is better.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

immaduddinahmed Ten Centuries Points: 1375 More actions · Answer 7

i do like this

when convert(varchar(10),t.[Timein],108) >= convert(varchar(10),t4.ltime,108) and

spendtime is not null then 'LATE '

else '' end

+ case when (t4.minute- Time_Minutes) >= 120 then + 'HALF DAY'

else '' end Remarks

immad

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 8

immaduddinahmed (6/18/2013)
i do like this
when convert(varchar(10),t.[Timein],108) >= convert(varchar(10),t4.ltime,108) and
spendtime is not null then 'LATE '
else '' end
+ case when (t4.minute- Time_Minutes) >= 120 then + 'HALF DAY'
else '' end Remarks

Does this give you the desired output( output like 'LATE/HALF DAY' )? I don't think it will.

Check the solution I have posted and let me know if it gives you the correct output.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

immaduddinahmed Ten Centuries Points: 1375 More actions · Answer 9

actullay i am fresh in this field so its ok(LATE HALFDAY). i have to learn more from the internet and the forum like sql server central

yes your query is giving me the correct out put

immad

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 10

immaduddinahmed (6/18/2013)
actullay i am fresh in this field so its ok(LATE HALFDAY). i have to learn more from the internet and the forum like sql server central
yes your query is giving me the correct out put

Don't worry. You will learn more and more with experience. Good luck 🙂

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

immaduddinahmed Ten Centuries Points: 1375 More actions · Answer 11

immaduddinahmed

Ten Centuries

Points: 1375

June 18, 2013 at 10:48 pm

#1625278

Thanks 🙂

immad