ISSUE WITH WRITING QUERY USING 3 CTE
46 views
Skip to first unread message
amit chauhan
Aug 6, 2021, 5:13:15 AM8/6/21
to H2 Database
Hi Team,
I am writing one query using 3 CTE. and the same is working fine.
working query:
************************************************************************
WITH recursive DOD_BASECTE (eventDate,ratingGroup,totalVolume,Volume_Percentage_Change,Result_totalVolume_DOD) AS
(
select
eventDate
, ratingGroup
, cast(totalVolume/(1024*1024.0) as DECIMAL(22,2))
, Volume_Percentage_Change
, Result_totalVolume_DOD
from
(
select
basetable.eventDate
, basetable.ratingGroup
, basetable.totalVolume
, previous_totalVolume
, Volume_Percentage_Change
, basetable.dod_totalVolume_cal
, mean_dev.avg_totalVolume
, mean_dev.stddev_totalVolume
, mean_dev.avg_totalVolume - mean_dev.stddev_totalVolume as lower_totalVolume
, mean_dev.avg_totalVolume + mean_dev.stddev_totalVolume as upper_totalVolume
, casewhen(basetable.dod_totalVolume_cal between (mean_dev.avg_totalVolume- 1.5*mean_dev.stddev_totalVolume) and
(
mean_dev.avg_totalVolume+ 1.5*mean_dev.stddev_totalVolume
)
,'PASS','FAIL' )as Result_totalVolume_DOD
from
(
SELECT
dayofweek
, ratingGroup
, eventDate
, datediff
, totalVolume
, previous_totalVolume
, dod_totalVolume
, CASEWHEN(datediff =-1,dod_totalVolume,CASEWHEN(datediff is null,totalVolume,0)) as dod_totalVolume_cal
, CASEWHEN(COALESCE(previous_totalVolume,0)=0
and totalVolume ='0',0,CASEWHEN(COALESCE(previous_totalVolume,0)=0
and totalVolume!='0',100,cast((totalVolume-previous_totalVolume)*100.0/previous_totalVolume as DECIMAL(22,2)))) as Volume_Percentage_Change
from
(
SELECT
DAY_OF_WEEK(eventDate) as dayofweek
, ratingGroup
, eventDate
, totalVolume
, lag(eventDate) OVER ( partition by ratingGroup ORDER BY
eventdate ) AS prevdate
, DATEDIFF(dd,PARSEDATETIME(eventdate,'yyyy-MM-dd'),PARSEDATETIME(lag(eventDate) OVER ( partition by ratingGroup ORDER BY
eventdate ),'yyyy-MM-dd')) as datediff
, lag(totalVolume,1) OVER ( partition by ratingGroup ORDER BY
eventdate ) AS previous_totalVolume
, totalVolume - lag(totalVolume,1) OVER ( partition by ratingGroup ORDER BY
eventdate) as dod_totalVolume
FROM
`ZM-RBK-DU-002-GatewayNode-DOD`
order by
eventdate
)
where
eventDate =
(
select
max(eventDate)
from
`ZM-RBK-DU-002-GatewayNode-DOD`
)
)
basetable
left join
(
select
dayofweek
, ratingGroup
, AVG(dod_totalVolume) as avg_totalVolume
, COALESCE(STDDEV(dod_totalVolume),0) as stddev_totalVolume
from
(
select *
from
(
SELECT
DAY_OF_WEEK(eventDate) as dayofweek
, ratingGroup
, eventDate
, totalVolume
, lag(eventDate) OVER ( partition by ratingGroup ORDER BY
eventdate ) AS prevdate
, DATEDIFF(dd,PARSEDATETIME(eventdate,'yyyy-MM-dd'),PARSEDATETIME(lag(eventDate) OVER ( partition by ratingGroup ORDER BY
eventdate ),'yyyy-MM-dd')) as datediff
, lag(totalVolume,1) OVER ( partition by ratingGroup ORDER BY
eventdate ) AS previous_totalVolume
, totalVolume - lag(totalVolume,1) OVER ( partition by ratingGroup ORDER BY
eventdate ) as dod_totalVolume
FROM
`ZM-RBK-DU-002-GatewayNode-Historical-DOD`
order by
eventDate
)
where
datediff=-1
)
group by
dayofweek
, ratingGroup
)
mean_dev
on
basetable.dayofweek =mean_dev.dayofweek
and basetable.ratingGroup=mean_dev.ratingGroup
)
)
, DOW_BASECTE (eventDate,ratingGroup,totalVolume,Volume_Percentage_Change,Result_totalVolume_DOW) AS
(
select
eventDate
, ratingGroup
, cast(totalVolume/(1024*1024.0) as DECIMAL(22,2))
, Volume_Percentage_Change
, Result_totalVolume_DOW
from
(
select
basetable.eventDate
, basetable.ratingGroup
, basetable.totalVolume
, previous_totalVolume
, Volume_Percentage_Change
, basetable.dod_totalVolume_cal
, mean_dev.avg_totalVolume
, mean_dev.stddev_totalVolume
, mean_dev.avg_totalVolume - mean_dev.stddev_totalVolume as lower_totalVolume
, mean_dev.avg_totalVolume + mean_dev.stddev_totalVolume as upper_totalVolume
, casewhen(basetable.dod_totalVolume_cal between (mean_dev.avg_totalVolume- 1.5*mean_dev.stddev_totalVolume) and
(
mean_dev.avg_totalVolume+ 1.5*mean_dev.stddev_totalVolume
)
,'PASS','FAIL' )as Result_totalVolume_DOW
from
(
SELECT
dayofweek
, ratingGroup
, eventDate
, datediff
, totalVolume
, previous_totalVolume
, dod_totalVolume
, CASEWHEN(datediff =-7,dod_totalVolume,CASEWHEN(datediff is null,totalVolume,0)) as dod_totalVolume_cal
, CASEWHEN(COALESCE(previous_totalVolume,0)=0
and totalVolume ='0',0,CASEWHEN(COALESCE(previous_totalVolume,0)=0
and totalVolume!='0',100,cast((totalVolume-previous_totalVolume)*100.0/previous_totalVolume as DECIMAL(22,2)))) as Volume_Percentage_Change
from
(
SELECT
DAY_OF_WEEK(eventDate) as dayofweek
, ratingGroup
, eventDate
, totalVolume
, lag(eventDate) OVER ( partition by ratingGroup ORDER BY
eventdate ) AS prevdate
, DATEDIFF(dd,PARSEDATETIME(eventdate,'yyyy-MM-dd'),PARSEDATETIME(lag(eventDate) OVER ( partition by ratingGroup ORDER BY
eventdate ),'yyyy-MM-dd')) as datediff
, lag(totalVolume,1) OVER ( partition by ratingGroup ORDER BY
eventdate ) AS previous_totalVolume
, totalVolume - lag(totalVolume,1) OVER ( partition by ratingGroup ORDER BY
eventdate) as dod_totalVolume
FROM
`ZM-RBK-DU-002-GatewayNode-DOW`
order by
eventdate
)
where
eventDate =
(
select
max(eventDate)
from
`ZM-RBK-DU-002-GatewayNode-DOW`
)
)
basetable
left join
(
select
dayofweek
, ratingGroup
, AVG(dod_totalVolume) as avg_totalVolume
, COALESCE(STDDEV(dod_totalVolume),0) as stddev_totalVolume
from
(
select *
from
(
SELECT
DAY_OF_WEEK(eventDate) as dayofweek
, ratingGroup
, eventDate
, totalVolume
, lag(eventDate) OVER ( partition by ratingGroup ORDER BY
eventdate ) AS prevdate
, DATEDIFF(dd,PARSEDATETIME(eventdate,'yyyy-MM-dd'),PARSEDATETIME(lag(eventDate) OVER ( partition by ratingGroup ORDER BY
eventdate ),'yyyy-MM-dd')) as datediff
, lag(totalVolume,1) OVER ( partition by ratingGroup ORDER BY
eventdate ) AS previous_totalVolume
, totalVolume - lag(totalVolume,1) OVER ( partition by ratingGroup ORDER BY
eventdate ) as dod_totalVolume
FROM
`ZM-RBK-DU-002-GatewayNode-Historical-DOW`
order by
eventDate
)
where
datediff=-7
)
group by
dayofweek
, ratingGroup
)
mean_dev
on
basetable.dayofweek =mean_dev.dayofweek
and basetable.ratingGroup=mean_dev.ratingGroup
)
)
, MOTHLY_BASECTE (ratingGroup,avg_volume_last30,avg_volume_last60,Monthly_Percentage_Change) AS
(
select
ratingGroup
, cast(avg_volume_last30/(1024*1024.0) as DECIMAL(22,2))
, cast(avg_volume_last60/(1024*1024.0) as DECIMAL(22,2))
, Monthly_Percentage_Change
from
(
select
basetable.ratingGroup
, avg_volume_last30
, avg_volume_last60
, CASEWHEN(COALESCE(avg_volume_last60,0)=0
and avg_volume_last30 ='0',0,CASEWHEN(COALESCE(avg_volume_last60,0)=0
and avg_volume_last30!='0',100,cast((avg_volume_last30-avg_volume_last60)*100.0/avg_volume_last60 as DECIMAL(22,2)))) as Monthly_Percentage_Change
from
(
select
ratingGroup
, COALESCE(avg(totalVolume),0) as avg_volume_last30
from
`ZM-RBK-DU-002-GatewayNode-MONTHLY`
where
PARSEDATETIME(eventdate,'yyyy-MM-dd') <=
(
select
max(PARSEDATETIME(eventdate,'yyyy-MM-dd'))
from
`ZM-RBK-DU-002-GatewayNode-MONTHLY`
)
and PARSEDATETIME(eventdate,'yyyy-MM-dd') >=
(
select
DATEADD(DAY,-29,max(PARSEDATETIME(eventdate,'yyyy-MM-dd')))
from
`ZM-RBK-DU-002-GatewayNode-MONTHLY`
)
group by
ratingGroup
)
basetable
left outer join
(
select
ratingGroup
, COALESCE(avg(totalVolume),0) as avg_volume_last60
from
`ZM-RBK-DU-002-GatewayNode-MONTHLY`
where
PARSEDATETIME(eventdate,'yyyy-MM-dd') <=
(
select
DATEADD(DAY,-30,max(PARSEDATETIME(eventdate,'yyyy-MM-dd')))
from
`ZM-RBK-DU-002-GatewayNode-MONTHLY`
)
and PARSEDATETIME(eventdate,'yyyy-MM-dd') >=
(
select
DATEADD(DAY,-59,max(PARSEDATETIME(eventdate,'yyyy-MM-dd')))
from
`ZM-RBK-DU-002-GatewayNode-MONTHLY`
)
group by
ratingGroup
)
history
on
basetable.ratingGroup=history.ratingGroup
)
)
select
a.ratingGroup
, a.totalVolume as `VOL [MB]`
, a.Volume_Percentage_Change as `DOD_VOL [%DIFF]`
, Result_totalVolume_DOD as DOD_STATUS
, b.Volume_Percentage_Change as `DOW_VOL [%DIFF]`
, Result_totalVolume_DOW as DOW_STATUS
, avg_volume_last30 as `AVG_VOL [PRESENT 30 DAYS]`
, avg_volume_last60 as `AVG_VOL [PREVIOUS 30 DAYS]`
, Monthly_Percentage_Change as `MONTH_VOL [%DIFF]`
, CASE
WHEN (
Result_totalVolume_DOD ='FAIL'
and Result_totalVolume_DOW ='FAIL'
and abs(Monthly_Percentage_Change) > 2
)
THEN 'RULE 1'
WHEN (
Result_totalVolume_DOD ='PASS'
and Result_totalVolume_DOW ='PASS'
and abs(Monthly_Percentage_Change) > 10
and abs(avg_volume_last30 - avg_volume_last60) > 100
)
THEN 'RULE 2'
WHEN (
Result_totalVolume_DOD ='FAIL'
and Result_totalVolume_DOW ='PASS'
and abs(Monthly_Percentage_Change) > 5
and abs(avg_volume_last30 - avg_volume_last60) > 100
)
THEN 'RULE 3'
WHEN (
Result_totalVolume_DOD ='PASS'
and Result_totalVolume_DOW ='FAIL'
and abs(Monthly_Percentage_Change) > 5
and abs(avg_volume_last30 - avg_volume_last60) > 100
)
THEN 'RULE 4'
ELSE 'NEW RULE'
END as REASON
from
DOD_BASECTE as a
inner join
DOW_BASECTE as b
on
a.ratingGroup = b.ratingGroup
inner join
MOTHLY_BASECTE c
on
a.ratingGroup = c.ratingGroup
where
(
(
(
Result_totalVolume_DOD ='FAIL'
and Result_totalVolume_DOW ='FAIL'
and abs(Monthly_Percentage_Change) > 2
)
or
(
Result_totalVolume_DOD ='PASS'
and Result_totalVolume_DOW ='PASS'
and abs(Monthly_Percentage_Change) > 10
and abs(avg_volume_last30 - avg_volume_last60) > 100
)
or
(
Result_totalVolume_DOD ='FAIL'
and Result_totalVolume_DOW ='PASS'
and abs(Monthly_Percentage_Change) > 5
and abs(avg_volume_last30 - avg_volume_last60) > 100
)
or
(
Result_totalVolume_DOD ='PASS'
and Result_totalVolume_DOW ='FAIL'
and abs(Monthly_Percentage_Change) > 5
and abs(avg_volume_last30 - avg_volume_last60) > 100
)
)
and a.totalVolume >5
)
;
************************************************************************
Due to some technological limitation on downstream i need to do one more select on the selected columns but below query is not working:
I also tried to create a CTE from in last query and select from same CTE but same also does not work. Can anyone please check what is wrong with below query last step select .
WITH recursive DOD_BASECTE (eventDate,ratingGroup,totalVolume,Volume_Percentage_Change,Result_totalVolume_DOD) AS
(
select
eventDate
, ratingGroup
, cast(totalVolume/(1024*1024.0) as DECIMAL(22,2))
, Volume_Percentage_Change
, Result_totalVolume_DOD
from
(
select
basetable.eventDate
, basetable.ratingGroup
, basetable.totalVolume
, previous_totalVolume
, Volume_Percentage_Change
, basetable.dod_totalVolume_cal
, mean_dev.avg_totalVolume
, mean_dev.stddev_totalVolume
, mean_dev.avg_totalVolume - mean_dev.stddev_totalVolume as lower_totalVolume
, mean_dev.avg_totalVolume + mean_dev.stddev_totalVolume as upper_totalVolume
, casewhen(basetable.dod_totalVolume_cal between (mean_dev.avg_totalVolume- 1.5*mean_dev.stddev_totalVolume) and
(
mean_dev.avg_totalVolume+ 1.5*mean_dev.stddev_totalVolume
)
,'PASS','FAIL' )as Result_totalVolume_DOD
from
(
SELECT
dayofweek
, ratingGroup
, eventDate
, datediff
, totalVolume
, previous_totalVolume
, dod_totalVolume
, CASEWHEN(datediff =-1,dod_totalVolume,CASEWHEN(datediff is null,totalVolume,0)) as dod_totalVolume_cal
, CASEWHEN(COALESCE(previous_totalVolume,0)=0
and totalVolume ='0',0,CASEWHEN(COALESCE(previous_totalVolume,0)=0
and totalVolume!='0',100,cast((totalVolume-previous_totalVolume)*100.0/previous_totalVolume as DECIMAL(22,2)))) as Volume_Percentage_Change
from
(
SELECT
DAY_OF_WEEK(eventDate) as dayofweek
, ratingGroup
, eventDate
, totalVolume
, lag(eventDate) OVER ( partition by ratingGroup ORDER BY
eventdate ) AS prevdate
, DATEDIFF(dd,PARSEDATETIME(eventdate,'yyyy-MM-dd'),PARSEDATETIME(lag(eventDate) OVER ( partition by ratingGroup ORDER BY
eventdate ),'yyyy-MM-dd')) as datediff
, lag(totalVolume,1) OVER ( partition by ratingGroup ORDER BY
eventdate ) AS previous_totalVolume
, totalVolume - lag(totalVolume,1) OVER ( partition by ratingGroup ORDER BY
eventdate) as dod_totalVolume
FROM
`ZM-RBK-DU-002-GatewayNode-DOD`
order by
eventdate
)
where
eventDate =
(
select
max(eventDate)
from
`ZM-RBK-DU-002-GatewayNode-DOD`
)
)
basetable
left join
(
select
dayofweek
, ratingGroup
, AVG(dod_totalVolume) as avg_totalVolume
, COALESCE(STDDEV(dod_totalVolume),0) as stddev_totalVolume
from
(
select *
from
(
SELECT
DAY_OF_WEEK(eventDate) as dayofweek
, ratingGroup
, eventDate
, totalVolume
, lag(eventDate) OVER ( partition by ratingGroup ORDER BY
eventdate ) AS prevdate
, DATEDIFF(dd,PARSEDATETIME(eventdate,'yyyy-MM-dd'),PARSEDATETIME(lag(eventDate) OVER ( partition by ratingGroup ORDER BY
eventdate ),'yyyy-MM-dd')) as datediff
, lag(totalVolume,1) OVER ( partition by ratingGroup ORDER BY
eventdate ) AS previous_totalVolume
, totalVolume - lag(totalVolume,1) OVER ( partition by ratingGroup ORDER BY
eventdate ) as dod_totalVolume
FROM
`ZM-RBK-DU-002-GatewayNode-Historical-DOD`
order by
eventDate
)
where
datediff=-1
)
group by
dayofweek
, ratingGroup
)
mean_dev
on
basetable.dayofweek =mean_dev.dayofweek
and basetable.ratingGroup=mean_dev.ratingGroup
)
)
, DOW_BASECTE (eventDate,ratingGroup,totalVolume,Volume_Percentage_Change,Result_totalVolume_DOW) AS
(
select
eventDate
, ratingGroup
, cast(totalVolume/(1024*1024.0) as DECIMAL(22,2))
, Volume_Percentage_Change
, Result_totalVolume_DOW
from
(
select
basetable.eventDate
, basetable.ratingGroup
, basetable.totalVolume
, previous_totalVolume
, Volume_Percentage_Change
, basetable.dod_totalVolume_cal
, mean_dev.avg_totalVolume
, mean_dev.stddev_totalVolume
, mean_dev.avg_totalVolume - mean_dev.stddev_totalVolume as lower_totalVolume
, mean_dev.avg_totalVolume + mean_dev.stddev_totalVolume as upper_totalVolume
, casewhen(basetable.dod_totalVolume_cal between (mean_dev.avg_totalVolume- 1.5*mean_dev.stddev_totalVolume) and
(
mean_dev.avg_totalVolume+ 1.5*mean_dev.stddev_totalVolume
)
,'PASS','FAIL' )as Result_totalVolume_DOW
from
(
SELECT
dayofweek
, ratingGroup
, eventDate
, datediff
, totalVolume
, previous_totalVolume
, dod_totalVolume
, CASEWHEN(datediff =-7,dod_totalVolume,CASEWHEN(datediff is null,totalVolume,0)) as dod_totalVolume_cal
, CASEWHEN(COALESCE(previous_totalVolume,0)=0
and totalVolume ='0',0,CASEWHEN(COALESCE(previous_totalVolume,0)=0
and totalVolume!='0',100,cast((totalVolume-previous_totalVolume)*100.0/previous_totalVolume as DECIMAL(22,2)))) as Volume_Percentage_Change
from
(
SELECT
DAY_OF_WEEK(eventDate) as dayofweek
, ratingGroup
, eventDate
, totalVolume
, lag(eventDate) OVER ( partition by ratingGroup ORDER BY
eventdate ) AS prevdate
, DATEDIFF(dd,PARSEDATETIME(eventdate,'yyyy-MM-dd'),PARSEDATETIME(lag(eventDate) OVER ( partition by ratingGroup ORDER BY
eventdate ),'yyyy-MM-dd')) as datediff
, lag(totalVolume,1) OVER ( partition by ratingGroup ORDER BY
eventdate ) AS previous_totalVolume
, totalVolume - lag(totalVolume,1) OVER ( partition by ratingGroup ORDER BY
eventdate) as dod_totalVolume
FROM
`ZM-RBK-DU-002-GatewayNode-DOW`
order by
eventdate
)
where
eventDate =
(
select
max(eventDate)
from
`ZM-RBK-DU-002-GatewayNode-DOW`
)
)
basetable
left join
(
select
dayofweek
, ratingGroup
, AVG(dod_totalVolume) as avg_totalVolume
, COALESCE(STDDEV(dod_totalVolume),0) as stddev_totalVolume
from
(
select *
from
(
SELECT
DAY_OF_WEEK(eventDate) as dayofweek
, ratingGroup
, eventDate
, totalVolume
, lag(eventDate) OVER ( partition by ratingGroup ORDER BY
eventdate ) AS prevdate
, DATEDIFF(dd,PARSEDATETIME(eventdate,'yyyy-MM-dd'),PARSEDATETIME(lag(eventDate) OVER ( partition by ratingGroup ORDER BY
eventdate ),'yyyy-MM-dd')) as datediff
, lag(totalVolume,1) OVER ( partition by ratingGroup ORDER BY
eventdate ) AS previous_totalVolume
, totalVolume - lag(totalVolume,1) OVER ( partition by ratingGroup ORDER BY
eventdate ) as dod_totalVolume
FROM
`ZM-RBK-DU-002-GatewayNode-Historical-DOW`
order by
eventDate
)
where
datediff=-7
)
group by
dayofweek
, ratingGroup
)
mean_dev
on
basetable.dayofweek =mean_dev.dayofweek
and basetable.ratingGroup=mean_dev.ratingGroup
)
)
, MOTHLY_BASECTE (ratingGroup,avg_volume_last30,avg_volume_last60,Monthly_Percentage_Change) AS
(
select
ratingGroup
, cast(avg_volume_last30/(1024*1024.0) as DECIMAL(22,2))
, cast(avg_volume_last60/(1024*1024.0) as DECIMAL(22,2))
, Monthly_Percentage_Change
from
(
select
basetable.ratingGroup
, avg_volume_last30
, avg_volume_last60
, CASEWHEN(COALESCE(avg_volume_last60,0)=0
and avg_volume_last30 ='0',0,CASEWHEN(COALESCE(avg_volume_last60,0)=0
and avg_volume_last30!='0',100,cast((avg_volume_last30-avg_volume_last60)*100.0/avg_volume_last60 as DECIMAL(22,2)))) as Monthly_Percentage_Change
from
(
select
ratingGroup
, COALESCE(avg(totalVolume),0) as avg_volume_last30
from
`ZM-RBK-DU-002-GatewayNode-MONTHLY`
where
PARSEDATETIME(eventdate,'yyyy-MM-dd') <=
(
select
max(PARSEDATETIME(eventdate,'yyyy-MM-dd'))
from
`ZM-RBK-DU-002-GatewayNode-MONTHLY`
)
and PARSEDATETIME(eventdate,'yyyy-MM-dd') >=
(
select
DATEADD(DAY,-29,max(PARSEDATETIME(eventdate,'yyyy-MM-dd')))
from
`ZM-RBK-DU-002-GatewayNode-MONTHLY`
)
group by
ratingGroup
)
basetable
left outer join
(
select
ratingGroup
, COALESCE(avg(totalVolume),0) as avg_volume_last60
from
`ZM-RBK-DU-002-GatewayNode-MONTHLY`
where
PARSEDATETIME(eventdate,'yyyy-MM-dd') <=
(
select
DATEADD(DAY,-30,max(PARSEDATETIME(eventdate,'yyyy-MM-dd')))
from
`ZM-RBK-DU-002-GatewayNode-MONTHLY`
)
and PARSEDATETIME(eventdate,'yyyy-MM-dd') >=
(
select
DATEADD(DAY,-59,max(PARSEDATETIME(eventdate,'yyyy-MM-dd')))
from
`ZM-RBK-DU-002-GatewayNode-MONTHLY`
)
group by
ratingGroup
)
history
on
basetable.ratingGroup=history.ratingGroup
)
)
select * from (
select
a.ratingGroup
, a.totalVolume as `VOL [MB]`
, a.Volume_Percentage_Change as `DOD_VOL [%DIFF]`
, Result_totalVolume_DOD as DOD_STATUS
, b.Volume_Percentage_Change as `DOW_VOL [%DIFF]`
, Result_totalVolume_DOW as DOW_STATUS
, avg_volume_last30 as `AVG_VOL [PRESENT 30 DAYS]`
, avg_volume_last60 as `AVG_VOL [PREVIOUS 30 DAYS]`
, Monthly_Percentage_Change as `MONTH_VOL [%DIFF]`
, CASE
WHEN (
Result_totalVolume_DOD ='FAIL'
and Result_totalVolume_DOW ='FAIL'
and abs(Monthly_Percentage_Change) > 2
)
THEN 'RULE 1'
WHEN (
Result_totalVolume_DOD ='PASS'
and Result_totalVolume_DOW ='PASS'
and abs(Monthly_Percentage_Change) > 10
and abs(avg_volume_last30 - avg_volume_last60) > 100
)
THEN 'RULE 2'
WHEN (
Result_totalVolume_DOD ='FAIL'
and Result_totalVolume_DOW ='PASS'
and abs(Monthly_Percentage_Change) > 5
and abs(avg_volume_last30 - avg_volume_last60) > 100
)
THEN 'RULE 3'
WHEN (
Result_totalVolume_DOD ='PASS'
and Result_totalVolume_DOW ='FAIL'
and abs(Monthly_Percentage_Change) > 5
and abs(avg_volume_last30 - avg_volume_last60) > 100
)
THEN 'RULE 4'
ELSE 'NEW RULE'
END as REASON
from
DOD_BASECTE as a
inner join
DOW_BASECTE as b
on
a.ratingGroup = b.ratingGroup
inner join
MOTHLY_BASECTE c
on
a.ratingGroup = c.ratingGroup
where
(
(
(
Result_totalVolume_DOD ='FAIL'
and Result_totalVolume_DOW ='FAIL'
and abs(Monthly_Percentage_Change) > 2
)
or
(
Result_totalVolume_DOD ='PASS'
and Result_totalVolume_DOW ='PASS'
and abs(Monthly_Percentage_Change) > 10
and abs(avg_volume_last30 - avg_volume_last60) > 100
)
or
(
Result_totalVolume_DOD ='FAIL'
and Result_totalVolume_DOW ='PASS'
and abs(Monthly_Percentage_Change) > 5
and abs(avg_volume_last30 - avg_volume_last60) > 100
)
or
(
Result_totalVolume_DOD ='PASS'
and Result_totalVolume_DOW ='FAIL'
and abs(Monthly_Percentage_Change) > 5
and abs(avg_volume_last30 - avg_volume_last60) > 100
)
)
and a.totalVolume >5
)
) as test
;
Reply all
Reply to author
Forward
0 new messages