Change output from string to integer

Transact-SQL
1

Morning all,

I've been muling this over for a few days now and can't seem to get my head around it. I need to change the A_Color column to output a number instead of an integer ie 'color [yellow]' might = 1 and 'color [green]' might = 2

Original query:
with tallyno10(n)
as (select 0 from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as tallyno10(n))
,tallyno1000(n)
as (select row_number() over(order by (select null))-1
from tallyno10 as a
cross apply tallyno10 as b
cross apply tallyno10 as c)
select A_Tid
,A_AvailDate
,A_Colour
from availability
where a_availdate>=cast(GETDATE() as date)
union all
select b.b_tid
,dateadd(day,a.n,b.b_startdate)
,'Color [Yellow]'
from tallyno1000 as a
cross apply bookings as b
where dateadd(day,a.n,b.b_startdate)>=cast(GETDATE() as date)
and dateadd(day,a.n,b.b_startdate)<=b.b_enddate

I thought I cold use a case statement but it doesn't seem to work

with tallyno10(n) 
as (select 0 from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as tallyno10(n)) 
,tallyno1000(n) 
as (select row_number() over(order by (select null))-1 
from tallyno10 as a 
cross apply tallyno10 as b 
cross apply tallyno10 as c) 
select A_Tid 
,A_AvailDate 
,A_Colour ,
	CASE
	WHEN A_Colour = 'Color [Violet]' THEN 1
	WHEN A_Colour = 'Color [Green]' THEN 2
	ELSE 0
	END As A_Num
from availability 
where a_availdate>=cast(GETDATE() as date) 
union all 
select b.b_tid 
,dateadd(day,a.n,b.b_startdate) 
,'Color [Yellow]' 
from tallyno1000 as a 
cross apply bookings as b 
where dateadd(day,a.n,b.b_startdate)>=cast(GETDATE() as date) 
and dateadd(day,a.n,b.b_startdate)<=b.b_enddate

Any idea what i'm doing wrong?

thanks

2

You have two select's merged with "union all" where the first one produces 4 columns but the second produces 3 columns.

You should actually receive an error.

3

Thanks for your reply, sorry i wasnt running it in Management Studio. Yes its giving an error in management studio. What would be the best way to recifiy the issue, should I use 2 case statements so both selects produce 4 columns or should I use 1 case statement to cover both selects?

Hope that makes sense....

thanks

4

Well you know your data better than I do, but seeing that column 4 is "just" a code representation of the text i column 3, and that you in the second select has a fixed value in column 3 "Yellow", you could probably just type the fixed color value for yellow in column 4.

5

Doh! Doh! Doh! Kicking my self now..... How did I not see that...!!

Thank you for pointing that out.