Calculate sum of salary with multiple PayRoll in SQL Server

iammann

SOLVED CLOSED ACTIVE ANSWERED
User: iammann
Posted: on Jul 28, 2016 01:33 AM
Forum: SQL Server
Answer: 1
Views: 5867

USE [dbDoclandCRM]GO
StoredProcedure [dbo].[Select_salary]    
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Select_salary]@date date=null
As
Begincreate table #temp( Employeeid bigint, Ename varchar(max), ECode int,salary decimal(18,2),noofdays int)
declare @empid int,@salary decimal(15,2);
DECLARE Employee_Cursor 
CURSOR FOR SELECT employeeidFROM TblEmployee
OPEN Employee_Cursor;  
FETCH NEXT FROM Employee_Cursor INTO @empid;  
WHILE @@FETCH_STATUS = 0     
BEGIN  
	insert into #tempselect  Employeeid,EName,ECode,isnull(((select top(1) SalaryAmount 
	from TblEmployeePayroll 
	where Employeeid=@empid and Month(DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,EPayrollDate)+1,0))) = Month(DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@date)+1,0))) 
	order  by EPayrollDate desc ) * (select COUNT(Attendancedate) from TblEmployeeAttendance where Employeeid=@empid and Attendancedate between @date and DATEADD(DD,30, @date) ) /[dbo].[ufn_GetDaysInMonth](@date)),0) as salary,
	(select COUNT(Attendancedate) from TblEmployeeAttendance where Employeeid=@empid and Attendancedate between @date and DATEADD(DD,30, @date)) as noofdays from TblEmployee where Employeeid=@empid  
	FETCH NEXT FROM Employee_Cursor INTO @empid;     
END;  
CLOSE Employee_Cursor;  
DEALLOCATE Employee_Cursor;   
select * from #tempdrop table #tempEnd

i want to calculate salary of employee if there is multiple payroll in a month for a employee like

in july month from 01/07/2016 to 13/07/2016 his payroll was 12500

and after that his payroll was 17000 so how to calculate total salary for july month of that employee

in my query its calculated only on one payroll..but i want to calculate on both payroll

Download FREE API for Word, Excel and PDF in ASP.Net

AnandM

0 1

Reply
User: AnandM
Replied: on Jul 28, 2016 02:10 AM
Report

Hi mann,

Please share your whole data with tables which you are using in query and excepted output result

iammann

0 2

Reply
User: iammann
Replied: on Jul 28, 2016 05:26 AM
Report

