Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

SQL compare sum and values in a table

Hello Experts,

I have a table with n rows (Attached is the pic of the table)

I am asked to write an IF condition on these table rows . The prin_amount of the min(end_balance) should be equal to the SUM of (min(end_balance) + and end_balance one above that.)

What is the Best SQL script , I wrote something which works but looks too rookie...

Here is the code
IF ((select prin_amount from #ws_cdc_install where end_balance in(select min(end_balance) from #ws_cdc_install)) =
		(SELECT sum (abs(end_balance))FROM (
		  SELECT
			ROW_NUMBER() OVER (ORDER BY due_date desc) AS rownumber,
			end_balance
		  from #ws_cdc_install
		) AS foo
		WHERE rownumber <= ((select  COUNT(*) from #ws_cdc_install)-1)))
Begin

Open in new window

SQL-image-EE.JPG
0
Dan Schimo
Asked:
Dan Schimo
  • 5
  • 2
1 Solution
 
PortletPaulCommented:
what version of sql server is this for?
>> it will matter as available functions to meet this need differ

e.g. the lead/lag functions

(as a general rule please do indicate version - it helps)
0
 
PortletPaulCommented:
{sorry corrections needed)
0
 
PortletPaulCommented:
think the following may be useful. The upper subquery (row 4) can only return a single result so no point using IN(), the filter (row 13) in the second subquery only wants 2 rows I believe else the calculation probably will never equal prin_amount. Then not sure what the IF evaluates to (not null perhaps?)
IF (
    SELECT prin_amount
    FROM ws_cdc_install
    WHERE end_balance = ( SELECT min(end_balance) FROM ws_cdc_install  )
    AND prin_amount = (
                        SELECT sum(abs(end_balance))
                        FROM (
                                SELECT
                                  ROW_NUMBER() OVER (ORDER BY due_date DESC) AS rownumber
                                , end_balance
                                FROM ws_cdc_install
                                ) AS foo
                        WHERE rownumber < 3 /* only rows 1 and 2 */
                        )
        ) /*  > 0?, not null? */
BEGIN

Open in new window

see http://sqlfiddle.com/#!3/94e1e/1
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Dan SchimoAnalystAuthor Commented:
Count (3) is variable, Its the bottom two rows which is a concern
0
 
PortletPaulCommented:
>>Its the bottom two rows which is a concern
agreed (as I understand it), but won't these always have rownumber 1 and 2?

i.e.
ROW_NUMBER() OVER (ORDER BY due_date DESC) AS rownumber

the most recent is rownumber 1 (always)
the nest most recent ronumber 2 (always)
the rest don't matter here

don't forget you are doing a sum() of this subquery, so if you included too many rows you get the wrong sum.

I think... you are in charge

e.g. if the count = 50, your existing code would take in 49 rows - is that what you expect?
0
 
Dan SchimoAnalystAuthor Commented:
its just the bottom to 2 rows which is a concern
0
 
PortletPaulCommented:
very good, then "less than 3" is correct then.

take at look at the sqlfiddle - you can run a query based on this logic and see how it works
http://sqlfiddle.com/#!3/94e1e/1

you can alter the figures in that table to simulate other test scenarios too
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now