USE ArizonaCUST;
SET TRAN ISOLATION LEVEL READ UNCOMMITTED

/*============================================================================= 

Check "D00441 accounting turnover extraction" in SUN central

Parameters
----------------------



Creation : ??.??.????? / LPO (Luc Pouly)
Modifications:

=============================================================================*/ 
DECLARE @Year INT,
        @Month INT,
        @Day INT,
        @D_0 DATETIME,
        @D_1 DATETIME,
        @D_2 DATETIME;

SELECT @Year = DATEPART(YEAR, CURRENT_TIMESTAMP),--2022,
       @Month = DATEPART(MONTH, CURRENT_TIMESTAMP), --12,
       @Day = 2;

SELECT @D_0
    = CONVERT(
                 DATETIME,
                 CONVERT(VARCHAR(4), @Year) + '-' + CONVERT(VARCHAR(4), @Month) + '-' + CONVERT(VARCHAR(2), @Day)
             );
SELECT @D_1 = GETDATE() --DATEADD(HOUR, 2, @D_0);
SELECT @D_2 = EOMONTH(DATEADD(MONTH, -2, GETDATE()));

SELECT @D_0,
       @D_1,
       @D_2;

SELECT 'Night execution has been not done or reloaded' AS [Description],
       t.ABDCT_SUB_code AS SubCode,
       t.ABDCT_OU_code AS OuCode
FROM
(
    SELECT DISTINCT
           t.ABDCT_SUB_code,
           t.ABDCT_OU_code
    FROM dbo.ABD_Cent t
    WHERE t.ABDCT_Accounting_value_date = @D_2
    EXCEPT
    SELECT DISTINCT
           t.ACAD_sub_code,
           t.ACAD_ou_code
    FROM dbo.AT_Cent_Aggregated_Data t
    WHERE t.ACAD_load_date
    BETWEEN @D_0 AND @D_1
) t
ORDER BY t.ABDCT_SUB_code,
         t.ABDCT_OU_code;
		 
SELECT 'No reload since last missing night execution' AS [Description],
       t.ABDCT_SUB_code AS SubCode,
       t.ABDCT_OU_code AS OuCode
FROM
(
    SELECT DISTINCT
           t.ABDCT_SUB_code,
           t.ABDCT_OU_code
    FROM dbo.ABD_Cent t
    WHERE t.ABDCT_Accounting_value_date = @D_2
    EXCEPT
    SELECT DISTINCT
           t.ACAD_sub_code,
           t.ACAD_ou_code
    FROM dbo.AT_Cent_Aggregated_Data t
    WHERE t.ACAD_load_date >= @D_0
) t
ORDER BY t.ABDCT_SUB_code,
         t.ABDCT_OU_code;