usp_selecttimesheetsytd.sql

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

CREATE PROCEDURE usp_SelectTimeSheetsYTD
(
   @ManagerID   UNIQUEIDENTIFIER
)
AS

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

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

-- Select year-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 @FirstDayOfYear AND @WeekEndingDate
AND Submitted = 1
GROUP BY WeekEndingDate, FirstName, LastName, ApprovalDate