Skip to Content
0
Former Member
Mar 18, 2009 at 09:48 AM

Not able to use" having " condition in crystal

159 Views

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