MS SQL-2005 RUNNING BALANCE FUNCTION

IS THERE ANY FUNCTION TO GET RUNNING BALANCE IN MS SQL-2005 STATEMENT
MehramAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
MehramAuthor Commented:
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
dqmqCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.