Need query in Month wise consolidate and Dept wise

27 Views

Hi experts

i had created a SPT based on HEM1 Table

pls find the SPT Below

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[Employee Vaccation Details based on HEM-1]

(

@LeaveFrom DateTime,

@LeaveTo DateTime

)

AS

Begin

SELECT OHEM.empID as 'Emp SAP ID',OHEM.U_EMPOLD_ID AS 'Emp File No',OHEM.firstName + ' ' + isNULL(OHEM.middleName,'') + ' ' + OHEM.lastName AS 'Employee Name',

OUDP.Name AS 'Department',OHEM.U_DeptName AS 'Previous Dept',OHEM.U_IQProf as 'Iqama Profession',OHPS.name as 'Position',OCRY.Name as 'Citizenship',OHEM.U_IQId as 'Iqama ID',

OHEM.passportNo as 'Passport No',OHEM.U_IQExDate as 'Iqama Exp Date',HEM1.U_SYear as 'Year',[@ABLEAVET].Name as 'Holiday Type',

HEM1.fromDate as 'Leave Start Date',HEM1.toDate as 'Leave End Date', HEM1.U_ResumeDt AS 'Resume Date',

HEM1.U_TotDays as 'Total days Leave Alloted',HEM1.reason as 'Any Reason' from OHEM

inner join OUDP on OHEM.dept=OUDP.Code

inner join OHPS on OHEM.position=OHPS.posID

inner join OCRY on OHEM.citizenshp=OCRY.Code

inner join HEM1 on OHEM.empID=HEM1.empID

inner join [@ABLEAVET] on HEM1.U_LeaveID=[@ABLEAVET].Code

Where OHEM.dept not in (47,49) and HEM1.fromDate between @LeaveFrom and @LeaveTo AND HEM1.reason = 'Vacation'

END

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

i need this in the below given format

company name Type of Actions Jan - Feb - Mar - Apr - May - June

ABCD Vaccation 1 0 1 5 6 0

Terminiation 5 0 0 5 10 15

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

EFGH Vaccation 0 1 6 5 15 25

Termination 10 5 0 0 0 1

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

filtering is not needed

also in the same query i need to attach the Terminated Employees count in the same format

Regards