USE [dbDoclandCRM]
GO
/****** Object: Table [dbo].[TblEmployee] Script Date: 28/07/2016 4:46:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TblEmployee](
[Employeeid] [int] IDENTITY(1,1) NOT NULL,
[ECode] [char](10) NULL,
[EName] [varchar](255) NULL,
[EMobile] [varchar](50) NULL,
[EEmail] [varchar](50) NULL,
[EAddress] [varchar](max) NULL,
[EDoJ] [datetime] NULL,
[EDate] [datetime] NULL,
[StaffCategoryid] [int] NULL,
[Cityid] [int] NULL,
[Stateid] [int] NULL,
[ESalary] [money] NULL,
CONSTRAINT [PK_TblEmployee] PRIMARY KEY CLUSTERED
(
[Employeeid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[TblEmployeeAttendance] Script Date: 28/07/2016 4:46:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblEmployeeAttendance](
[EAttendanceid] [int] IDENTITY(1,1) NOT NULL,
[Attendancedate] [datetime] NULL,
[Employeeid] [int] NULL,
[FromTime] [datetime] NULL,
[ToTime] [datetime] NULL,
[Totalhours] [float] NULL,
CONSTRAINT [PK_TblEmployeeAttendance] PRIMARY KEY CLUSTERED
(
[EAttendanceid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[TblEmployeePayroll] Script Date: 28/07/2016 4:46:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblEmployeePayroll](
[EPayrollid] [int] IDENTITY(1,1) NOT NULL,
[EPayrollDate] [datetime] NULL,
[Employeeid] [int] NULL,
[SalaryAmount] [money] NULL,
CONSTRAINT [PK_TblEmployeePayroll] PRIMARY KEY CLUSTERED
(
[EPayrollid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[TblEmployee] ON

GO
INSERT [dbo].[TblEmployee] ([Employeeid], [ECode], [EName], [EMobile], [EEmail], [EAddress], [EDoJ], [EDate], [StaffCategoryid], [Cityid], [Stateid], [ESalary]) VALUES (3069, N'002 ', N'Prakhar', N'1234567890', N'prakhar@gmail.com', N'dilkusha', CAST(0x0000A61800000000 AS DateTime), CAST(0x0000A62C0137B9EC AS DateTime), 2, 1062, 1, 6000.0000)
GO
INSERT [dbo].[TblEmployee] ([Employeeid], [ECode], [EName], [EMobile], [EEmail], [EAddress], [EDoJ], [EDate], [StaffCategoryid], [Cityid], [Stateid], [ESalary]) VALUES (3071, N'003 ', N'Aditya', N'9044430431', N'aditya@gmail.com', N'alambagh', CAST(0x0000A61A00000000 AS DateTime), CAST(0x0000A63400CC5C05 AS DateTime), 1, 1049, 1, 8000.0000)
GO
INSERT [dbo].[TblEmployee] ([Employeeid], [ECode], [EName], [EMobile], [EEmail], [EAddress], [EDoJ], [EDate], [StaffCategoryid], [Cityid], [Stateid], [ESalary]) VALUES (3072, N'004 ', N'Manish Kumar', N'9807257827', N'kumarmann2015@gmail.com', N'E 404 a ravi khand sharda nagar', CAST(0x0000A63C00000000 AS DateTime), CAST(0x0000A63B011F4D17 AS DateTime), 1, 1049, 1, NULL)
GO
INSERT [dbo].[TblEmployee] ([Employeeid], [ECode], [EName], [EMobile], [EEmail], [EAddress], [EDoJ], [EDate], [StaffCategoryid], [Cityid], [Stateid], [ESalary]) VALUES (3073, N'005 ', N'Shanu Ali', N'9044430431', N'shanu1234@gmail.com', N'ALAMBAGH', CAST(0x0000A64200000000 AS DateTime), CAST(0x0000A64400FC2179 AS DateTime), 1010, 1049, 1, NULL)
GO
SET IDENTITY_INSERT [dbo].[TblEmployee] OFF
GO
SET IDENTITY_INSERT [dbo].[TblEmployeeAttendance] ON

GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1087, CAST(0x0000A63400000000 AS DateTime), 3069, CAST(0x0000A633006B1DE0 AS DateTime), CAST(0x0000A63300FF6EA0 AS DateTime), 9)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1088, CAST(0x0000A63300000000 AS DateTime), 3069, CAST(0x0000A633005AA320 AS DateTime), CAST(0x0000A63301499700 AS DateTime), 14.5)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1092, CAST(0x0000A63500BAA9F7 AS DateTime), 3071, NULL, NULL, 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1093, CAST(0x0000A63500BAF78D AS DateTime), 3071, NULL, NULL, 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1094, CAST(0x0000A63300000000 AS DateTime), 3071, NULL, NULL, 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1095, CAST(0x0000A63400000000 AS DateTime), 3071, NULL, NULL, 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1096, CAST(0x0000A63200000000 AS DateTime), 3071, NULL, NULL, 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1097, CAST(0x0000A63100000000 AS DateTime), 3071, NULL, NULL, 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1098, CAST(0x0000A63000000000 AS DateTime), 3071, NULL, NULL, 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1099, CAST(0x0000A63000000000 AS DateTime), 3069, NULL, NULL, 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1100, CAST(0x0000A62F00000000 AS DateTime), 3069, NULL, NULL, 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1101, CAST(0x0000A62E00000000 AS DateTime), 3071, NULL, NULL, 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1102, CAST(0x0000A62D00000000 AS DateTime), 3071, NULL, NULL, 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1103, CAST(0x0000A63600000000 AS DateTime), 3071, NULL, NULL, 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1104, CAST(0x0000A63700000000 AS DateTime), 3071, NULL, NULL, 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1105, CAST(0x0000A63800000000 AS DateTime), 3071, NULL, NULL, 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1106, CAST(0x0000A63600000000 AS DateTime), 3069, NULL, NULL, 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1107, CAST(0x0000A63700000000 AS DateTime), 3069, NULL, NULL, 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (1108, CAST(0x0000A63B00000000 AS DateTime), 3071, CAST(0x0000A63B0083D600 AS DateTime), CAST(0x0000A63B0107AC00 AS DateTime), 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (2108, CAST(0x0000A64800000000 AS DateTime), 3069, CAST(0x0000A6440083D600 AS DateTime), CAST(0x0000A644011826C0 AS DateTime), 9)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (2109, CAST(0x0000A64800000000 AS DateTime), 3072, CAST(0x0000A6440083D600 AS DateTime), CAST(0x0000A6440107AC00 AS DateTime), 8)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (2110, CAST(0x0000A64B00000000 AS DateTime), 3072, CAST(0x0000A644005AA320 AS DateTime), CAST(0x0000A64400D63BC0 AS DateTime), 7.5)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (2111, CAST(0x0000A64C00000000 AS DateTime), 3072, CAST(0x0000A64400AD08E0 AS DateTime), CAST(0x0000A644010FE960 AS DateTime), 6)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (2112, CAST(0x0000A63900000000 AS DateTime), 3069, CAST(0x0000A65100AD08E0 AS DateTime), CAST(0x0000A65100F73140 AS DateTime), 4.5)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (2113, CAST(0x0000A63A00000000 AS DateTime), 3069, CAST(0x0000A65100B54640 AS DateTime), CAST(0x0000A65100EEF3E0 AS DateTime), 3.5)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (2114, CAST(0x0000A63B00000000 AS DateTime), 3069, CAST(0x0000A65100C5C100 AS DateTime), CAST(0x0000A65100EEF3E0 AS DateTime), 2.5)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (2115, CAST(0x0000A63C00000000 AS DateTime), 3069, CAST(0x0000A65100E6B680 AS DateTime), CAST(0x0000A65100FF6EA0 AS DateTime), 1.5)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (2116, CAST(0x0000A63D00000000 AS DateTime), 3069, CAST(0x0000A65100EEF3E0 AS DateTime), CAST(0x0000A65100F73140 AS DateTime), 0.5)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (2117, CAST(0x0000A63E00000000 AS DateTime), 3069, CAST(0x0000A651009C8E20 AS DateTime), CAST(0x0000A651010FE960 AS DateTime), 7)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (2118, CAST(0x0000A63F00000000 AS DateTime), 3069, CAST(0x0000A65100BD83A0 AS DateTime), CAST(0x0000A65100CDFE60 AS DateTime), 1)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (2119, CAST(0x0000A64D00000000 AS DateTime), 3069, CAST(0x0000A65100CDFE60 AS DateTime), CAST(0x0000A65100F73140 AS DateTime), 2.5)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (2120, CAST(0x0000A65200000000 AS DateTime), 3069, CAST(0x0000A65100B54640 AS DateTime), CAST(0x0000A65100CDFE60 AS DateTime), 1.5)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (2121, CAST(0x0000A64E00000000 AS DateTime), 3069, CAST(0x0000A65100B54640 AS DateTime), CAST(0x0000A65100FF6EA0 AS DateTime), 4.5)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (2122, CAST(0x0000A64F00000000 AS DateTime), 3069, CAST(0x0000A651008C1360 AS DateTime), CAST(0x0000A65100F73140 AS DateTime), 6.5)
GO
INSERT [dbo].[TblEmployeeAttendance] ([EAttendanceid], [Attendancedate], [Employeeid], [FromTime], [ToTime], [Totalhours]) VALUES (2123, CAST(0x0000A65100000000 AS DateTime), 3071, CAST(0x0000A6510083D600 AS DateTime), CAST(0x0000A65100D63BC0 AS DateTime), 5)
GO
SET IDENTITY_INSERT [dbo].[TblEmployeeAttendance] OFF
GO
SET IDENTITY_INSERT [dbo].[TblEmployeePayroll] ON

GO
INSERT [dbo].[TblEmployeePayroll] ([EPayrollid], [EPayrollDate], [Employeeid], [SalaryAmount]) VALUES (56, CAST(0x0000A61800000000 AS DateTime), 3069, 6000.0000)
GO
INSERT [dbo].[TblEmployeePayroll] ([EPayrollid], [EPayrollDate], [Employeeid], [SalaryAmount]) VALUES (57, CAST(0x0000A61800000000 AS DateTime), 3071, 8000.0000)
GO
INSERT [dbo].[TblEmployeePayroll] ([EPayrollid], [EPayrollDate], [Employeeid], [SalaryAmount]) VALUES (58, CAST(0x0000A62600000000 AS DateTime), 3069, 9000.0000)
GO
INSERT [dbo].[TblEmployeePayroll] ([EPayrollid], [EPayrollDate], [Employeeid], [SalaryAmount]) VALUES (59, CAST(0x0000A63600000000 AS DateTime), 3069, 9000.0000)
GO
INSERT [dbo].[TblEmployeePayroll] ([EPayrollid], [EPayrollDate], [Employeeid], [SalaryAmount]) VALUES (60, CAST(0x0000A63600000000 AS DateTime), 3071, 12500.0000)
GO
INSERT [dbo].[TblEmployeePayroll] ([EPayrollid], [EPayrollDate], [Employeeid], [SalaryAmount]) VALUES (62, CAST(0x0000A65800000000 AS DateTime), 3071, 6000.0000)
GO
INSERT [dbo].[TblEmployeePayroll] ([EPayrollid], [EPayrollDate], [Employeeid], [SalaryAmount]) VALUES (1061, CAST(0x0000A63D00000000 AS DateTime), 3072, 12072.0000)
GO
INSERT [dbo].[TblEmployeePayroll] ([EPayrollid], [EPayrollDate], [Employeeid], [SalaryAmount]) VALUES (1062, CAST(0x0000A64C00C72D08 AS DateTime), 3069, 12000.0000)
GO
INSERT [dbo].[TblEmployeePayroll] ([EPayrollid], [EPayrollDate], [Employeeid], [SalaryAmount]) VALUES (1063, CAST(0x0000A64C00C9C710 AS DateTime), 3071, 12000.0000)
GO
SET IDENTITY_INSERT [dbo].[TblEmployeePayroll] OFF
GO
ALTER TABLE [dbo].[TblEmployee] WITH CHECK ADD CONSTRAINT [FK_TblEmployee_Master_Category] FOREIGN KEY([StaffCategoryid])
REFERENCES [dbo].[Master_StaffCategory] ([StaffCategoryid])
GO
ALTER TABLE [dbo].[TblEmployee] CHECK CONSTRAINT [FK_TblEmployee_Master_Category]
GO
ALTER TABLE [dbo].[TblEmployee] WITH CHECK ADD CONSTRAINT [FK_TblEmployee_Master_City] FOREIGN KEY([Cityid])
REFERENCES [dbo].[Master_City] ([Cityid])
GO
ALTER TABLE [dbo].[TblEmployee] CHECK CONSTRAINT [FK_TblEmployee_Master_City]
GO
ALTER TABLE [dbo].[TblEmployee] WITH CHECK ADD CONSTRAINT [FK_TblEmployee_Master_State] FOREIGN KEY([Stateid])
REFERENCES [dbo].[Master_State] ([Stateid])
GO
ALTER TABLE [dbo].[TblEmployee] CHECK CONSTRAINT [FK_TblEmployee_Master_State]
GO
ALTER TABLE [dbo].[TblEmployeeAttendance] WITH CHECK ADD CONSTRAINT [FK_TblEmployeeAttendance_TblEmployee] FOREIGN KEY([Employeeid])
REFERENCES [dbo].[TblEmployee] ([Employeeid])
GO
ALTER TABLE [dbo].[TblEmployeeAttendance] CHECK CONSTRAINT [FK_TblEmployeeAttendance_TblEmployee]
GO
ALTER TABLE [dbo].[TblEmployeePayroll] WITH CHECK ADD CONSTRAINT [FK_TblEmployeePayroll_TblEmployee] FOREIGN KEY([Employeeid])
REFERENCES [dbo].[TblEmployee] ([Employeeid])
GO
ALTER TABLE [dbo].[TblEmployeePayroll] CHECK CONSTRAINT [FK_TblEmployeePayroll_TblEmployee]
GO

this is the whole script you can take all the data and table also.. if employee attendance between 1/07/2016 to 13/07/2016 and between this date his payroll is 5000 and between 14/07/2016 to 31/07/2016 payroll is 7000 then first 01/07/2016 to 13/07/2016 calculate his salary on payroll 5000 after remaining days calculate on 7000 5000/31*13(dayscount of attendance) 7000/31*18(dayscount of attendance)

USE [dbDoclandCRM]
GO/****** Object:  UserDefinedFunction [dbo].[ufn_GetDaysInMonth]    Script Date: 28/07/2016 4:55:32 PM ******/
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ufn_GetDaysInMonth] ( @pDate    DATETIME )RETURNS INTASBEGIN
    RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31 
				WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30                
	ELSE CASE	WHEN (YEAR(@pDate) % 4    = 0 AND YEAR(@pDate) % 100 != 0) OR (YEAR(@pDate) % 400  = 0)  THEN 29
	            ELSE 28
				END 
	END
