cte sql

async def getDataMeasureDtlsQry(reqInf: ReqInference):
    
    select = '''
        dmtbl.itm_id as "Part", 
        dmtbl.site_id as "DISCP Site", 
        dmtbl.rgn_cd as "DISCP Region", 
        dmtbl.bucket_dt as "Date",
        dmtbl.data_msr as "data_msr", 
        sum(dmtbl.qty) as "qty"
    '''
    
    group = '''
        dmtbl.itm_id, dmtbl.site_id, dmtbl.rgn_cd, dmtbl.bucket_dt, dmtbl.data_msr
    '''
    
    where = f'''
        {TBL_ALIAS}.data_version = :{DN_DATA_VERSION}
        and {TBL_ALIAS}.{TableCol.DATA_MEASURE.value} in :{DATA_MEASURES}
        and {TBL_ALIAS}.{TableCol.BUCKET_DT.value} >= :{LAST_SATURDAY}
        and {TBL_ALIAS}.{TableCol.BUCKET_DT.value} < :{END_DAY}
        and {TBL_ALIAS}.{TableCol.PART_SITE.value} in :{PART_SITE}
        and {TBL_ALIAS}.{TableCol.ITM_ID.value} in :{PART}
        and {TBL_ALIAS}.{TableCol.SITE_ID.value} in :{DISCP_SITE}
    '''
    
    # NEW: CTE for filtering suppliers with non-zero qty in 182-day period
    supplier_filter_cte = ''
    
    if 'Supplier' in reqInf.table:
        select += ', dmtbl.supplr_nm as "Supplier"'
        group += ', dmtbl.supplr_nm'
        where += 'and dmtbl.supplr_nm IN :Supplier'
        
        # NEW: Build CTE to filter suppliers based on 182-day total qty
        supplier_filter_cte = f'''
        WITH valid_suppliers AS (
            SELECT 
                supplr_nm,
                itm_id,
                site_id,
                rgn_cd,
                data_msr
            FROM dsc_procure.{reqInf.table}
            WHERE data_version = :{DN_DATA_VERSION}
              AND {TableCol.DATA_MEASURE.value} in :{DATA_MEASURES}
              AND {TableCol.BUCKET_DT.value} >= :{LAST_SATURDAY}
              AND {TableCol.BUCKET_DT.value} < :{LAST_SATURDAY} + INTERVAL '182 days'
              AND {TableCol.PART_SITE.value} in :{PART_SITE}
              AND {TableCol.ITM_ID.value} in :{PART}
              AND {TableCol.SITE_ID.value} in :{DISCP_SITE}
              AND supplr_nm IN :Supplier
            GROUP BY supplr_nm, itm_id, site_id, rgn_cd, data_msr
            HAVING SUM(qty) > 0
        )
        '''
        
        # Add join condition to main query
        where += '''
        and EXISTS (
            SELECT 1 
            FROM valid_suppliers vs 
            WHERE vs.supplr_nm = dmtbl.supplr_nm
              AND vs.itm_id = dmtbl.itm_id
              AND vs.site_id = dmtbl.site_id
              AND vs.rgn_cd = dmtbl.rgn_cd
              AND vs.data_msr = dmtbl.data_msr
        )
        '''
    
    if reqInf.table == GDS_SUPPLY_ALLOCATION:
        where += f''' and {TBL_ALIAS}.{TableCol.GDS_RVG_TYPE.value} = 'PostTAN'
        '''
    
    query = f'''
        {supplier_filter_cte}
        SELECT {select}
        FROM dsc_procure.{reqInf.table} {TBL_ALIAS}
        WHERE {where}
        GROUP BY {group}
    '''
    
    return query