I have query in Teradata SQL like below:

CREATE TABLE ABC.SUMMARY AS
(
SELECT
DISTINCT A.NR,
A.DATE,
COUNT(DISTINCT B.ID) AS TRX_CNT,
MAX(CASE WHEN B.ID IS NOT NULL THEN 1 ELSE 0 END) AS TRX_AB
FROM BASE AS A
LEFT JOIN TRX_BASE AS B
    ON A.NR = B.NR
    WHERE 1=1
    AND B.TRX_DATE BETWEEN ADD_MONTHS(TRUNC(CURRENT_DATE, 'MM'), -6) AND TRUNC(CURRENT_DATE, 
    'MM')-1
GROUP BY 1, 2
)
WITH DATA PRIMARY INDEX(NR);

I need to shrink this code and my question is: I need to remove lines that are redundant and do not change the result if they are removed, to make this code easier. I think that rather all these line are needed but I am not sure.

What can I drop from above code so as to do not change the final result of this code ?

CodePudding user response：

CREATE TABLE ABC.SUMMARY AS
(
SELECT
  A.NR,
  A.DATE,
  COUNT(DISTINCT B.ID) TRX_CNT,
  MAX(CASE WHEN B.ID IS NOT NULL THEN 1 ELSE 0 END) TRX_AB
FROM BASE A
INNER JOIN TRX_BASE B
    ON A.NR = B.NR
WHERE B.TRX_DATE BETWEEN ADD_MONTHS(TRUNC(CURRENT_DATE, 'MM'), -6) AND TRUNC(CURRENT_DATE, 
    'MM')-1
GROUP BY A.NR, A.Date
)
WITH DATA PRIMARY INDEX(NR);

Remaining Concerns:

1. date may/may not be a reserved word. as such I try and avoid columns like that and alias them out to define what the date is for like Base_Date or something.
2. still unsure if it should be an inner join or left. but as you don't want the nature of the query to change, Inner makes sense as the current query behaves like an inner join. that is becuase if a record existed in base but not in trx_base, it would be eliminated by the B.TRX_Date evaluation as no nulls would be included.

What we removed and why:

1. Distinct on NR... the group by handles it
2. 1=1 this adds no value and makes no changes to data. it's always true..
3. Group by spelled out. if someone makes a change to code adding a column in the 1st two columns, this will break.
4. Removed AS.. it's optional in SQL Server...
5. CHanged left join to inner as your limit on B.TRX_Date will eliminate all the null values the left join generates: so this is an inner join and changing it doesn't impact results.