usp_selecttimesheetsqtd.sql

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

CREATE PROCEDURE usp_SelectTimeSheetsQTD
(
   @ManagerID   UNIQUEIDENTIFIER
)
AS

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

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

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