CREATE PROCEDURE [dbo].[GetMCCRoomsWithToDosForUserx] @AUserIdNumber INT, @daysLookBack INT =0 AS
SET NOCOUNT ON
/*
  Created by Carsten Cremer October 2013 
  
  Notes:
    Based on FollowupRoomCleaningList by Kent Johansen 

  Change log:
  -- CC 20180530  Optimisation, remove all the fat.
*/


/*set @daysLookBack = 0*/
declare @WorkingDate DATETIME
set @WorkingDate = CAST((SELECT TOP 1 SystemDate FROM System WITH (NOLOCK)) AS DATETIME)

-- The FIRST SELECT only delivers rooms which actually have a folowup task to be handled
SELECT 
DISTINCT -- CC 20180530  
  -- CC 20180530 f.FollowupNumber   --0
  -- CC 20180530 ,CONVERT(VARCHAR(8000), f.Note)   --1
  -- CC 20180530 ,MAX(f.Date)     --2 
  frx.FK_RoomNumber  --3
  -- CC 20180530 ,ISNULL(f.Deviation, 0) AS Deviation--0 as Deviation   --4
  -- CC 20180530 ,f.Priority as Priority   --5
  -- CC 20180530 ,-1 as FK_FollowupNumber  --6
  ,ROOMS.Number as Number  --7
  ,ROOMS.Name as Name  --8
  ,Rooms.CleanStatus as CleanStatus  --9
-- CC 20180530 ,f.[State] -- 0 as [State]  --10
  -- CC 20180530 ,SECTORS.Name as Name  --11
  -- CC 20180530 ,0 as FK_FollowupNumber  --12
--  ,convert(varchar(200),Rooms.NOTES) as NOTES  --13 -- CC 20180530 Optimization
-- CC 20180530   ,Rooms.NOTES as NOTES							 --13 -- CC 20180530 Optimization

  ,ROOMS.Description as RoomsDescription  -- 14  lje: added 2011.03.02
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
INNER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = frx.FK_RoomNumber
  --AND ((Rooms.CleanStatus <> 0)OR(ISNULL(f.State, 0) IN (0,-1))) -- 0=Clean
  AND (Rooms.CleanStatus <> 0) -- 0=Clean
  
-- CC 20180530 LEFT OUTER JOIN Sectors WITH (NOLOCK) ON Rooms.Sector = SectorNumber
WHERE f.Type = 19 -- Only cleaning type
AND f.FollowupNumber IN (SELECT DISTINCT FK_FollowupNumber FROM FollowupFollowupXTab WITH (NOLOCK))
AND Date >= @WorkingDate - @daysLookBack--3--@AStartDate
AND Date <= @WorkingDate
/*
--lje: Only extract row with max FollowupNumber.
AND f.FollowupNumber in (select Top 1
  frx2.FK_FollowupNumber
FROM Followup f2 WITH (NOLOCK)
  INNER JOIN PersonalFollowUpXTab pfx2 WITH (NOLOCK) ON pfx2.FollowupNumber = f2.FollowupNumber
  AND ((@AUserIdNumber = -1) OR (pfx2.UserIDNumber = @AUserIdNumber))
  INNER JOIN FollowupRoomsXtab frx2 WITH (NOLOCK) ON frx2.FK_FollowupNumber = f2.FollowupNumber
WHERE f2.Type = 19 -- Only cleaning type
  AND f2.FollowupNumber IN (SELECT DISTINCT frx3.FK_FollowupNumber FROM FollowupFollowupXTab frx3 WITH (NOLOCK))
  AND Date >= @WorkingDate - @daysLookBack
  AND Date <= @WorkingDate
  AND frx2.FK_RoomNumber = frx.FK_RoomNumber
order by 1 desc)
*/
GROUP BY 
-- CC 20180530 f.FollowupNumber,
frx.FK_RoomNumber,
-- CC 20180530 f.Deviation,
ROOMS.Number
-- CC 20180530 ,CONVERT(VARCHAR(8000), f.Note)
,Rooms.Name,Rooms.CleanStatus
-- CC 20180530 ,f.State
-- CC 20180530 ,SECTORS.Name
--,convert(varchar(200),Rooms.NOTES) -- CC 20180530 Optimization
-- CC 20180530 ,Rooms.NOTES						 -- CC 20180530 Optimization
-- CC 20180530 ,f.Priority
,ROOMS.Description  -- lje: added 2011.03.02



UNION 

SELECT DISTINCT 
-- CC 20180530 f.FollowupNumber
-- CC 20180530 , CONVERT(VARCHAR(8000), f.Note)
-- CC 20180530 , f.Date
 frx.FK_RoomNumber
-- CC 20180530 , ISNULL(f.Deviation, 0) AS Deviation
-- CC 20180530 , f.Priority
-- CC 20180530 , frx.FK_FollowupNumber
, Rooms.Number
, Rooms.Name
, Rooms.CleanStatus
-- CC 20180530, f.State
-- CC 20180530 , Sectors.Name
-- CC 20180530 , ffx.FK_FollowupNumber

---, convert(varchar(100),Rooms.Notes)  --'Notes not found.' as NOTES, lje: fix 2011.11.22 -- CC 20180530 Optimization
-- CC 20180530 , Rooms.Notes  --'Notes not found.' as NOTES, lje: fix 2011.11.22 -- CC 20180530 Optimization
, Rooms.Description as RoomsDescription  -- lje: added 2011.03.02

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
INNER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = frx.FK_RoomNumber
-- CC 20180530 LEFT OUTER JOIN Sectors WITH (NOLOCK) ON Rooms.Sector = SectorNumber
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 (0,-1)
AND Date >= @WorkingDate - @daysLookBack
AND Date <= @WorkingDate


ORDER BY Rooms.Number
-- CC 20180530 , f.FollowupNumber
-- CC OLD SQL SERVER FAILS OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))  -- CC 20180529 Optimization
