Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL to return balances

Posted on 2006-04-14
16
Medium Priority
?
1,262 Views
Last Modified: 2012-08-14
im trying to write a script that declares and sets a variable that’s equal to the total outstanding balance due. If that balance due is greater than $10,000.00, the script should return a result set consisting of VendorName, InvoiceNumber, InvoiceDueDate, and Balance for each invoice with a balance due, sorted with the oldest due date first. If the total outstanding balance due is less than $10,000.00, return the message “Balance due is less than $10,000.00.”

i have this so far but  it does not return the individual balances by looking at the declare variable.
US Ap
Delcare @totbaldue money
set @totbaldue = (select sum(invoicetotal-paymenttotal-credittotal) from invoices)

select vendorname, invoicenumber, invoicedate, @totbaldue as balance
from vendors join invoices on vendors.vendoris=invoices.vendorid

0
Comment
Question by:tagtekin
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16455750
Something like this perhaps:

select      v.vendorname,
      i.invoicenumber,
      i.invoicedate,
      SUM(i.invoicetotal - i.paymenttotal - i.credittotal) balance,
      CASE
            WHEN SUM(i.invoicetotal - i.paymenttotal - i.credittotal) < 10000 THEN 'Balance due is less than $10,000.00.'
            ELSE Null
      END Message
from      vendors v
      join invoices i on v.vendoris = i.vendorid
Group By
      v.vendorname,
      i.invoicenumber,
      i.invoicedate
Order By
      i.invoicedate DESC
0
 

Author Comment

by:tagtekin
ID: 16455909
yes thats by looping thgroug the data however i have to do it by declaring and setting the variable at the begining.
thanks
0
 

Author Comment

by:tagtekin
ID: 16455914
bascially it needs to be T-sql form
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16455946
>>bascially it needs to be T-sql form <<
Can you describe what is a "T-sql form".
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16456525
when you say "i have to do it by declaring and setting the variable at the begining", this sounds very much like a homework assignment - why else would you be required to do this in any particualr fashion?

IF this is a homework assignment, then EE Experts are NOT permitted to provide you with a complete answer.  We can assist you in figuring out the correct answer for yourself, but please do not ask any of us to do the work for you.

AW
0
 

Author Comment

by:tagtekin
ID: 16457821
I already put my script in the question but im having problems where i m doing it worng I m not asking anybody to do my hw.

THanks regards
Orcun Tagtekin
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16458075
That link told me nothing about the infamous "T-sql form", icould not find the phrase any where in that article.  I then did a search on Google and came up with zip.  So is there any chance you can give in your words what you mean?
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16458597
I think he is refereing to SQL Server T-SQL (Transact-SQL) - the SQL Server Stored Procedure programming language.

This is from SQL Server Books OnLine:

"Transact-SQL is central to using SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application"

But that does not explain what tagtekin means by 'T-SQL form' and he has not answered my question about this being a Homework/class assignment.

AW
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16458688
>>But that does not explain what tagtekin means by 'T-SQL form' <<
I managed to figure out the T-SQL = Transact-SQL part :)

But it is the phrase "T-SQL form" that is totally meaningless.
0
 

Author Comment

by:tagtekin
ID: 16460458
I guess  I finally fogured it out. Since I only need to loop through the records.

MY state would be correct with this and here is what i got.
US Ap
Delcare @totbaldue money
set @totbaldue = (select sum(invoicetotal-paymenttotal-credittotal) from invoices)
if
@totbaldue >10000
select vendorname, invoicenumber, invoicedate, @totbaldue as balance
from vendors join invoices on vendors.vendoris=invoices.vendorid
else
print 'There are outstanding balances'

and thats about it
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16460668
Great!  Please close the question.  Here is how from the EE Help:

I answered my question myself. What do I do?
http://www.experts-exchange.com/Databases/help.jsp#hi70
0
 

Author Comment

by:tagtekin
ID: 16510972
please close question
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16512685
This is how it works here, form the link I posted previously:

<quote>
I answered my question myself. What do I do?
 
Post a question in the Community Support topic area asking for a refund, and asking the Moderators to close the question. You'll be required to post your solution in your original question. A Moderator will post a notice of your request which will give the participants 96 hours to object to the refund. Note that if it resembles one of the suggested comments, the likelihood is that your request will not be granted, but rather, the points will be awarded to the Expert who makes the suggestion. In your Community Support request, remember to post a link to the original question.
</quote>
0
 

Accepted Solution

by:
GranMod earned 0 total points
ID: 16546622
Closed, 50 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 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