Hi there, I have an (SQL)load script that takes the snapshot of defects each week by GRP_ID and produces a summary table, as shown below. I also want to track which new defects have been added.
As you see below, on Monday, 01/01/2020, there were 70 defects, and the following week 06/01/2020, they became 71. This means one more defect got added, and I want to know which defect is that ( by DEFECT_NO). Similarly, for Actioned Defects; on Monday, 01/01/2020, they were 55, and the following week, they became 56, so I want to know which DEFECT_NO was actioned/completed.
Appreciate your input on this.
| GRP_ID |
Weekly snap shot |
Open Defects |
Actioned Defects |
| CABLE |
1/01/2020 |
70 |
55 |
| |
6/01/2020 |
71 |
56 |
| |
13/01/2020 |
73 |
56 |
| |
20/01/2020 |
75 |
60 |
| |
27/01/2020 |
75 |
62 |
| JOINTS |
1/01/2020 |
70 |
55 |
| |
6/01/2020 |
71 |
56 |
| |
13/01/2020 |
73 |
56 |
| |
20/01/2020 |
75 |
60 |
| |
27/01/2020 |
75 |
62 |
The script to produce above table is this.
NoConcatenate
DATELIST:
LOAD
Distinct
Date(MONDAY_WK_STRT_DT, 'DD-MM-YYYY') as ReportingWEEK
FROM DATE.qvd]
(qvd)
WHERE
FISC_PRD_NAM = '2020' AND MTH_NO = 1;
for _i = 0 to NoOfRows('DATELIST')-1
LET vWeek = peek('ReportingWEEK', _i, 'DATELIST');
Fact_defect:
SQL
WITH CTE1 AS(
SELECT TO_DATE('$(vWeek)', 'DD-MM-YYYY') AS WEEKLY_SNAPSHOT,
E.GRP_ID,
E.ID,
D.DEFECT_NO,
D.DEFECT_STATUS_CHECK,
DECODE(DEFECT_STATUS_CHECK, 'OPEN', 1, 0) AS OPEN,
DECODE (DEFECT_STATUS_CHECK, 'PROCESSED', 1, 0) AS CONVERTED_2_WO_OR_PROCESSED,
DECODE (DEFECT_STATUS_CHECK, 'NOA', 1, 0) AS NO_ACTION_REQUIRED,
DECODE (DEFECT_STATUS_CHECK, 'OTHERS', 1, 0) AS OTHERS,
DECODE (DEFECT_STATUS_CHECK, 'ARCHIVED', 1, 0) AS ARCHIVED
FROM ACCESORIES E
JOIN DEFECT D ON E.ID = D.ID
WHERE TO_DATE('$(vWeek)', 'DD-MM-YYYY') BETWEEN D.EFEC_STRT_DT AND D.EFEC_END_DT
ORDER BY WEEKLY_SNAPSHOT, DEFECT_NO)
SELECT * FROM CTE1;
SET vWeek =;
next _i;
