Determine Traffic Light Status with Sql

Question

Post reply

Determine Traffic Light Status with Sql

za34571

SSC Veteran

Points: 201
More actions
July 7, 2009 at 4:39 pm

#133669

I have two tables as structured and populated below. The #TrafficLight table has up to 3 rows for each row
in #TableA, each row indicating a traffic light colour/ status (red, amber, greeen) for the #TableA row it is related to.
The CompareOperator in the #TrafficLight table can contain any of the following operators (<, >, =, <>, <=, =>, between)
The CompareOffset and CompareUpperOffset coloumns in #TrafficLight can be used to set and determine what number or range indicates what colour/status the related row in #TableA is.
The value in the Value column in #TableA is used to compare against the CompareOffset and CompareUpperOffset columns.
How would I determine the current colour of a row in #TableA with Sql?
CREATE TABLE #TableA
(
TableAId INT,
Value INT
)
GO
INSERT #TableA
SELECT 1, 70
UNION ALL
SELECT 2, 60
GO
CREATE TABLE #TrafficLight
(
TrafficLightId INT,
TableAId_Fk INT,
Colour INT,
CompareOperator VARCHAR(15),
CompareOffset INT,
CompareUpperOffset int
)
GO
INSERT #TrafficLight (TrafficLightId, TableAId_Fk, Colour, CompareOperator, CompareOffset, CompareUpperOffset)
SELECT 1, 1, 1 /* red */, '<', 50, 0
UNION ALL
SELECT 2, 1, 3 /* green */, '>', 51, 0
UNION ALL
SELECT 3, 2, 1 /* red */, 'between', 10, 100
UNION ALL
SELECT 4, 2, 3 /* green */, 'between', 101, 200
SELECT * FROM #TableA
SELECT * FROM #TrafficLight
DROP TABLE #TableA
DROP TABLE #TrafficLight
Thanks in advance, Zak.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Amu-1082941 SSC Enthusiast Points: 195 More actions · Answer 1

Hi,

Hope this is what you are expecting :

declare @sql varchar(1000)

select @sql = 'case '

select @sql = @sql + ' when value ' + compareoperator + ' ' +

case

when compareoperator 'between' then convert (varchar(1000),CompareOffset)

else convert (varchar(1000),CompareOffset) + ' and ' +convert (varchar(1000),CompareupperOffset)

end

+' then '+ convert(varchar(100),colour )

from #TrafficLight

execute ('Select distinct A.TableAId,'+@sql+' end as light

from #TableA A,#TrafficLight L

where a.TableAId=l.TableAId_Fk ')

Try this...

ravi4work SSC Veteran Points: 201 More actions · Answer 2

select A.tableAid , TrafficLightId, B.colour AS colour from #TableA A

inner join #TrafficLight B

on. A.TableAId=B.TableAId_Fk

where case compareoperator when '>' then A.value end > convert(varchar(20),compareoffset) OR

case compareoperator when '<' then A.value end < convert(varchar(20),compareoffset) OR

case compareoperator when 'between' then A.value end between convert(varchar(20),compareoffset) and convert(varchar(20),compareupperoffset)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

za34571 ,

You all set or what? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)