USE [ACube_PRL]
GO
/****** Object: StoredProcedure [PAY].[usp_YTDSum] Script Date: 3/9/2016 3:38:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER
PROCEDURE [PAY].[usp_YTDSum]
@EmpId int
AS
BEGIN
BEGIN TRY
SELECT * FROM (
select PC.EmpId, PC.SalaryComponentId, SC.Description,PC.Amount,CG.GroupName,
Convert(varchar(20),left(DATENAME(MONTH,PC.CreatedDateTime),3))+'_'+CONVERT(varchar,DATENAME(YEAR,PC.CreatedDateTime)) as MonthYear
from PAY.PaySlipComponents PC
INNER JOIN PAY.SalaryComponents SC ON PC.SalaryComponentId = SC.SalaryComponentId
INNER JOIN PAY.ComponentGroups CG ON SC.GroupId = CG.GroupId
WHERE PC.EmpId = @EmpId AND SC.Description <> 'MonthlyCTC'
) as s
UNION
SELECT * FROM(
select OSD.EmpId, OSD.SalaryComponentId, SC.Description,OSD.Amount,CG.GroupName,
Convert(varchar(20),left(DATENAME(MONTH,OSD.CreatedDateTime),3))+'_'+CONVERT(varchar,DATENAME(YEAR,OSD.CreatedDateTime)) as MonthYear
from pay.OtherSalaryComponents OSD
INNER JOIN PAY.SalaryComponents SC ON OSD.SalaryComponentId = SC.SalaryComponentId
INNER JOIN PAY.ComponentGroups CG ON SC.GroupId = CG.GroupId
where OSD.EmpId = @EmpId
) as q
UNION
SELECT * FROM(
select SD.EmpId, SD.SalaryComponentId, SC.Description,SD.Amount,CG.GroupName,
Convert(varchar(20),left(DATENAME(MONTH,SD.CreatedDateTime),3))+'_'+CONVERT(varchar,DATENAME(YEAR,SD.CreatedDateTime)) as MonthYear
from pay.SalaryDeductions SD
INNER JOIN PAY.SalaryComponents SC ON SD.SalaryComponentId = SC.SalaryComponentId
INNER JOIN PAY.ComponentGroups CG ON SC.GroupId = CG.GroupId
where SD.EmpId = @EmpId
) as q
return
END TRY
BEGIN CATCH
END CATCH
END
........................
The above Amount colum some months have no amount means empty itshows thats y insted of empty i wana repaced with zero plz reply fast