Looping In SQL 2000 (Can it be a nested loop)

I have to automate a process that assigns sales leads to sales people.

For example:
Every day we buy a list of sales leads, it ranges in size from 50 -
100 records.
We have a team of sales people that also can range from 5 - 8 people.

I need to take the new records and divide them evenly among the sales
people.

If i get 50 records, and have 5 sales people, then each sales person
gets 10 leads.
--
So, im guessing that I may need to have a nested loop inside this. I
have tried it several different ways, but cant seem to get it quite
right.

DECLARE @.TotalRecordCount int, @.TotalSalesPeopleCount int,
@.AmountForEach int, @.LooperSalesPeoplerecords int,
@.LooperNewSalesLeadsRecords int, @.SalesPersonID int

SELECT @.TotalSalesPeopleCount = COUNT(UserId)
FROM SalesPeople
WHERE Active = 1
--
SELECT @.TotalRecordCount = COUNT(*)
FROM NewSalesLeads
--
SELECT @.AmountForEach = (@.TotalRecordCount/@.TotalSalesPeopleCount)
--
SELECT @.LooperSalesPeoplerecords = 1
SELECT @.LooperNewSalesLeadsRecords = 1
--
WHILE @.LooperSalesPeoplerecords <= @.TotalSalesPeopleCount
BEGIN
WHILE @.LooperNewSalesLeadsRecords <= @.TotalRecordCount
BEGIN
SELECT @.SalesPersonID = (SELECT UserID
FROM SalesPeople
WHERE UniqueId = @.LooperSalesPeoplerecords)

SELECT @.LooperSalesPeoplerecords =
(@.LooperSalesPeoplerecords + 1)

UPDATE SalesLeads
SET SalesPerson_ID = @.SalesPersonID
WHERE UNIQUEID = @.LooperSalesPeoplerecords

SELECT @.LooperSalesPeoplerecords =
(@.LooperSalesPeoplerecords + 1)
END
END

--
Table structures

CREATE TABLE [dbo].[SalesPeople] (
[SalesPerson_ID] [int] NOT NULL ,
[FirstName] [varchar](20)NOT NULL
) ON [PRIMARY]
--
INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (26, 'Bill')
INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (28, 'Bob')
INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (37,
'Chris')
---------------
CREATE TABLE [dbo].[SalesLeads] (
[SalesLeadID] [int]NOT NULL ,
[SalesPerson_ID] [int]NOT NULL
) ON [PRIMARY]
--
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1001,0)
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1002,0)
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1003,0)
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1004,0)
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1005,0)
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1006,0)
---------------

So in this case, all 3 salespeople should receive 2 salesleads each.

I dummied this down quite a bit. It actually ends up being more like
15 sales people, and about 400,000 sales leads. But it should work on
any level.

Thanks for any help you might shed on this.Hi

Something like this may help. It assumes the sales IDs are sequential, and
does not worry if there are a an exact division.

UPDATE L
SET SalesPerson_ID = M.SalesPerson_ID
FROM SalesLeads L JOIN
( SELECT P.[FirstName], P.[SalesPerson_ID] ,
( SELECT COUNT(*) FROM [dbo].[SalesPeople] S WHERE S.[SalesPerson_ID] <
P.[SalesPerson_ID] ) AS Rank
FROM [dbo].[SalesPeople] P ) M
ON L.SalesLeadID % ( SELECT COUNT(*) FROM [dbo].[SalesPeople] ) = M.Rank

John

"Dave" <funkdm1@.yahoo.com> wrote in message
news:f5174e0f.0406180540.2afaed20@.posting.google.c om...
> I have to automate a process that assigns sales leads to sales people.
> For example:
> Every day we buy a list of sales leads, it ranges in size from 50 -
> 100 records.
> We have a team of sales people that also can range from 5 - 8 people.
> I need to take the new records and divide them evenly among the sales
> people.
> If i get 50 records, and have 5 sales people, then each sales person
> gets 10 leads.
> --
> So, im guessing that I may need to have a nested loop inside this. I
> have tried it several different ways, but cant seem to get it quite
> right.
> DECLARE @.TotalRecordCount int, @.TotalSalesPeopleCount int,
> @.AmountForEach int, @.LooperSalesPeoplerecords int,
> @.LooperNewSalesLeadsRecords int, @.SalesPersonID int
> SELECT @.TotalSalesPeopleCount = COUNT(UserId)
> FROM SalesPeople
> WHERE Active = 1
> --
> SELECT @.TotalRecordCount = COUNT(*)
> FROM NewSalesLeads
> --
> SELECT @.AmountForEach = (@.TotalRecordCount/@.TotalSalesPeopleCount)
> --
> SELECT @.LooperSalesPeoplerecords = 1
> SELECT @.LooperNewSalesLeadsRecords = 1
> --
> WHILE @.LooperSalesPeoplerecords <= @.TotalSalesPeopleCount
> BEGIN
> WHILE @.LooperNewSalesLeadsRecords <= @.TotalRecordCount
> BEGIN
> SELECT @.SalesPersonID = (SELECT UserID
> FROM SalesPeople
> WHERE UniqueId = @.LooperSalesPeoplerecords)
> SELECT @.LooperSalesPeoplerecords =
> (@.LooperSalesPeoplerecords + 1)
> UPDATE SalesLeads
> SET SalesPerson_ID = @.SalesPersonID
> WHERE UNIQUEID = @.LooperSalesPeoplerecords
> SELECT @.LooperSalesPeoplerecords =
> (@.LooperSalesPeoplerecords + 1)
> END
> END
> --
> Table structures
> CREATE TABLE [dbo].[SalesPeople] (
> [SalesPerson_ID] [int] NOT NULL ,
> [FirstName] [varchar](20)NOT NULL
> ) ON [PRIMARY]
> --
> INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (26, 'Bill')
> INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (28, 'Bob')
> INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (37,
> 'Chris')
> ---------------
> CREATE TABLE [dbo].[SalesLeads] (
> [SalesLeadID] [int]NOT NULL ,
> [SalesPerson_ID] [int]NOT NULL
> ) ON [PRIMARY]
> --
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1001,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1002,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1003,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1004,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1005,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1006,0)
> ---------------
> So in this case, all 3 salespeople should receive 2 salesleads each.
> I dummied this down quite a bit. It actually ends up being more like
> 15 sales people, and about 400,000 sales leads. But it should work on
> any level.
> Thanks for any help you might shed on this.|||John,

