SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 how to show regionwise male and female result
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kishorGadekar
Starting Member

India
1 Posts

Posted - 11/19/2012 :  04:49:06  Show Profile  Reply with Quote
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

99 Posts

Posted - 11/19/2012 :  12:27:11  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000