[SQL] Mutiple Distinct Record Count from TWO tables having different value need to be matched...

DELIQUENCY_COUNT 0 1 2 3 4 5 6 7 8 9 TABLE 1
CYCLE_DELIQUENCY A B C D E F G H I J TABLE 2


Attribute Name = DELIQUENCY_COUNT, Value = 0 1 2 3 4 5 6 7 8 9, Table Name = Table1
Attribute Name = CYCLE_DELIQUENCY, Value = A B C D E F G H I J, Table Name = Table2


Here, I want compare the count of DELIQUENCY_COUNT=0 with count of CYCLE_DELIQUENCY = A and Count of DELIQUENCY_COUNT=1 with count of CYCLE_DELIQUENCY = B and so on.

If the count is having difference then we have report mismatch

EQUVALENT VALUES (A=0, B=1, C=2, D=3, E=4, F=5, G=6, H=7, I=8, J=9)

Here, i am expecting

if the Table1, DELIQUENCY_COUNT for value 0 is 1000, and Table2, CYCLE_DELIQUENCY for value A is 1001, Then we have to report it.

Similarly, for next value Table1, DELIQUENCY_COUNT for value 1 is 5000, and Table2, CYCLE_DELIQUENCY for value A is 5000, Then we don't have to report it.

Table1 and Table2 equivalent Value attribute wise EQUVALENT VALUES (A=0, B=1, C=2, D=3, E=4, F=5, G=6, H=7, I=8, J=9)

I have written a SQL but not sure if it will work

SELECT DISTINCT COUNT(CYCLE_DELIQUENCY)
CASE
WHEN 'A' THEN
COUNT(CYCLE_DELIQUENCY)
MINUS
SELECT DISTINCT COUNT(DELIQUENCY_COUNT) FROM {}.{} WHERE DELIQUENCY_COUNT = 0
END ' A REPORT ',
WHEN 'B' THEN
COUNT(CYCLE_DELIQUENCY)
MINUS
SELECT DISTINCT COUNT(DELIQUENCY_COUNT) FROM {}.{} WHERE DELIQUENCY_COUNT = 1
END ' B REPORT ',

FROM {}.{};

Continue reading...