REPLACE
MACRO ARMSDAT_I_DM.AISLOGSELDATE
(iBusNo Integer,
dtEndDate Date,
dtBegDate Date)
AS
(
/*************************************************************************/
/* Applications: AISLOG
*/
/* Purpose: Retrieve all
Models and Series which are operated by */
/*
the business whose iBusNo is specified, in the
month */
/*
defined by the specified date range. For those
*/
/*
Model/Series combinations where Received Dates have */
/*
been specified for the time period, also get those */
/*
Received Dates.
*/
/* Special: This query is
composed of the union of two
*/
/*
complementary SELECTs. The first SELECT gets the */
/*
Received Dates from the AIS_DATA_LOG table for
*/
/*
Model/Series combinations operated by iBusNo
during the */
/*
period. The second SELECT
accesses the AP_SER_HIST and */
/*
AP_OPR_HIST tables to get the rest of the Model/Series */
/*
combinations operated by iBusNo during the
month, which */
/*
were not found by the first SELECT.
*/
/* Inputs: iBusNo = Operator"s Business Number
*/
/*
dtEndDate = Date of month"s
last day
*/
/*
dtBegDate = Date of month"s
first day
*/
/* Outputs: AC_MDL_ID = Model operated by iBusNo during the month */
/*
AC_SER_ID = Series
operated by iBusNo during the month*/
/*
FORM_RCV_DT = Date on which data was received for the */
/*
operator, for a Model and Series.
*/
/* Created:
17-July-2005
*/
/* Modification:
*/
/*
Jeff Martin, TCR 2553: 17-July-2005
*/
/*
1) SPEC2000:
*/
/*
Added Teradata REGION prefixes
*/
/*
Make columns match table attributes
*/
/*
Get MAX FORM_RCV_DT from a group of SRC_DATA_BEG_DT */
/*
*/
/*************************************************************************/
--
First SELECT: Get the Received Dates which have already been stored.
SELECT
' ',
TRIM(L1.AC_MDL_ID),
TRIM(L1.AC_SER_ID),
MAX(L1.FORM_RCV_DT)
(FORMAT 'MM-DD-YYYY') (CHAR(10))
FROM ARMSDAT_I_DV.AIS_DATA_LOG L1
WHERE L1.BUS_NO = :iBusNo
AND L1.SRC_DATA_BEG_DT BETWEEN
(CAST(((:dtEndDate/100)*100)+1 AS
DATE)) AND
(ADD_MONTHS(CAST(((:dtEndDate/100)*100)+1
AS DATE),1) - 1)
AND (L1.AC_MDL_ID, L1.AC_SER_ID) IN
(SELECT
S1.AC_MDL_ID,
S1.AC_SER_ID
FROM ARMSDAT_I_DV.AC_SER_HIST S1,
ARMSDAT_I_DV.AC_OPR_HIST
O1
WHERE
O1.BUS_NO
= :iBusNo
AND S1.AC_ID_NO = O1.AC_ID_NO
AND S1.AFR_MFR_CD = O1.AFR_MFR_CD
AND S1.AC_MDL_ID = O1.AC_MDL_ID
AND O1.OPR_CHG_DT < :dtEndDate
AND O1.OPR_END_DT >= :dtBegDate
AND S1.SER_CHG_DT < :dtEndDate
AND S1.SER_END_DT >= :dtBegDate
AND S1.SER_CHG_DT < O1.OPR_END_DT
AND S1.SER_END_DT > O1.OPR_CHG_DT)
GROUP
BY L1.AC_MDL_ID, L1.AC_SER_ID
Union
--Second
SELECT: Get the rest of the Models and Series
--
operated by iBusNo in the specified month.
SELECT
' ',
TRIM(S2.AC_MDL_ID),
TRIM(S2.AC_SER_ID),
''
FROM ARMSDAT_I_DV.AC_SER_HIST S2,
ARMSDAT_I_DV.AC_OPR_HIST O2
WHERE O2.BUS_NO
= :iBusNo
AND S2.AC_ID_NO = O2.AC_ID_NO
AND S2.AFR_MFR_CD = O2.AFR_MFR_CD
AND S2.AC_MDL_ID = O2.AC_MDL_ID
AND O2.OPR_CHG_DT < :dtEndDate
AND O2.OPR_END_DT >=
:dtBegDate
AND S2.SER_CHG_DT < :dtEndDate
AND S2.SER_END_DT >=
:dtBegDate
AND S2.SER_CHG_DT < O2.OPR_END_DT
AND S2.SER_END_DT > O2.OPR_CHG_DT
AND (S2.AC_MDL_ID, S2.AC_SER_ID) NOT
IN
(SELECT
L3.AC_MDL_ID, L3.AC_SER_ID FROM
(SELECT
L2.AC_MDL_ID,
L2.AC_SER_ID,
MAX(L2.FORM_RCV_DT)
FORM_RCV_DT
FROM ARMSDAT_I_DV.AIS_DATA_LOG L2
WHERE L2.BUS_NO
= :iBusNo
AND L2.SRC_DATA_BEG_DT BETWEEN
(CAST(((:dtEndDate/100)*100)+1 AS
DATE)) AND
(ADD_MONTHS(CAST(((:dtEndDate/100)*100)+1
AS DATE),1) - 1)
GROUP
BY 1,2 ) L3)
ORDER
BY 2,3;
);