Hello All ,
I am using one field in crystal report , i want it with a range so i have to apply having clause for the condition but i am not able to do it ,condition part is bold one,so pl help me in this regards
select ZONE,TRNNO=AA.TRNNO,CLASS_CODE,AVG_WL_PSGN=0, CUMMULATIVE_AVG_WL_PSGN=SUM(PSGN_NUMBERS)/FREQ from
DW_EARNINGS_DETAIL as AA,
(select TRNNO,FREQ=COUNT(*) from
DW_FREQ_NEW where JOURNEYDATE between (case when month(@J_FROMDATE) between 1 and 3 then "DATE"(YEAR(@J_FROMDATE)-1 || '-' || '04' || '-' || '01') else "DATE"(YEAR(@J_FROMDATE) || '-' || '04' || '-' || '01') end)
and @J_TODATE group by TRNNO) as BB,
(select TRNNO=TRAIN_NUMBER,ZONE=SRC_STN_ZONE from TRAIN_ORG_ZONE group by TRAIN_NUMBER,SRC_STN_ZONE) as CC
where AA.trnno=BB.trnno and BB.trnno=CC.trnno and AA.trnno=CC.trnno and CC.zone = @ZONE
and JOURNEYDATE between (case when month(@J_FROMDATE) between 1 and 3 then "DATE"(YEAR(@J_FROMDATE)-1 || '-' || '04' || '-' || '01') else "DATE"(YEAR(@J_FROMDATE) || '-' || '04' || '-' || '01') end)
and @J_TODATE and status ='WL'
group by CC.ZONE,AA.trnno,AA.class_code,freq
having
CUMMULATIVE_AVG_WL_PSGN > case when CLASS_CODE = '1A' then 5
when CLASS_CODE = '2A' then 23
when CLASS_CODE = '3A' then 32
when CLASS_CODE = 'CC' then 36
when CLASS_CODE = 'FC' then 12
when CLASS_CODE = 'SL' then 36
when CLASS_CODE = '2S' then 36 end ) as AA
group by zone,trnno,class_code