Please see my query below
Please find attached screen print of the results once the query has been excuted
However i have an issue, as you can see from my screen print i have multiple 2m- 4m accounts. I want to assign these '2M to 4M' equally between two people.
For eg, if there is 100accounts within the '2M to 4M', i want 50 of those accounts to go to seller r14 and the others to go to r15.
Can anyone help me with this
case when SalesBandRM = '2M to 4M'
and MCC_Code not in ('7997','7941') then 'R14'
when fdmsaccountno IN ('878177270880','878030700883','878231021881','878 233596880','878970059886','878970013883') OR MCC_Code in ('7997','7941')
else RMSC end as RMSC
sum ([Gross_Sales]) as Sales,
WHEN Isnumeric(RIGHT(LEFT(post_code, 2), 1)) = '0' THEN
ELSE LEFT(post_code, 1)
END AS 'sPostcode',
From [FDMS].[dbo].[Fact_Financial_History_Annualised] f
inner join Dim_Outlet o
on f.FDMSAccountNo = o.FDMSAccountNo
WHERE [rm_sales_band]IN ( '2M to 4m', '4m +' )
AND [ho] = 'Y'
and Account_Status = '16'
and LBG_Status <> 'Accepted'
AND iso_account = 'N'
AND Open_Date < dateadd(mm, -3, getdate())
and Agent_Chain_No not in ('878970059886', '878970013883')
AND o.fdmsaccountno NOT IN (SELECT [ta_mid]
INNER JOIN [geo_pca_sellers]
ON [pca] = spostcode
with CTE as
ROW_NUMBER() OVER(ORDER BY OrderColumn) AS RowNum,
COUNT(*) OVER() AS Tot
FROM MyTable as t
(case when c.RowNum > c.Tot / 2 then 'B' else 'A' end) as MyColumn
from CTE as c