Yeah, we arent that concerned about having an exactly even
distribution so uneven divisors are not a problem.

And, unfortunately the sales people arent in sequential order. WE have
hundreds of sales people that are assigned to various campaigns at any
given time. So this particular campaign may change on a daily basis.
Meaning that the salespersonid is always non sequential for this
campaign.

Thanks anyway.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<_mDAc.3861$Is4.34270449@.news-text.cableinet.net>...
> Hi
> Something like this may help. It assumes the sales IDs are sequential, and
> does not worry if there are a an exact division.
> UPDATE L
> SET SalesPerson_ID = M.SalesPerson_ID
> FROM SalesLeads L JOIN
> ( SELECT P.[FirstName], P.[SalesPerson_ID] ,
> ( SELECT COUNT(*) FROM [dbo].[SalesPeople] S WHERE S.[SalesPerson_ID] <
> P.[SalesPerson_ID] ) AS Rank
> FROM [dbo].[SalesPeople] P ) M
> ON L.SalesLeadID % ( SELECT COUNT(*) FROM [dbo].[SalesPeople] ) = M.Rank
> John
> "Dave" <funkdm1@.yahoo.com> wrote in message
> news:f5174e0f.0406180540.2afaed20@.posting.google.c om...
> > I have to automate a process that assigns sales leads to sales people.
> > For example:
> > Every day we buy a list of sales leads, it ranges in size from 50 -
> > 100 records.
> > We have a team of sales people that also can range from 5 - 8 people.
> > I need to take the new records and divide them evenly among the sales
> > people.
> > If i get 50 records, and have 5 sales people, then each sales person
> > gets 10 leads.
> > --
> > So, im guessing that I may need to have a nested loop inside this. I
> > have tried it several different ways, but cant seem to get it quite
> > right.
> > DECLARE @.TotalRecordCount int, @.TotalSalesPeopleCount int,
> > @.AmountForEach int, @.LooperSalesPeoplerecords int,
> > @.LooperNewSalesLeadsRecords int, @.SalesPersonID int
> > SELECT @.TotalSalesPeopleCount = COUNT(UserId)
> > FROM SalesPeople
> > WHERE Active = 1
> > --
> > SELECT @.TotalRecordCount = COUNT(*)
> > FROM NewSalesLeads
> > --
> > SELECT @.AmountForEach = (@.TotalRecordCount/@.TotalSalesPeopleCount)
> > --
> > SELECT @.LooperSalesPeoplerecords = 1
> > SELECT @.LooperNewSalesLeadsRecords = 1
> > --
> > WHILE @.LooperSalesPeoplerecords <= @.TotalSalesPeopleCount
> > BEGIN
> > WHILE @.LooperNewSalesLeadsRecords <= @.TotalRecordCount
> > BEGIN
> > SELECT @.SalesPersonID = (SELECT UserID
> > FROM SalesPeople
> > WHERE UniqueId = @.LooperSalesPeoplerecords)
> > SELECT @.LooperSalesPeoplerecords =
> > (@.LooperSalesPeoplerecords + 1)
> > UPDATE SalesLeads
> > SET SalesPerson_ID = @.SalesPersonID
> > WHERE UNIQUEID = @.LooperSalesPeoplerecords
> > SELECT @.LooperSalesPeoplerecords =
> > (@.LooperSalesPeoplerecords + 1)
> > END
> > END
> > --
> > Table structures
> > CREATE TABLE [dbo].[SalesPeople] (
> > [SalesPerson_ID] [int] NOT NULL ,
> > [FirstName] [varchar](20)NOT NULL
> > ) ON [PRIMARY]
> > --
> > INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (26, 'Bill')
> > INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (28, 'Bob')
> > INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (37,
> > 'Chris')
> > ---------------
> > CREATE TABLE [dbo].[SalesLeads] (
> > [SalesLeadID] [int]NOT NULL ,
> > [SalesPerson_ID] [int]NOT NULL
> > ) ON [PRIMARY]
> > --
> > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1001,0)
> > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1002,0)
> > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1003,0)
> > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1004,0)
> > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1005,0)
> > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1006,0)
> > ---------------
> > So in this case, all 3 salespeople should receive 2 salesleads each.
> > I dummied this down quite a bit. It actually ends up being more like
> > 15 sales people, and about 400,000 sales leads. But it should work on
> > any level.
> > Thanks for any help you might shed on this.|||Hi

The solution does not rely on salesperson id being sequential as it ranks
them separately. If the salesleads id are reasonably sequential then you
should be ok.

John

