none
how do i achieve the below requirement

    Question

  • I have an requirement like

    Customer table:different customer ,Account Table :savings, current ,Transactions table : withdraw, deposit

    I want different customer with different account type with last withdraw amount and last deposit amount.


    franklinsentil

    Saturday, April 12, 2014 6:03 PM

Answers

All replies

  • Sorry can you give some sample data and explain what you want as output. The above information is not sufficient to understand what you're after. Do you mean only customers who've multiple account types?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, April 12, 2014 7:12 PM
  • If I understand correctly you are trying to get a report for customer's last transaction. As you have not posted any DDL, I am assuming that the query should look like as below,

    SELECT CustomerID,
    CustomerName,
    AccountType,
    TransactionType,
    max(TransactionDate) as TransactionDate,
    sum(TransactionAmt) as TransactionAmt
    FROM CUSTOMER C
    INNER JOIN ACCOUNT A ON C.CustomerID = A.CustomerID
    INNER JOIN TRANSACTION T ON C.AccountID = T.AccountID
    WHERE T.TRANS_TYPE IN ('WITHDRAW','DEPOSIT')
    AND A.ACCOUNT_TYPE IN ('SAVING','CURRENT')
    GROUP BY CustomerID, CustomerName, AccountType, TransactionType


    Regards, RSingh

    Sunday, April 13, 2014 10:26 AM
  • Hello FranklinSentil,

    You can design the expected dataset via "Query Designer" to join "Customer", "Account type", "withdraw" and "deposit" fields in SSDT/BIDS environment. SQL Server Reporting Services provide the Last function to return the last value in the given scope of the specified expression.

    Here are some articles for your reference, please see:
    SSRS Group:http://technet.microsoft.com/en-us/library/ms170712.aspx
    Last Function: http://technet.microsoft.com/en-us/library/dd283113.aspx

    Regards,


    Elvis Long
    TechNet Community Support

    Friday, April 18, 2014 2:43 AM
    Moderator