IF exists(SELECT * FROM sysobjects WHERE name = 'LD0SPGetUnfinishedDecisionsCount')
	DROP PROCEDURE &OWNER.LD0SPGetUnfinishedDecisionsCount
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE &OWNER.[LD0SPGetUnfinishedDecisionsCount](@CaseWorkersXml VARCHAR(MAX), @RestrictedFromDate varchar(8))
AS
BEGIN
  set nocount on;

  DECLARE @XMLlist XML; SELECT @XMLList = @CaseWorkersXml;
  DECLARE @CaseWorkersTable AS TABLE ([UserName][varchar](100) NOT NULL);
  INSERT INTO @CaseWorkersTable SELECT x.y.value('.','nvarchar(100)') AS UserNames FROM @XMLList.nodes('/list/user') AS x(y);
  DECLARE @STOPDAT VARCHAR(8); SET @STOPDAT = CONVERT(VARCHAR,GETDATE(),112);
  DECLARE @CountA1 INT, @CountA2 INT, @CountB1 INT, @CountB2 INT, @CountC1 INT, @CountC2 INT;

  SELECT @CountA1 = COUNT(*)
  FROM LD0V0CUNFINISHEDDECISIONS_WITH_SERVICE_IN_EXE_ALERTS U
  JOIN @CaseWorkersTable t on U.CASEWORKERID = t.UserName
  WHERE (DECISIONSTATUS = 2 
    OR (DECISIONSTATUS = 3 AND REFDECISIONID = 0)
    OR (DECISIONSTATUS = 1 AND REFDECISIONID = 0)
    OR (DECISIONSTATUS = 4 AND SERVICENUMBER = 0 AND DECISIONLEADSTOSERVICE = 1))
    AND (STOPDAT = '' OR STOPDAT = ' ' OR STOPDAT IS NULL OR STOPDAT > @STOPDAT)
    AND (DECISIONDATE >= @RestrictedFromDate);

  SELECT @CountA2 = COUNT(*)
  FROM LD0V0CUNFINISHEDDECISIONS_WITH_SERVICE_IN_EXE_ALERTS U
  JOIN @CaseWorkersTable t on U.COCASEWORKERID = t.UserName
  WHERE (DECISIONSTATUS = 2
    OR (DECISIONSTATUS = 3 AND REFDECISIONID = 0)
    OR (DECISIONSTATUS = 1 AND REFDECISIONID = 0)
    OR (DECISIONSTATUS = 4 AND SERVICENUMBER = 0 AND DECISIONLEADSTOSERVICE = 1))
    AND (STOPDAT = '' OR STOPDAT = ' ' OR STOPDAT IS NULL OR STOPDAT > @STOPDAT)
    AND (DECISIONDATE >= @RestrictedFromDate)
    AND (CASEWORKERID not in (select UserName from @CaseWorkersTable));

  SELECT @CountB1 = COUNT(*)
  FROM LD0V0CUNFINISHEDDECISIONS_WITH_SERVICE_NOTIN_EXE_ALERTS U
  JOIN @CaseWorkersTable t on U.CASEWORKERID = t.UserName
  WHERE (DECISIONSTATUS = 2 
    OR (DECISIONSTATUS = 3 AND REFDECISIONID = 0)
    OR (DECISIONSTATUS = 1 AND REFDECISIONID = 0)
    OR (DECISIONSTATUS = 4 AND SERVICENUMBER = 0 AND DECISIONLEADSTOSERVICE = 1))
    AND (STOPDAT = '' OR STOPDAT = ' ' OR STOPDAT IS NULL OR STOPDAT > @STOPDAT)
    AND (DECISIONDATE >= @RestrictedFromDate);

  SELECT @CountB2 = COUNT(*)
  FROM LD0V0CUNFINISHEDDECISIONS_WITH_SERVICE_NOTIN_EXE_ALERTS U
  JOIN @CaseWorkersTable t on U.COCASEWORKERID = t.UserName
  WHERE (DECISIONSTATUS = 2
    OR (DECISIONSTATUS = 3 AND REFDECISIONID = 0)
    OR (DECISIONSTATUS = 1 AND REFDECISIONID = 0)
    OR (DECISIONSTATUS = 4 AND SERVICENUMBER = 0 AND DECISIONLEADSTOSERVICE = 1))
    AND (STOPDAT = '' OR STOPDAT = ' ' OR STOPDAT IS NULL OR STOPDAT > @STOPDAT)
    AND (DECISIONDATE >= @RestrictedFromDate)
    AND (CASEWORKERID not in (select UserName from @CaseWorkersTable));

  SELECT @CountC1 = COUNT(*)
  FROM LD0V0CUNFINISHEDDECISIONS_WITHOUT_SERVICE_ALERTS U
  JOIN @CaseWorkersTable t on U.CASEWORKERID = t.UserName
  WHERE (DECISIONSTATUS = 2 
    OR (DECISIONSTATUS = 3 AND REFDECISIONID = 0)
    OR (DECISIONSTATUS = 1 AND REFDECISIONID = 0)
    OR (DECISIONSTATUS = 4 AND SERVICENUMBER = 0 AND DECISIONLEADSTOSERVICE = 1))
    AND (STOPDAT = '' OR STOPDAT = ' ' OR STOPDAT IS NULL OR (STOPDAT > @STOPDAT))
    AND (DECISIONDATE >= @RestrictedFromDate);

  SELECT @CountC2 = COUNT(*)
  FROM LD0V0CUNFINISHEDDECISIONS_WITHOUT_SERVICE_ALERTS U
  JOIN @CaseWorkersTable t on U.COCASEWORKERID = t.UserName
  WHERE (DECISIONSTATUS = 2
    OR (DECISIONSTATUS = 3 AND REFDECISIONID = 0)
    OR (DECISIONSTATUS = 1 AND REFDECISIONID = 0)
    OR (DECISIONSTATUS = 4 AND SERVICENUMBER = 0 AND DECISIONLEADSTOSERVICE = 1))
    AND (STOPDAT = '' OR STOPDAT = ' ' OR STOPDAT IS NULL OR (STOPDAT > @STOPDAT))
    AND (DECISIONDATE >= @RestrictedFromDate)
    AND (CASEWORKERID not in (select UserName from @CaseWorkersTable));

  SELECT @CountA1 + @CountA2 + @CountB1 + @CountB2 + @CountC1 + @CountC2 AS CountValue;

END
GO

GRANT EXECUTE ON &OWNER.LD0SPGetUnfinishedDecisionsCount TO LD0ROANV
GO