Solved

SQL compare sum and values in a table

Posted on 2013-06-12
7
334 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
  • 5
  • 2
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
{sorry corrections needed)
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

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

Expert Comment

by:PortletPaul
Comment Utility
>>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
Comment Utility
its just the bottom to 2 rows which is a concern
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now