sampling data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sampling data

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-15 : 08:37:01

Hey guys

i need some urgent help

i have created the following query, which is returning the exact results which i want

SELECT sub.*,

case when rm_sales_band = '2M to 4M'
and MCC_Code not in ('7997','7941') then 'Kirsty'
when fdmsaccountno IN ('878177270880','878231021881','878233596880','878970059886','878970013883') OR MCC_Code in ('7997','7941')
then 'Ian Hatton'

else RM end as RM

FROM
(SELECT
[DBA_Name],
o.[fdmsaccountno],
[ho],
[rm_sales_band],
sum ([Gross_Sales]) as Sales,
dbo.salesbandRM(sum(Gross_Sales))as SalesBandRM,
[rm_code],
[post_code],
[Open_Date],

CASE
WHEN Isnumeric(RIGHT(LEFT(post_code, 2), 1)) = '0' THEN
LEFT(post_code, 2)
ELSE LEFT(post_code, 1)
END AS 'sPostcode',
[mcc_code]
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]
FROM
fdms_partnerreporting.tmp.trade_assocations)

group by
[DBA_Name],
o.[fdmsaccountno],
[ho],
[rm_sales_band],
[rm_code],
[post_code],
[Open_Date],
[MCC_Code]

) Sub
INNER JOIN [geo_pca_sellers]
ON [pca] = spostcode

order by DBA_Name

one of my criteria’s in the query is that if the rm_sales_band = '2M to 4M' then put the name kirsty in the Rm column . However i also need to say that if the rm_sales_band = '2M to 4M' also put Chris.

For eg, i have approx 300records for '2M to 4M' range, i need this to be equally split between chris and kirsty . eg 150 each .

My only problem that i can see is that when the results are turned, the rm_sales_banding will be all over the shot. For eg

row 1, could have a value of 2-4m
row 2 2-4mil
row 3 4m+
row 4 2-4m

has any1 got any ideas , how i resolve this problem ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-15 : 12:57:05

use a case when statement for that. add a sequential field based on row_number and use it in CASE to divide it to two sets of values ie condition like RowNo %2 = 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources