0

The code below executes perfectly but I would like to know how to optimize it

select distinct 
    c.customerid,
    Ca.CUAccountID,
    c.number,
    ca.AccountNumber,
    c.name,
    b.name as Branch,
    cp.description as product,
    max(ct.valuedate) as last_deposit_withdrwal_Date,
    (ca.CRAmount-ca.DRAmount) as balance,
    ca.balancedate,
    (case 
        when max(ct.valuedate) < DATEADD(MM, DATEDIFF(MM, 0, DATEADD(MM, -6, GetDate())), 0) 
           then 'Dormant' 
        when max(ct.valuedate) <= DATEADD(MM, DATEDIFF(MM, 0, DATEADD(MM, -3, GetDate())), 0) 
           then 'Inactive'
        else 'Active'
      end) as Customer_Status
from 
    CUAccount CA
join 
    CUProduct CP on CA.ProductID = Cp.ProductID
join 
    cutransaction ct on ca.cuaccountid = ct.cuaccountid
join 
    branches b on ca.BranchID = b.BranchID
join 
    customer c on ca.customerid = c.customerid
where 
    CA.Active = 1
    and CP.Active = 1
    and c.closed = 0
    and ct.TransactionTypeID in (1, 2, 256)
    and ct.transactionsourceid in (1, 2)
    and cp.productid in (117, 118, 119, 120, 121, 122, 123, 124, 125, 158, 168, 58) 
group by 
    c.customerid, c.number, ca.AccountNumber, Ca.CUAccountID,
    b.name, c.name, cp.description, (ca.CRAmount-ca.DRAmount), ca.balancedate
having 
    max(ct.valuedate) > DATEADD(MM, Datediff(MM, 0, dateadd(MM, -3, getdate())), 0)
order by 
    max(ct.valuedate) asc
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Derrick Santeng
  • 1
  • 1
  • Is this Oracle's MySQL or Microsoft SQL Server? You've tagged both. – tadman Jan 29 '17 at 23:11
  • Is this an OLAP-like query with a standard star schema? – joanolo Jan 29 '17 at 23:29
  • MySQL is not "Mycrosoft SQL". Please don't tag it that way. – tadman Jan 29 '17 at 23:48
  • I reformatted by putting in enough newlines so we don't have to scroll back and forth to read your query. – Bill Karwin Jan 30 '17 at 00:35
  • I am not a Microsoft SQL Server user, but getting the current optimization plan for the query is the first step. There's a great post on how to do that for Microsoft SQL Server here: http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan – Bill Karwin Jan 30 '17 at 00:40
  • It's also recommended to show the table definitions for all tables involved in this query. Anyone who wants to help you answer this question will ask for that, so they can see the current indexes, constraints, and data types you are using. See http://stackoverflow.com/questions/3526982/show-create-table-tablename-how-do-i-do-this-in-sql-server – Bill Karwin Jan 30 '17 at 00:42
  • Also some clue about how many rows are in each table (approximately). All these details are things you know better than us, and will help people to answer the question for you. – Bill Karwin Jan 30 '17 at 00:44

0 Answers0