Hi Forum,
Could
Can some one help me with the query to achieve the result in the format listed in the image attached.
The data is also in the image attached.
Please post (in consumable -- that is, copy/paste -- format)
- CREATE TABLE statements for all tables involved
- INSERT INTO statements for all tables, to populate them with sample data that shows the condition
- Your query so far, whether it works or not!
- The actual desired output
As gbriton said, you have to provide sample data and expected results. If what you want is get rows from both tables even if the criterias don't match you can use a Full Join
Hi gbritton and scarela,
I have provided the sample data in the image attached, which involves the tables involved as well as the expected output.
Table 1 - ECRLevel
vcrName
ECRLevel_I
ECRLevel_II
ECRLevel_III
ECRLevel_IV
ECRLevel_V
ECRLevel_VI
Table 2-
vcrProgramGroupName EclerxOffices ECRLevel TotalEmployees
CLX Airoli 11 ECRLevel_II 1
CLX Airoli 11 ECRLevel_III 1
CLX Airoli 11 ECRLevel_V 10
CLX Airoli 14 ECRLevel_IV 1
CLX Airoli 14 ECRLevel_V 11
CTS Airoli 11 ECRLevel_I 2
CTS Airoli 11 ECRLevel_II 2
CTS Airoli 11 ECRLevel_III 4
CTS Airoli 11 ECRLevel_IV 3
CTS Airoli 11 ECRLevel_V 226
CTS Airoli 14 ECRLevel_I 1
CTS Airoli 14 ECRLevel_II 3
CTS Airoli 14 ECRLevel_III 1
CTS Airoli 14 ECRLevel_IV 1
CTS Airoli 14 ECRLevel_V 68
CTS Chandigarh ECRLevel_I 1
CTS Chandigarh ECRLevel_V 1880
CTS Chandigarh 2 ECRLevel_V 473
CTS Chandigarh 3 ECRLevel_V 2
CTS Pune 1 ECRLevel_I 1
CTS Pune 1 ECRLevel_IV 2
CTS Pune 1 ECRLevel_V 4
DMS Airoli 11 ECRLevel_I 91
DMS Airoli 11 ECRLevel_II 138
DMS Airoli 11 ECRLevel_III 133
DMS Airoli 11 ECRLevel_IV 136
DMS Airoli 11 ECRLevel_V 466
DMS Airoli 14 ECRLevel_I 39
DMS Airoli 14 ECRLevel_II 57
DMS Airoli 14 ECRLevel_III 47
DMS Airoli 14 ECRLevel_IV 70
DMS Airoli 14 ECRLevel_V 352
DMS Chandigarh ECRLevel_II 4
DMS Chandigarh ECRLevel_III 1
DMS Chandigarh ECRLevel_IV 1
DMS Chandigarh ECRLevel_V 31
DMS Chandigarh 2 ECRLevel_V 1
DMS Pune 1 ECRLevel_I 31
DMS Pune 1 ECRLevel_II 50
DMS Pune 1 ECRLevel_III 60
DMS Pune 1 ECRLevel_IV 76
DMS Pune 1 ECRLevel_V 205
DMS Pune 2 ECRLevel_I 2
DMS Pune 2 ECRLevel_III 3
DMS Pune 3 ECRLevel_I 9
DMS Pune 3 ECRLevel_II 4
DMS Pune 3 ECRLevel_III 8
DMS Pune 3 ECRLevel_IV 4
DMS Pune 3 ECRLevel_V 56
Financial Services Airoli 11 ECRLevel_I 17
Financial Services Airoli 11 ECRLevel_II 40
Financial Services Airoli 11 ECRLevel_III 36
Financial Services Airoli 11 ECRLevel_IV 15
Financial Services Airoli 11 ECRLevel_V 156
Financial Services Airoli 14 ECRLevel_I 128
Financial Services Airoli 14 ECRLevel_II 43
Financial Services Airoli 14 ECRLevel_III 54
Financial Services Airoli 14 ECRLevel_IV 65
Financial Services Airoli 14 ECRLevel_V 487
Financial Services Pune 1 ECRLevel_I 109
Financial Services Pune 1 ECRLevel_II 31
Financial Services Pune 1 ECRLevel_III 32
Financial Services Pune 1 ECRLevel_IV 23
Financial Services Pune 1 ECRLevel_V 593
Financial Services Pune 2 ECRLevel_I 145
Financial Services Pune 2 ECRLevel_II 81
Financial Services Pune 2 ECRLevel_III 55
Financial Services Pune 2 ECRLevel_IV 44
Financial Services Pune 2 ECRLevel_V 372
Financial Services Pune 3 ECRLevel_I 16
Financial Services Pune 3 ECRLevel_V 50
Shared Services Airoli 11 ECRLevel_I 12
Shared Services Airoli 11 ECRLevel_II 39
Shared Services Airoli 11 ECRLevel_III 19
Shared Services Airoli 11 ECRLevel_IV 25
Shared Services Airoli 11 ECRLevel_V 446
Shared Services Airoli 14 ECRLevel_I 5
Shared Services Airoli 14 ECRLevel_II 3
Shared Services Airoli 14 ECRLevel_IV 7
Shared Services Airoli 14 ECRLevel_V 118
Shared Services Chandigarh ECRLevel_I 3
Shared Services Chandigarh ECRLevel_II 3
Shared Services Chandigarh ECRLevel_III 9
Shared Services Chandigarh ECRLevel_IV 2
Shared Services Chandigarh ECRLevel_V 189
Shared Services Chandigarh 2 ECRLevel_I 2
Shared Services Chandigarh 2 ECRLevel_II 1
Shared Services Chandigarh 2 ECRLevel_III 1
Shared Services Chandigarh 2 ECRLevel_IV 4
Shared Services Chandigarh 2 ECRLevel_V 52
Shared Services Chandigarh 3 ECRLevel_V 1
Shared Services Parel ECRLevel_III 6
Shared Services Parel ECRLevel_IV 7
Shared Services Parel ECRLevel_V 33
Shared Services Pune 1 ECRLevel_I 2
Shared Services Pune 1 ECRLevel_III 3
Shared Services Pune 1 ECRLevel_IV 6
Shared Services Pune 1 ECRLevel_V 136
Shared Services Pune 2 ECRLevel_II 3
Shared Services Pune 2 ECRLevel_III 2
Shared Services Pune 2 ECRLevel_IV 4
Shared Services Pune 2 ECRLevel_V 96
Shared Services Pune 3 ECRLevel_I 1
Shared Services Pune 3 ECRLevel_V 8
Expected Output:-
For each group I want to have output as below, I have jsut mentioned for a single group (vcrProgramGroupName-CLX, EclerxOffices-Airoli 11)
vcrProgramGroupName EclerxOffices ECRLevel TotalEmployees
CLX Airoli 11 ECRLevel_I 0
CLX Airoli 11 ECRLevel_II 1
CLX Airoli 11 ECRLevel_III 1
CLX Airoli 11 ECRLevel_IV 0
CLX Airoli 11 ECRLevel_V 10
CLX Airoli 11 ECRLevel_VI 0
My queries:-
**Note-**Data for Table 2 is being fetched in temp table #a
select vcrProgramGroupName,l.vcrLocation,EL.vcrName ECRLevel,COUNT(EL.vcrName)ECRCount
INTO #ECRLocationMapping1
from viewemployees E
LEFT JOIN ProcessEmployeeMappingDetails PEM ON E.intemployeeid = PEM.intemployeeid
LEFT JOIN ECRLevel EL ON PEM.intECRLevel = EL.intECRLevelId
LEFT JOIN location l on e.vcrLocation = l.vcrLocation
LEFT JOIN city c on l.intCityId = c.intCityId
where E.bitActive = 1 and c.vcrcity in ('Mumbai','Pune','Chandigarh')
group by E.vcrProgramGroupName,l.vcrLocation,EL.vcrName
SELECT vcrProgramGroupName,Building EclerxOffices,ECRLevel,SUM(ECRCount)TotalEmployees
--into #a
FROM
(
SELECT
COALESCE(A.B11,A.B14,vcrLocation)Building
,* FROM
(
SELECT
CASE WHEN CHARINDEX('B11',vcrLocation)>0 THEN 'Airoli 11' ELSE NULL END B11
,CASE WHEN CHARINDEX('B14',vcrLocation)>0 THEN 'Airoli 14' ELSE NULL END B14
,* FROM #ECRLocationMapping1
)A
)B
GROUP BY vcrProgramGroupName, Building, ECRLevel
Query for the result which I tried
select
a.vcrProgramGroupName
,a.EclerxOffices
,e.vcrName ECRLevel1
,a.TotalEmployees
,a.ECRLevel
from
ECRLevel e
left join #a a on e.vcrName = a.ECRLevel
where a.ecrlevel is null
order by vcrProgramGroupName,EclerxOffices,ECRLevel
I had tried using full outer join, but it did not give me the result as expected.
We still don't have CREATE TABLE and INSERT INTO statements. We need those.
Thanks for the feedback till now, but I have figured it out.
;
WITH cte AS (
SELECT DISTINCT
t2.vcrProgramGroupName,
t2.EclerxOffices,
t1.vcrName ECRLevel
FROM #a t2
CROSS JOIN ECRLevel t1
)
SELECT cte.vcrProgramGroupName,
cte.EclerxOffices,
cte.ECRLevel,
SUM(ISNULL(t2.TotalEmployees, 0))
FROM cte
LEFT OUTER JOIN table2 t2 ON CTE.vcrProgramGroupName= T2.vcrProgramGroupName AND CTE.EclerxOffices = T2.EclerxOffices AND cte.ECRLevel = t2.ECRLevel
GROUP BY CTE.vcrProgramGroupName, CTE.EclerxOffices,CTE.ECRLevel