"Dave" <funkdm1@.yahoo.com> wrote in message
news:f5174e0f.0406181026.4c69acff@.posting.google.c om...
> John,
> Yeah, we arent that concerned about having an exactly even
> distribution so uneven divisors are not a problem.
> And, unfortunately the sales people arent in sequential order. WE have
> hundreds of sales people that are assigned to various campaigns at any
> given time. So this particular campaign may change on a daily basis.
> Meaning that the salespersonid is always non sequential for this
> campaign.
> Thanks anyway.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<_mDAc.3861$Is4.34270449@.news-text.cableinet.net>...
> > Hi
> > Something like this may help. It assumes the sales IDs are sequential,
and
> > does not worry if there are a an exact division.
> > UPDATE L
> > SET SalesPerson_ID = M.SalesPerson_ID
> > FROM SalesLeads L JOIN
> > ( SELECT P.[FirstName], P.[SalesPerson_ID] ,
> > ( SELECT COUNT(*) FROM [dbo].[SalesPeople] S WHERE S.[SalesPerson_ID] <
> > P.[SalesPerson_ID] ) AS Rank
> > FROM [dbo].[SalesPeople] P ) M
> > ON L.SalesLeadID % ( SELECT COUNT(*) FROM [dbo].[SalesPeople] ) = M.Rank
> > John
> > "Dave" <funkdm1@.yahoo.com> wrote in message
> > news:f5174e0f.0406180540.2afaed20@.posting.google.c om...
> > > I have to automate a process that assigns sales leads to sales people.
> > > > For example:
> > > Every day we buy a list of sales leads, it ranges in size from 50 -
> > > 100 records.
> > > We have a team of sales people that also can range from 5 - 8 people.
> > > > I need to take the new records and divide them evenly among the sales
> > > people.
> > > > If i get 50 records, and have 5 sales people, then each sales person
> > > gets 10 leads.
> > > --
> > > So, im guessing that I may need to have a nested loop inside this. I
> > > have tried it several different ways, but cant seem to get it quite
> > > right.
> > > > DECLARE @.TotalRecordCount int, @.TotalSalesPeopleCount int,
> > > @.AmountForEach int, @.LooperSalesPeoplerecords int,
> > > @.LooperNewSalesLeadsRecords int, @.SalesPersonID int
> > > > SELECT @.TotalSalesPeopleCount = COUNT(UserId)
> > > FROM SalesPeople
> > > WHERE Active = 1
> > > --
> > > SELECT @.TotalRecordCount = COUNT(*)
> > > FROM NewSalesLeads
> > > --
> > > SELECT @.AmountForEach = (@.TotalRecordCount/@.TotalSalesPeopleCount)
> > > --
> > > SELECT @.LooperSalesPeoplerecords = 1
> > > SELECT @.LooperNewSalesLeadsRecords = 1
> > > --
> > > WHILE @.LooperSalesPeoplerecords <= @.TotalSalesPeopleCount
> > > BEGIN
> > > WHILE @.LooperNewSalesLeadsRecords <= @.TotalRecordCount
> > > BEGIN
> > > SELECT @.SalesPersonID = (SELECT UserID
> > > FROM SalesPeople
> > > WHERE UniqueId = @.LooperSalesPeoplerecords)
> > > > SELECT @.LooperSalesPeoplerecords =
> > > (@.LooperSalesPeoplerecords + 1)
> > > > UPDATE SalesLeads
> > > SET SalesPerson_ID = @.SalesPersonID
> > > WHERE UNIQUEID = @.LooperSalesPeoplerecords
> > > > SELECT @.LooperSalesPeoplerecords =
> > > (@.LooperSalesPeoplerecords + 1)
> > > END
> > > END
> > > > --
> > > Table structures
> > > > CREATE TABLE [dbo].[SalesPeople] (
> > > [SalesPerson_ID] [int] NOT NULL ,
> > > [FirstName] [varchar](20)NOT NULL
> > > ) ON [PRIMARY]
> > > --
> > > INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (26, 'Bill')
> > > INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (28, 'Bob')
> > > INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (37,
> > > 'Chris')
> > > ---------------
> > > CREATE TABLE [dbo].[SalesLeads] (
> > > [SalesLeadID] [int]NOT NULL ,
> > > [SalesPerson_ID] [int]NOT NULL
> > > ) ON [PRIMARY]
> > > --
> > > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1001,0)
> > > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1002,0)
> > > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1003,0)
> > > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1004,0)
> > > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1005,0)
> > > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1006,0)
> > > ---------------
> > > > So in this case, all 3 salespeople should receive 2 salesleads each.
> > > > I dummied this down quite a bit. It actually ends up being more like
> > > 15 sales people, and about 400,000 sales leads. But it should work on
> > > any level.
> > > > Thanks for any help you might shed on this.|||I think meant to have something like this, with the keys shown and
some extra columns addded:

CREATE TABLE SalesPeople
(salesperson_id INTEGER NOT NULL PRIMARY KEY,
firstname VARCHAR(20)NOT NULL,
seq_nbr INTEGER NOT NULL,
.. );

Run this guy when then table is changed.

UPDATE SalesPeople
SET seq_nbr
= (SELECT COUNT(*)
FROM SalesPeople AS P1
WHERE P1.saleperson_id <= SalesPeople.salesperson_id)

Now a table for the leads as leads and not what you had:

CREATE TABLE SalesLeads
(saleslead_id INTEGER NOT NULL PRIMARY KEY,
grp_nbr INTEGER,
..);

First group the leads based on the number of sales people:

UPDATE SalesLeads
SET grp_nbr
= (SELECT COUNT(*)
FROM SalesLeads AS L1
WHERE L1.saleslead_id <= SalesLeads.saleslead_id)
% (SELECT COUNT(*) FROM SalesPeople);

Now look at a VIEW to get each sales person the group of leads they
are to work.

CREATE VIEW LeadAssignments (saleslead_id, salesperson_id)
AS SELECT L1.saleslead_id, P1.salesperson_id
FROM Salesleads AS L1, SalesPeople AS P1
WHERE L1.grp_nbr = P1.seq_nbr;|||I think I understand what you are saying here. However, This thing has
to be comletely automated and just do the update everynight.

I do appreciate the suggestions.

I have to think that this is used fairly often, and cant be this
difficult to find some folks that have written something similar.

Thanks,
Dave

