Solved

MS SQL-2005 RUNNING BALANCE FUNCTION

Posted on 2007-04-11
3
216 Views
Last Modified: 2008-02-01
IS THERE ANY FUNCTION TO GET RUNNING BALANCE IN MS SQL-2005 STATEMENT
0
Comment
Question by:Mehram
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18888437
there is no build-in function.
can you give more details about the table/query/data... ?

note: please avoid posting in the internet with only CAPS lock on.
0
 

Author Comment

by:Mehram
ID: 18888485
Statement:
-------------
SELECT      A.FILE_JOB,
            A.AGENT,
            B.INVOICENO,
            B.ExpenseDate,
            DAYSPASS=DATEDIFF(DAY,B.ExpenseDate,GETDATE())+1,
            DEBIT=SUM(B.DEBIT),
            RECEIVED_FM_PARTY=C.BALANCE,
            RUNNINGBALANCE=CASE WHEN SUM(B.DEBIT) <= C.BALANCE THEN SUM(B.DEBIT) ELSE 0 END
FROM
(select a.agent, Balance=sum(debit-credit) from cargodetail a join cargodetailinvoice b on a.file_job=b.file_job
where a.agent='Ghorayeb-2007' and a.doc_Arvd_on >='01/01/2007' and a.doc_arvd_on <='4/30/2007'
group by a.agent
having sum(debit-credit) <> 0)C
JOIN DLIServer.DLI_DB.DBO.CARGODETAIL A ON (A.AGENT=C.AGENT)
JOIN DLIServer.DLI_DB.DBO.CARGODETAILINVOICE B ON (A.FILE_JOB=B.FILE_JOB)
where a.agent='Ghorayeb-2007' and a.doc_Arvd_on >='01/01/2007' and a.doc_arvd_on <='4/30/2007'
GROUP BY A.FILE_JOB, A.AGENT, B.INVOICENO, B.ExpenseDate, B.DEBIT, C.BALANCE
HAVING SUM(B.DEBIT) > 0
ORDER BY DAYSPASS DESC

