82 lines
2.0 KiB
Transact-SQL
82 lines
2.0 KiB
Transact-SQL
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;
|
|
|
|
|