urgent help or adive (Spliting data equally )

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

General SQL Server Forums

New to SQL Server Programming

urgent help or adive (Spliting data equally )

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

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

Hey guys

This is my current query

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,
[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

as you can see from my query anything between the rm_sales_band '2M to 4M'is assigned to kirsty

i would like this changed so anything between rm_sales_band '2M to to 4M'is assigned to kirsty and chris. However i want the data to be split down the middle

For eg

if there were 100 rm_sales_band '2M to 4M, both kirsty and chris would get 50each

Can anyone help me with this ?

however i want

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-15 : 06:20:36

you could do a ROW_NUMBER() over the set and mod the result by 2. if it's even give it to Chris and Odd, give it to Kirsty.

This will ensure an even distribution.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-15 : 06:35:25

Have you abandonned the sales_band table.

I would go for the cte solution then you can add a row_number to the resultset then allocate.

;with sub as
(
(SELECT
[DBA_Name],
o.[fdmsaccountno],
[ho],
[rm_sales_band],
sum ([Gross_Sales]) as Sales,
[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]
) ,
cte2 as
(
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
Sub
INNER JOIN [geo_pca_sellers]
ON [pca] = spostcode
) ,
cte3 as
(
select *, seq = ROW_NUMBER() over (partition by RM order by Sales)
from cte2
)
select *, Allocated = case when seq%2=1 then 'Kirsty' else 'Ian Hatton' end
from cte3

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-15 : 07:35:42

Hi, Nigel,

I have resolved that issue now. I have sent you a response directly

masond
Constraint Violating Yak Guru

447 Posts

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

Hi Transact Charlie

In regards to your solution

here is some example data
The first top five lines are
Accountno Sales Salesband based on Sales RM
001 2269985.64 2M to 4M Kirsty
002 2060494.37 2M to 4M Kirsty
003 2587166.89 2M to 4M Kirsty
004 5207898.00 50M to 100M New starter
005 2977166.89 2M to 4M Kirsty

I need the
1st line to go to Kirsty
2nd chris
3rd Kirsty
4th remain new starter
5th chris

any ideas ?

How will i be able to do that ?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-15 : 10:46:18

exclude the set for only jobs that currently go to KIRSTY. (Filtered on Salesband?)

Then ROW_NUMBER and allocate even's to CHRIS.

As per Nigel's script above.

(something like this)


BEGIN TRAN

DECLARE @sample TABLE (
	[AccountNo] INT
	, [Sales] MONEY
	, [Salesband based on Sales] VARCHAR(255)
	, [RM] VARCHAR(255)
	)
INSERT @sample
VALUES ( 001, 2269985.64, '2M to 4M', 'Kirsty' )
     , ( 002, 2060494.37, '2M to 4M', 'Kirsty' )
     , ( 003, 2587166.89, '2M to 4M', 'Kirsty' )
     , ( 004, 5207898.00, '50M to 100M', 'New starter' )
     , ( 005, 2977166.89, '2M to 4M', 'Kirsty' )

; WITH sampleRowed AS (
	SELECT
		*
		, [rowNo] = ROW_NUMBER() OVER ( ORDER BY [AccountNo] ) 
	FROM
		@sample
	WHERE
		[RM] = 'Kirsty'
	)
SELECT
	*
	, CASE [rowNo] % 2 WHEN 0 THEN 'Kirsty' WHEN 1 THEN 'Chris' END AS [GiveTo]
FROM
	sampleRowed

ROLLBACK

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-15 : 11:15:07

Hi Transact Charlie

i dont think your method would work, as my data will grow and shrink on a regular basis, and i dont want to keep typing/updating the values.

Is there a way to say, go through the table, if its 2-4mil put kirsty, then the next one as chris, untill it reaches the end of the results ?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-15 : 11:30:17

My earlier post was aimed at this question.
Do you mean that you have people in a table?
then
declare @num int
select @num = count(*) from peopletable
;with cte as
(
select name, seq = row_number() over (order by name) frrom peopletable
) ,
cte2 as
(
select *, seq = rownumber() over (partition by Salesband order by sales)
from sample
) ,
cte3 as
(select *, band = seq % @num frrom cte2
select cte3.*, allocated = coalesce(cte1.name, cte3.RM)
from cte3
left join cte1
on cte3.seq = cte1.seq
and cte3.Salesband = '2M to 4M'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-15 : 11:44:02

HI Guys

Thank you for your answers,
i am even more confused now than i was before. i see if i can upload a screen print so you can see the table layout

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources