how to show regionwise male and female result

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 show regionwise male and female result

Author

Topic

kishorGadekar
Starting Member

1 Post

Posted - 2012-11-19 : 04:49:06

Table
1)EmployeeDetails(empID int,empName varchar(100),officeid int,gender varchar(6))

empID EmpName officeid gender
5041 Mahavir 1 Male
5042 Kishor 2 Female

2)EmployeeWorkHistrory(id int,empID int,officeid int,fromdate datetime,todate datetime)

id EmpID officeID frmdate todate
1 5041 1 2011-09-30 null
2 5041 11 2010-09-30 2011-09-30
3 5042 2 2010-09-30 2011-08-30
4 5042 12 2011-08-30 null

3)OfficeDetails(Office_id int,regionid int ,officeaddress nvarchar(500))

officeid regionid officeadd
1 1 pune office
11 2 mumbai office
2 1 pune office
12 2 mumbai office

4)TrainingDivision(TrainingID int,BatchName nvarchar(500))

TrainingID BatchName
1 --Select--
2 DDCAH/ACAH/DAHO
3 LDO
4 ALDO/LSS
5 AO/OS/SR.AST/SR.CRK/JR.CRK

5)TraineeBatchType(BatchTypeID int,BatchType nvarchar(100))

BatchTypeID BatchType
1 ---Select---
2 In-service Refreshement
3 In-service Orientation
4 MACP

6)BatchTimeTable(divisionID int,TrainingID int,batchstart datetime ,batchend datetime,batchTypeID int)

divisionID TrainingID batchstart batchend batchTypeID
1 2 2011-09-05 2011-09-09 2
2 3 2011-09-12 2011-09-16 3
3 4 2011-09-19 2011-09-23 2
4 5 2011-09-26 2011-09-30 4
5 2 2012-04-01 2012-04-05 2

7)TraineeRegistration(regiD int,empID int,divisionid int)

regiD empID divisionid
1 5041 1
2 5042 1

8)TraineeJoined(joinid int,empID int,regID int,officeid int)

joinid empID regID officeid

1 5042 2 2
2 5041 1 1

Now i want to show result as a region wise male and female in a training batch ,

PUNE Mumbai
male | Female Male | Female
DDCAH/ACAH/DAHO 1 0 0 0
LDO 0 1 0 0
ALDO/LSS 0 0 0 0
AO/OS/SR.AST/SR.CRK/JR.CRK 0 0 0 0

shilpash
Posting Yak Master

103 Posts

Posted - 2012-11-19 : 12:27:11

SELECT OfficeDetails.officeadd
,Male = CASE WHEN EmployeeDetails.gender = 'male' THEN 'x'
END
,Female = CASE WHEN EmployeeDetails.gender = 'female' THEN 'x'
END
,TrainingDivision.BatchName

FROM employeedetails
LEFT JOIN EmployeeWorkHistrory
ON EmployeeWorkHistrory.EmpID = employeedetails.EmpID
LEFT JOIN OfficeDetails
ON OfficeDetails.Office_id = EmployeeWorkHistrory.officeid
LEFT JOIN TrainingDivision
ON TrainingDivision.TrainingID = EmployeeWorkHistrory.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources