Solved

How to get this writing a sql statement

Posted on 2013-12-04
11
406 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
[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
  • 5
  • 5
11 Comments
 
LVL 74

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 74

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
LVL 74

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 74

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 74

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

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

710 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