This will get you the Status Description:
SELECT s.SR_Service_RecID, SR_Status.Description, c.Company_Name, s.Last_Update, s.Date_Req
FROM SR_Service s
INNER JOIN Company c ON s.Company_RecID = c.Company_RecID
INNER JOIN SR_Status ON s.ST_Status_RecID = SR_Status.SR_Status_RecID
WHERE SR_Type_RecID IN (SELECT t.SR_Type_RecID
FROM SR_Type t
INNER JOIN SR_Board b
ON t.SR_Board_RecID = b.SR_Board_RecID
WHERE t.description LIKE @Type
AND b.Board_Name LIKE @Board)
AND s.SR_Status_RecID IN (SELECT SR_Status_RecID
FROM SR_Status
WHERE Closed_Flag = 0)
To add the schedule, you'd need to add a key field from the schedule
table to the SR_Service
table.
The performance impact from multiple logical reads could be a result of your UDF.
Provided is a great article which describes using inline table-valued UDFs to reduce the row-by-row calls to the scalar UDF. UPDATE: I noticed you were already using table-valued UDF.
http://dataeducation.com/scalar-functions-inlining-and-performance-an-entertaining-title-for-a-boring-post/
To replicate your scenario, I executed the following query:
DECLARE
@Measurements TABLE(Precidence int, -- the relative scale of a measurement
Measurement varchar(max), -- Grams, Kilograms, Liters, etc
MeasurementType varchar(max)); -- Weight, Volume, etc`
DECLARE
@Items TABLE(ItemType varchar(max),
Quantity float,
Measurement varchar(max),
ToMeasurement varchar(max));
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','G');
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','KG');
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','ML');
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','L');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('2','G','Weight');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('1','KG','Weight');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('1','GAL','Volume');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('2','L','Volume');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('3','ML','Volume');
Afterwards, I complied and executed the following update statement:
WITH Items_CTE AS
(
SELECT A.ItemType,
A.Quantity,
A.Measurement,
(SELECT TOP 1 M.Measurement FROM @Measurements M
JOIN @Items C ON C.Measurement = M.Measurement
WHERE M.MeasurementType = B.MeasurementType
AND C.ItemType = A.ItemType
ORDER BY Precidence)ToMeasurement
FROM @Items A
JOIN @Measurements B
ON A.Measurement = B.Measurement
)
UPDATE @Items
SET ToMeasurement = cte.ToMeasurement
FROM ITEMS_CTE as cte
JOIN @Items as i
ON cte.measurement = i.measurement;
The UPDATE
statement uses a CTE and the results were as follows:
ItemType | Quantity | Measurement | ToMeasurement
Widget | 1 | G | KG
Widget | 1 | KG | KG
Widget | 1 | ML | L
Widget | 1 | L | L
Best Answer
Postgres allows to join tables in an UPDATE statement:
This assumes that
incident_id
is unique in both tables.