What the best way to put these long SQL code into a string to use in VBA - I.E QUOTATION MARKS

S

Siyanna

Well-known Member
Joined
Nov 7, 2011
Messages
1,146
Hi All,

Whats the best/simplest way of putting these 2 long SQL codes in to a VBA string so it recognises it. It is taking me forever getting the right syntaxt for VBA

strSQL =

...These are the 2 SQLs...

Code: 
select
nvl(Status,'Total') as status,
my_type,
my_subtype,
sum(one_month) as one_month,
sum(three_month) as three_month,
sum(seven_month) as seven_month,
sum(twelve_month) as over_seven_month,
sum(over_twelve_month) As over_twelve_month
from (
SELECT
'Not Billed' as Status,
COUNT(*) AS VOLUME,
a.my_type,
A.my_subtype,
ROUND(SUM(
Case WHEN
To_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
 
THEN
ACCRUED_DAYS * ADCHARGE
Else
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end ),2) AS ONE_MONTH,

SUM(CASE
 
WHEN
ACCRUED_DAYS > 0 THEN 1 ELSE 0 END) AS C_ONE_MONTH,

ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS - TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end ),2) as THREE_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 1 ELSE 0 END) AS C_THREE_MONTH,

ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end),2)  as SEVEN_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 1 ELSE 0 END) AS C_SEVEN_MONTH,
 
ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end),2) as TWELVE_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))

THEN 1 ELSE 0 END) AS C_TWELVE_MONTH,

ROUND(SUM(
CASE

WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS

THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
Else
end),2) as OVER_TWELVE_MONTH,

SUM(CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS

THEN 1 ELSE 0 END) AS C_OVER_TWELVE_MONTH

from tmason1.accrual_out2 A, CIS.TVP057TARIFF B
WHERE a.CD_TARIFF = B.CD_TARIFF
AND  A.CD_service_prov NOT in ('SW','TW')
and   a.cd_tariff not in ('1STW-RVD01','1STW-RVD02','1STW-RVN01','1STW-RVS01','1STW-RVS02','1STW-RVU01','1STW-RVW01',
'1STW-RVW02','1STW-RVW03')
and substr(a.cd_tariff,2,3) = 'STW'
AND ACCRUED_DAYS > 0
and a.NO_ACCOUNT IN ( SELECT T0.NO_ACCOUNT FROM tmason1.ACCRUAL_APP_DATA T0 WHERE T0.NO_ACCOUNT = a.NO_ACCOUNT AND T0.NO_PROPERTY = a.NO_PROPERTY AND B_STATUS <> 'Billed')
GROUP BY a.my_type,A.MY_SUBTYPE
Union
SELECT
'Indicates Billed Action' as Status,
COUNT(*) AS VOLUME,
a.my_type,
A.my_subtype,
ROUND(SUM(
Case WHEN
To_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
 
THEN
ACCRUED_DAYS * ADCHARGE
Else
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end ),2) AS ONE_MONTH,

SUM(CASE
 
WHEN
ACCRUED_DAYS > 0 THEN 1 ELSE 0 END) AS C_ONE_MONTH,

ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS - TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end ),2) as THREE_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 1 ELSE 0 END) AS C_THREE_MONTH,

ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end),2)  as SEVEN_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 1 ELSE 0 END) AS C_SEVEN_MONTH,
 
ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end),2) as TWELVE_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))

THEN 1 ELSE 0 END) AS C_TWELVE_MONTH,

ROUND(SUM(
CASE

WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS

THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
Else
end),2) as OVER_TWELVE_MONTH,

SUM(CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS

THEN 1 ELSE 0 END) AS C_OVER_TWELVE_MONTH

from tmason1.accrual_out2 A, CIS.TVP057TARIFF B
WHERE a.CD_TARIFF = B.CD_TARIFF
AND  A.CD_service_prov NOT in ('SW','TW')
and   a.cd_tariff not in ('1STW-RVD01','1STW-RVD02','1STW-RVN01','1STW-RVS01','1STW-RVS02','1STW-RVU01','1STW-RVW01',
'1STW-RVW02','1STW-RVW03')
and substr(a.cd_tariff,2,3) = 'STW'
AND ACCRUED_DAYS > 0
and a.NO_ACCOUNT IN ( SELECT T0.NO_ACCOUNT FROM tmason1.ACCRUAL_APP_DATA T0 WHERE T0.NO_ACCOUNT = a.NO_ACCOUNT AND T0.NO_PROPERTY = a.NO_PROPERTY AND B_STATUS = 'Billed' AND I_TYPE <> 'No Action Required')

