asked
Calculate Salary during a week starting Sunday thru Saturday
I need to calculate provider salaries in a week that starts on Sunday and ends Saturday. If the calculated salary is <$455, then the total salary=calculated+($455-calculated salary). This seems to be straightforward until I found out that beginning of the first week in a particular month may start in the previous month. For example, For example, April 1st 2015 is Wednesday and that week starts with Sunday March 29. Thus, I need to calculate the salary from Sunday March 29 thru Saturday April 4th. I was thinking of creating pay week table for each month and then go fro there. Any suggestions? Thank You,
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
I think I understand what you're trying to achieve. What I have created for you is a query which returns the date of the first day of the month, the date the first week started (based on a Sunday) and the date the week ended (based on Saturday). This uses a Tally to generate the dates. ; WITH lv0 AS ( SELECT 0 AS g UNION ALL SELECT 0 ), lv1 AS ( SELECT 0 AS g FROM lv0 AS a CROSS JOIN lv0 AS b ), lv2 AS ( SELECT 0 AS g FROM lv1 AS a CROSS JOIN lv1 AS b ), lv3 AS ( SELECT 0 AS g FROM lv2 AS a CROSS JOIN lv2 AS b ), lv4 AS ( SELECT 0 AS g FROM lv3 AS a CROSS JOIN lv3 AS b ), lv5 AS ( SELECT 0 AS g FROM lv4 AS a CROSS JOIN lv4 AS b ), Nums AS ( SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) AS n FROM lv5 ) SELECT TOP 12 DATEADD(DAY, n - 1, '20150101') AS 'MonthStart' , DATEADD(DAY, DATEPART(WEEKDAY, DATEADD(DAY, n - 1, '20150101')) * -1, DATEADD(DAY, n, '20150101')) AS 'MonthWeekStart' , DATEADD(DAY, 6, DATEADD(DAY, DATEPART(WEEKDAY, DATEADD(DAY, n - 1, '20150101')) * -1, DATEADD(DAY, n, '20150101'))) AS 'MonthWeekEnd' FROM Nums WHERE DATEPART(DAY, DATEADD(DAY, n - 1, '20150101')) = 1 ORDER BY n
thank you. Thhis worked great. Now, how do I add the begindate and enddate for each week? for example, May 16 thru May 31 includes 3 weeks (your query confirms that). Now, I need to know the begindate and enddate for each of these 3 weeks. First week would be Sunday 17 (skipping sat the 16th) and end sat 23. The second week starts Sun 24th and end Sat 30 and the third week starts Sun 31 and end Sat June 6th. Thank You, Helal
You can use DATEPART function for this task. Take a look this example: DECLARE @Date datetime SET @Date = '2015-04-01' SELECT @Date SELECT DATEPART(weekday,@date) AS WeekDay ,DATEPART(week,@date) AS Week SELECT SUM(Salary) FROM YourTable WHERE DATEPART(week,SalaryDate) = 14 Sunday for default is always first day of week (when use datepart weekday, =1) and saturday is last day of week (when use datepart weekday, =7) Datepart function: [
https://msdn.microsoft.com/en-us/library/ms174420.aspx][1] [1]:
https://msdn.microsoft.com/en-us/library/ms174420.aspx
Be careful with this function though. If you read the article you'll see that the weekday number may vary depending on the connection's settings. You can make sure you get what you expect by using the SET DATEFIRST statement.
The worked great. Now, how do I add the begindate and enddate for each week? for example, May 16 thru May 31 includes 3 weeks (your query confirms that). Now, I need to know the begindate and enddate for each of these 3 weeks. First week would be Sunday 17 (skipping sat the 16th) and end sat 23. The second week starts Sun 24th and end Sat 30 and the third week starts Sun 31 and end Sat June 6th. Thank You, Helal
18 People are following this question.
