Solved

SQL compare sum and values in a table

Posted on 2013-06-12
7
402 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 48

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 48

Expert Comment

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

Accepted Solution

by:
PortletPaul earned 500 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

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

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 48

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

732 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