Help on basic SQL View query

[scoveg13]

Hello,

I'm trying to compare two columns based on # of lives and I want to select the greater of the two and have the result be placed in a new column.

Here's my current query:

SELECT [id], [account], [# of full time equivalents], [number of eligible employees]
FROM MaxLivesVol
Where [# of full time equivalents] > [number of eligible employees] or [# of full time equivalents] < [number of eligible employees]

Pulling the data is fine, it's just getting the result of the where clause as a standalone column - something like "MaxLives" is the issue,

 

[xenou]

It would have to go in the select clause to be in the results as a column. The query is not clear and almost looks to be self-contradictory. Perhaps provide an example and explain better what you want to see.

 

[scoveg13]

It would have to go in the select clause to be in the results as a column. The query is not clear and almost looks to be self-contradictory. Perhaps provide an example and explain better what you want to see.

Basically I'm tryng to compare two columns and have the result in a separate column

 

[xenou]

By way of example, this would tell you which is greater and show the result in a third column:

SELECT 
    [id], 
    [account], 
    [# of full time equivalents],
    [number of eligible employees],
    [# of full time equivalents]>[number of eligible employees] as ComparedValues
FROM MaxLivesVol

in MSAccess a -1 means true and a 0 means false, so your results are -1 for true and 0 for false.

 

[scoveg13]

By way of example, this would tell you which is greater and show the result in a third column:

SELECT 
    [id], 
    [account], 
    [# of full time equivalents],
    [number of eligible employees],
    [# of full time equivalents]>[number of eligible employees] as ComparedValues
FROM MaxLivesVol

in MSAccess a -1 means true and a 0 means false, so your results are -1 for true and 0 for false.

This is helpful but I need the actual # not just True/False

 

[xenou]

What number do you want to see?

 

[scoveg13]

the greater of the two columns - #of full time equivalents and number of eligible employees.

if # of full time equivalents is 10 and the number of eligible employees is 12 for Company ABC, I need 12 to show up in a new column - "Compared Values" column.

if # of full time equivalents is 50 and the number of eligible employees is 45 for Company ABC, I need 50 to show up in the "Compared Values" column as well.

 

[xenou]

In this case you can try:

SELECT 
    [id], 
    [account], 
    [# of full time equivalents],
    [number of eligible employees],
	switch(
		[# of full time equivalents]>[number of eligible employees],[# of full time equivalents],
		[# of full time equivalents]<[number of eligible employees],[number of eligible employees],
		1=1,-1
		)
FROM MaxLivesVol

You can remove the line breaks in the switch function, though.

There are two cases you did not specify what you want so the function returns -1 for both of these:
1) if the two values are equal
2) if one or both of the values is NULL.