usp_selecttimesheet.sql

Beginning VB.NET DatabasesAll_Code.rar

CREATE PROCEDURE usp_SelectTimeSheet
(
   @UserID           UNIQUEIDENTIFIER,
   @WeekEndingDate   DATETIME
)
AS
-- Select the TimeSheetID, Submitted, ProjectID and ProjectName
SELECT TimeSheets.TimeSheetID, Submitted, Projects.ProjectID, ProjectName, 
   -- Select the TimeSheetItemID and Hours for Monday
   Monday.TimeSheetItemID AS MondayTimeSheetItemID, 
   Monday.Hours AS MondayHours,
   -- Select the TimeSheetItemID and Hours for Tuesday
   Tuesday.TimeSheetItemID AS TuesdayTimeSheetItemID, 
   Tuesday.Hours AS TuesdayHours, 
   -- Select the TimeSheetItemID and Hours for Wednesday
   Wednesday.TimeSheetItemID AS WednesdayTimeSheetItemID, 
   Wednesday.Hours AS WednesdayHours, 
   -- Select the TimeSheetItemID and Hours for Thursday
   Thursday.TimeSheetItemID AS ThursdayTimeSheetItemID, 
   Thursday.Hours AS ThursdayHours,
   -- Select the TimeSheetItemID and Hours for Friday
   Friday.TimeSheetItemID AS FridayTimeSheetItemID, 
   Friday.Hours AS FridayHours
-- TimeSheets is the main table
FROM TimeSheets
-- Join TimeSheetItems table for Monday's data
LEFT OUTER JOIN TimeSheetItems Monday ON 
   TimeSheets.TimeSheetID = Monday.TimeSheetID 
   AND Monday.TimeSheetDate = DATEADD(day,-4,@WeekEndingDate)
-- Join Projects table for Project names
LEFT OUTER JOIN Projects ON 
   Monday.ProjectID = Projects.ProjectID
-- Join TimeSheetItems table for Tuesday's data
LEFT OUTER JOIN TimeSheetItems Tuesday ON 
   TimeSheets.TimeSheetID = Tuesday.TimeSheetID 
   AND Tuesday.ProjectID = Monday.ProjectID 
   AND Tuesday.TimeSheetDate = DATEADD(day,-3,@WeekEndingDate)
   - Join TimeSheetItems table for Wednesday's data
LEFT OUTER JOIN TimeSheetItems Wednesday ON 
   TimeSheets.TimeSheetID = Wednesday.TimeSheetID 
   AND Wednesday.ProjectID = Monday.ProjectID 
   AND Wednesday.TimeSheetDate = DATEADD(day,-2,@WeekEndingDate)
-- Join TimeSheetItems table for Thursday's data
LEFT OUTER JOIN TimeSheetItems Thursday 
   ON TimeSheets.TimeSheetID = Thursday.TimeSheetID 
   AND Thursday.ProjectID = Monday.ProjectID 
   AND Thursday.TimeSheetDate = DATEADD(day,-1,@WeekEndingDate)
-- Join TimeSheetItems table for Friday's data
LEFT OUTER JOIN TimeSheetItems Friday ON 
   TimeSheets.TimeSheetID = Friday.TimeSheetID 
   AND Friday.ProjectID = Monday.ProjectID 
   AND Friday.TimeSheetDate = @WeekEndingDate
-- Search criteria is the WeekEndingDate and UserID
WHERE WeekEndingDate = @WeekEndingDate
   AND UserID = @UserID
-- Order the results by the SequenceNumber of the projects
ORDER BY SequenceNumber