GROUP BY a.my_type,A.MY_SUBTYPE
Union
SELECT
'Indicates Billed No Action' as Status,
COUNT(*) AS VOLUME,
a.my_type,
A.my_subtype,
ROUND(SUM(
Case WHEN
To_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
 
THEN
ACCRUED_DAYS * ADCHARGE
Else
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end ),2) AS ONE_MONTH,

SUM(CASE
 
WHEN
ACCRUED_DAYS > 0 THEN 1 ELSE 0 END) AS C_ONE_MONTH,

ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS - TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end ),2) as THREE_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 1 ELSE 0 END) AS C_THREE_MONTH,

ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end),2)  as SEVEN_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 1 ELSE 0 END) AS C_SEVEN_MONTH,
 
ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end),2) as TWELVE_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))

THEN 1 ELSE 0 END) AS C_TWELVE_MONTH,

ROUND(SUM(
CASE

WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS

THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
Else
end),2) as OVER_TWELVE_MONTH,

SUM(CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS

THEN 1 ELSE 0 END) AS C_OVER_TWELVE_MONTH

from tmason1.accrual_out2 A, CIS.TVP057TARIFF B
WHERE a.CD_TARIFF = B.CD_TARIFF
AND  A.CD_service_prov NOT in ('SW','TW')
and   a.cd_tariff not in ('1STW-RVD01','1STW-RVD02','1STW-RVN01','1STW-RVS01','1STW-RVS02','1STW-RVU01','1STW-RVW01',
'1STW-RVW02','1STW-RVW03')
and substr(a.cd_tariff,2,3) = 'STW'
AND ACCRUED_DAYS > 0
and a.NO_ACCOUNT IN ( SELECT T0.NO_ACCOUNT FROM tmason1.ACCRUAL_APP_DATA T0 WHERE T0.NO_ACCOUNT = a.NO_ACCOUNT AND T0.NO_PROPERTY = a.NO_PROPERTY AND B_STATUS = 'Billed' AND I_TYPE = 'No Action Required')

GROUP BY a.my_type,A.MY_SUBTYPE
order by 1,3)
group by rollup(status),my_type,my_subtype

AND

Code: 
select 
T0.NO_PROPERTY
,T0.NO_ACCOUNT
,T0.CD_PROPERTY_USE
,T0.MY_TYPE
,T0.MY_SUBTYPE
,T0.ACCRUED_FROM
,T0.ACCRUAL_DATE
,T0.OVER_YEAR
,T0.ST_ACCOUNT
,T0.B_STATUS
,T0.I_TYPE
,T0.USER_ID
,T0.TS_UPDATE
,T0.DEL_TO
,T0.HOLD_DATE
,to_char(ts_update,'HH24') as hr
,to_char(ts_update,'Day') as My_Day
from TMASON1.accrual_app_data t0
where to_date(to_char(ts_update,'dd/mm/yyyy'),'dd/mm/yyyy') = to_date(to_char(sysdate,'dd/mm/yyyy'),'dd/mm/yyyy') - 3
and user_id != 'NINJA01'
 
