Chris Drew
asked on
T SQL Query to detect value errors
I have 3 tables, COMPONENT, CHASSISHISTORY and COMPONENTHISTORY which together determine the distance travelled by a component and the sessions it was involved in.
In COMPONENT I have the fields ID, LEFT and NEW where
ID is the database ID of the component
LEFT is the distance remaining on the component
NEW is the original value for distance
So the component has run NEW - LEFT miles.
The component history has fields ID which is the database table ID, COMPONENTID which links to the entry in the COMPONENT Table and CHASSISHISTORY_ID which links to the CHASSISHISTORY table. CHASSISHISTORY holds the distance run by the chassis for a specific session as DISTANCERUN
SO - by summing DISTANCERUN for a specific component I should get the total distance run for the component which in turn should be the same as NEW - LEFT in the component table.
I am looking for errors however so need a query which will list the component ID for each component where these 2 distances do not equal each other but am having problems trying to work it out
So far I have got as far as
SELECT icomponent.icomponent_id, SUM(CHASHIST.ichassishisto ry_distanc e) AS SESSTOTAL
FROM icomponent
LEFT JOIN icomponenthistory COMPHIST ON COMPHIST.icomponent_id = icomponent.icomponent_id
LEFT JOIN ichassishistory CHASHIST ON CHASHIST.ichassishistory_i d = COMPHIST.ichassishistory_i d
GROUP BY icomponent.icomponent_id
ORDER BY icomponent.icomponent_id
This gives me the total for each component as obtained from the CHASSISHISTORY table but I can't see how to also get tyhe component table total in the same query and hopefully how to see if these differ.
In COMPONENT I have the fields ID, LEFT and NEW where
ID is the database ID of the component
LEFT is the distance remaining on the component
NEW is the original value for distance
So the component has run NEW - LEFT miles.
The component history has fields ID which is the database table ID, COMPONENTID which links to the entry in the COMPONENT Table and CHASSISHISTORY_ID which links to the CHASSISHISTORY table. CHASSISHISTORY holds the distance run by the chassis for a specific session as DISTANCERUN
SO - by summing DISTANCERUN for a specific component I should get the total distance run for the component which in turn should be the same as NEW - LEFT in the component table.
I am looking for errors however so need a query which will list the component ID for each component where these 2 distances do not equal each other but am having problems trying to work it out
So far I have got as far as
SELECT icomponent.icomponent_id, SUM(CHASHIST.ichassishisto
FROM icomponent
LEFT JOIN icomponenthistory COMPHIST ON COMPHIST.icomponent_id = icomponent.icomponent_id
LEFT JOIN ichassishistory CHASHIST ON CHASHIST.ichassishistory_i
GROUP BY icomponent.icomponent_id
ORDER BY icomponent.icomponent_id
This gives me the total for each component as obtained from the CHASSISHISTORY table but I can't see how to also get tyhe component table total in the same query and hopefully how to see if these differ.
will this work for you?
It would be very helpful if you could provide a few records from each table so that we could be more precise.
SELECT
icomponent.icomponent_id
, (MAX(icomponent.new) - MAX(icomponent.left)) AS has_run
, SUM(CHASHIST.ichassishistory_distance) AS SESSTOTAL
FROM icomponent
LEFT JOIN icomponenthistory COMPHIST
ON COMPHIST.icomponent_id = icomponent.icomponent_id
LEFT JOIN ichassishistory CHASHIST
ON CHASHIST.ichassishistory_id = COMPHIST.ichassishistory_id
GROUP BY icomponent.icomponent_id
HAVING (MAX(icomponent.new) - MAX(icomponent.left)) <> SUM(CHASHIST.ichassishistory_distance)
ORDER BY
icomponent.icomponent_id
;
nb. the MAX() on new and left is used to avoid listing these into the group by clause. If I understand the data model ok, it should work.It would be very helpful if you could provide a few records from each table so that we could be more precise.
ASKER
I have slightly updated PortletPaul's answer as below just because rounding errors were throwing up more mis-matches
_left so that the distance run matches that returned in the history table?
SELECT icomponent.icomponent_id
, CAST((MAX(icomponent.icomponent_life_new) - MAX(icomponent.icomponent_life_left)) as int) AS has_run
, CAST(SUM(CHASHIST.ichassishistory_distance) as int) AS SESSTOTAL
FROM icomponent
LEFT JOIN icomponenthistory COMPHIST
ON COMPHIST.icomponent_id = icomponent.icomponent_id
LEFT JOIN ichassishistory CHASHIST
ON CHASHIST.ichassishistory_id = COMPHIST.ichassishistory_id
GROUP BY icomponent.icomponent_id
HAVING CAST((MAX(icomponent.icomponent_life_new) - MAX(icomponent.icomponent_life_left)) as int) <> CAST(SUM(CHASHIST.ichassishistory_distance) as int)
ORDER BY
icomponent.icomponent_id
This works great - however would it be possible to modify this so that it would update icomponent.icomponent_lifeSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for this - unfortunately query analyzer doesn't like this query and I am way out of my depth with it! Straight copy and paste gives the following syntax errors...
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'inner'.
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'inner'.
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'AS'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
:) thanks Sharath, haven't seen you in a while.
Yes Paul. busy with project work and not getting time here.
;WITH Cte_Distance
AS
(
SELECT icomponent.icomponent_id, SUM(CHASHIST.ichassishisto
FROM icomponent
LEFT JOIN icomponenthistory COMPHIST ON COMPHIST.icomponent_id = icomponent.icomponent_id
LEFT JOIN ichassishistory CHASHIST ON CHASHIST.ichassishistory_i
GROUP BY icomponent.icomponent_id
)
,
cte_New_Minus_Left
AS
(
SELECT
component_id
,New - Left AS DistanceCheck
FROM Component
)
SELECT
component_id
,SESSTOTAL
,DistanceCheck
FROM
Cte_Distance
JOIN cte_New_Minus_Left
ON Cte_Distance.component_id = cte_New_Minus_Left.compone
AND SESSTOTAL <> DistanceCheck -- WHERE the 2 distances do not match