Dim TSQLEXEC = "INSERT INTO INT_OP_AS_AGEING SELECT DISTINCT INT_ACCOUNT_ADDRESS.ACCNT_CODE," &
" (CASE WHEN(SELECT SUM(T_DEBIT_CREDIT)" &
" FROM INT_A_SALFLDG_ACCOUNT_STATEMENT_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 0 AND 30 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "') IS NULL THEN 0 ELSE " &
" (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_A_SALFLDG_ACCOUNT_STATEMENT_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 0 AND 30 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "')END)+ " &
" (CASE WHEN(SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_OPENING_BALANCE_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 0 AND 30 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "')IS NULL THEN 0 ELSE " &
" (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_OPENING_BALANCE_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 0 AND 30 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "')END) AS BAND30, " &
" (CASE WHEN(SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_A_SALFLDG_ACCOUNT_STATEMENT_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 31 AND 60 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "')IS NULL THEN 0 ELSE " &
" (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_A_SALFLDG_ACCOUNT_STATEMENT_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 31 AND 60 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "') END) + " &
" (CASE WHEN(SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_OPENING_BALANCE_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 31 AND 60 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "')IS NULL THEN 0 ELSE " &
" (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_OPENING_BALANCE_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 31 AND 60 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "') END) AS BAND60, " &
" (CASE WHEN(SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_A_SALFLDG_ACCOUNT_STATEMENT_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 61 AND 90 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "')IS NULL THEN 0 ELSE " &
" (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_A_SALFLDG_ACCOUNT_STATEMENT_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 61 AND 90 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "') END) + " &
" (CASE WHEN(SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_OPENING_BALANCE_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 61 AND 90 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "')IS NULL THEN 0 ELSE " &
" (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_OPENING_BALANCE_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 61 AND 90 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "') END) AS BAND90, " &
" (CASE WHEN(SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_A_SALFLDG_ACCOUNT_STATEMENT_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 91 AND 120 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "')IS NULL THEN 0 ELSE " &
" (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_A_SALFLDG_ACCOUNT_STATEMENT_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 91 AND 120 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "') END) + " &
" (CASE WHEN(SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_OPENING_BALANCE_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 91 AND 120 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "')IS NULL THEN 0 ELSE " &
" (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_OPENING_BALANCE_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 91 AND 120 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "') END) AS BAND120, " &
" (CASE WHEN (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_A_SALFLDG_ACCOUNT_STATEMENT_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 121 AND 180 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "') IS NULL THEN 0 ELSE " &
" (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_A_SALFLDG_ACCOUNT_STATEMENT_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 121 AND 180 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "') END) + " &
" (CASE WHEN (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_OPENING_BALANCE_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 121 AND 180 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "') IS NULL THEN 0 ELSE " &
" (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_OPENING_BALANCE_AGEING O " &
" WHERE O.DATEDIFFS BETWEEN 121 AND 180 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "') END) AS BAND180, " &
" (CASE WHEN (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_A_SALFLDG_ACCOUNT_STATEMENT_AGEING O " &
" WHERE O.DATEDIFFS >= 181 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "') IS NULL THEN 0 ELSE " &
" (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_A_SALFLDG_ACCOUNT_STATEMENT_AGEING O " &
" WHERE O.DATEDIFFS >= 181 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "') END) + " &
" (CASE WHEN (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_OPENING_BALANCE_AGEING O " &
" WHERE O.DATEDIFFS >= 181 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "') IS NULL THEN 0 ELSE " &
" (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_OPENING_BALANCE_AGEING O " &
" WHERE O.DATEDIFFS >= 181 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "') END) AS BANDMORE180, " &
" (CASE WHEN(SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_A_SALFLDG_ACCOUNT_STATEMENT_AGEING O " &
" WHERE O.DATEDIFFS < 0 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "')IS NULL THEN 0 ELSE " &
" (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_A_SALFLDG_ACCOUNT_STATEMENT_AGEING O " &
" WHERE O.DATEDIFFS < 0 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "') END) + " &
" (CASE WHEN(SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_OPENING_BALANCE_AGEING O " &
" WHERE O.DATEDIFFS < 0 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "')IS NULL THEN 0 ELSE " &
" (SELECT SUM(T_DEBIT_CREDIT) " &
" FROM INT_OPENING_BALANCE_AGEING O " &
" WHERE O.DATEDIFFS < 0 AND O.ACCNT_CODE = INT_ACCOUNT_ADDRESS.ACCNT_CODE AND O.STUSER = '" & USERNAME & "') END) AS BANDAFTERDUE, " &
" FROM INT_ACCOUNT_ADDRESS LEFT OUTER JOIN " &
" INT_OPENING_BALANCE_AGEING ON INT_ACCOUNT_ADDRESS.ACCNT_CODE = INT_OPENING_BALANCE_AGEING.ACCNT_CODE LEFT OUTER JOIN " &
" INT_A_SALFLDG_ACCOUNT_STATEMENT_AGEING ON " &
" INT_ACCOUNT_ADDRESS.ACCNT_CODE = INT_A_SALFLDG_ACCOUNT_STATEMENT_AGEING.ACCNT_CODE " &
" WHERE INT_ACCOUNT_ADDRESS.STUSER ='" & USERNAME & "' " &
" ORDER BY INT_ACCOUNT_ADDRESS.ACCNT_CODE "
Console.WriteLine("{0}", TSQLEXEC)
