Help with SQL Query

Dev Shed Forums Sponsor:

December 15th, 2012, 03:02 PM

wwwkris

Registered User

Join Date: Dec 2012

Posts: 1

Time spent in forums: 20 m 3 sec
Reputation Power: 0

Help with SQL Query

Given the following tables, how would I set the status in TableA to 'S' only if *all* of the entries in TableB have the same value for amountclaim and amountpay? Thanks!

TableA
+------+--------+
| id | status |
+------+--------+
| 1432 | B |
+------+--------+

TableB
+-------------+------------+-------------+-----------+
| radetail_no | billing_no | amountclaim | amountpay |
+-------------+------------+-------------+-----------+
| 4655 | 1432 | 6.60 | 6.60 |
| 4656 | 1432 | 4.45 | 4.45 |
| 4657 | 1432 | 74.55 | 74.55 |
| 4658 | 1432 | 74.10 | 74.10 |
| 4659 | 1432 | 44.00 | 44.00 |
| 4660 | 1432 | 36.90 | 36.90 |
+-------------+------------+-------------+-----------+

December 15th, 2012, 03:13 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,369

Time spent in forums: 3 Months 1 Week 2 Days 6 h 19 m 34 sec
Reputation Power: 4140

Code:

UPDATE tablea
   SET status =
       CASE WHEN ( SELECT COUNT(DISTINCT amountclaim) FROM tableb ) = 1
             AND ( SELECT COUNT(DISTINCT amountpay) FROM tableb ) = 1
            THEN 'S' 
            ELSE status END

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

December 15th, 2012, 04:51 PM

MrFujin

Lord of the Dance

Join Date: Oct 2003

Posts: 3,129

Time spent in forums: 2 Months 2 Weeks 22 h 54 m 43 sec
Reputation Power: 1736

I think OP want to check, for each row in table B, that the value for amountclaim equals the value for amountpay.
Then, if all rows with same billing_no is equals, update table with a 'S' where id is equal to the billng_no.

Something like below, which is an idea based on on my thought and is not tested:

Code:

UPDATE tableA
	SET status =
		CASE WHEN
			(SELECT billing_no as bno
			 FROM TableB
			 GROUP BY billing_no
			 HAVING SUM(amountclaim) = SUM(amountpay)) = tableA.id 
			THEN 'S'
			ELSE status END

December 15th, 2012, 04:57 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,369

Time spent in forums: 3 Months 1 Week 2 Days 6 h 19 m 34 sec
Reputation Power: 4140

that subquery has to be a scalar subquery (i.e. exactly 1 row, exactly 1 column) and it isn't

December 15th, 2012, 04:58 PM

wwwkris2

Registered User

Join Date: Dec 2012

Posts: 3

Time spent in forums: 23 m 14 sec
Reputation Power: 0

Thank you for the response Rudy. I tried to put together my own query and came up with the one shown below. I think I'm close but, unfortunately, it doesn't update any rows.

Can you see what I'm doing wrong?

UPDATE TableA SET status='S'
WHERE status = 'B'
AND (select sum(amountclaim) FROM TableB WHERE billing_no=TableA.id) = (select sum(amountpay) FROM TableB where billing_no=TableA.id)
AND TableA.id=1432;

December 15th, 2012, 05:38 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,369

Time spent in forums: 3 Months 1 Week 2 Days 6 h 19 m 34 sec
Reputation Power: 4140

just a wild guess, but are you using decimal or float for those columns?

December 15th, 2012, 06:38 PM

wwwkris2

Registered User

Join Date: Dec 2012

Posts: 3

Time spent in forums: 23 m 14 sec
Reputation Power: 0

The amountclaim and amountpay fields both store dollar values but they are varchar(8) fields.

December 15th, 2012, 06:57 PM

MrFujin

Lord of the Dance

Join Date: Oct 2003

Posts: 3,129

Time spent in forums: 2 Months 2 Weeks 22 h 54 m 43 sec
Reputation Power: 1736

Can you change the type to Decimal instead of Varchar? This will be preferred.

Otherwise you will have to perform a CAST to DECIMAL before doing any SUM.
for example:

sql Code:

Original - sql Code
SUM(CAST(amountclaim AS DECIMAL(10,2)))

SUM(CAST(amountclaim AS DECIMAL(10,2)))

December 15th, 2012, 07:12 PM

wwwkris2

Registered User

Join Date: Dec 2012

Posts: 3

Time spent in forums: 23 m 14 sec
Reputation Power: 0

Unfortunately I have no control over the fields. Is that the only thing preventing my query from working?

#10

December 15th, 2012, 08:05 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,369

Time spent in forums: 3 Months 1 Week 2 Days 6 h 19 m 34 sec
Reputation Power: 4140

Quote:

Originally Posted by wwwkris2

Unfortunately I have no control over the fields.

the guy who decided to store numeric data in varchar columns should be shot

without CAST, the varchars are probably being converted to floats (i'm guessing)

try the CAST AS DECIMAL solution, it should work

Help with SQL Query

Developer Shed Advertisers and Affiliates