jcelko212@.earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0406181437.187bf80a@.posting.google.com>...
> I think meant to have something like this, with the keys shown and
> some extra columns addded:
> CREATE TABLE SalesPeople
> (salesperson_id INTEGER NOT NULL PRIMARY KEY,
> firstname VARCHAR(20)NOT NULL,
> seq_nbr INTEGER NOT NULL,
> .. );
> Run this guy when then table is changed.
> UPDATE SalesPeople
> SET seq_nbr
> = (SELECT COUNT(*)
> FROM SalesPeople AS P1
> WHERE P1.saleperson_id <= SalesPeople.salesperson_id)
> Now a table for the leads as leads and not what you had:
> CREATE TABLE SalesLeads
> (saleslead_id INTEGER NOT NULL PRIMARY KEY,
> grp_nbr INTEGER,
> ..);
> First group the leads based on the number of sales people:
> UPDATE SalesLeads
> SET grp_nbr
> = (SELECT COUNT(*)
> FROM SalesLeads AS L1
> WHERE L1.saleslead_id <= SalesLeads.saleslead_id)
> % (SELECT COUNT(*) FROM SalesPeople);
> Now look at a VIEW to get each sales person the group of leads they
> are to work.
> CREATE VIEW LeadAssignments (saleslead_id, salesperson_id)
> AS SELECT L1.saleslead_id, P1.salesperson_id
> FROM Salesleads AS L1, SalesPeople AS P1
> WHERE L1.grp_nbr = P1.seq_nbr;|||How about something like this:

----------------------
Create Table #Leads
(
LeadID Int Identity Not Null,
SalesID Int,
UniqueId Int -- Bad name
)

Create Table #SalesPeople
(
SalesID Int Identity (0, 1) Not Null,
UserID Char(30) -- type?
)

DECLARE @.SalesPeopleCount int

-- Put sales people in the temp table.
Insert Into #SalesPeople (UserID)
Select UserID From SalesPeople Where Active = 1

-- How many were there?
Set @.SalesPeopleCount = @.@.RowCount

-- Put leads in the temp table.
Insert Into #Leads (UniqueId)
Select UniqueId From NewSalesLeads

-- Match sales people to leads
Update #Leads Set SalesID = LeadID % @.SalesPeopleCount

-- Save the matches.
UPDATE SalesLeads
SET SalesPerson_ID = UserID
From
SalesLeads
Join #Leads On SalesLeads.UniqueId = #Leads.UniqueId
Join #SalesPeople On #Leads.SalesID = #SalesPeople.SalesID

----------------------

Temporary IDs will always be sequential. The only problem with
this is that lower sales IDs will consistently get more leads.
You could fix that by adding something variable to LeadID before
performing the mod.

If you're going to use ID columns, you need to treat them as keys.
Joe Celko was using something like this as a bad example a few
weeks ago, trying to convince me that ID columns are a bad idea.
Used halfway like this, I would agree.

Bill

Dave wrote:
> I have to automate a process that assigns sales leads to sales people.
> For example:
> Every day we buy a list of sales leads, it ranges in size from 50 -
> 100 records.
> We have a team of sales people that also can range from 5 - 8 people.
> I need to take the new records and divide them evenly among the sales
> people.
> If i get 50 records, and have 5 sales people, then each sales person
> gets 10 leads.
> --
> So, im guessing that I may need to have a nested loop inside this. I
> have tried it several different ways, but cant seem to get it quite
> right.
> DECLARE @.TotalRecordCount int, @.TotalSalesPeopleCount int,
> @.AmountForEach int, @.LooperSalesPeoplerecords int,
> @.LooperNewSalesLeadsRecords int, @.SalesPersonID int
> SELECT @.TotalSalesPeopleCount = COUNT(UserId)
> FROM SalesPeople
> WHERE Active = 1
> --
> SELECT @.TotalRecordCount = COUNT(*)
> FROM NewSalesLeads
> --
> SELECT @.AmountForEach = (@.TotalRecordCount/@.TotalSalesPeopleCount)
> --
> SELECT @.LooperSalesPeoplerecords = 1
> SELECT @.LooperNewSalesLeadsRecords = 1
> --
> WHILE @.LooperSalesPeoplerecords <= @.TotalSalesPeopleCount
> BEGIN
> WHILE @.LooperNewSalesLeadsRecords <= @.TotalRecordCount
> BEGIN
> SELECT @.SalesPersonID = (SELECT UserID
> FROM SalesPeople
> WHERE UniqueId = @.LooperSalesPeoplerecords)
> SELECT @.LooperSalesPeoplerecords =
> (@.LooperSalesPeoplerecords + 1)
> UPDATE SalesLeads
> SET SalesPerson_ID = @.SalesPersonID
> WHERE UNIQUEID = @.LooperSalesPeoplerecords
> SELECT @.LooperSalesPeoplerecords =
> (@.LooperSalesPeoplerecords + 1)
> END
> END
> --
> Table structures
> CREATE TABLE [dbo].[SalesPeople] (
> [SalesPerson_ID] [int] NOT NULL ,
> [FirstName] [varchar](20)NOT NULL
> ) ON [PRIMARY]
> --
> INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (26, 'Bill')
> INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (28, 'Bob')
> INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (37,
> 'Chris')
> ---------------
> CREATE TABLE [dbo].[SalesLeads] (
> [SalesLeadID] [int]NOT NULL ,
> [SalesPerson_ID] [int]NOT NULL
> ) ON [PRIMARY]
> --
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1001,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1002,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1003,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1004,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1005,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1006,0)
> ---------------
> So in this case, all 3 salespeople should receive 2 salesleads each.
> I dummied this down quite a bit. It actually ends up being more like
> 15 sales people, and about 400,000 sales leads. But it should work on
> any level.
> Thanks for any help you might shed on this.

LookupCube: Unexpected Results with StrToSet

