Solved

How to get this writing a sql statement

Posted on 2013-12-04
11
394 Views
Last Modified: 2013-12-05
Hi
I have a table with value
Beginning_dr Beginning_CR, status , Running total
I have attached the file i which the last column is ther running total
but based on the status flag
if it is B then subtract if it is A then add

can you please tell me how to write the SQL statements

6,000.00      0.00      B      6,000.00
0.00      200.00      A      5,800.00
4,200.00      0.00      B      10,000.00


Thanks,
Rahul.
0
Comment
Question by:pardeshirahul
  • 5
  • 5
11 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39696695
what is the ordering criteria?

How do we know 6000 comes before 4200  ?

also, your description and the results don't seem to match.

It looks like your B is an add and A is subtract.

last thing.

Do B records always use the beginning_dr value  and A records always use the beginning_cr value for the math, or should we sum the two columns and use the results or use some other operation?


Assuming...
there is a sortable column like an ID
and you always ADD beginning_dr for B
and always SUBTRACT beginning_cr for A
then try this..

SELECT beginning_dr,
         beginning_cr,
         status,
         SUM(CASE WHEN status = 'B' THEN beginning_dr ELSE -beginning_cr END) OVER (ORDER BY id)
    FROM yourtable
ORDER BY id;


if any of those assumptions are not correct, then please elaborate.  Note a running total is ONLY possible if you have a sortable criteria of some kind.
0
 

Author Comment

by:pardeshirahul
ID: 39696702
is just told you the short part of the table

the primary key is

LEdger_id
legal_entity
Period_year
period_name
main_account

order by these 5 columns
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39696711
sorting by more than one column is ok.
what about the other questions?

SELECT beginning_dr,
         beginning_cr,
         status,
         SUM(CASE WHEN status = 'B' THEN beginning_dr ELSE -beginning_cr END)
                    OVER (ORDER BY LEdger_id,legal_entity,Period_year,period_name,main_account)
    FROM yourtable
ORDER BY LEdger_id,legal_entity,Period_year,period_name,main_account;
0
 

Author Comment

by:pardeshirahul
ID: 39696827
what if the status column is not there

only we assume

if beginning_dr then 'B'
and if Beginning_cr then 'A'
0
 

Author Comment

by:pardeshirahul
ID: 39696830
then how to write the query
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 39697323
SELECT beginning_dr,
         beginning_cr,
         SUM(case when beginning_dr != 0 then beginning_dr ELSE -beginning_cr END)
                    OVER (ORDER BY LEdger_id,legal_entity,Period_year,period_name,main_account)
    FROM yourtable
ORDER BY LEdger_id,legal_entity,Period_year,period_name,main_account;
0
 
LVL 2

Expert Comment

by:Aijaz Chauhan
ID: 39697734
You can use case statement

i have created one example for you

declare @table table(pk bigint,amount1 float,amount2 float,cse varchar(max))

insert into @table values(1,6000.00,5,'B')
insert into @table values(2,5000.00,10,'A')
insert into @table values(3,6500.00,45,'B')
insert into @table values(4,5200,82,'A')


select * from @table
select pk,amount1,amount2,cse,

case when cse='A' then amount1+amount2
      when cse='B' then amount1-amount2
      else 0
end

from @table
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39698105
that's sql server syntax, this is an oracle question

the oracle syntax provided above should work in sql server 2008 and above (maybe 2005 too, not sure anymore)
0
 

Author Comment

by:pardeshirahul
ID: 39699100
sorry please see the attached data from the excel file i have attached in this case how can i get the running total

the primary key is

LEdger_id
legal_entity
Period_year
period_name
main_account
Copy-of-Running-Total-Example.xls
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39699139
you shouldn't keep changing the data and the requirements.
when you do that it should be a new question.

sorting by period_name doesn't make sense because JAN-13 will come AFTER FEB-13.
you need to convert that text into an actual date value.
The query below does that.

However, your results are still not reliable.
Lines 3 and 4
and
Lines 5 and 6

are each identical based on your sorting criteria, so there is no way to ensure one row will follow the other reliably, thus your running total might change from mine as you cross those days but the final value should be the same.


  SELECT ledger_id,
         legal_entity_id,
         period_year,
         period_name,
         main_account,
         account_description,
         begin_debit,
         begin_credit,
         account,
         sequence_number,
         net,
         CASE WHEN begin_debit > begin_credit THEN begin_debit - begin_credit ELSE 0 END debit,
         CASE WHEN begin_debit > begin_credit THEN 0 ELSE begin_credit - begin_debit END credit,
         SUM(net)
             OVER(
                 ORDER BY
                     ledger_id,
                     legal_entity_id,
                     period_year,
                     TO_DATE(period_name, 'MON-RR'),
                     main_account
             )
    FROM yourtable
ORDER BY ledger_id,
         legal_entity_id,
         period_year,
         TO_DATE(period_name, 'MON-RR'),
         main_account;



If that doesn't work,  please close this question as it relates to what you ORIGINALLY asked, or continue pursuing the original question and data
to resolve that.

And then open a new question for the new data and new requirements, explaining what this query does or does not do correctly.
0
 

Author Comment

by:pardeshirahul
ID: 39699181
sorry for that i will open a new question if ihave any other issues

Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

705 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now