usp_selecttimesheetsmtd.sql

来自「Beginning VB.NET DatabasesAll_Code.rar」· SQL 代码 · 共 26 行

CREATE PROCEDURE usp_SelectTimeSheetsMTD
(
   @ManagerID   UNIQUEIDENTIFIER
)
AS

-- Declare local variables
DECLARE @FirstDayOfMonth DATETIME,
        @WeekEndingDate DATETIME

-- Calculate the first day of the month date and current week ending date
SET @FirstDayOfMonth = DATEADD(MM, DATEDIFF(MM,0,GETDATE()), 0)
SET @WeekEndingDate = DATEADD(WK, DATEDIFF(WK,0,GETDATE()), 4)

-- Select month-to-date submitted timesheets
SELECT FirstName + ' ' + LastName AS UserName, SUM(Hours) AS TotalHours,
   WeekEndingDate, ApprovalDate
FROM TimeSheets
LEFT OUTER JOIN TimeSheetItems ON TimeSheets.TimeSheetID = 
   TimeSheetItems.TimeSheetID
LEFT OUTER JOIN Users ON TimeSheets.UserID = Users.UserID
WHERE Users.ManagerID = @ManagerID
AND WeekEndingDate BETWEEN @FirstDayOfMonth AND @WeekEndingDate
AND Submitted = 1
GROUP BY WeekEndingDate, FirstName, LastName, ApprovalDate