Sql query need to be updated as group by

Question

Post reply

Sql query need to be updated as group by

mohammedaala

SSC-Addicted

Points: 466
More actions
February 25, 2015 at 5:56 am

#398075

Dear Team,
I have a 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:
SAP IDIqama NoEmployee Name Department Work Month Project Code Project Name Days Worked
2122264594090Abdul Hoque SafiHidadaAug-2014MPS-0001Al Hidada - Jeddah 30
2122264594090Abdul Hoque SafiHidadaAug-2014MPS-0001Al Hidada - Jeddah 1
I want the results should be returned as:
SAP IDIqama NoEmployee Name Department Work Month Project Code Project Name Days Worked
2122264594090Abdul Hoque SafiHidadaAug-2014MPS-0001Al Hidada - Jeddah 31

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

mohammedaala (2/25/2015)
Dear Team,
I have a query:

And?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

It looks like you need to sum the days worked, but without an actual question, it's hard to give you an actual answer.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Wild guess:

SELECT

a.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],

a.U_PyrlMnth AS [Work Month],

a.U_ProjectCode AS [Project Code],

OPRJ.PrjName AS [Project Name],

SUM(x.CountMe) AS [Days Worked]

FROM [@abc_prj_att] a

INNER JOIN OHEM ON OHEM.empid = a.U_Empid

INNER JOIN OUDP ON OHEM.dept = OUDP.Code

INNER JOIN OPRJ ON OPRJ.PrjCode = a.U_ProjectCode

CROSS APPLY (

SELECT [CountMe] = CASE

WHEN a.U_OTType NOT IN ('SOT','NOT') AND a.U_ProjectCode NOT LIKE 'INT%' THEN 1

WHEN a.U_OTType NOT IN ('SOT','NOT') AND a.U_ProjectCode LIKE 'INT%' THEN 0

WHEN a.U_OTType BETWEEN 'NOT' AND 'SOT' THEN 1

END

) x

WHERE a.U_Empid = 212 AND a.U_PyrlMnth = 'Aug-2014'

AND OUDP.Remarks = 'Hidada'

AND x.CountMe IS NOT NULL

GROUP BY

a.U_Empid,

OHEM.U_IQId,

OHEM.firstName,

OHEM.middleName,

OHEM.lastName,

OUDP.Remarks,

a.U_PyrlMnth,

a.U_ProjectCode,

OPRJ.PrjName--,

--a.U_OTType,

--a.U_OTHours

ORDER BY [Employee Name]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mohammedaala SSC-Addicted Points: 466 More actions · Answer 4

thanks Chris,

it worked for me and now I am checking for all employees...