Solved

SQL compare sum and values in a table

Posted on 2013-06-12
7
417 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 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
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!

 

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

717 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