Need to group these union queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need to group these union queries

Author

Topic

md_aala42
Starting Member

1 Post

Posted - 2015-02-25 : 07:46:53

I have a query which comes by three different queries by Union All clause and returns two rows. but i need to again group by those two rows as same values in some cols.

query:

select [@abc_prj_att].U_Empid as 'SAP ID',OHEM.U_IQId as 'Iqama No', isNull(OHEM.firstName,'') + ' ' + isNull(OHEM.middleName,'') + ' ' + isNull(OHEM.lastName,'') as 'Employee Name',OUDP.Remarks as 'Department',[@abc_prj_att].U_PyrlMnth as 'Work Month',[@abc_prj_att].U_ProjectCode as 'Project Code',OPRJ.PrjName as 'Project Name',
COUNT([@abc_prj_att].U_ProjectCode) as 'Days Worked'
from [@abc_prj_att]
INNER JOIN OHEM ON OHEM.empid = [@abc_prj_att].U_Empid
INNER JOIN OUDP ON OHEM.dept = OUDP.Code
INNER JOIN OPRJ ON OPRJ.PrjCode = [@abc_prj_att].U_ProjectCode
where [@abc_prj_att].U_Empid = 212
and OUDP.Remarks = 'Hidada'
and [@abc_prj_att].U_PyrlMnth = 'Aug-2014'
and [@abc_prj_att].U_OTType not like 'SOT' and [@abc_prj_att].U_OTType not like 'NOT'
and [@abc_prj_att].U_ProjectCode not like 'INT%'
group by [@abc_prj_att].U_Empid,OHEM.U_IQId,OHEM.firstName,OHEM.middleName,OHEM.lastName,OUDP.Remarks,[@abc_prj_att].U_PyrlMnth,[@abc_prj_att].U_ProjectCode,OPRJ.PrjName,
[@abc_prj_att].U_OTType,[@abc_prj_att].U_OTHours

union all

select [@abc_prj_att].U_Empid as 'SAP ID',OHEM.U_IQId as 'Iqama No', isNull(OHEM.firstName,'') + ' ' + isNull(OHEM.middleName,'') + ' ' + isNull(OHEM.lastName,'') as 'Employee Name',OUDP.Remarks as 'Department',[@abc_prj_att].U_PyrlMnth as 'Work Month',[@abc_prj_att].U_ProjectCode as 'Project Code',OPRJ.PrjName as 'Project Name',
'' as 'Days Worked'
from [@abc_prj_att]
INNER JOIN OHEM ON OHEM.empid = [@abc_prj_att].U_Empid
INNER JOIN OUDP ON OHEM.dept = OUDP.Code
INNER JOIN OPRJ ON OPRJ.PrjCode = [@abc_prj_att].U_ProjectCode
where [@abc_prj_att].U_Empid = 212
and OUDP.Remarks = 'Hidada'
and [@abc_prj_att].U_PyrlMnth = 'Aug-2014'
and [@abc_prj_att].U_OTType not like 'SOT' and [@abc_prj_att].U_OTType not like 'NOT'
and [@abc_prj_att].U_ProjectCode like 'INT%'
group by [@abc_prj_att].U_Empid,OHEM.U_IQId,OHEM.firstName,OHEM.middleName,OHEM.lastName,OUDP.Remarks,[@abc_prj_att].U_PyrlMnth,[@abc_prj_att].U_ProjectCode,OPRJ.PrjName,
[@abc_prj_att].U_OTType,[@abc_prj_att].U_OTHours

union all

select [@abc_prj_att].U_Empid as 'SAP ID',OHEM.U_IQId as 'Iqama No', isNull(OHEM.firstName,'') + ' ' + isNull(OHEM.middleName,'') + ' ' + isNull(OHEM.lastName,'') as 'Employee Name',OUDP.Remarks as 'Department',[@abc_prj_att].U_PyrlMnth as 'Work Month',[@abc_prj_att].U_ProjectCode as 'Project Code',OPRJ.PrjName as 'Project Name',
COUNT([@abc_prj_att].U_ProjectCode) as 'Days Worked'
from [@abc_prj_att]
INNER JOIN OHEM ON OHEM.empid = [@abc_prj_att].U_Empid
INNER JOIN OUDP ON OHEM.dept = OUDP.Code
INNER JOIN OPRJ ON OPRJ.PrjCode = [@abc_prj_att].U_ProjectCode
where [@abc_prj_att].U_Empid = 212
and OUDP.Remarks = 'Hidada'
and [@abc_prj_att].U_PyrlMnth = 'Aug-2014' and [@abc_prj_att].U_OTType between 'NOT' and 'SOT'
group by [@abc_prj_att].U_Empid,OHEM.U_IQId,OHEM.firstName,OHEM.middleName,OHEM.lastName,OUDP.Remarks,[@abc_prj_att].U_PyrlMnth,[@abc_prj_att].U_ProjectCode,OPRJ.PrjName,
[@abc_prj_att].U_OTType,[@abc_prj_att].U_OTHours
ORDER BY [Employee Name]

Results Returned:
ID Iqama No Employee Name Department Work Month Project Code Project Name Days worked
212 2264594090 Abdul Hoque Safi Hidada Aug-2014 MPS-0001 Al Hidada - Jeddah 30
212 2264594090 Abdul Hoque Safi Hidada Aug-2014 MPS-0001 Al Hidada - Jeddah 1

I want:
ID Iqama No Employee Name Department Work Month Project Code Project Name Days worked
212 2264594090 Abdul Hoque Safi Hidada Aug-2014 MPS-0001 Al Hidada - Jeddah 31

Mohammed Aala

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-25 : 13:09:02

You can do this:

select ID, [Iqama No], [Employee Name], Department, [Work Month], [Project Code], [Project Name], MAX(Days) as Days
from
(
your query goes here
) t
group by ID, [Iqama No], [Employee Name], Department, [Work Month], [Project Code], [Project Name]

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources