Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    15

    Unanswered: can somebody help my optimise my code please

    Hi

    I have the following SQL, which I am using to query our mainframe (some kind of IBM DB2 type thing)
    Basically there are two tables joined by account number. One contains info about the customer, and the other contains details about transactions that have happened for each month. I am trying to get the total spend for last month and the month before. (I know I am not using anything from the CUS_ACC table at the mo, but I will need to)

    SELECT PROD.CUST_ACC_NUM, Sum(SPEND) AS SUMSPEND, STMT_MONTH, STMT_YEAR
    FROM PROD INNER JOIN CUST_ACC ON PROD.CUST_ACC_NUM=CUST_ACC.CUST_ACC_NUM

    WHERE PROD.CUST_ACC_NUM=630822
    And
    (
    (STMT_MONTH=Month(DateAdd("m",-1,Date())) And STMT_YEAR=Year(DateAdd("m",-1,Date())))
    Or
    (STMT_MONTH=Month(DateAdd("m",-2,Date())) And STMT_YEAR=Year(DateAdd("m",-2,Date())))
    )
    GROUP BY PROD.CUST_ACC_NUM, STMT_MONTH, STMT_YEAR;

    now, as the query is at the moment, it takes tens of minutes to run for a single account (I will have to do it for about 3,000 accounts). However, if remove the line that starts with 'OR' and the line below it (i.e. only get last months sales) it runs in seconds.

    Any ideas why?
    Thanks
    Kevin

  2. #2
    Join Date
    Apr 2004
    Posts
    15
    forget it, its working at full speed. hmmmmmm, very very strange

Posting Permissions

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