Post reply

TSQL Query help plz..

  • September 24, 2017 at 3:01 pm

    #340190

    Hi All,

    Need tsql help please. Below is the sample schema/tables and description is written at the end(my requirement).

    use master
    go
    create database banking
    go

    use banking
    go

    create table BankBranches
    (BranchNo int primary key identity(1,1),
    BranchLocation varchar(100)
    )
    insert into BankBranches(BranchLocation)
    select 'United States'
    union all
    select 'Australia'
    union all
    select 'South Africs '
    go

    create table BankAccountType
    (AccntTypeid int primary key identity(101,1),
    AccntTypeDesc Varchar(100)
    )
    insert into BankAccountType(AccntTypeDesc)
    select 'Savings'
    union all
    select 'Current'
    union all
    select 'Fixed Deposit'
    union all
    select 'Recurring Deposit'
    go

    create table AccountHolder
    (
    Accntnum int primary key identity(1001,1),
    AccountHolderFirstname varchar(100),
    AccountHolderLastname varchar(100),
    Address1 varchar(100),
    email varchar(30),
    Bankbranchid int references BankBranches(BranchNo),
    AccountType int references BankAccountType(AccntTypeid)
    )

    insert into AccountHolder(AccountHolderFirstname,AccountHolderLastname,Address1,email,Bankbranchid,AccountType)
    select 'Smith','Allan','US','allan@xyz.com',1,101
    union all
    select 'Warner','David','Australia','Dwarner@xyz.com',2,102
    union all
    select 'Jonty','Rhodes','South Africa','Jonty@xyz.com',3,103
    go

    --1- many relationship between Account and LoanPayment tables.
    create table LoanPaymentHistory
    (
    paymtid int primary key identity(3001,1),
    account_no int references AccountHolder(Accntnum),
    payment_dt datetime,
    amount_paid float,
    check_num varchar(100)
    )

    insert into LoanPaymentHistory(account_no,payment_dt,amount_paid,check_num)
    select 1001,getdate(),5000,'AAA1'
    union all
    select 1001,getdate()+2,10000,'AAA2'
    union all
    select 1001,getdate()+30,5000,'AAA3'
    union all
    select 1002,getdate(),10000,'BBB1'
    union all
    select 1003,getdate(),1000,'CCC1'
    union all
    select 1003,getdate()+10,5000,'CCC2'
    union all
    select 1003,getdate()+3,8000,'CCC3'

    ------------------------------------------------------------------------------------------------------------------------------------
    select * From BankBranches
    select * From BankAccountType
    select * From AccountHolder
    select * From LoanPaymentHistory
    ------------------------------------------------------------------------------------------------------------------------------------

    Question : I want to display Account holder details, branch location, type of account . loan payment detail's etc ...

    --- My Requiement is: for each account holder I need to get the min(loanpaymentdt) from LoanPaymentHistory tbl. Is there a way, to get the minimum of the loan payment date for each account using GROUP BY clause??
    How can we do it ? any differents ways to acheive it? please help !!

    select
    ah.AccountHolderFirstname+' '+AccountHolderLastname as Fullname,
    ah.email,
    at.AccntTypeDesc,
    bb.BranchLocation
    -- get the min() of loan payment date for each accountno
    ---,min(lph.payment_dt) pymtdt //for this i have to use a GROUP BY
    from BankBranches bb
    inner join AccountHolder ah on ah.Bankbranchid = bb.BranchNo
    inner join BankAccountType at on at.AccntTypeid = ah.AccountType
    inner join LoanPaymentHistory lph on ah.Accntnum = lph.account_no
    go

    Thanks in Advance.

  • September 24, 2017 at 5:10 pm

    #1960660

    Homework? Exam? Interview?

    Yes,  you can get the minimum loan payment date using Group By.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 25, 2017 at 2:25 am

    #1960681

    What have you tried so far? Could you post the T-SQL you tried that didn't give you the result(s) you were after? We could then tell you where you went wrong and give you a nudge in the right direction.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • September 25, 2017 at 5:13 am

    #1960693

    Have you even tried the sample code you posted?  Because it sure looks to me that it will fail the integrity constraints.

  • September 25, 2017 at 6:05 am

    #1960704

    gvoshol 73146 - Monday, September 25, 2017 5:13 AM

    Have you even tried the sample code you posted?  Because it sure looks to me that it will fail the integrity constraints.

    The sample code does work. Give it a try yourself. 😉

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • September 25, 2017 at 6:29 am

    #1960710

    Thom A - Monday, September 25, 2017 6:05 AM

    gvoshol 73146 - Monday, September 25, 2017 5:13 AM

    Have you even tried the sample code you posted?  Because it sure looks to me that it will fail the integrity constraints.

    The sample code does work. Give it a try yourself. 😉

    Yup, my mistake.  I thought there was a constraint on branch location country matching the account country.  But I see not, and that the account "country" is really the address field.

  • September 25, 2017 at 11:52 am

    #1960822

    Tried it myself.

    ;With MyCTE as(
    SELECT account_no, MIN(payment_dt) AS MinPymtDt
    FROM LoanPaymentHistory
    GROUP BY account_no
    )

    select
    ah.AccountHolderFirstname+' '+AccountHolderLastname as Fullname,
    ah.email,
    at.AccntTypeDesc,
    bb.BranchLocation,
    c.MinPymtDt
    from BankBranches bb
    inner join AccountHolder ah on ah.Bankbranchid = bb.BranchNo
    inner join BankAccountType at on at.AccntTypeid = ah.AccountType
    inner join MyCTE c on ah.Accntnum = c.account_no

  • September 26, 2017 at 2:14 am

    #1960901

    As an aside, I'm pretty sure you won't want the amount to be FLOAT as this will not give exact results in calculations.

  • September 29, 2017 at 9:14 am

    #1961682

    Here is my answer based on your code......

    .....but the CTE solution is more elegant.

    Sorry the code isn't copyable.  My browser crashes when I try and paste code directly into the window.

  • October 3, 2017 at 3:20 am

    #1962017

    kevaburg - Friday, September 29, 2017 9:14 AM

    Here is my answer based on your code......

    .....but the CTE solution is more elegant.

    Sorry the code isn't copyable.  My browser crashes when I try and paste code directly into the window.

    vsamantha35 - Monday, September 25, 2017 11:52 AM

    Tried it myself.

    ;With MyCTE as(
    SELECT account_no, MIN(payment_dt) AS MinPymtDt
    FROM LoanPaymentHistory
    GROUP BY account_no
    )

    select
    ah.AccountHolderFirstname+' '+AccountHolderLastname as Fullname,
    ah.email,
    at.AccntTypeDesc,
    bb.BranchLocation,
    c.MinPymtDt
    from BankBranches bb
    inner join AccountHolder ah on ah.Bankbranchid = bb.BranchNo
    inner join BankAccountType at on at.AccntTypeid = ah.AccountType
    inner join MyCTE c on ah.Accntnum = c.account_no

    Here is a better approach 

    select * From AccountHolder a
    cross apply(select min(payment_dt) as MinPayDate from LoanPaymentHistory b where a.Accntnum = b.account_no) as c;

    First solve the problem then write the code !

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply