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