I have the tables
create table #EmployeeMemberMaster(EmployeeID int identity(1,1), EmployeeName nvarchar(250), NationalityID int, EmployeeSystemID int)
insert into #EmployeeMemberMaster (EmployeeName,NationalityID,EmployeeSystemID)
select 'IQ', 1, 1 union
select 'JK', 3, 6 union
select 'JOP',1, 4 union
select 'IPO', 4, 3 union
select 'RERT', 1, 2 union
select 'GT',1, 2 union
select 'Kakka', 1, 1 union
select 'Wala Weber', 1, 1 union
select 'Coffee Bean', 1, 1
select * from #EmployeeMemberMaster
create table #NationalityMaster(NationalityID int, NationalityName nvarchar(150))
insert into #NationalityMaster
select 1, 'Saudi Arabia' union
select 2, 'India' union
select 3, 'United States' union
select 4, 'United Kingdom' union
select 5, 'Canada' union
select 6, 'Australia' union
select 7, 'Singapore'
select * from #NationalityMaster
create table #Gendermaster(GenderID int, GenderName nvarchar(150))
insert into #Gendermaster
select 1, 'Male' union
select 2, 'Female'
select * from #Gendermaster
I have written query:
CREATE TABLE #tempSectorData
( SaudiCounts bigint NULL
,NonSaudiCounts bigint NULL
,MaleCounts bigint NULL
,FeMaleCounts bigint NULL
);
select
E.EmployeeID,E.MemberID,N.NationalityTypeID
into #tempNationality1
from EmployeeMemberMaster E
Inner join NationalityMaster N ON E.NationalityID = N.NationalityID
Inner join NationalityTypeMaster NT ON N.NationalityTypeID = NT.NationalityTypeID
WHERE ISNULL(E.IsDeleted, 0) = 0
select NationalityTypeID, Count(NationalityTypeID) as Nationalitycount
into #tempNationality2
from #tempNationality1
Group by NationalityTypeID
order by NationalityTypeID asc;
INSERT INTO #tempSectorData (SaudiCounts)
SELECT ISNULL(V.Nationalitycount,0) as SaudiCounts FROM #tempNationality2 V WHERE V.NationalityTypeID = 1
INSERT INTO #tempSectorData (NonSaudiCounts)
SELECT ISNULL(V.Nationalitycount,0) as NonSaudiCounts FROM #tempNationality2 V WHERE V.NationalityTypeID <> 1
select
E.EmployeeID,E.MemberID,N.GenderID
into #tempGender1
from EmployeeMemberMaster E
Inner join GenderMaster N ON E.GenderID = N.GenderID
WHERE ISNULL(E.IsDeleted, 0) = 0
select GenderID, Count(GenderID) as GenderCount
into #tempGender2
from #tempGender1
Group by GenderID
order by GenderID asc;
INSERT INTO #tempSectorData (MaleCounts)
SELECT ISNULL(V.GenderCount,0) as MaleCounts FROM #tempGender2 V WHERE V.GenderID = 1
INSERT INTO #tempSectorData (FeMaleCounts)
SELECT ISNULL(V.GenderCount,0) as FeMaleCounts FROM #tempGender2 V WHERE V.GenderID = 2
select * from #tempSectorData
DROP TABLE #tempNationality1
DROP TABLE #tempNationality2
DROP TABLE #tempGender1
DROP TABLE #tempGender2
DROP TABLE #tempSectorData
This query returns the result in this way:
SaudiCounts | NonSaudiCounts | MaleCounts | FeMaleCounts |
---|---|---|---|
7 | NULL | NULL | NULL |
NULL | 2 | NULL | NULL |
NULL | NULL | 7 | NULL |
NULL | NULL | NULL | 2 |
Expected result is this way below:
SaudiCounts | NonSaudiCounts | MaleCounts | FeMaleCounts |
---|---|---|---|
7 | 2 | 7 | 2 |
How to achieve this. Please help on this.