TSQL Query help plz..
-
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
gouse banking
gocreate 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 '
gocreate 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'
gocreate 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
goThanks in Advance.
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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 -
Have you even tried the sample code you posted? Because it sure looks to me that it will fail the integrity constraints.
- gvoshol 73146 - Monday, September 25, 2017 5:13 AM
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 - Thom A - Monday, September 25, 2017 6:05 AMgvoshol 73146 - Monday, September 25, 2017 5:13 AM
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.
-
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 -
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.
-
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.
- kevaburg - Friday, September 29, 2017 9:14 AMHere 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 AMHere 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