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.
ChrisMDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
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
0
PortletPaulfreelancerCommented:
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.
0
ChrisMDAuthor Commented:
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?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

PortletPaulfreelancerCommented:
untested, but it would look something like this (using a join);
update icomponent
set icomponent_life_left = h.SESSTOTAL
inner join (
            SELECT
                     i.icomponent_id
                   , CAST(SUM(CHASHIST.ichassishistory_distance) as int) AS SESSTOTAL
            FROM icomponent AS I
            LEFT JOIN icomponenthistory COMPHIST
                    ON COMPHIST.icomponent_id = i.icomponent_id
            LEFT JOIN ichassishistory CHASHIST
                    ON CHASHIST.ichassishistory_id = COMPHIST.ichassishistory_id
            GROUP BY i.icomponent_id
            HAVING CAST((MAX(i.icomponent_life_new) - MAX(i.icomponent_life_left)) as int) <> CAST(SUM(CHASHIST.ichassishistory_distance) as int)
          ) AS H
            ON icomponent.icomponent_id = h.icomponent_id

Open in new window

please test carefully.
0
ChrisMDAuthor Commented:
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'.
0
SharathData EngineerCommented:
Paul missed the "from" part.
update i1
set icomponent_life_left = h.SESSTOTAL
from icomponent i1
inner join (
            SELECT
                     i.icomponent_id
                   , CAST(SUM(CHASHIST.ichassishistory_distance) as int) AS SESSTOTAL
            FROM icomponent AS I
            LEFT JOIN icomponenthistory COMPHIST
                    ON COMPHIST.icomponent_id = i.icomponent_id
            LEFT JOIN ichassishistory CHASHIST
                    ON CHASHIST.ichassishistory_id = COMPHIST.ichassishistory_id
            GROUP BY i.icomponent_id
            HAVING CAST((MAX(i.icomponent_life_new) - MAX(i.icomponent_life_left)) as int) <> CAST(SUM(CHASHIST.ichassishistory_distance) as int)
          ) AS H
            ON i1.icomponent_id = h.icomponent_id

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
:) thanks Sharath, haven't seen you in a while.
0
SharathData EngineerCommented:
Yes Paul. busy with project work and not getting time here.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.