January 28, 2011 at 9:31 am
I have 3 tables, the structures are:
1. Client - ClientID, Name,DOB,PAN,IsActive - ClientID of course being the Primary Key(uniqueidentifier)
2. Address - AddressID(PK - uniqueidentifier),House,Street,City,State,Address_ClientID - FK, references ClientID
3. ITReturn - ReturnID(PK - uniqueidentifier), AssesmentStartYear,AssesmentEndYear,TaxPaid,ReturnedIncome, ITR_ClientID - FK, references ClientID
Relation between Client and Address is 1 - *
Relation between Client and ITReturn is 1 - *
I need to get a list of the client, with some details like Address and the Last Tax Paid (if any)
Select ClientFName + ' ' + ISNULL(C.ClientMName, ' ') + ' ' + C.ClientLName as Name,
C.ClientLandPhone, C.ClientCellPhone, C.ClientPAN, C.ClientTAN, C.ClientRemarks, C.ClientIsActive,
A.AddressHouseNumber + ' ' + ISNULL(A.AddressFlat,'') + ' ' + ISNULL(A.AddressFloor,' ') + ' ' + ISNULL(A.AddressBuilding,' ') + ' ' + A.AddressStreet + ' ' + A.AddressCity + ' : ' + A.AddressZipCode +
' ' + A.AddressState + ' ' + A.AddressCountry as [Address],
R.ITRAssesmentStartYear,R.ITRAssesmentEndYear,R.ITRTaxPaid,R.ITRReturnedIncome
From Client C
Inner Join [Address] A On C.ClientID = A.Address_ClientID
Left Join [ITReturn] R
On R.ITR_ClientID = C.ClientID
Order By Name Desc
returns all the ITReturns for a particular client(if he/she happens to have multiple returns) - I just want the last return to be returned along with its TaxPaid and ReturnedIncome information (+ the usual Client details stuff - like Name,DOB,etc)
Can you guys help me solve it, I have been at it for quite sometime now but cannot get any further
Regards,
Joy
January 28, 2011 at 9:52 am
Try something like:
SELECT *
FROM Client C
JOIN [Address] A
ON C.ClientID = A.Address_ClientID
LEFT JOIN
(
ITReturn R
JOIN
(
SELECT R1.ITR_ClientID, MAX(R1.AssesmentEndYear) AS AssesmentEndYear
FROM ITReturn R1
GROUP BY R1.ITR_ClientID
) D
ON R.ITR_ClientID = D.ITR_ClientID
AND R.AssesmentEndYear = D.AssesmentEndYear
)
ON C.ClientID = R.ITR_ClientID
January 28, 2011 at 11:05 pm
Yep, its done 😀
I tried with the inner sql previously but I was just comparing with the ID instead of date :w00t:
Anyways,thanks a lot! - :smooooth:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply