a complex query needs a solution

I need help with determining which salespeople should be
assigned bonuses based off a relationship between the
salesperson and their manager and also the relationship
between the salesperson and the customer. Below are 3
tables, the salesperson table which contains the
salesperson's number and a joblevel column. The joblevel
you can think of 10 = manager and 5 = worker. There are
more levels but just assume the higher the number the
higher the level of the person. Next is the customer
table. After that is the customer to salesrep assignment
table. If you read the first insert, we insert for
customer 779 the rep 5000 who happens to be a manager, a
date of 1/1/04, and an ownership of 1 (100%). That means
that the rep 5000 would get 100% of his bonus based on the
revenue that customer 779 generates. Next you see for the
same customer 5002 (a subordinate) is inserted. After
that you see at 3/1/04 a new subordinate level person
(level = 5) gets inserted. This means that at 1/1/04
subordinate 5002 is assigned to cusomter 779 but at 3/1/04
subordinate 5003 is assigned to customer 779. Since no
subordinate is assigned at 7/1/04, then we conclude that
5003 is still assigned to 779 at that date.
Skipping down we can se for customer 716 that 2 level=5
subordinates are assigned but one's ownership is 40% and
the other's is 60%. the next month 2 new subordinates are
rotated in. Don't worry about the insert logic to this
table. If 2 subordinates are split on a customer and one
gets replaced the next month, the other gets replaced too
so that in one month the total for each joblevel = 1 (100%)
create table salesrep (salesrepno int not null primary
key, joblevel int not null)
insert salesrep values (5000, 10)
insert salesrep values (5001, 10)
insert salesrep values (5002, 5)
insert salesrep values (5003, 5)
insert salesrep values (5004, 5)
insert salesrep values (5005, 5)
insert salesrep values (5006, 5)
insert salesrep values (5007, 5)
insert salesrep values (5008, 5)
insert salesrep values (5009, 10)
insert salesrep values (5010, 5)
create table customer (custno int not null primary key)
insert customer values (234)
insert customer values (332)
insert customer values (213)
insert customer values (716)
insert customer values (879)
insert customer values (267)
create table customer_salesrep (
custno int not null foreign key references customer,
salesrepno int not null foreign key references salesrep,
dateassigned datetime not null,
ownership int check (ownership between 0 and 1),
primary key (custno, salesrepno, dateassigned))
insert customer_salesrep values (779, 5000, '1/1/2004', 1)
insert customer_salesrep values (779, 5002, '1/1/2004', 1)
insert customer_salesrep values (779, 5003, '3/1/2004', 1)
insert customer_salesrep values (332, 5000, '1/1/2004', 1)
insert customer_salesrep values (213, 5000, '7/1/2004', 1)
insert customer_salesrep values (213, 5004, '7/1/2004', 1)
insert customer_salesrep values (716, 5000, '1/1/2004', 1)
insert customer_salesrep values (716, 5005, '1/1/2004', .4)
insert customer_salesrep values (716, 5006, '1/1/2004', .6)
insert customer_salesrep values (716, 5007, '3/1/2004', .4)
insert customer_salesrep values (716, 5008, '3/1/2004', .6)
insert customer_salesrep values (879, 5000, '1/1/2004')
insert customer_salesrep values (879, 5002, '1/1/2004')
insert customer_salesrep values (879, 5001, '7/1/2004')
insert customer_salesrep values (267, 5009, '1/1/2004', 1)
insert customer_salesrep values (267, 5010, '1/1/2004', 1)
I want to run a query where I pass in the manager level
number (in this case 5000) and a date. I want to know who
(the subordinate) is assigned to the customer at any date
I pass in. If another manager is assigned to a customer
(like 5009) then I don't want to see the customer
information for him.
If I run the query at 1/1/2004 for the manager 5000, I
should get in the results
779 5002 1
716 5005 .4
716 5006 .6
879 5002 1
If I run the query at 3/1/2004 for the manager 5000, I
should get in the results
779 5003 1
716 5007 .4
716 5008 .6
879 5002 1
If I run the query at 7/1/2004 for the manager 5000, I
should get in the results (notice customer 879 drops off
because another manager takes over the relationship)
779 5003 1
213 5004 1
716 5007 .4
716 5008 .6
Please let me know if you need any more information"Jerry Fortaine" <anonymous@.discussions.microsoft.com> wrote in message
news:26b001c50d4d$8823af10$a401280a@.phx.gbl...
>I need help with determining which salespeople should be
> assigned bonuses based off a relationship between the
> salesperson and their manager and also the relationship
> between the salesperson and the customer. Below are 3
> tables, the salesperson table which contains the
> salesperson's number and a joblevel column. The joblevel
> you can think of 10 = manager and 5 = worker. There are
> more levels but just assume the higher the number the
> higher the level of the person. Next is the customer
> table. After that is the customer to salesrep assignment
> table. If you read the first insert, we insert for
> customer 779 the rep 5000 who happens to be a manager, a
> date of 1/1/04, and an ownership of 1 (100%). That means
> that the rep 5000 would get 100% of his bonus based on the
> revenue that customer 779 generates. Next you see for the
> same customer 5002 (a subordinate) is inserted. After
> that you see at 3/1/04 a new subordinate level person
> (level = 5) gets inserted. This means that at 1/1/04
> subordinate 5002 is assigned to cusomter 779 but at 3/1/04
> subordinate 5003 is assigned to customer 779. Since no
> subordinate is assigned at 7/1/04, then we conclude that
> 5003 is still assigned to 779 at that date.
> Skipping down we can se for customer 716 that 2 level=5
> subordinates are assigned but one's ownership is 40% and
> the other's is 60%. the next month 2 new subordinates are
> rotated in. Don't worry about the insert logic to this
> table. If 2 subordinates are split on a customer and one
> gets replaced the next month, the other gets replaced too
> so that in one month the total for each joblevel = 1 (100%)
> create table salesrep (salesrepno int not null primary
> key, joblevel int not null)
> insert salesrep values (5000, 10)
> insert salesrep values (5001, 10)
> insert salesrep values (5002, 5)
> insert salesrep values (5003, 5)
> insert salesrep values (5004, 5)
> insert salesrep values (5005, 5)
> insert salesrep values (5006, 5)
> insert salesrep values (5007, 5)
> insert salesrep values (5008, 5)
> insert salesrep values (5009, 10)
> insert salesrep values (5010, 5)
> create table customer (custno int not null primary key)
> insert customer values (234)
> insert customer values (332)
> insert customer values (213)
> insert customer values (716)
> insert customer values (879)
> insert customer values (267)
> create table customer_salesrep (
> custno int not null foreign key references customer,
> salesrepno int not null foreign key references salesrep,
> dateassigned datetime not null,
> ownership int check (ownership between 0 and 1),
> primary key (custno, salesrepno, dateassigned))
> insert customer_salesrep values (779, 5000, '1/1/2004', 1)
> insert customer_salesrep values (779, 5002, '1/1/2004', 1)
> insert customer_salesrep values (779, 5003, '3/1/2004', 1)
> insert customer_salesrep values (332, 5000, '1/1/2004', 1)
> insert customer_salesrep values (213, 5000, '7/1/2004', 1)
> insert customer_salesrep values (213, 5004, '7/1/2004', 1)
> insert customer_salesrep values (716, 5000, '1/1/2004', 1)
> insert customer_salesrep values (716, 5005, '1/1/2004', .4)
> insert customer_salesrep values (716, 5006, '1/1/2004', .6)
> insert customer_salesrep values (716, 5007, '3/1/2004', .4)
> insert customer_salesrep values (716, 5008, '3/1/2004', .6)
> insert customer_salesrep values (879, 5000, '1/1/2004')
> insert customer_salesrep values (879, 5002, '1/1/2004')
> insert customer_salesrep values (879, 5001, '7/1/2004')
> insert customer_salesrep values (267, 5009, '1/1/2004', 1)
> insert customer_salesrep values (267, 5010, '1/1/2004', 1)
> I want to run a query where I pass in the manager level
> number (in this case 5000) and a date. I want to know who
> (the subordinate) is assigned to the customer at any date
> I pass in. If another manager is assigned to a customer
> (like 5009) then I don't want to see the customer
> information for him.
>
> If I run the query at 1/1/2004 for the manager 5000, I
> should get in the results
> 779 5002 1
> 716 5005 .4
> 716 5006 .6
> 879 5002 1
>
> If I run the query at 3/1/2004 for the manager 5000, I
> should get in the results
> 779 5003 1
> 716 5007 .4
> 716 5008 .6
> 879 5002 1
> If I run the query at 7/1/2004 for the manager 5000, I
> should get in the results (notice customer 879 drops off
> because another manager takes over the relationship)
> 779 5003 1
> 213 5004 1
> 716 5007 .4
> 716 5008 .6
> Please let me know if you need any more information
-- Customer and sales rep along with job level of rep
CREATE VIEW AccountReps (custno, salesrepno, joblevel, dateassigned, ownersh
ip)
AS
SELECT CSR.custno, CSR.salesrepno, SR.joblevel, CSR.dateassigned, CSR.owners
hip
FROM customer_salesrep AS CSR
INNER JOIN
salesrep AS SR
ON CSR.salesrepno = SR.salesrepno
-- For each customer account, all overlaps between a manager's and
-- worker's tenure
CREATE VIEW AccountAssignments
(custno, manager, manager_ownership,
manager_dateassigned, manager_dateunassigned,
worker, worker_ownership, worker_dateassigned, worker_dateunassigned)
AS
SELECT M.custno, M.salesrepno, M.ownership,
M.dateassigned, M.dateunassigned,
W.salesrepno, W.ownership, W.dateassigned, W.dateunassigned
FROM (SELECT R1.custno, R1.salesrepno, R1.dateassigned, R1.ownership,
COALESCE(MIN(R2.dateassigned), '99991231')
AS dateunassigned
FROM AccountReps AS R1
LEFT OUTER JOIN
AccountReps AS R2
ON R1.custno = R2.custno AND
R1.joblevel = R2.joblevel AND
R1.salesrepno <> R2.salesrepno AND
R1.dateassigned < R2.dateassigned
WHERE R1.joblevel = 10
GROUP BY R1.custno, R1.salesrepno, R1.dateassigned, R1.ownership)
AS M
INNER JOIN
(SELECT R1.custno, R1.salesrepno, R1.dateassigned, R1.ownership,
COALESCE(MIN(R2.dateassigned), '99991231')
AS dateunassigned
FROM AccountReps AS R1
LEFT OUTER JOIN
AccountReps AS R2
ON R1.custno = R2.custno AND
R2.joblevel < 10 AND
R1.salesrepno <> R2.salesrepno AND
R1.dateassigned < R2.dateassigned
WHERE R1.joblevel < 10
GROUP BY R1.custno, R1.salesrepno, R1.dateassigned, R1.ownership)
AS W
ON W.dateassigned < M.dateunassigned AND
W.dateunassigned > M.dateassigned AND
W.custno = M.custno
-- For manager 5000 on 20040101
SELECT custno, worker, worker_ownership
FROM AccountAssignments
WHERE manager = 5000 AND
worker_dateassigned <= '20040101' AND
worker_dateunassigned > '20040101' AND
manager_dateassigned <= '20040101' AND
manager_dateunassigned > '20040101'
ORDER BY custno, worker
-- For manager 5000 on 20040301
SELECT custno, worker, worker_ownership
FROM AccountAssignments
WHERE manager = 5000 AND
worker_dateassigned <= '20040301' AND
worker_dateunassigned > '20040301' AND
manager_dateassigned <= '20040301' AND
manager_dateunassigned > '20040301'
ORDER BY custno, worker
-- For manager 5000 on 20040701
SELECT custno, worker, worker_ownership
FROM AccountAssignments
WHERE manager = 5000 AND
worker_dateassigned <= '20040701' AND
worker_dateunassigned > '20040701' AND
manager_dateassigned <= '20040701' AND
manager_dateunassigned > '20040701'
ORDER BY custno, worker
-- Note that we can check assignments on an arbitrary date
-- For manager 5000 on 20040515
SELECT custno, worker, worker_ownership
FROM AccountAssignments
WHERE manager = 5000 AND
worker_dateassigned <= '20040515' AND
worker_dateunassigned > '20040515' AND
manager_dateassigned <= '20040515' AND
manager_dateunassigned > '20040515'
ORDER BY custno, worker
JAG|||Hi Jerry. I see that John already posted an answer to your question. I
took a look and came up with something a bit different. I think that the
problem lies in that you have relationships and data that are implicit in
your table schema. It may make for faster queries if you explicitly set an
owning manager to each line item, rather than calculate it each time you ran
your query. Right now this ownership is burried in the ordering of the
rows which is a bit tricky. It may be possible to create an indexed view
with this information in it. Maintaining "reportsto" relationships would
also make the queries a bit cleaner.
Here is my attempt:
--use master
--go
--DROP DATABASE complexquery
--go
create database complexquery
go
use complexquery
go
create table salesrep (salesrepno int not null primary
key, joblevel int not null)
go
insert salesrep values (5000, 10)
insert salesrep values (5001, 10)
insert salesrep values (5002, 5)
insert salesrep values (5003, 5)
insert salesrep values (5004, 5)
insert salesrep values (5005, 5)
insert salesrep values (5006, 5)
insert salesrep values (5007, 5)
insert salesrep values (5008, 5)
insert salesrep values (5009, 10)
insert salesrep values (5010, 5)
go
--delete from salesrep
create table customer (custno int not null primary key)
go
insert customer values (234)
insert customer values (332)
insert customer values (213)
insert customer values (716)
insert customer values (879)
insert customer values (267)
insert customer values (779) -- bug!bug! added value
go
--drop table customer_salesrep
create table customer_salesrep (
custno int not null foreign key references customer,
salesrepno int not null foreign key references salesrep,
dateassigned datetime not null,
ownership decimal(5,2) check (ownership between 0 and 1), -- bug!bug! int
truncation, changed to decimal
primary key (custno, salesrepno, dateassigned))
go
delete from customer_salesrep
go
insert customer_salesrep values (779, 5000, '1/1/2004', 1)
insert customer_salesrep values (779, 5002, '1/1/2004', 1)
insert customer_salesrep values (779, 5003, '3/1/2004', 1)
insert customer_salesrep values (332, 5000, '1/1/2004', 1)
insert customer_salesrep values (213, 5000, '7/1/2004', 1)
insert customer_salesrep values (213, 5004, '7/1/2004', 1)
insert customer_salesrep values (716, 5000, '1/1/2004', 1)
insert customer_salesrep values (716, 5005, '1/1/2004', .4)
insert customer_salesrep values (716, 5006, '1/1/2004', .6)
insert customer_salesrep values (716, 5007, '3/1/2004', .4)
insert customer_salesrep values (716, 5008, '3/1/2004', .6)
insert customer_salesrep values (879, 5000, '1/1/2004', 1)
insert customer_salesrep values (879, 5002, '1/1/2004', 1)
insert customer_salesrep values (879, 5001, '7/1/2004', 1)
insert customer_salesrep values (267, 5009, '1/1/2004', 1)
insert customer_salesrep values (267, 5010, '1/1/2004', 1)
go
--drop function utvf_whichboss
CREATE FUNCTION utvf_whichboss(@.lookupdate datetime) returns table as return
--
-- This function returns the list of customer accounts
-- that are owned by a given manager at any time. Caller
-- still needs to find the maximum dateassigned since all
-- previous owning managers will also show up.
SELECT
csr.salesrepno AS mgrno, csr.dateassigned, csr.custno
FROM
customer_salesrep csr
JOIN salesrep sr ON sr.salesrepno = csr.salesrepno
WHERE
sr.joblevel = 10
AND dateassigned <= @.lookupdate
GO
--drop function utvf_subs
CREATE FUNCTION utvf_subs( @.mgrno int, @.lookupdate datetime) RETURNS TABLE
AS RETURN
--
-- this function returns all of the rows that match a given mgrno and within
-- the lookupdate range. Caller still must find the max(dateassigned) since
-- it does not track duplicates (change of ownership)
SELECT csr2.custno, csr2.salesrepno, csr2.ownership, csr2.dateassigned
FROM
customer_salesrep csr2
join salesrep sr2 on sr2.salesrepno = csr2.salesrepno
WHERE csr2.custno IN
(
SELECT b.custno
FROM utvf_whichboss(@.lookupdate) b
WHERE b.dateassigned = (
SELECT max(dateassigned)
FROM utvf_whichboss(@.lookupdate)
WHERE custno = b.custno
)
AND b.mgrno = @.mgrno
)
AND sr2.joblevel = 5
AND csr2.dateassigned <= @.lookupdate
GO
--drop procedure usp_getsubs
CREATE PROCEDURE usp_getsubs
@.mgrno INT,
@.lookupdate DATETIME
AS
--
-- This sproc takes all of the rows that belong to
-- a given manager at a certain time and prunes out the
-- duplicates (i.e. customers who's owning subordinate have
-- changed.) It should only return the current subordinate
SELECT s.custno, s.salesrepno, s.ownership
FROM utvf_subs(@.mgrno, @.lookupdate) s
WHERE
s.dateassigned = (
SELECT MAX(dateassigned)
FROM utvf_subs(@.mgrno, @.lookupdate)
WHERE custno = s.custno
)
ORDER BY s.dateassigned
GO
-- Sample tvf output: note that all of the
-- rows are present, even though the ownership
-- has changed to a new subordinate.
SELECT * FROM utvf_subs(5000, '3/1/2004')
GO
-- sample sproc output: these are the samples in your
-- original mail. They all output as you described
-- but I could not preserve your ordering since it does
-- not appear to be encoded in your data anywhere
EXEC usp_getsubs 5000, '1/1/2004'
GO
EXEC usp_getsubs 5000, '3/1/2004'
GO
EXEC usp_getsubs 5000, '7/1/2004'
GO