I would like to select which rate is greater and enter it in a single query result based on the results of a UNION ALL. For example employee 200 makes 25 dollars as a base rate per hour but the job he works on has a base rate of 10.00. He should be getting 25.00 per hour then. Employee 100 has a base rate of 10.00 but the job's base rate is 25.00. So he should get 25.00 per hour as well. I would like to select the highest rate for each employee. Something similar to this idea. SELECT EmployeeID, RATE_A or RATE_B from .... Here is some data I put together
CREATE Table WageRate(
[ID] [int] IDENTITY(1,1) NOT NULL,
[RateCode] int NULL,
[Rate] Decimal (10,2) NULL
)
INSERT INTO WageRate( RateCode,Rate)
Values (1,10.00), (2,15.00), (3,20.00), (4,25.00)
Create Table Employee(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] int NULL,
[RateCode] int NULL
)
Insert Into Employee (EmployeeID,RateCode)
Values (100,1), (200,4)
Create Table TimeCards(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] int NULL,
[Hours] Decimal (10,2) NULL,
[JobRateCode] int NULL
)
Insert Into TimeCards (EmployeeID,[Hours],JobRateCode)
Values (100,8.00,4), (200,8.00,1)
SELECT t1.Employeeid ,(t0.Rate) as [Rate_A] ,Null FROM WageRate t0
INNER JOIN Employee t1 ON t1.RateCode= t0.RateCode
INNER JOIN TimeCards t2 on t1.EmployeeID = t2.EmployeeID
UNION ALL
SELECT t4.Employeeid ,Null,(t3.Rate) As [Rate_B] FROM WageRate t3
INNER JOIN TimeCards t4 on t4.JobRateCode = t3.RateCode
INNER JOIN Employee t5 ON t4.EmployeeID= t5.EmployeeID
Using a case expression you can do it with a single query without a union:
SELECT e.EmployeeID,
CASE WHEN ISNULL(ew.Rate, 0.0) > ISNULL(jw.Rate, 0.0) THEN
ew.Rate
ELSE
jw.Rate
END As Rate
FROM Employee e
LEFT JOIN TimeCards t On e.EmployeeID = t.EmployeeID
LEFT JOIN WageRate ew ON e.RateCode = ew.RateCode
LEFT JOIN WageRate jw ON t.JobRateCode = jw.RateCode