Need query in Month wise consolidate and Dept wise

[Former Member]

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

[Former Member]

Hi,

You may check this first:

Thanks,

Gordon

[Former Member]

Hi gordon

Thanks for the support ,,but am not an expert in SQL,, pls could u help me by editing

Regards

[Former Member]

You have created the SP above, do you?

Then, you can learn Pivot function to update your query. There are too many UDF in your query so that it is not for others to edit.