Compare total values in two tables, find changes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Compare total values in two tables, find changes

Author

Topic

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-10-12 : 07:10:34

Hey I store a total value (I.e the final value of an order in 2 seperate tables).

I do this because I store the value my application calculates and then to store the value my customer is charged by a 3rd party credit card company. Anyway this all isnt that important.

What I want to do is as follows. Perform a select query on both tables so that my select query contains a comman id to link between the two and a total

i.e

SELECT applicationId, totalAmount
FROM tblApplicationPaymentLogs
WHERE (dateAdded >= '20100101') AND (statusId = 1)
ORDER BY dateAdded

SELECT id, total
FROM tblApplications
WHERE (dateAdded > '20091231') AND (status = 2)
ORDER BY dateAdded

So id like the query to find any differences between the two where applicationId in query 1 and id in query 2 are the link between the two.

Just 1 thing to note the values are stored slightly different
i.e in query 1 = 6200
in query 2 = 62

in query 1 = 5450
in query 2 = 54.5

Where the 3 rdparty Total are multiplied by 100.

Cheers

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-12 : 07:21:31

What datatypes are the columns? From the description I'm expecting them to be strings....... which isn't a good idea.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-10-12 : 07:23:09

varchar(50)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-12 : 07:28:42

Can they only contain numbers?

If they can (why the hell are they strings....? If these are MONEY values store them as MONEY) you can do:


SELECT
    a.[Id] AS [Unique Id]
    , a.[total] AS [application Data]
    , l.[total] AS [Log Data]
FROM
    tblApplications AS a
    JOIN tblApplicationPaymentLogs AS
        l ON l.[applicationID] = a.[ID]
        AND l.[statusId] = 1
WHERE
    CAST(a.[totalAmount] AS MONEY) <> ( CAST(l.[total] AS MONEY) * 1000 )

But I expect that you will actually have data in the table that will cause this to fail.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-10-12 : 07:44:31

Thanks man did the trick, yeah they only contained numbers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources