CREATE PROCEDURE dbo.FollowupCleaningList @AUserIdNumber INT, @ARoomNumber INT, @AFollowupState VARCHAR(100), @AStartDate DATETIME, @AEndDate DATETIME AS
/*
  Created by Kent Johansen May 2009
  Notes:
    @AUserIdNumber = -1 Means all users
    @ARoomNumber = -1 Means all rooms

  Change log:

*/
SELECT DISTINCT f.FollowupNumber, CONVERT(VARCHAR(8000), f.Note), f.Date, frx.FK_RoomNumber, ISNULL(f.Deviation, 0) AS Deviation, f.Priority, -1
FROM Followup f WITH (NOLOCK)
INNER JOIN PersonalFollowUpXTab pfx WITH (NOLOCK) ON pfx.FollowupNumber = f.FollowupNumber
  AND ((@AUserIdNumber = -1) OR (pfx.UserIDNumber = @AUserIdNumber))
INNER JOIN FollowupRoomsXtab frx WITH (NOLOCK) ON frx.FK_FollowupNumber = f.FollowupNumber
  AND ((@ARoomNumber = -1) OR (frx.FK_RoomNumber = @ARoomNumber))
INNER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = frx.FK_RoomNumber
WHERE f.Type = 19 -- Only cleaning type
AND f.FollowupNumber IN (SELECT DISTINCT FK_FollowupNumber FROM FollowupFollowupXTab WITH (NOLOCK))
AND ISNULL(f.State, 0) IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AFollowupState))
AND Date >= @AStartDate
AND Date <= @AEndDate

UNION ALL

SELECT DISTINCT f.FollowupNumber, CONVERT(VARCHAR(8000), f.Note), f.Date, frx.FK_RoomNumber, ISNULL(f.Deviation, 0) AS Deviation, f.Priority, frx.FK_FollowupNumber
FROM Followup f WITH (NOLOCK)
INNER JOIN PersonalFollowUpXTab pfx WITH (NOLOCK) ON pfx.FollowupNumber = f.FollowupNumber
  AND ((@AUserIdNumber = -1) OR (pfx.UserIDNumber = @AUserIdNumber))
INNER JOIN FollowupFollowupXtab ffx WITH (NOLOCK) ON ffx.FK_FollowupNumberSub = f.FollowupNumber
INNER JOIN FollowupRoomsXtab frx WITH (NOLOCK) ON frx.FK_FollowupNumber = f.FollowupNumber
  AND ((@ARoomNumber = -1) OR (frx.FK_RoomNumber = @ARoomNumber))
INNER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = frx.FK_RoomNumber
WHERE f.Type = 19 -- Only cleaning type
AND f.FollowupNumber IN (SELECT DISTINCT FK_FollowupNumberSub FROM FollowupFollowupXTab WITH (NOLOCK))
AND ISNULL(f.State, 0) IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AFollowupState))
AND Date >= @AStartDate
AND Date <= @AEndDate

ORDER BY f.FollowupNumber

GO