Hi.
SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
{StrToSet(CStr(LookupCube("Sales", "SetToStr({[Product].Member
s})")))} ON
ROWS
FROM [Sales]
Does anyone have any idea why the above query fails with an "Unknown
internal error"? When I replace the "[Product].Members" string with
"[Time].Members", it works fine.
The following query, which I think is quite similar, works fine:
SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
{StrToSet("[Product].Members")} ON ROWS
FROM [Sales]
Thanks.I believe this happens because the list of product members exceeds the
maximum string length that can be handled by "SetToStr", "LookupCube", or
both.
This is supported by testing your query replacing [Product].Members by
Head([Product].Members, n).
Up to n = 344 this works, then it breaks.
At this point , the string length should be over 32K, which is a likely limi
t.
HTH,
Brian
www.geocities.com/brianaltmann/olap.html
"John" wrote:

> Hi.
> SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
> {StrToSet(CStr(LookupCube("Sales", "SetToStr({[Product].Memb
ers})")))} ON
> ROWS
> FROM [Sales]
> Does anyone have any idea why the above query fails with an "Unknown
> internal error"? When I replace the "[Product].Members" string with
> "[Time].Members", it works fine.
> The following query, which I think is quite similar, works fine:
> SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
> {StrToSet("[Product].Members")} ON ROWS
> FROM [Sales]
> Thanks.
>
>

Looking to optimize a query

Hi, I have a query which is rather heavy which calculates commissions. It
is essentially a table of sales which relate to a table of commission
ranges and exceptions. Due to the way in which it works using left/right
joins does not work.
So, what I have done is a select in the following way
select
field1, field2, field3,
(select top 1 comission from comm
where key1=field2 or key2=field2 or key3=field3
order by nnnn)
from sales
and this works. However, I have to select more than one field from the comm
table, so I have
select
field1, field2, field3,
(select top 1 comission from comm
where key1=field2 or key2=field2 or key3=field3
order by nnnn),
(select top 1 range from comm
where key1=field2 or key2=field2 or key3=field3
order by nnnn),
(select top 1 comissionname from comm
where key1=field2 or key2=field2 or key3=field3
order by nnnn)
from sales
so in essence I am matching a row from the sales table to a single row in
the commisions table and taking three fields from the commisions table.
my problem ? - performance.
Is there any better way to do this? Is there a syntax to pull more than one
field from a subselect into the main query? Fields comission, range and
comissionname are all from the same row.
Alternatly is there any way to get a right join to only join the first row
which matches and not all ?
Regards
Ian MurphyIan Murphy wrote:
> Hi, I have a query which is rather heavy which calculates commissions. It
> is essentially a table of sales which relate to a table of commission
> ranges and exceptions. Due to the way in which it works using left/right
> joins does not work.
> So, what I have done is a select in the following way
> select
> field1, field2, field3,
> (select top 1 comission from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn)
> from sales
> and this works. However, I have to select more than one field from the com
m
> table, so I have
> select
> field1, field2, field3,
> (select top 1 comission from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn),
> (select top 1 range from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn),
> (select top 1 comissionname from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn)
> from sales
> so in essence I am matching a row from the sales table to a single row in
> the commisions table and taking three fields from the commisions table.
> my problem ? - performance.
> Is there any better way to do this? Is there a syntax to pull more than on
e
> field from a subselect into the main query? Fields comission, range and
> comissionname are all from the same row.
> Alternatly is there any way to get a right join to only join the first row
> which matches and not all ?
> Regards
> Ian Murphy
Haven't had my caffeine injection yet this morning, but I think
something like this is what you want. You should also review the
execution plan to look for potential bottlenecks:
SELECT
sales.field1,
sales.field2,
sales.field3,
comm.commission,
comm.range,
comm.commissionname
FROM sales sales
LEFT JOIN comm comm
ON
(
sales.field1 = comm.key1
OR sales.field2 = comm.key2
OR sales.field3 = comm.key3
)
WHERE comm.nnnnn =
(
SELECT MIN(nnnnn)
FROM comm
WHERE
(
sales.field1 = comm.key1
OR sales.field2 = comm.key2
OR sales.field3 = comm.key3
)
)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Ian,
There are a few options. Here is one of them...
SELECT field1, field2, field3, comission, range, comissionname
FROM comm
INNER JOIN (
SELECT key1, key2, key3, MIN(nnnn) AS min_nnnn
FROM comm
GROUP BY key1, key2, key3
) T
ON T.key1 = comm.key1
AND T.key2 = comm.key2
AND T.key3 = comm.key3
AND T.min_nnnn = comm.nnnn
RIGHT JOIN sales
ON field1 = comm.key1
AND field2 = comm.key2
AND field3 = comm.key3
Hope this helps,
Gert-Jan
Ian Murphy wrote:
> Hi, I have a query which is rather heavy which calculates commissions. It
> is essentially a table of sales which relate to a table of commission
> ranges and exceptions. Due to the way in which it works using left/right
> joins does not work.
> So, what I have done is a select in the following way
> select
> field1, field2, field3,
> (select top 1 comission from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn)
> from sales
> and this works. However, I have to select more than one field from the com
m
> table, so I have
> select
> field1, field2, field3,
> (select top 1 comission from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn),
> (select top 1 range from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn),
> (select top 1 comissionname from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn)
> from sales
> so in essence I am matching a row from the sales table to a single row in
> the commisions table and taking three fields from the commisions table.
> my problem ? - performance.
> Is there any better way to do this? Is there a syntax to pull more than on
e
> field from a subselect into the main query? Fields comission, range and
> comissionname are all from the same row.
> Alternatly is there any way to get a right join to only join the first row
> which matches and not all ?
> Regards
> Ian Murphy|||hmm, hadn't thought of using a subselect in the where condition. I'll have
a go and see if it speeds things up a bit.
Thanks
Ian|||On Mon, 17 Jul 2006 07:17:28 -0500, Tracy McKibben wrote:

>Haven't had my caffeine injection yet this morning, but I think
>something like this is what you want.
Hi Tracy,
Now that you've had your first cup of coffee, I'm sure you'll agree that
Ian will have to replace WHERE with AND in order to make it work as
required.

> You should also review the
>execution plan to look for potential bottlenecks:
>SELECT
> sales.field1,
> sales.field2,
> sales.field3,
> comm.commission,
> comm.range,
> comm.commissionname
>FROM sales sales
>LEFT JOIN comm comm
> ON
> (
> sales.field1 = comm.key1
> OR sales.field2 = comm.key2
> OR sales.field3 = comm.key3
> )
>WHERE comm.nnnnn =
> (
> SELECT MIN(nnnnn)
> FROM comm
> WHERE
> (
> sales.field1 = comm.key1
> OR sales.field2 = comm.key2
> OR sales.field3 = comm.key3
> )
> )
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:
> On Mon, 17 Jul 2006 07:17:28 -0500, Tracy McKibben wrote:
>
> Hi Tracy,
> Now that you've had your first cup of coffee, I'm sure you'll agree that
> Ian will have to replace WHERE with AND in order to make it work as
> required.
>
No, actually I intended that to be a WHERE clause. I think in this
instance it will work either way. '
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Tue, 18 Jul 2006 15:32:16 -0500, Tracy McKibben wrote:

>Hugo Kornelis wrote:
>No, actually I intended that to be a WHERE clause. I think in this
>instance it will work either way. '
Hi Tracy,
If every row in the sales table has at least one matching row in the
comm table, then it will work either way - but in that case, I see no
reason to use the LEFT JOIN instead of an INNER JOIN.
If there might be rows in the sales tabel without matching rows in the
comm table, then the LEFT JOIN will retain those rows, with NULL for all
values from the comm table (just as in Ian's original query) - but the
WHERE clause will throw them out after all, since it attempts to compare
NULL to the result of a subquery - and a comparison that involves NULL
can never evaluate to True.
Of course, since Ian didn't post anything to test it with, we don't know
if there can be any sales without commission in his database. :-)
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:
> If every row in the sales table has at least one matching row in the
> comm table, then it will work either way - but in that case, I see no
> reason to use the LEFT JOIN instead of an INNER JOIN.
> If there might be rows in the sales tabel without matching rows in the
> comm table, then the LEFT JOIN will retain those rows, with NULL for all
> values from the comm table (just as in Ian's original query) - but the
> WHERE clause will throw them out after all, since it attempts to compare
> NULL to the result of a subquery - and a comparison that involves NULL
> can never evaluate to True.
> Of course, since Ian didn't post anything to test it with, we don't know
> if there can be any sales without commission in his database. :-)
>
Doh! I see what you're talking about now... You're right, that belongs
in the join...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||There can indeed be sales without commissions. This is the easy part to
deal with. What has caused me a nightmare with this query is that the
commission is calculated depending on the discount. But the commission can
be different for the product, product family or the client in question. AND
it is applied in ranges, so a discount of 5% will earn 10% commission but a
discount of 15% will earn 3% and so on.
There is a simple order of priority to the above.
Thus for each line there are lots of possible commissions which could apply
(or none) but only the first should be selected, hence my solution of
select field1, field2, field3,
(select top 1 commission from comm table where xxxxxxx order by
zzzz,yyy,xxx)
but maybe I have to take another look at using a join.
Ian|||Unfortunatly I already tried that route. I can't use the min() function
because there is no row which will have a min value. The order of the
subselect will be by family, productid and client ref, meaning I don't have
a numeric or alphanumeric value which will be the lowest.
I can order the subselect so that the value I am after is the top row but
can't then pick it for the join.
I think my solution will be to subselect an index value from the comms
table and use that for the left join.
Thanks
Ian

Looking to optimize a query

Hi, I have a query which is rather heavy which calculates commissions. It
is essentially a table of sales which relate to a table of commission
ranges and exceptions. Due to the way in which it works using left/right
joins does not work.
So, what I have done is a select in the following way
select
field1, field2, field3,
(select top 1 comission from comm
where key1=field2 or key2=field2 or key3=field3
order by nnnn)
from sales
and this works. However, I have to select more than one field from the comm
table, so I have
select
field1, field2, field3,
(select top 1 comission from comm
where key1=field2 or key2=field2 or key3=field3
order by nnnn),
(select top 1 range from comm
where key1=field2 or key2=field2 or key3=field3
order by nnnn),
(select top 1 comissionname from comm
where key1=field2 or key2=field2 or key3=field3
order by nnnn)
from sales
so in essence I am matching a row from the sales table to a single row in
the commisions table and taking three fields from the commisions table.
my problem ? - performance.
Is there any better way to do this? Is there a syntax to pull more than one
field from a subselect into the main query? Fields comission, range and
comissionname are all from the same row.
Alternatly is there any way to get a right join to only join the first row
which matches and not all ?
Regards
Ian MurphyIan Murphy wrote:
> Hi, I have a query which is rather heavy which calculates commissions. It
> is essentially a table of sales which relate to a table of commission
> ranges and exceptions. Due to the way in which it works using left/right
> joins does not work.
> So, what I have done is a select in the following way
> select
> field1, field2, field3,
> (select top 1 comission from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn)
> from sales
> and this works. However, I have to select more than one field from the comm
> table, so I have
> select
> field1, field2, field3,
> (select top 1 comission from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn),
> (select top 1 range from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn),
> (select top 1 comissionname from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn)
> from sales
> so in essence I am matching a row from the sales table to a single row in
> the commisions table and taking three fields from the commisions table.
> my problem ? - performance.
> Is there any better way to do this? Is there a syntax to pull more than one
> field from a subselect into the main query? Fields comission, range and
> comissionname are all from the same row.
> Alternatly is there any way to get a right join to only join the first row
> which matches and not all ?
> Regards
> Ian Murphy
Haven't had my caffeine injection yet this morning, but I think
something like this is what you want. You should also review the
execution plan to look for potential bottlenecks:
SELECT
sales.field1,
sales.field2,
sales.field3,
comm.commission,
comm.range,
comm.commissionname
FROM sales sales
LEFT JOIN comm comm
ON
(
sales.field1 = comm.key1
OR sales.field2 = comm.key2
OR sales.field3 = comm.key3
)
WHERE comm.nnnnn = (
SELECT MIN(nnnnn)
FROM comm
WHERE
(
sales.field1 = comm.key1
OR sales.field2 = comm.key2
OR sales.field3 = comm.key3
)
)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Ian,
There are a few options. Here is one of them...
SELECT field1, field2, field3, comission, range, comissionname
FROM comm
INNER JOIN (
SELECT key1, key2, key3, MIN(nnnn) AS min_nnnn
FROM comm
GROUP BY key1, key2, key3
) T
ON T.key1 = comm.key1
AND T.key2 = comm.key2
AND T.key3 = comm.key3
AND T.min_nnnn = comm.nnnn
RIGHT JOIN sales
ON field1 = comm.key1
AND field2 = comm.key2
AND field3 = comm.key3
Hope this helps,
Gert-Jan
Ian Murphy wrote:
> Hi, I have a query which is rather heavy which calculates commissions. It
> is essentially a table of sales which relate to a table of commission
> ranges and exceptions. Due to the way in which it works using left/right
> joins does not work.
> So, what I have done is a select in the following way
> select
> field1, field2, field3,
> (select top 1 comission from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn)
> from sales
> and this works. However, I have to select more than one field from the comm
> table, so I have
> select
> field1, field2, field3,
> (select top 1 comission from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn),
> (select top 1 range from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn),
> (select top 1 comissionname from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn)
> from sales
> so in essence I am matching a row from the sales table to a single row in
> the commisions table and taking three fields from the commisions table.
> my problem ? - performance.
> Is there any better way to do this? Is there a syntax to pull more than one
> field from a subselect into the main query? Fields comission, range and
> comissionname are all from the same row.
> Alternatly is there any way to get a right join to only join the first row
> which matches and not all ?
> Regards
> Ian Murphy|||hmm, hadn't thought of using a subselect in the where condition. I'll have
a go and see if it speeds things up a bit.
Thanks
Ian|||On Mon, 17 Jul 2006 07:17:28 -0500, Tracy McKibben wrote:
>Haven't had my caffeine injection yet this morning, but I think
>something like this is what you want.
Hi Tracy,
Now that you've had your first cup of coffee, I'm sure you'll agree that
Ian will have to replace WHERE with AND in order to make it work as
required.
> You should also review the
>execution plan to look for potential bottlenecks:
>SELECT
> sales.field1,
> sales.field2,
> sales.field3,
> comm.commission,
> comm.range,
> comm.commissionname
>FROM sales sales
>LEFT JOIN comm comm
> ON
> (
> sales.field1 = comm.key1
> OR sales.field2 = comm.key2
> OR sales.field3 = comm.key3
> )
>WHERE comm.nnnnn => (
> SELECT MIN(nnnnn)
> FROM comm
> WHERE
> (
> sales.field1 = comm.key1
> OR sales.field2 = comm.key2
> OR sales.field3 = comm.key3
> )
> )
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:
> On Mon, 17 Jul 2006 07:17:28 -0500, Tracy McKibben wrote:
>> Haven't had my caffeine injection yet this morning, but I think
>> something like this is what you want.
> Hi Tracy,
> Now that you've had your first cup of coffee, I'm sure you'll agree that
> Ian will have to replace WHERE with AND in order to make it work as
> required.
>
No, actually I intended that to be a WHERE clause. I think in this
instance it will work either way. '
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Tue, 18 Jul 2006 15:32:16 -0500, Tracy McKibben wrote:
>Hugo Kornelis wrote:
>> On Mon, 17 Jul 2006 07:17:28 -0500, Tracy McKibben wrote:
>> Haven't had my caffeine injection yet this morning, but I think
>> something like this is what you want.
>> Hi Tracy,
>> Now that you've had your first cup of coffee, I'm sure you'll agree that
>> Ian will have to replace WHERE with AND in order to make it work as
>> required.
>No, actually I intended that to be a WHERE clause. I think in this
>instance it will work either way. '
Hi Tracy,
If every row in the sales table has at least one matching row in the
comm table, then it will work either way - but in that case, I see no
reason to use the LEFT JOIN instead of an INNER JOIN.
If there might be rows in the sales tabel without matching rows in the
comm table, then the LEFT JOIN will retain those rows, with NULL for all
values from the comm table (just as in Ian's original query) - but the
WHERE clause will throw them out after all, since it attempts to compare
NULL to the result of a subquery - and a comparison that involves NULL
can never evaluate to True.
Of course, since Ian didn't post anything to test it with, we don't know
if there can be any sales without commission in his database. :-)
--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:
> If every row in the sales table has at least one matching row in the
> comm table, then it will work either way - but in that case, I see no
> reason to use the LEFT JOIN instead of an INNER JOIN.
> If there might be rows in the sales tabel without matching rows in the
> comm table, then the LEFT JOIN will retain those rows, with NULL for all
> values from the comm table (just as in Ian's original query) - but the
> WHERE clause will throw them out after all, since it attempts to compare
> NULL to the result of a subquery - and a comparison that involves NULL
> can never evaluate to True.
> Of course, since Ian didn't post anything to test it with, we don't know
> if there can be any sales without commission in his database. :-)
>
Doh! I see what you're talking about now... You're right, that belongs
in the join...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||There can indeed be sales without commissions. This is the easy part to
deal with. What has caused me a nightmare with this query is that the
commission is calculated depending on the discount. But the commission can
be different for the product, product family or the client in question. AND
it is applied in ranges, so a discount of 5% will earn 10% commission but a
discount of 15% will earn 3% and so on.
There is a simple order of priority to the above.
Thus for each line there are lots of possible commissions which could apply
(or none) but only the first should be selected, hence my solution of
select field1, field2, field3,
(select top 1 commission from comm table where xxxxxxx order by
zzzz,yyy,xxx)
but maybe I have to take another look at using a join.
Ian|||Unfortunatly I already tried that route. I can't use the min() function
because there is no row which will have a min value. The order of the
subselect will be by family, productid and client ref, meaning I don't have
a numeric or alphanumeric value which will be the lowest.
I can order the subselect so that the value I am after is the top row but
can't then pick it for the join.
I think my solution will be to subselect an index value from the comms
table and use that for the left join.
Thanks
Ian|||Well I've managed an improvement, but its not great. I was unable to use
either your suggestion or Tracy's as they both relied on min() to select a
row, which doesn't work in my case. The only was I see to select the
commision row I want is to order the select result and select top 1
As it happens the comm table contains an index and I so I do
select field1, field2, field3,
(select top 1 indexfield from comm where xxx order by aaa,bbb,ccc) as
index
which I save as a view
and I then have another view which does
select * from view1 left join comm on view1.index=comm.index
It runs about 3-4 times faster but is still slow due to the above select
top 1.
Not sure there is really a better way to achieve what I'm trying to do.
Ian|||If you have multiple columns to order by, then it is still possible to
use the method I posted (using derived tables), but it will be more
complex, and might not give the desired performance gain. The derived
table would look something like this:
SELECT JoinKeys, family, productid, MIN("client ref") as "client ref"
FROM ..
INNER JOIN (
SELECT JoinKeys, family, MIN(productid) as min_productid
FROM ...
INNER JOIN (
SELECT JoinKeys, MIN(family) as min_family
FROM ..
GROUP BY JoinKeys
) T1 ON ..JoinKeys = ..JoinKeys AND ..family = ..min_family
GROUP BY JoinKeys, family
) T2 ON ..JoinKeys = ..JoinKeys AND ..family = ..family AND
..productid = ..min_productid
BTW: If you post incomplete queries then you can expect solutions that
are not usuable in your situation.
Gert-Jan
Ian Murphy wrote:
> Unfortunatly I already tried that route. I can't use the min() function
> because there is no row which will have a min value. The order of the
> subselect will be by family, productid and client ref, meaning I don't have
> a numeric or alphanumeric value which will be the lowest.
> I can order the subselect so that the value I am after is the top row but
> can't then pick it for the join.
> I think my solution will be to subselect an index value from the comms
> table and use that for the left join.
> Thanks
> Ian|||Ian,
Another general approach (since you didn't give specifics for a repro) is
select
f1, f2, f3,
cast(substring(commission_range_commissionname,1,25) as money) as
commission,
rtrim(substring(commission_range_commissionname,26,2)) as range,
rtrim(substring(commission_range_commissionname,28,30)) as commissionname
from (
select
f1, f2, f3,
(
select top 1
cast(commission as char(25)) +
cast(range as char(2)) +
cast(commissionname as char(30))
from comm
where key1=f1 or key2=f2 or key3= f3
order by nnnn
) as commission_range_commissionname
) as T
You could probably also solve this in SQL Server 2005 using the APPLY
operator.
Steve Kass
Drew University
www.stevekass.com
"Ian Murphy" <ian@.integra.antispam.-xp.com> wrote in message
news:o9rgie851g1p.icxpoxp9ubv2.dlg@.40tude.net...
> Well I've managed an improvement, but its not great. I was unable to use
> either your suggestion or Tracy's as they both relied on min() to select a
> row, which doesn't work in my case. The only was I see to select the
> commision row I want is to order the select result and select top 1
> As it happens the comm table contains an index and I so I do
> select field1, field2, field3,
> (select top 1 indexfield from comm where xxx order by aaa,bbb,ccc) as
> index
> which I save as a view
> and I then have another view which does
> select * from view1 left join comm on view1.index=comm.index
> It runs about 3-4 times faster but is still slow due to the above select
> top 1.
> Not sure there is really a better way to achieve what I'm trying to do.
> Ian
>|||Thanks for the suggestion steve. I had thought of doing something similar
but didn't try it as I thought it would rather heavy and I was sure there
had to be a more elegant solution. Converting to strings and back again
seems a lot of unnecessary work.|||On Wed, 19 Jul 2006 14:40:06 +0200, Ian Murphy wrote:
>There can indeed be sales without commissions. This is the easy part to
>deal with. What has caused me a nightmare with this query is that the
>commission is calculated depending on the discount. But the commission can
>be different for the product, product family or the client in question. AND
>it is applied in ranges, so a discount of 5% will earn 10% commission but a
>discount of 15% will earn 3% and so on.
>There is a simple order of priority to the above.
>Thus for each line there are lots of possible commissions which could apply
>(or none) but only the first should be selected, hence my solution of
>select field1, field2, field3,
> (select top 1 commission from comm table where xxxxxxx order by
>zzzz,yyy,xxx)
>but maybe I have to take another look at using a join.
Hi Ian,
It might help if you could post CREATE TABLE statements to show us the
structure of your tables (please do include all constraints, indexes,
and properties), INSERT statements with some sample data to illustrate
the problem, and expected results. That would probably give you better
answers faster than continuing this game of twenty questions.
--
Hugo Kornelis, SQL Server MVP|||i didn't post either the query or the table structure because it involves
- a db written in france with slightly cryptic column names in french
- a couple sub queries pulling data from 5 or 6 tables
- A main query pulling data from 4 tables/queries
- output column names in spanish
- its rather a large query in any case
so it didn't seem to make much sense, its unlikely anyone would have been
much the wiser due to the rather cryptic column names.
Ian