Solved

MS SQL-2005 RUNNING BALANCE FUNCTION

Posted on 2007-04-11
3
213 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 142

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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