Sort by date Sort by votes
I use: Instant SQL Formatter
Which outputs:
Rich (BB code): 
varname1 = ""
varname1 = varname1 & "select " & vbCrLf
varname1 = varname1 & "nvl(Status,'Total') as status, " & vbCrLf
varname1 = varname1 & "my_type, " & vbCrLf
varname1 = varname1 & "my_subtype, " & vbCrLf
varname1 = varname1 & "sum(one_month) as one_month, " & vbCrLf
varname1 = varname1 & "sum(three_month) as three_month, " & vbCrLf
varname1 = varname1 & "sum(seven_month) as seven_month, " & vbCrLf
varname1 = varname1 & "sum(twelve_month) as over_seven_month, " & vbCrLf
varname1 = varname1 & "sum(over_twelve_month) As over_twelve_month " & vbCrLf
varname1 = varname1 & "from ( " & vbCrLf
varname1 = varname1 & "SELECT " & vbCrLf
varname1 = varname1 & "'Not Billed' as Status, " & vbCrLf
varname1 = varname1 & "COUNT(*) AS VOLUME, " & vbCrLf
varname1 = varname1 & "a.my_type, " & vbCrLf
varname1 = varname1 & "A.my_subtype, " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "Case WHEN " & vbCrLf
varname1 = varname1 & "To_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) AS ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > 0 THEN 1 ELSE 0 END) AS C_ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) as THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2)  as SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2) as TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "end),2) as OVER_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_OVER_TWELVE_MONTH " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "from tmason1.accrual_out2 A, CIS.TVP057TARIFF B " & vbCrLf
varname1 = varname1 & "WHERE a.CD_TARIFF = B.CD_TARIFF " & vbCrLf
varname1 = varname1 & "AND  A.CD_service_prov NOT in ('SW','TW') " & vbCrLf
varname1 = varname1 & "and   a.cd_tariff not in ('1STW-RVD01','1STW-RVD02','1STW-RVN01','1STW-RVS01','1STW-RVS02','1STW-RVU01','1STW-RVW01', " & vbCrLf
varname1 = varname1 & "'1STW-RVW02','1STW-RVW03') " & vbCrLf
varname1 = varname1 & "and substr(a.cd_tariff,2,3) = 'STW' " & vbCrLf
varname1 = varname1 & "AND ACCRUED_DAYS > 0 " & vbCrLf
varname1 = varname1 & "and a.NO_ACCOUNT IN ( SELECT T0.NO_ACCOUNT FROM tmason1.ACCRUAL_APP_DATA T0 WHERE T0.NO_ACCOUNT = a.NO_ACCOUNT AND T0.NO_PROPERTY = a.NO_PROPERTY AND B_STATUS <> 'Billed') " & vbCrLf
varname1 = varname1 & "GROUP BY a.my_type,A.MY_SUBTYPE " & vbCrLf
varname1 = varname1 & "Union " & vbCrLf
varname1 = varname1 & "SELECT " & vbCrLf
varname1 = varname1 & "'Indicates Billed Action' as Status, " & vbCrLf
varname1 = varname1 & "COUNT(*) AS VOLUME, " & vbCrLf
varname1 = varname1 & "a.my_type, " & vbCrLf
varname1 = varname1 & "A.my_subtype, " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "Case WHEN " & vbCrLf
varname1 = varname1 & "To_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) AS ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > 0 THEN 1 ELSE 0 END) AS C_ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) as THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2)  as SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2) as TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "end),2) as OVER_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_OVER_TWELVE_MONTH " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "from tmason1.accrual_out2 A, CIS.TVP057TARIFF B " & vbCrLf
varname1 = varname1 & "WHERE a.CD_TARIFF = B.CD_TARIFF " & vbCrLf
varname1 = varname1 & "AND  A.CD_service_prov NOT in ('SW','TW') " & vbCrLf
varname1 = varname1 & "and   a.cd_tariff not in ('1STW-RVD01','1STW-RVD02','1STW-RVN01','1STW-RVS01','1STW-RVS02','1STW-RVU01','1STW-RVW01', " & vbCrLf
varname1 = varname1 & "'1STW-RVW02','1STW-RVW03') " & vbCrLf
varname1 = varname1 & "and substr(a.cd_tariff,2,3) = 'STW' " & vbCrLf
varname1 = varname1 & "AND ACCRUED_DAYS > 0 " & vbCrLf
varname1 = varname1 & "and a.NO_ACCOUNT IN ( SELECT T0.NO_ACCOUNT FROM tmason1.ACCRUAL_APP_DATA T0 WHERE T0.NO_ACCOUNT = a.NO_ACCOUNT AND T0.NO_PROPERTY = a.NO_PROPERTY AND B_STATUS = 'Billed' AND I_TYPE <> 'No Action Required') " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "GROUP BY a.my_type,A.MY_SUBTYPE " & vbCrLf
varname1 = varname1 & "Union " & vbCrLf
varname1 = varname1 & "SELECT " & vbCrLf
varname1 = varname1 & "'Indicates Billed No Action' as Status, " & vbCrLf
varname1 = varname1 & "COUNT(*) AS VOLUME, " & vbCrLf
varname1 = varname1 & "a.my_type, " & vbCrLf
varname1 = varname1 & "A.my_subtype, " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "Case WHEN " & vbCrLf
varname1 = varname1 & "To_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) AS ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > 0 THEN 1 ELSE 0 END) AS C_ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) as THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2)  as SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2) as TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "end),2) as OVER_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_OVER_TWELVE_MONTH " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "from tmason1.accrual_out2 A, CIS.TVP057TARIFF B " & vbCrLf
varname1 = varname1 & "WHERE a.CD_TARIFF = B.CD_TARIFF " & vbCrLf
varname1 = varname1 & "AND  A.CD_service_prov NOT in ('SW','TW') " & vbCrLf
varname1 = varname1 & "and   a.cd_tariff not in ('1STW-RVD01','1STW-RVD02','1STW-RVN01','1STW-RVS01','1STW-RVS02','1STW-RVU01','1STW-RVW01', " & vbCrLf
varname1 = varname1 & "'1STW-RVW02','1STW-RVW03') " & vbCrLf
varname1 = varname1 & "and substr(a.cd_tariff,2,3) = 'STW' " & vbCrLf
varname1 = varname1 & "AND ACCRUED_DAYS > 0 " & vbCrLf
varname1 = varname1 & "and a.NO_ACCOUNT IN ( SELECT T0.NO_ACCOUNT FROM tmason1.ACCRUAL_APP_DATA T0 WHERE T0.NO_ACCOUNT = a.NO_ACCOUNT AND T0.NO_PROPERTY = a.NO_PROPERTY AND B_STATUS = 'Billed' AND I_TYPE = 'No Action Required') " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "GROUP BY a.my_type,A.MY_SUBTYPE " & vbCrLf
varname1 = varname1 & "order by 1,3) " & vbCrLf
varname1 = varname1 & "group by rollup(status),my_type,my_subtype"
 