END

im using this function for getting days in month

AnandM

0 3

Reply
ANSWER
User: AnandM
Replied: on Aug 04, 2016 08:27 AM
Report

Hi mann,

Please refer below query

SQL

DECLARE @EPayrollDate DATETIME
SET @EPayrollDate = '2016/07/01'
DECLARE @Employeeid INT
SET @Employeeid = 3071

;WITH CTE AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNumber
,EPayrollDate
,Employeeid
,SalaryAmount
,DATEDIFF(DAY
		 ,(SELECT TOP 1 EPayrollDate 
		   FROM #TblEmployeePayroll 
		   WHERE EPayrollDate = @EPayrollDate AND Employeeid = @Employeeid )
		,(SELECT TOP 1 EPayrollDate 
		  FROM #TblEmployeePayroll 
		  WHERE Employeeid = @Employeeid 
		  AND EPayrollDate BETWEEN (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @EPayrollDate), 0))
						            AND 
								   (SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, @EPayrollDate), -1))
		  ORDER BY EPayrollDate DESC)) AS Days
,(SalaryAmount/DATEDIFF(DAY,@EPayrollDate, DATEADD(MONTH, 1, @EPayrollDate))) AS SalaryPerDay
FROM #TblEmployeePayroll
WHERE EPayrollDate BETWEEN (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @EPayrollDate), 0)) 
							 AND 
						   (SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, @EPayrollDate), -1))
AND Employeeid = @Employeeid
),CTE1 AS
(
SELECT RowNumber
,EPayrollDate
,Employeeid
,SalaryAmount
,Days
,SalaryPerDay
FROM CTE
WHERE RowNumber = 1
UNION ALL
SELECT Y.RowNumber
,Y.EPayrollDate
,Y.Employeeid
,Y.SalaryAmount
,DATEDIFF(DAY,(SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @EPayrollDate), 0)),(SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, @EPayrollDate), -1))) - Y.Days
,Y.SalaryPerDay
FROM CTE1 
INNER JOIN CTE Y
ON Y.RowNumber = CTE1.RowNumber + 1
)
SELECT Employeeid,SUM([Days]*SalaryPerDay) AS MonthlySalary
FROM CTE1
GROUP BY Employeeid

This query is for only single employee whose payrolldate is more then one in single month

Hope this works for you

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Calculate sum of salary with multiple PayRoll in SQL Server

Company

Explore

Follow us

Contact Us