Link to home
Start Free TrialLog in
Avatar of Chris Drew
Chris DrewFlag for United Kingdom of Great Britain and Northern Ireland

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.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

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.
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

Using CTE's may be a good approach and give clarity - one CTE for the already stored Distance and another CTE for the calculated distance(New - Left)

;WITH Cte_Distance
AS
(
SELECT icomponent.icomponent_id,  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
)
,
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.component_id
AND SESSTOTAL <> DistanceCheck    -- WHERE the 2 distances do not match
Avatar of PortletPaul
will this work for you?
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
;

Open in new window

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.
Avatar of Chris Drew

ASKER

I have slightly updated PortletPaul's answer as below just because rounding errors were throwing up more mis-matches
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

Open in new window

This works great - however would it be possible to modify this so that it would update icomponent.icomponent_life_left so that the distance run matches that returned in the history table?
SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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'.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
:) thanks Sharath, haven't seen you in a while.
Yes Paul. busy with project work and not getting time here.