Solved

MS SQL-2005 RUNNING BALANCE FUNCTION

Posted on 2007-04-11
3
220 Views
Last Modified: 2008-02-01
IS THERE ANY FUNCTION TO GET RUNNING BALANCE IN MS SQL-2005 STATEMENT
0
Comment
Question by:Mehram
[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
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

615 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