Results 1 to 10 of 10

Thread: Mysql

  1. #1
    Join Date
    Nov 2012
    Posts
    8

    Thumbs down Unanswered: Mysql

    i have following table and sql quaries.

    Index| i002_number|orig_msg_type|i004_txrn_type|Posted date
    1 |451263 |PAYNT |10,000 |2012-10-02
    2 |654236 |DBINT |5000 |2012-10-30
    3
    4

    select SUBSTRING(i002_number,-16,6)as bin,
    i004_amt_trxn as Payment
    FROM ctransactions
    where posteddate BETWEEN CAST('2012-10-02' AS DATE)
    AND CAST('2012-10-30' AS DATE)and orig_msg_type ='PAYMT' ;


    select SUBSTRING(i002_number,-16,6)as bin,
    i004_amt_trxn as Debit_Interest
    FROM ctransactions
    where posteddate BETWEEN CAST('2012-10-02' AS DATE)
    AND CAST('2012-10-30' AS DATE) and orig_msg_type ='DBINT';


    i want combine above two quarries together and display following table.plz help me.
    bin DBINT PAYNT
    564236 5000 12000

  2. #2
    Join Date
    Nov 2012
    Posts
    8

    mysql queries

    Quote Originally Posted by rchathur View Post
    i have following table and sql quaries.

    Index| i002_number|orig_msg_type|i004_txrn_type|Posted date
    1 |451263 |PAYNT |10,000 |2012-10-02
    2 |654236 |DBINT |5000 |2012-10-30
    3
    4

    select SUBSTRING(i002_number,-16,6)as bin,
    i004_amt_trxn as Payment
    FROM ctransactions
    where posteddate BETWEEN CAST('2012-10-02' AS DATE)
    AND CAST('2012-10-30' AS DATE)and orig_msg_type ='PAYMT' ;


    select SUBSTRING(i002_number,-16,6)as bin,
    i004_amt_trxn as Debit_Interest
    FROM ctransactions
    where posteddate BETWEEN CAST('2012-10-02' AS DATE)
    AND CAST('2012-10-30' AS DATE) and orig_msg_type ='DBINT';


    i want combine above two quarries together and display following table.plz help me.
    bin DBINT PAYNT
    564236 5000 12000
    querries

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rchathur View Post

    querries
    queries



    Code:
    SELECT SUBSTRING(i002_number,-16,6) AS bin
         , MAX(CASE WHEN orig_msg_type ='DBINT' 
                    THEN i004_amt_trxn 
                    ELSE NULL END) AS DBINT
         , MAX(CASE WHEN orig_msg_type ='PAYNT' 
                    THEN i004_amt_trxn 
                    ELSE NULL END) AS PAYNT
      FROM ctransactions
     WHERE posteddate BETWEEN '2012-10-02 AND '2012-10-30
    GROUP
        BY bin
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2012
    Posts
    8

    Thank You..........



    Thank you soo much..****ddy

    Mysql statement is working!

  5. #5
    Join Date
    Nov 2012
    Posts
    8

    Hi...

    again i want help from you.here is my stored procedure ,

    SELECT SUBSTRING(i002_number,-16,6) AS bin
    , MAX(CASE WHEN orig_msg_type ='DBINT'
    THEN i004_amt_trxn
    ELSE NULL END) AS DBINT
    , MAX(CASE WHEN orig_msg_type ='PAYNT'
    THEN i004_amt_trxn
    ELSE NULL END) AS PAYNT
    FROM ctransactions
    WHERE posteddate BETWEEN '2012-10-02 AND '2012-10-30
    GROUP
    BY bin

    and it is giving following output.

    bin DBINT PAYNT
    564236 5000 12000

    but the problem is,lets think we have more than one entries both of the PAYNT and DBINT for each bin.Then we want to get total DBINT and PAYNT without repeating bin.and also above sp print DBINT and PANT in two rows not in one.
    eg.

    bin DBINT PAYNT
    564236 5000 12000
    596598 3200 5000
    564236 8000 10000

    but i want,

    bin DBINT PAYNT
    564236 13000 22000
    596598 3200 5000
    ................................

    Please help me to do this.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change MAX(...) to SUM(...)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2012
    Posts
    8

    Thank You..........

    Thank you so much... it is working correctly.

  8. #8
    Join Date
    Nov 2012
    Posts
    8
    Hi .....
    I want to convert below sql stored procedure in to IBM Informix.Can you help me?
    DELIMITER $$

    DROP PROCEDURE IF EXISTS `pay_details` $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `pay_details`( in start_date date,in end_date date)
    begin
    SELECT SUBSTRING(i002_number,-16,6) AS bin,
    SUM(CASE WHEN orig_msg_type ='DBINT'
    THEN i004_amt_trxn ELSE NULL END) AS Debit_Intrest,
    SUM(CASE WHEN orig_msg_type ='PAYMT'
    THEN i004_amt_trxn ELSE NULL END) AS Payments
    FROM ctransactions
    WHERE posteddate
    BETWEEN start_date AND end_date GROUP BY bin;
    end $$

    DELIMITER ;

    Thanx!

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rchathur View Post
    Can you help me?
    me? nope

    perhaps consider asking in the informix forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2012
    Posts
    8
    Thanx...I will try it.

Posting Permissions

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