mardi 5 septembre 2017

How to get an exact number of rows from the temp table?

I have the query below that runs okay, however at the end of the query when I specify number of rows, I do not get the specific number that I am selecting. Instead, the numbers are either greater or lower. I probably know the cause but do not know how to work around it and get the exact numbers.

 `WITH CTE AS
 (
 SELECT  ind_int_code as [Individual Type],
        ind_first_name as [First Name],
        ind_last_name as [Last Name],
        cst_recno as [Member ID],
        cst_eml_address_dn as [Email Address],
        adr_city as City,
        adr_state as [State],
        adr_country as Country,
        cst_org_name_dn as Company,
        cst_ixo_title_dn as [Job Title],
        case
               when mem_member_product like '%Stud%' then 'Associate 
               Student'
               when mem_member_type in ('Associate Corporate','Associate 
               Member') then 'Private Associate'
               When Mem_member_type in ('Associate Under 35 
               Sustaining','Young  Leader Sustaining') Then 'Associate 
               Member Under 35'
            When Mem_member_type = 'Full Under 35 Sustaining ' Then 'Full 
            Member Under 35'
            When Mem_member_type in ('Associate Corporate', 'Associate 
            Member', 
            'Associate Private Member', 'Associate Sustaining', 'Supporting 
            Coordinator', 
           'Sustaining Coordinator') then 'Private Associate'
            When Mem_member_type in ('Full Member', 'Full Member - ULI 
            GreenPrint Center', 'Full Private Member', 'Full Sustaining') 
            Then 'Private Full Member'
        When Mem_member_type in ('Associate Public Agency', 'Associate 
        Public Member') then 'Public Associate'
        When mem_member_type = 'Full Public Agency' then 'Public Full 
   Member'
        Else 'Exclude'
        End as 'Member Type', 
        ROW_NUMBER() OVER(PARTITION BY mem_member_type ORDER BY NEWID()) as 
        RN
   FROM co_individual WITH (NOLOCK)
INNER JOIN co_individual_ext WITH (NOLOCK)  
    ON ind_cst_key_ext = ind_cst_key 
INNER JOIN co_customer WITH (NOLOCK)  
    ON cst_key = ind_cst_key 
    AND ind_delete_flag=0
    AND ind_deceased_flag=0 
LEFT JOIN co_customer_x_address WITH (NOLOCK)  
    ON cst_cxa_key = cxa_key 
LEFT JOIN co_address WITH (NOLOCK)  
    ON adr_key = cxa_adr_key 
LEFT JOIN vw_client_uli_member_type WITH (NOLOCK)  
    ON cst_key = mem_cst_key 
WHERE mem_member_type IS NOT NULL 
AND adr_country = N'UNITED STATES' 
AND ind_deceased_flag != 1 
AND ind_key_leader_flag_ext != 1 
AND ind_int_code != N'Staff' 
AND ind_int_code != N'Spouse' 
AND ind_int_code != N'Press'
)
SELECT *
FROM CTE
where ([Member Type]= 'Private Associate' AND RN <= 3200)
OR ([Member Type]= 'Associate Member Under 35' AND RN <= 1680)
OR ([Member Type]= 'Private Full Member' AND RN <= 1040)
OR ([Member Type]= 'Public Associate' AND RN <= 960)
OR ([Member Type]= 'Associate Student' AND RN <= 640)
OR ([Member Type]= 'Public Full Member' AND RN <= 240)
OR ([Member Type]= 'Full Member Under 35' AND RN <= 80);`

Can someone help and fix the query?




Aucun commentaire:

Enregistrer un commentaire