Result
---------
00569/AFG/GHB/07      Ghorayeb-2007      2853      2007-01-23 00:00:00.000      79      6650.00      55449.90      6650.00
00576/AFG/GHB/07      Ghorayeb-2007      2868      2007-02-14 00:00:00.000      57      6650.00      55449.90      6650.00
00577/AFG/GHB/07      Ghorayeb-2007      2874      2007-02-14 00:00:00.000      57      15950.00      55449.90      15950.00
00578/AFG/GHB/07      Ghorayeb-2007      2877      2007-02-14 00:00:00.000      57      2800.00      55449.90      2800.00
00579/AFG/GHB/07      Ghorayeb-2007      2878      2007-02-19 00:00:00.000      52      2800.00      55449.90      2800.00
00575/AFG/GHB/07      Ghorayeb-2007      2867      2007-02-19 00:00:00.000      52      3300.00      55449.90      3300.00
00581/AFG/GHB/07      Ghorayeb-2007      2880      2007-02-19 00:00:00.000      52      31800.00      55449.90      31800.00
00580/AGH/GHB/07      Ghorayeb-2007      2879      2007-02-21 00:00:00.000      50      3250.00      55449.90      3250.00
00583/AFG/GHB/07      Ghorayeb-2007      2882      2007-02-24 00:00:00.000      47      3250.00      55449.90      3250.00
00585/AFG/GHB/07      Ghorayeb-2007      2884      2007-02-24 00:00:00.000      47      3325.00      55449.90      3325.00
00582/AFG/GHB/07      Ghorayeb-2007      2881      2007-02-27 00:00:00.000      44      5600.00      55449.90      5600.00
00586/AFG/GHB/07      Ghorayeb-2007      2893      2007-03-01 00:00:00.000      42      11200.00      55449.90      11200.00
00587/AFG/GHB/07      Ghorayeb-2007      2896      2007-03-06 00:00:00.000      37      11200.00      55449.90      11200.00
00569/AFG/GHB/07      Ghorayeb-2007      2853/00411      2007-03-08 00:00:00.000      35      125.49      55449.90      125.49
00569/AFG/GHB/07      Ghorayeb-2007      2853/00411      2007-03-08 00:00:00.000      35      1400.00      55449.90      1400.00
00569/AFG/GHB/07      Ghorayeb-2007      2853/00411      2007-03-08 00:00:00.000      35      1139.60      55449.90      1139.60
00589/AFG/GHB/07      Ghorayeb-2007      2902      2007-03-13 00:00:00.000      30      16800.00      55449.90      16800.00
00590/AFG/GHB/07      Ghorayeb-2007      2904      2007-03-15 00:00:00.000      28      5600.00      55449.90      5600.00
00591/AFG/GHB/07      Ghorayeb-2007      2905      2007-03-19 00:00:00.000      24      5600.00      55449.90      5600.00
00592/AFG/GHB/07      Ghorayeb-2007      2907      2007-03-27 00:00:00.000      16      5600.00      55449.90      5600.00
00594/AFG/GHB/07      Ghorayeb-2007      2913      2007-03-27 00:00:00.000      16      2800.00      55449.90      2800.00
00576/AFG/GHB/07      Ghorayeb-2007      2868/00437      2007-03-29 00:00:00.000      14      50.84      55449.90      50.84
00576/AFG/GHB/07      Ghorayeb-2007      2868/00437      2007-03-29 00:00:00.000      14      167.00      55449.90      167.00
00578/AFG/GHB/07      Ghorayeb-2007      2877/00436      2007-03-29 00:00:00.000      14      256.25      55449.90      256.25
00579/AFG/GHB/07      Ghorayeb-2007      2878/00438      2007-03-29 00:00:00.000      14      55.79      55449.90      55.79
00579/AFG/GHB/07      Ghorayeb-2007      2878/00438      2007-03-29 00:00:00.000      14      100.00      55449.90      100.00
00578/AFG/GHB/07      Ghorayeb-2007      2877/00436      2007-03-29 00:00:00.000      14      142.65      55449.90      142.65
00576/AFG/GHB/07      Ghorayeb-2007      2868/00437      2007-03-29 00:00:00.000      14      300.00      55449.90      300.00
00582/AFG/GHB/07      Ghorayeb-2007      2881/00435      2007-03-29 00:00:00.000      14      55.00      55449.90      55.00
00582/AFG/GHB/07      Ghorayeb-2007      2881/00435      2007-03-29 00:00:00.000      14      125.49      55449.90      125.49
00582/AFG/GHB/07      Ghorayeb-2007      2881/00435      2007-03-29 00:00:00.000      14      200.00      55449.90      200.00
00582/AFG/GHB/07      Ghorayeb-2007      2881/00435      2007-03-29 00:00:00.000      14      328.75      55449.90      328.75
00580/AGH/GHB/07      Ghorayeb-2007      2879/00434      2007-03-29 00:00:00.000      14      100.00      55449.90      100.00
00580/AGH/GHB/07      Ghorayeb-2007      2879/00434      2007-03-29 00:00:00.000      14      200.00      55449.90      200.00
00579/AFG/GHB/07      Ghorayeb-2007      2878/00438      2007-03-29 00:00:00.000      14      128.00      55449.90      128.00
00580/AGH/GHB/07      Ghorayeb-2007      2879/00434      2007-03-29 00:00:00.000      14      78.10      55449.90      78.10
00580/AGH/GHB/07      Ghorayeb-2007      2879/00434      2007-03-29 00:00:00.000      14      85.34      55449.90      85.34
00595/AFG/GHB/07      Ghorayeb-2007      2918      2007-03-31 00:00:00.000      12      5600.00      55449.90      5600.00
00593/AFG/GHB/07      Ghorayeb-2007      2908      2007-03-31 00:00:00.000      12      3325.00      55449.90      3325.00
00596/AFG/GHB/07      Ghorayeb-2007      2923      2007-04-03 00:00:00.000      9      2075.00      55449.90      2075.00
00586/AFG/GHB/07      Ghorayeb-2007      2893/00448      2007-04-05 00:00:00.000      7      249.96      55449.90      249.96
00586/AFG/GHB/07      Ghorayeb-2007      2893/00448      2007-04-05 00:00:00.000      7      560.29      55449.90      560.29
00586/AFG/GHB/07      Ghorayeb-2007      2893/00448      2007-04-05 00:00:00.000      7      200.00      55449.90      200.00

Please help
0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 18891679
Here's the general way to produce a running total.  Problem is, I don't know exactly what you want the running total over. But you can adapt the general form.  This example produces the running total by date over agent and job.  


...
 RUNNINGBALANCE=
(
select sum(bs.debit-bs.credit) from cargodetail as join cargodetailinvoice bs on as.file_job=bs.file_job
where
--this filters the results over a  date range, copied from main query
as.doc_Arvd_on >='01/01/2007' and as.doc_arvd_on <='4/30/2007'

--this causes each agent/job to have separate running total
and as.agent=a.agent
and as.file_job = a.file_job

--this effects the same sequence as the ORDER BY clause
and bs.expensedate <= b.expensedate
)
...
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Select - Finding chars in a column 2 67
Help with SQL - TOP 10 by date and by group 13 40
convert null in sql server 12 48
Query group by data in SQL Server - cursor? 3 50
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

821 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