Need Better SQL Query And Datstage Logic(Using Only Files)

Question

I have 2 files, I need to write a SQL query to check the data in Value1 column is equal or not.

The condition is: from file 1, the value from the last week of a month, plus the value of all the weeks of the next month should be equal to the value from file 2 of a month.

In the screenshot about, the value of last week of month 4 (1523) and all the weeks of month 5 (9+0+2601+22) is 4155 from file 1.

This should be equal to the value of month 4 (4155) from file 2.

The file contains multiple records for different key(s) and many months and weeks.

Currently to perform this from file 1, I use this SQL:

SELECT 
    (A.VAL + B.VAL) AS OUTPUT
FROM
    (SELECT VALUE1 AS VAL 
     FROM File1 
     WHERE KEY1 = 'ABC' 
       AND MONTHNO = 4 
       AND WEEKNO = (SELECT MAX(WEEKNO) 
                     FROM File1 
                     WHERE KEY1 = 'ABC' AND MONTHNO = 4)) AS A,
   (SELECT SUM(VALUE1) AS VAL 
    FROM File1 
    WHERE KEY1 = 'ABC' 
      AND MONTHNO = 5   
    GROUP BY MONTHNO) AS B;

If using SQL - I need a better SQL query in order to perform this for all available records of file 1.

And Datastage: I'm planning to implement it without using any database stages in datastage. Need logic.

Answer 1

SELECT
  File2.MonthNo,
  File2.Key1,
  File2.Value1    AS File2Value1,
  File1.Value1    AS File1Value1,
  CASE WHEN File2.Value1 = File1.Value1 THEN 0 ELSE 1 END    AS difference
FROM
  File2
LEFT JOIN
(
  SELECT
    MonthNo,
    Key1,
    SUM(Value1)   AS Value1
  FROM
    File1
  GROUP BY
    MonthNo,
    Key1
)
  File1
    ON  File2.MonthNo = File1.MonthNo
    AND File2.Key1    = File1.Key1