I need to write a sub query for after where clause to avoid the duplicate part.
any ideas.
SelectDistinct
appl.LICENSENO,appl.LICENSECAT,appl.licstatus,appl.Businessname,addr.SERVICE_ADDRESS,
sspg.ANALYSISENDDATE,sspg.COMPLETEDDATE,suic.APLICAPPLDTLKEY,suic.PERMITADMIN,
sspg.LOGINBATCH,sspg.LOGINBATCHID,sspg.SAMPLEANALYSISNUM,sgd.ADDITIONALCONCLIMITS,
-- case when sspg.SAMPLEANALYSISNUM > sgd.ADDITIONALCONCLIMITS then 'Out of Range' end RangeIndicator,
casewhen sspg.SAMPLEANALYSISNUM >CasewhenISNUMERIC(isnull(ADDITIONALCONCLIMITS,CasewhenISNUMERIC(MAXDAILYCONCENTRATION)= 0 then'0'else MAXDAILYCONCENTRATION end))= 0 then'0'elsereplace(ADDITIONALCONCLIMITS,'-','0')endthen'Out of Range'end outRange ,
sspg.SAMPLEID,sspg.SAMPLEPARAMETER,sspg.SAMPLEPOINT,sspg.SAMPLUNITS,sgd.MAXDAILYCONCENTRATION,
sgd.ANALYSISMETHOD,sgd.SAMPLUNITS as sgd_sampleunits,sgd.ADDDTTM,d.CODE,d.DESCRIPT
from CDR_BUSINESSLICENSE.LICAPPL(NOLOCK) appl
LEFTJOIN CDR_BUSINESSLICENSE.LICAPPLDETAIL(NOLOCK) licd ON appl.APLICKEY = licd.APLICKEY
AND licd.APLICAPPLDTLTYPEKEY = 2065
--Left JOIN CDR_SIU.SIUCORE (NOLOCK)suic ON licd.APLICAPPLDTLKEY = suic.APLICAPPLDTLKEY
--and licd.APLICAPPLDTLTYPEKEY = 2063
Leftjoin CDR_BUSINESSLICENSE.LICAPPLDETAIL c on appl.APLICKEY = c.APLICKEY and
c.APLICAPPLDTLTYPEKEY = 2063
LeftJOIN CDR_SIU.SIUCORE(NOLOCK)suic ON c.APLICAPPLDTLKEY = suic.APLICAPPLDTLKEY
LeftJoin CDR_SIU.SIUSAMPLING(NOLOCK) ssg on licd.APLICAPPLDTLKEY = ssg.APLICAPPLDTLKEY
LeftJoin CDR_SIU.SIUSAMPLINGGRID(NOLOCK)sspg on ssg.SIUSAMPLINGKEY = sspg.APLICAPPLDTLKEY
LEFTJOIN VW_ADDRESS(NOLOCK) addr ON appl.ADDRKEY = addr.HANSEN_ADDRESS_KEY
Leftjoin CDR_SIU.SIUDISCHARGELIMITSGRID(NOLOCK) SGD ON ssg.SIUSAMPLINGKEY = sgd.APLICAPPLDTLKEY
Leftjoin CDR_NSIU.SAMPLEPARAMETER d on sgd.SAMPLEPARAMETER = d.CODE
--where appl.LICENSENO = 'SIU-16904' and sspg.ANALYSISENDDATE between '01/01/2020' and '03/31/2020'
where appl.LICSTATUS ='Active'
--and appl.LICENSECAT = 'CAT'
AND(appl.LICENSECAT ='HV'OR APPL.LICENSECAT ='MISC')
and(suic.IUSURCHARGEDD ='Yes'AND D.CODE NOTIN('1650','1634')
ORisnull(suic.IUSURCHARGEDD,'NO')<>'Yes'AND D.CODE IN('1650','1634'))
and sspg.ANALYSISENDDATE between'01/01/2020'and'04/16/2020'and appl.LICENSENO ='SIU-17724'
