?
Solved

SQL compare sum and values in a table

Posted on 2013-06-12
7
Medium Priority
?
434 Views
Last Modified: 2013-06-14
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
Comment
Question by:Dan Schimo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39243233
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39243265
{sorry corrections needed)
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1500 total points
ID: 39243314
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:Dan Schimo
ID: 39244672
Count (3) is variable, Its the bottom two rows which is a concern
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39244702
>>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
 

Author Comment

by:Dan Schimo
ID: 39247625
its just the bottom to 2 rows which is a concern
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39247656
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question