Upvote 0
Kyle123 said:
I use: Instant SQL Formatter
Which outputs:
Rich (BB code): 
varname1 = ""
varname1 = varname1 & "select " & vbCrLf
varname1 = varname1 & "nvl(Status,'Total') as status, " & vbCrLf
varname1 = varname1 & "my_type, " & vbCrLf
varname1 = varname1 & "my_subtype, " & vbCrLf
varname1 = varname1 & "sum(one_month) as one_month, " & vbCrLf
varname1 = varname1 & "sum(three_month) as three_month, " & vbCrLf
varname1 = varname1 & "sum(seven_month) as seven_month, " & vbCrLf
varname1 = varname1 & "sum(twelve_month) as over_seven_month, " & vbCrLf
varname1 = varname1 & "sum(over_twelve_month) As over_twelve_month " & vbCrLf
varname1 = varname1 & "from ( " & vbCrLf
varname1 = varname1 & "SELECT " & vbCrLf
varname1 = varname1 & "'Not Billed' as Status, " & vbCrLf
varname1 = varname1 & "COUNT(*) AS VOLUME, " & vbCrLf
varname1 = varname1 & "a.my_type, " & vbCrLf
varname1 = varname1 & "A.my_subtype, " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "Case WHEN " & vbCrLf
varname1 = varname1 & "To_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) AS ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > 0 THEN 1 ELSE 0 END) AS C_ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) as THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2)  as SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2) as TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "end),2) as OVER_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_OVER_TWELVE_MONTH " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "from tmason1.accrual_out2 A, CIS.TVP057TARIFF B " & vbCrLf
varname1 = varname1 & "WHERE a.CD_TARIFF = B.CD_TARIFF " & vbCrLf
varname1 = varname1 & "AND  A.CD_service_prov NOT in ('SW','TW') " & vbCrLf
varname1 = varname1 & "and   a.cd_tariff not in ('1STW-RVD01','1STW-RVD02','1STW-RVN01','1STW-RVS01','1STW-RVS02','1STW-RVU01','1STW-RVW01', " & vbCrLf
varname1 = varname1 & "'1STW-RVW02','1STW-RVW03') " & vbCrLf
varname1 = varname1 & "and substr(a.cd_tariff,2,3) = 'STW' " & vbCrLf
varname1 = varname1 & "AND ACCRUED_DAYS > 0 " & vbCrLf
varname1 = varname1 & "and a.NO_ACCOUNT IN ( SELECT T0.NO_ACCOUNT FROM tmason1.ACCRUAL_APP_DATA T0 WHERE T0.NO_ACCOUNT = a.NO_ACCOUNT AND T0.NO_PROPERTY = a.NO_PROPERTY AND B_STATUS <> 'Billed') " & vbCrLf
varname1 = varname1 & "GROUP BY a.my_type,A.MY_SUBTYPE " & vbCrLf
varname1 = varname1 & "Union " & vbCrLf
varname1 = varname1 & "SELECT " & vbCrLf
varname1 = varname1 & "'Indicates Billed Action' as Status, " & vbCrLf
varname1 = varname1 & "COUNT(*) AS VOLUME, " & vbCrLf
varname1 = varname1 & "a.my_type, " & vbCrLf
varname1 = varname1 & "A.my_subtype, " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "Case WHEN " & vbCrLf
varname1 = varname1 & "To_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) AS ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > 0 THEN 1 ELSE 0 END) AS C_ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) as THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2)  as SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2) as TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "end),2) as OVER_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_OVER_TWELVE_MONTH " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "from tmason1.accrual_out2 A, CIS.TVP057TARIFF B " & vbCrLf
varname1 = varname1 & "WHERE a.CD_TARIFF = B.CD_TARIFF " & vbCrLf
varname1 = varname1 & "AND  A.CD_service_prov NOT in ('SW','TW') " & vbCrLf
varname1 = varname1 & "and   a.cd_tariff not in ('1STW-RVD01','1STW-RVD02','1STW-RVN01','1STW-RVS01','1STW-RVS02','1STW-RVU01','1STW-RVW01', " & vbCrLf
varname1 = varname1 & "'1STW-RVW02','1STW-RVW03') " & vbCrLf
varname1 = varname1 & "and substr(a.cd_tariff,2,3) = 'STW' " & vbCrLf
varname1 = varname1 & "AND ACCRUED_DAYS > 0 " & vbCrLf
varname1 = varname1 & "and a.NO_ACCOUNT IN ( SELECT T0.NO_ACCOUNT FROM tmason1.ACCRUAL_APP_DATA T0 WHERE T0.NO_ACCOUNT = a.NO_ACCOUNT AND T0.NO_PROPERTY = a.NO_PROPERTY AND B_STATUS = 'Billed' AND I_TYPE <> 'No Action Required') " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "GROUP BY a.my_type,A.MY_SUBTYPE " & vbCrLf
varname1 = varname1 & "Union " & vbCrLf
varname1 = varname1 & "SELECT " & vbCrLf
varname1 = varname1 & "'Indicates Billed No Action' as Status, " & vbCrLf
varname1 = varname1 & "COUNT(*) AS VOLUME, " & vbCrLf
varname1 = varname1 & "a.my_type, " & vbCrLf
varname1 = varname1 & "A.my_subtype, " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "Case WHEN " & vbCrLf
varname1 = varname1 & "To_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) AS ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > 0 THEN 1 ELSE 0 END) AS C_ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) as THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2)  as SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2) as TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "end),2) as OVER_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_OVER_TWELVE_MONTH " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "from tmason1.accrual_out2 A, CIS.TVP057TARIFF B " & vbCrLf
varname1 = varname1 & "WHERE a.CD_TARIFF = B.CD_TARIFF " & vbCrLf
varname1 = varname1 & "AND  A.CD_service_prov NOT in ('SW','TW') " & vbCrLf
varname1 = varname1 & "and   a.cd_tariff not in ('1STW-RVD01','1STW-RVD02','1STW-RVN01','1STW-RVS01','1STW-RVS02','1STW-RVU01','1STW-RVW01', " & vbCrLf
varname1 = varname1 & "'1STW-RVW02','1STW-RVW03') " & vbCrLf
varname1 = varname1 & "and substr(a.cd_tariff,2,3) = 'STW' " & vbCrLf
varname1 = varname1 & "AND ACCRUED_DAYS > 0 " & vbCrLf
varname1 = varname1 & "and a.NO_ACCOUNT IN ( SELECT T0.NO_ACCOUNT FROM tmason1.ACCRUAL_APP_DATA T0 WHERE T0.NO_ACCOUNT = a.NO_ACCOUNT AND T0.NO_PROPERTY = a.NO_PROPERTY AND B_STATUS = 'Billed' AND I_TYPE = 'No Action Required') " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "GROUP BY a.my_type,A.MY_SUBTYPE " & vbCrLf
varname1 = varname1 & "order by 1,3) " & vbCrLf
varname1 = varname1 & "group by rollup(status),my_type,my_subtype"
Click to expand...

