SQLTeam.com | Weblogs | Forums

Expected resultset

#1

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.

#2

What is in NationalityTypeMaster?

#3 
use sqlteam
go

if OBJECT_ID('tempdb..#EmployeeMemberMaster') is not null 
	drop table #EmployeeMemberMaster
	go

if OBJECT_ID('tempdb..#NationalityMaster') is not null 
	drop table #NationalityMaster
	go


if OBJECT_ID('tempdb..#Gendermaster') is not null 
	drop table #Gendermaster
	go



if OBJECT_ID('tempdb..#tempNationality2') is not null 
	drop table #tempNationality2
	go

create table #tempNationality2(NationalityTypeID int, Nationalitycount int)

create table #EmployeeMemberMaster(EmployeeID int identity(1,1), 
EmployeeName nvarchar(250), NationalityID int, 
EmployeeSystemID int, GenderID int)
insert into #EmployeeMemberMaster (EmployeeName,NationalityID,
EmployeeSystemID, GenderID)
--Saudis 8 
select 'Abdul Rahman', 1, 1, 1 union			
select 'Hassan Jameel',1, 4, 1 union
select 'Ibn Saud', 1, 2, 1 union
select 'Abu Bakr',1, 2, 1 union
select 'Faisal', 1, 1, 1 union
select 'Adnan Khashoggi', 1, 1, 1 union
select N'Leesa A ليزا أ', 1, 1, 2 union

--US
select 'Jimmy Dean', 3, 6, 1 union

--UK
select 'Margo Tatcher', 4, 3, 2 



if OBJECT_ID('tempdb..#tempSectorData') is not null 
	drop table #tempSectorData
	go

CREATE TABLE #tempSectorData 
(	SaudiCounts				bigint NULL
	,NonSaudiCounts				bigint NULL
	,MaleCounts					bigint NULL
	,FeMaleCounts			    bigint NULL
);


;with src
as
(
	select s.SaudiCounts, ns.NonSaudiCounts, mc.MaleCounts, fc.FeMaleCounts
	  from (
			select count(NationalityID) as SaudiCounts
			 from #EmployeeMemberMaster
			 where NationalityID = 1
			group by NationalityID
		) s
	cross apply (
	select count(NationalityID) as NonSaudiCounts
			 from #EmployeeMemberMaster
			 where NationalityID <> 1
			group by NationalityID
	) ns

	cross apply (
	select count(GenderID) as MaleCounts
			 from #EmployeeMemberMaster
			 where GenderID =  1
			group by GenderID
	) mc

	cross apply (
	select count(GenderID) as FeMaleCounts
			 from #EmployeeMemberMaster
			 where GenderID = 2
			group by GenderID
	) fc
) 
insert into #tempSectorData
select distinct SaudiCounts, NonSaudiCounts, MaleCounts, FeMaleCounts From src


select * from #tempSectorData
#4

NationalityType table has columns NationalityTypeID & NationalityName.
The NationalityTypeID is a FK in NationalityMaster table.
It has two values
1, Saudi
2, Non Saudi

NationalityMaster has columns NationalityID, Nationalityname, NationalityTypeID

Apologies for the confusion.