timesheetsduepackagebody.sql

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

CREATE OR REPLACE PACKAGE BODY TimeSheetsDuePackage 
AS
   PROCEDURE usp_SelectTimeSheetsDue (inManagerID IN CHAR, inWeekEndingDate IN 
      DATE, results_cursor OUT CURSOR_TYPE)
   AS
   BEGIN
      OPEN results_cursor FOR
      SELECT Email, 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 = 0
      GROUP BY FirstName, LastName, Email;
   END;
END;