Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2013
    Posts
    3

    Red face Unanswered: Need Help pls !!!

    this is my table

    CREATE TABLE ABC
    (
    SerialNum INT ,
    Amt INT,
    TypeOfAmt Char
    )

    data is
    insert into ABC values (1, 100, 'C')
    insert into ABC values (2, 200, 'D')
    insert into ABC values (3, 300, 'C')
    insert into ABC values (4, 400, 'D')
    insert into ABC values (5, 500, 'C')

    ie,
    SerialNum Amt TypeOfAmt
    1---------100 C
    2---------200 D
    3---------300 C
    4---------400 D
    5---------500 C

    i want an output as ,

    SerailNum Credit Debit
    1-----------100--0
    2-----------0----200
    3-----------300--0
    4-----------0----400
    5-----------500--0

    please give a solution for this.. i am a budding developer
    Last edited by Mazreen; 07-11-13 at 06:11.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Check out the CASE statement in the help file.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Matrix Methods in Accounting

    You might want to talk to an accountant who has done software development. The old "Debit/Credit" model goes back to Luca Pacioli and the first book on double-entry accounting in 1494. They used Roman Numerals, did not have negative numbers or decimal currency. His book described a method used by merchants in Venice during the Italian Renaissance period.

    Today, accounting system use Arabic numerals That means negative numbers and decimals. But we also use matrix models where the dimensions are accounts, where a transaction is source account(s) moving an amount to destination account(s).

    If you have a legal reason, you can use CASE to format the data in the "Debit/Credit" style display. But if you want to use Roman Numerals, you are on your own

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This will work.
    Code:
    SELECT SerialNum, 
    	CASE WHEN typeofamt = 'C' THEN Amt ELSE 0 END AS Credit,
    	CASE WHEN typeofamt = 'D' THEN Amt ELSE 0 END AS Debit
    FROM ABC
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jul 2013
    Posts
    3
    Quote Originally Posted by Wim View Post
    This will work.
    Code:
    SELECT SerialNum, 
    	CASE WHEN typeofamt = 'C' THEN Amt ELSE 0 END AS Credit,
    	CASE WHEN typeofamt = 'D' THEN Amt ELSE 0 END AS Debit
    FROM ABC

    thank you very much ...it was one of the interview questions I faced...

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    it was one of the interview questions I faced...
    You mean you know your interview questions in advance!?

    PM me for my bank account number. I will gladly accept 1% of your net yearly salary on your new job.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by Wim View Post
    You mean you know your interview questions in advance!?
    No, I think he/she did not answer the interview question correctly and wanted to know what was the correct answer. They used past tense in sentence. Now if you still want that percentage of income...

  8. #8
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by Mazreen View Post
    i want an output as ,

    SerailNum Credit Debit
    1-----------100--0
    2-----------0----200
    3-----------300--0
    4-----------0----400
    5-----------500--0
    If I remember my accounting correctly, it should be debits on the left and credits on the right when working with a faux t-accounts!

  9. #9
    Join Date
    Jul 2013
    Posts
    3
    Quote Originally Posted by Wim View Post
    You mean you know your interview questions in advance!?
    I didn't answer the question in the interview n i wanted to knw the ans....so i posted my q here...

  10. #10
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Gee. I always wanted to know the interview questions in advance This looks wrong. Right now I am writing an article on double entry bookkeeping, Roman Numerals and negative numbers.

Posting Permissions

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