timesheetssubmittedpackagebody.sql

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

CREATE OR REPLACE PACKAGE BODY TimeSheetsSubmittedPackage 
AS
   PROCEDURE usp_SelectTimeSheetsSubmitted (inManagerID IN CHAR, 
      inWeekEndingDate IN DATE, results_cursor OUT CURSOR_TYPE)
   AS
   BEGIN
      OPEN results_cursor FOR
      SELECT FirstName || ' ' || LastName AS UserName, SUM(Hours) AS TotalHours
      FROM TimeSheets
      LEFT OUTER JOIN TimeSheetItems ON TimeSheets.TimeSheetID = 
         TimeSheetItems.TimeSheetID
      LEFT OUTER JOIN Users ON TimeSheets.UserID = Users.UserID
      WHERE Users.ManagerID = inManagerID
      AND WeekEndingDate = inWeekEndingDate
      AND Submitted = 1
      GROUP BY FirstName, LastName;
   END;
END;