Thats Brill - ill give that a go..thats an awesome tool.......

Can you also advise on the below..

If i have a SQL statement like this
" and user_id != 'NINJA01'"

Do i need to wrap double quotes around the single quotation?

and how can i encorporate a variable in the string - this variable holds a number

No_Day = 2

" where to_date(to_char(ts_update,'dd/mm/yyyy'),'dd/mm/yyyy') = to_date(to_char(sysdate,'dd/mm/yyyy'),'dd/mm/yyyy') -" & No_Day & _

Thank You again
 
Upvote 0
If i have a SQL statement like this
" and user_id != 'NINJA01'"

Do i need to wrap double quotes around the single quotation?
Click to expand...
No. As long as they're properly nested and the syntax is correct, it should work. Your example looks fine if NINJA01 is a literal string. If it's a variable, then it's not correct. Whatever is inside a left/right pair of " has been delimited. When you concatenate the chunks, the ' are left over.
Assuming there is more text after and NINJA01 is a variable: " and user_id != '" & NINJA01 & "'"
This translates to and user_id != 'NINJA01' (although I don't get the use of !)

and how can i encorporate a variable in the string - this variable holds a number
No_Day = 2
" where to_date(to_char(ts_update,'dd/mm/yyyy'),'dd/mm/yyyy') = to_date(to_char(sysdate,'dd/mm/yyyy'),'dd/mm/yyyy') -" & No_Day & _
Click to expand...
Perhaps you already know that quotes are not to be used around numbers, so you'd simply replace the reference with the variable.
" where intMyNumber(..."
However, if the variable is a date or text, you're back to the first syntax, although ' would be replaced by # for dates.
Use temporary debug statements to see the result of your sql constructs.
 
Upvote 0
Kyle123 said:
I use: Instant SQL Formatter
Which outputs:
Rich (BB code): 
varname1 = ""
varname1 = varname1 & "select " & vbCrLf
varname1 = varname1 & "nvl(Status,'Total') as status, " & vbCrLf
varname1 = varname1 & "my_type, " & vbCrLf
varname1 = varname1 & "my_subtype, " & vbCrLf
varname1 = varname1 & "sum(one_month) as one_month, " & vbCrLf
varname1 = varname1 & "sum(three_month) as three_month, " & vbCrLf
varname1 = varname1 & "sum(seven_month) as seven_month, " & vbCrLf
varname1 = varname1 & "sum(twelve_month) as over_seven_month, " & vbCrLf
varname1 = varname1 & "sum(over_twelve_month) As over_twelve_month " & vbCrLf
varname1 = varname1 & "from ( " & vbCrLf
varname1 = varname1 & "SELECT " & vbCrLf
varname1 = varname1 & "'Not Billed' as Status, " & vbCrLf
varname1 = varname1 & "COUNT(*) AS VOLUME, " & vbCrLf
varname1 = varname1 & "a.my_type, " & vbCrLf
varname1 = varname1 & "A.my_subtype, " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "Case WHEN " & vbCrLf
varname1 = varname1 & "To_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) AS ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > 0 THEN 1 ELSE 0 END) AS C_ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) as THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2)  as SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2) as TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "end),2) as OVER_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_OVER_TWELVE_MONTH " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "from tmason1.accrual_out2 A, CIS.TVP057TARIFF B " & vbCrLf
varname1 = varname1 & "WHERE a.CD_TARIFF = B.CD_TARIFF " & vbCrLf
varname1 = varname1 & "AND  A.CD_service_prov NOT in ('SW','TW') " & vbCrLf
varname1 = varname1 & "and   a.cd_tariff not in ('1STW-RVD01','1STW-RVD02','1STW-RVN01','1STW-RVS01','1STW-RVS02','1STW-RVU01','1STW-RVW01', " & vbCrLf
varname1 = varname1 & "'1STW-RVW02','1STW-RVW03') " & vbCrLf
varname1 = varname1 & "and substr(a.cd_tariff,2,3) = 'STW' " & vbCrLf
varname1 = varname1 & "AND ACCRUED_DAYS > 0 " & vbCrLf
varname1 = varname1 & "and a.NO_ACCOUNT IN ( SELECT T0.NO_ACCOUNT FROM tmason1.ACCRUAL_APP_DATA T0 WHERE T0.NO_ACCOUNT = a.NO_ACCOUNT AND T0.NO_PROPERTY = a.NO_PROPERTY AND B_STATUS <> 'Billed') " & vbCrLf
varname1 = varname1 & "GROUP BY a.my_type,A.MY_SUBTYPE " & vbCrLf
varname1 = varname1 & "Union " & vbCrLf
varname1 = varname1 & "SELECT " & vbCrLf
varname1 = varname1 & "'Indicates Billed Action' as Status, " & vbCrLf
varname1 = varname1 & "COUNT(*) AS VOLUME, " & vbCrLf
varname1 = varname1 & "a.my_type, " & vbCrLf
varname1 = varname1 & "A.my_subtype, " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "Case WHEN " & vbCrLf
varname1 = varname1 & "To_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) AS ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > 0 THEN 1 ELSE 0 END) AS C_ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) as THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2)  as SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2) as TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "end),2) as OVER_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_OVER_TWELVE_MONTH " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "from tmason1.accrual_out2 A, CIS.TVP057TARIFF B " & vbCrLf
varname1 = varname1 & "WHERE a.CD_TARIFF = B.CD_TARIFF " & vbCrLf
varname1 = varname1 & "AND  A.CD_service_prov NOT in ('SW','TW') " & vbCrLf
varname1 = varname1 & "and   a.cd_tariff not in ('1STW-RVD01','1STW-RVD02','1STW-RVN01','1STW-RVS01','1STW-RVS02','1STW-RVU01','1STW-RVW01', " & vbCrLf
varname1 = varname1 & "'1STW-RVW02','1STW-RVW03') " & vbCrLf
varname1 = varname1 & "and substr(a.cd_tariff,2,3) = 'STW' " & vbCrLf
varname1 = varname1 & "AND ACCRUED_DAYS > 0 " & vbCrLf
varname1 = varname1 & "and a.NO_ACCOUNT IN ( SELECT T0.NO_ACCOUNT FROM tmason1.ACCRUAL_APP_DATA T0 WHERE T0.NO_ACCOUNT = a.NO_ACCOUNT AND T0.NO_PROPERTY = a.NO_PROPERTY AND B_STATUS = 'Billed' AND I_TYPE <> 'No Action Required') " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "GROUP BY a.my_type,A.MY_SUBTYPE " & vbCrLf
varname1 = varname1 & "Union " & vbCrLf
varname1 = varname1 & "SELECT " & vbCrLf
varname1 = varname1 & "'Indicates Billed No Action' as Status, " & vbCrLf
varname1 = varname1 & "COUNT(*) AS VOLUME, " & vbCrLf
varname1 = varname1 & "a.my_type, " & vbCrLf
varname1 = varname1 & "A.my_subtype, " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "Case WHEN " & vbCrLf
varname1 = varname1 & "To_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) AS ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > 0 THEN 1 ELSE 0 END) AS C_ONE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end ),2) as THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_THREE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2)  as SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_SEVEN_MONTH, " & vbCrLf
varname1 = varname1 & "  " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "AND " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & "THEN 0 " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "end),2) as TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "ROUND(SUM( " & vbCrLf
varname1 = varname1 & "CASE " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN " & vbCrLf
varname1 = varname1 & "(ACCRUED_DAYS - " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE " & vbCrLf
varname1 = varname1 & "Else " & vbCrLf
varname1 = varname1 & "end),2) as OVER_TWELVE_MONTH, " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "SUM(CASE " & vbCrLf
varname1 = varname1 & "WHEN " & vbCrLf
varname1 = varname1 & "TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  - " & vbCrLf
varname1 = varname1 & "TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "THEN 1 ELSE 0 END) AS C_OVER_TWELVE_MONTH " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "from tmason1.accrual_out2 A, CIS.TVP057TARIFF B " & vbCrLf
varname1 = varname1 & "WHERE a.CD_TARIFF = B.CD_TARIFF " & vbCrLf
varname1 = varname1 & "AND  A.CD_service_prov NOT in ('SW','TW') " & vbCrLf
varname1 = varname1 & "and   a.cd_tariff not in ('1STW-RVD01','1STW-RVD02','1STW-RVN01','1STW-RVS01','1STW-RVS02','1STW-RVU01','1STW-RVW01', " & vbCrLf
varname1 = varname1 & "'1STW-RVW02','1STW-RVW03') " & vbCrLf
varname1 = varname1 & "and substr(a.cd_tariff,2,3) = 'STW' " & vbCrLf
varname1 = varname1 & "AND ACCRUED_DAYS > 0 " & vbCrLf
varname1 = varname1 & "and a.NO_ACCOUNT IN ( SELECT T0.NO_ACCOUNT FROM tmason1.ACCRUAL_APP_DATA T0 WHERE T0.NO_ACCOUNT = a.NO_ACCOUNT AND T0.NO_PROPERTY = a.NO_PROPERTY AND B_STATUS = 'Billed' AND I_TYPE = 'No Action Required') " & vbCrLf
varname1 = varname1 & " " & vbCrLf
varname1 = varname1 & "GROUP BY a.my_type,A.MY_SUBTYPE " & vbCrLf
varname1 = varname1 & "order by 1,3) " & vbCrLf
varname1 = varname1 & "group by rollup(status),my_type,my_subtype"
Click to expand...


Hi Kyle and Micron, thank you for your help

With the above line - I get a run time error 2147217900
ORA - 00905:MISSING KEYWORD

rs.Open varname1, cn,
adOpenStatic, adLockReadOnly
 
Upvote 0
So maybe that's why I keep seeing &vbcrlf in posted examples here and elsewhere. They're not needed.

After the last post, I get the impression you're creating sql for a db like MySql?
 
Last edited:
Upvote 0
Try Oracle.
The vbCrLf appears in the previous example because Kyle123 was using a utility called SQLFormatter to generate the output and that's how sql formatter does. It's not necessary but it will makes for easier reading if you output to screen or text for a human to review (so in some cases it's a good idea).
 
Upvote 0
xenou said:
Try Oracle.
The vbCrLf appears in the previous example because Kyle123 was using a utility called SQLFormatter to generate the output and that's how sql formatter does. It's not necessary but it will makes for easier reading if you output to screen or text for a human to review (so in some cases it's a good idea).
Click to expand...


Ho Xenou, why am i getting the Missing error

i right in saying Access uses pl/sql and oracle uses t/sql?
 
Upvote 0
Just to note that Kyle's output (Post #11) and your original query (Post #1) are exactly the same (except for some spacing differences). This suggests that you either have invalid sql to start with (you need to test in Oracle to see if it works or not) or there is some problem with the oracle sql provider you are using.

Note link to SQL formatter again:
http://www.dpriver.com/pp/sqlformat.htm
 
Last edited:
Upvote 0
Hi Xenou

firstly thank you for your help..

i managed to get the Sql working via vba. The provider and everything was working ok but now ive run into another problem.

all of a sudden its saying error 3706 - provider not found it may not be installed properly

it was workung but not now...how can i ensure i have oracle providers that ensure 1 of them is working? So if 1 provider has a problem all of a sudden then go to next provider? or what code can you provide that will over come that?

again thank you so much
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA Automation Error - possibly born from my complete lack of understanding with Error Handling
2 3
Replies
23
Views
9K
RockandGrohl
R
M
  • Question Question
VBA Code - Not working when another user uses the macro
Replies
6
Views
1K
MHamid
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top