Solved

How to get this writing a sql statement

Posted on 2013-12-04
11
400 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PL/SQL - Leading zeros 7 59
Oracle RAC 12c 8 60
Oracle -- identify blocking session 24 43
Problems using Provider=OraOLEDB.Oracle via VBScript/Classic ASP 5 46
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

920 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

18 Experts available now in Live!

Get 1:1 Help Now