How to make this query?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to make this query??

Author

Topic

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-03-30 : 07:48:45

I have three tables
Profile_UserDetails, Profile_SpouseDetails, Profile_ChildDetails
Details of the table are as follows
Profile_UserDetails with columns
[Sno]—(pk)
,[EmpCode]
,[EmpInitial]
,[EmpFirstName]
,[EmpMiddleName]
,[EmpLastName]
,[DOB]
,[DOJ]
,[MobileNo]
,[LandlineNo]
,[PresentAddress]
,[ParmenantAddress]
,[MartialStatus]
,[Locality]
,[BloodGroup]
,[EmergencyContactNo]
,[EmergencyName]
,[CreatedBy](FK)
,[CreatedOn]
,[UserId]—(FK)

Profile_SpouseDetails with columns
[SpouseNo]—(PK)
,[UserId]—(FK)
,[SpouseName]
,[DOB]
,[BloodGroup]
,[AnniversaryDt]
Profile_ChildDetails With columns
[ChildId]—(PK)
,[UserId]—(FK)
,[ChildName]
,[ChildDOB]
,[ChildBloodGroup]
UserId field is contained in other table as User_Login and is the primary key of that table…
My Query is to find
1.Display all the employees having DOb in some specific month
2.If the employee is married then display the spouse details,AnniversaryDate etc..
3.If the employee has children then then display the dob of the children

i.e whenever one selects a month the records we achieve display the full information about the employee ,spouse ,children if they have any occasion in the given month…
Hope I am able to describe my problem fully,,,

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-03-31 : 00:45:28

Please do help....
I have tried this much..but i am really confused how to carry on further...
SELECT
Ename=Case When PSD.SpouseName is not null --and PCD.ChildName is null
Then PUD.EmpFirstName+' '+isnull(PUD.EmpMiddleName,'')+ ' '+PUD.EmpLastName+'/'+PSD.SpouseName
When PCD.ChildName is not null
Then PUD.EmpFirstName+' '+isnull(PUD.EmpMiddleName,'')+ ' '+PUD.EmpLastName+'/'+PCD.ChildName
Else PUD.EmpFirstName+' '+isnull(PUD.EmpMiddleName,'')+ ' '+PUD.EmpLastName
END,
PUD.MobileNo,
PUD.DOB,
PSD.AnniversaryDt,
PSD.DOB as SpouseDOB,
PCD.ChildDOB
FROM
Profile_UserDetails as PUD
Left join Profile_SpouseDetails as PSD on PUD.UserId=PSD.UserId
Left Join Profile_ChildDetails as PCD on PUD.UserId=PCD.UserId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources