Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Unanswered: Complex Calculations

    Hi guy I need help, im new in this development world, im having a problem with my calculations here is the scenario:

    Include the following data from GL_TRANSACTION_LOAD Table for the branch where BalancedInd <> Y:

    Include the following calculated data:
    Transaction Load Summary Not Balanced N
    count distinct TranType on GL_TRANSACTION_LOAD where BalancedInd = N for branch
    Transaction Load Summary Not Balanced
    Count rows on GL_TRANSACTION_LOAD where BalancedInd = for branch
    Transaction Load Summary Not Balanced Total
    Count rows on GL_TRANSACTION_LOAD where BalancedInd <> Y for branch

    please help, i've been working on this for the past 3 weeks but still no progress.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by comfizzy
    Transaction Load Summary Not Balanced ‘N’
    Transaction Load Summary Not Balanced ‘ ‘
    Not Balanced equal N and space?

  3. #3
    Join Date
    Sep 2009
    Posts
    3
    Yes, its like that.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    You've only given us part of your coursework so it's a bit difficult putting it into context. Have you managed to generate any SQL in the past 3 weeks?

  5. #5
    Join Date
    Sep 2009
    Posts
    3
    I did:

    " SELECT T.TranType , (SELECT count(distinct TranType) FROM GL_TRANSACTION_LOAD WHERE BalancedInd = 'N' AND BranchCode = 210 ) as TLSNBN,

    (SELECT count(*) FROM GL_TRANSACTION_LOAD WHERE BalancedInd = '' AND BranchCode = 210 ) as TLSNB ,

    (SELECT count(TranType) FROM GL_TRANSACTION_LOAD WHERE BalancedInd <> 'Y' AND BranchCode = 210 ) as TLSNBNTOT

    FROM GL_TRANSACTION_LOAD WHERE T.BalancedInd <> 'Y' and T.BranchCode = 210 group by TranType
    "

    I used branch 210. It returns wrong results. when i execute :
    SELECT count(TranType) FROM GL_TRANSACTION_LOAD
    WHERE BalancedInd <> 'Y' AND BranchCode = 210 group by TranType

    it give me the data in the format that i want it to be, so i can't collaborate all my sub Queries to give me one correct result

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Perhaps if you just describe (in plain English) what you want to see and we'll unravel your SQL to do this - your current explanation is confusing. It might also help if you explain what is wrong with the output from your current SQL. It would be good to show some sample data and what you would expect as output from the SQL.

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I agree with
    Quote Originally Posted by mike_bike_kite
    your current explanation is confusing...show some sample data and what you would expect as output
    My guess is you want
    Code:
    select 
     count(distinct case when BalancedInd ='N' then TranType  end) '#TranType4N'
    ,count(case when BalancedInd =  'N' then 1 end) '#N'
    ,count(case when BalancedInd =  ' ' then 1 end) '#space'
    ,count(case when BalancedInd <> 'Y' then 1 end) '#NotY'
    ,count(*) '#branch'
    from GL_TRANSACTION_LOAD 
    where branch=210

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •