|
kishorGadekar
Starting Member
India
1 Posts |
 Posted - 11/19/2012 : 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
Yak Posting Veteran
72 Posts |
Posted - 11/19/2012 : 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 |
 |
|
how to show regionwise male and female result
