Solved

MS SQL-2005 RUNNING BALANCE FUNCTION

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…

734 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