Hello,

 I'm trying to accomplish the below requirement. Please help.

declare @emp_pct_by_prod table 
( 
 prod_cd varchar(20),
 emp_id int, 
 begin_date datetime,
 end_date datetime,
 pct int
) 

declare @trans_by_prod table 
( 
 prod_cd varchar(20),
 conf_date datetime,
 buyer_name varchar(100)
) 

declare @start_date datetime
declare @stop_date datetime 

insert into @emp_pct_by_prod values ('abc123',1,'07/10/2015','07/11/2015',20) 
insert into @emp_pct_by_prod values ('abc123',2,'07/10/2015','07/11/2015',80) 
insert into @emp_pct_by_prod values ('abc123',3,'07/11/2015','07/12/2015',20) 
insert into @emp_pct_by_prod values ('abc123',2,'07/12/2015','07/30/2015',100) 


insert into @trans_by_prod values ('abc123','07/10/2015','Rich') 
insert into @trans_by_prod values ('abc123','07/11/2015','John') 
insert into @trans_by_prod values ('abc123','07/12/2015','Mark') 
insert into @trans_by_prod values ('abc123','07/29/2015','Shan') 

Case 1: 

set @start_date= '07/10/2015'
set @stop_date= '07/29/2015'

output: 

emp_id     conf_date         pct      buyer_name  
1          07/10/2015        20       Rich
2          07/10/2015        80       Rich
3          07/11/2015        20       John
2          07/11/2015        80       John 
2          07/12/2015       100       Mark
2          07/29/2015       100       Shan


Case 2: 

set @start_date= '07/11/2015'
set @stop_date= '07/11/2015'

output: 

emp_id     conf_date         pct      buyer_name  
3          07/11/2015        20       John
2          07/11/2015        80       John 


Case 3: 

set @start_date= '07/10/2015'
set @stop_date= '07/10/2015'

output: 

emp_id     conf_date         pct      buyer_name  
1          07/10/2015        20       Rich
2          07/10/2015        80       Rich

I think there should be an emp ID in @trans_by_prod table to join both tables logically there should be a relationship

if it is there let me know its simple

You're not going to get that to work with what you have. Try this:

declare @emp_pct_by_prod table 
( 
 prod_cd varchar(20),
 emp_id int, 
 pctdate datetime,
 pct int
) 

declare @trans_by_prod table 
( 
 prod_cd varchar(20),
 conf_date datetime,
 buyer_name varchar(100)
) 

insert into @emp_pct_by_prod values ('abc123',1,'07/10/2015',20) 
insert into @emp_pct_by_prod values ('abc123',2,'07/10/2015',80) 
insert into @emp_pct_by_prod values ('abc123',3,'07/11/2015',20) 
insert into @emp_pct_by_prod values ('abc123',3,'07/11/2015',80) 
insert into @emp_pct_by_prod values ('abc123',2,'07/12/2015',100) 
insert into @emp_pct_by_prod values ('abc123',2,'07/13/2015',100)
insert into @emp_pct_by_prod values ('abc123',2,'07/14/2015',100)
insert into @emp_pct_by_prod values ('abc123',2,'07/15/2015',100)
insert into @emp_pct_by_prod values ('abc123',2,'07/16/2015',100)
insert into @emp_pct_by_prod values ('abc123',2,'07/17/2015',100)
insert into @emp_pct_by_prod values ('abc123',2,'07/18/2015',100)
insert into @emp_pct_by_prod values ('abc123',2,'07/19/2015',100)
insert into @emp_pct_by_prod values ('abc123',2,'07/20/2015',100)
insert into @emp_pct_by_prod values ('abc123',2,'07/21/2015',100)
insert into @emp_pct_by_prod values ('abc123',2,'07/22/2015',100)
insert into @emp_pct_by_prod values ('abc123',2,'07/23/2015',100)
insert into @emp_pct_by_prod values ('abc123',2,'07/24/2015',100)
insert into @emp_pct_by_prod values ('abc123',2,'07/25/2015',100)
insert into @emp_pct_by_prod values ('abc123',2,'07/26/2015',100)
insert into @emp_pct_by_prod values ('abc123',2,'07/27/2015',100)
insert into @emp_pct_by_prod values ('abc123',2,'07/28/2015',100)
insert into @emp_pct_by_prod values ('abc123',2,'07/29/2015',100)
insert into @emp_pct_by_prod values ('abc123',2,'07/30/2015',100)


insert into @trans_by_prod values ('abc123','07/10/2015','Rich') 
insert into @trans_by_prod values ('abc123','07/11/2015','John') 
insert into @trans_by_prod values ('abc123','07/12/2015','Mark') 
insert into @trans_by_prod values ('abc123','07/29/2015','Shan') 


select a.emp_id, b.conf_date, a.pct, b.buyer_name
from @emp_pct_by_prod a
inner join @trans_by_prod b on (a.prod_cd = b.prod_cd) and (a.pctdate = b.conf_date)
where pctdate between '2015-07-10' and '2015-07-29'

select a.emp_id, b.conf_date, a.pct, b.buyer_name
from @emp_pct_by_prod a
inner join @trans_by_prod b on (a.prod_cd = b.prod_cd) and (a.pctdate = b.conf_date)
where pctdate between '2015-07-11' and '2015-07-11'

select a.emp_id, b.conf_date, a.pct, b.buyer_name
from @emp_pct_by_prod a
inner join @trans_by_prod b on (a.prod_cd = b.prod_cd) and (a.pctdate = b.conf_date)
where pctdate between '2015-07-10' and '2015-07-10'

Actually the first dataset will be as below

declare @emp_pct_by_prod table 
( 
 prod_cd varchar(20),
 emp_id int, 
 begin_date datetime,
 end_date datetime,
 pct int,
  buyer_name varchar(100)
) 




insert into @emp_pct_by_prod values ('abc123',1,'07/10/2015','07/11/2015',20,'Rich') 
insert into @emp_pct_by_prod values ('abc123',2,'07/10/2015','07/11/2015',80,'Rich') 
insert into @emp_pct_by_prod values ('abc123',2,'07/10/2015','07/11/2015',80,'John') 
insert into @emp_pct_by_prod values ('abc123',3,'07/11/2015','07/12/2015',20,'John')  
insert into @emp_pct_by_prod values ('abc123',2,'07/12/2015','07/30/2015',100,'Mark')  
insert into @emp_pct_by_prod values ('abc123',2,'07/12/2015','07/30/2015',100,'Shan') 

• Edited by guest369 13 hours 52 minutes ago

use between clause in your where condition section to filter date range or you can use startdate >= and end date<= condition as well

Actually the first dataset will be as below

declare @emp_pct_by_prod table 
( 
 prod_cd varchar(20),
 emp_id int, 
 begin_date datetime,
 end_date datetime,
 pct int,
  buyer_name varchar(100)
) 




insert into @emp_pct_by_prod values ('abc123',1,'07/10/2015','07/11/2015',20,'Rich') 
insert into @emp_pct_by_prod values ('abc123',2,'07/10/2015','07/11/2015',80,'Rich') 
insert into @emp_pct_by_prod values ('abc123',2,'07/10/2015','07/11/2015',80,'John') 
insert into @emp_pct_by_prod values ('abc123',3,'07/11/2015','07/12/2015',20,'John')  
insert into @emp_pct_by_prod values ('abc123',2,'07/12/2015','07/30/2015',100,'Mark')  
insert into @emp_pct_by_prod values ('abc123',2,'07/12/2015','07/30/2015',100,'Shan') 

You'll want to change this to what I posted above, which reports exactly what you are looking for.

Adding the buyer name to the @emp_pct_by_prod table does not make sense. What happens when someone else makes a buy on 7/30/2015? Does 'Shan' get overwritten with 'John'? This schema is broken and will not work.

In SQL Server and other relational databases you need to join tables on related columns. Since Shan is the buyer for 7/29 and there is no record related to that for start or end date then there is nothing to join on. You should instead have a record for each day and not bother with the range in your table.

Table "@emp_pct_by_prod " holds the commission of the sale for each emp .

Table "@trans_by_prod " holds all the transactions of the sale.

Answer to your below question  : No. New record will be created as below.

insert into @trans_by_prod values ('abc123','07/30/2015','XYZ')

What happens when someone else makes a buy on 7/30/2015? Does 'Shan' get overwritten with 'John'?

• Edited by guest369 12 hours 46 minutes ago

Table "@emp_pct_by_prod " holds the commission of the sale for each emp .

Table "@trans_by_prod " holds all the transactions of the sale.

Answer to your below question  : No. New record will be created as below.

insert into @trans_by_prod values ('abc123','07/30/2015','XYZ')

What happens when someone else makes a buy on 7/30/2015? Does 'Shan' get overwritten with 'John'?

You need a new schema. The one you have is lacking and will continue to cause problems like this one. Think to yourself: "Why would I want to have a table that has a commission of a sale and then give it a range of dates when the sale table has a single date?"

The schema in it's current form is not right and that is exactly why these problems are here.

Create new tables or alter the existing ones to meet your needs. The tables should easily relate to each other if you plan to join them. 

When a sale is made the commission on that sale is recorded along with the sale. There is no purpose in having a date range for the commission if it truly is a 1:1 relationship with a sale that takes place on a single date.

I suggest you look at some sample databases like Adventureworks and determine how sales may be recorded in a database.

Commission is not a 1:1 relationship with a sale. The table "@emp_pct_by_prod" has the commissions of each emp on a prod and changes can be made on any day. 

The catch here is, agent can assign the date as a begin_date or end_date.  See  '07/11/2015' in the table. The precedence will go for the begin_date . I can use prod_cd to join the table as it is the common column in both the tables.

If I use cursor or looping concept I can accomplish what I want with the existing schema. I'm looking for a efficient way to handle this .  

• Edited by guest369 11 hours 53 minutes ago

Hi guest369,

Is it possible that there would be more than one row for a buyer in @trans_by_prod?


In such a case, how to ensure that the row in @emp_pct_by_prod belongs to which row in @trans_by_prod?
IMHO, it is necessary an extra primay key in @trans_by_prod referenced by @emp_pct_by_prod belongs.

declare @emp_pct_by_prod table 
( 
 prod_cd varchar(20),
 emp_id int, 
 begin_date datetime,
 end_date datetime,
 pct int,
 buyer_name varchar(100),
 trans_id INT
) 




insert into @emp_pct_by_prod values ('abc123',1,'07/10/2015','07/11/2015',20,'Rich',1) 
insert into @emp_pct_by_prod values ('abc123',2,'07/10/2015','07/11/2015',80,'Rich',1) 
insert into @emp_pct_by_prod values ('abc123',2,'07/10/2015','07/11/2015',80,'John',2) 
insert into @emp_pct_by_prod values ('abc123',3,'07/11/2015','07/12/2015',20,'John',2)  
insert into @emp_pct_by_prod values ('abc123',2,'07/12/2015','07/30/2015',100,'Mark',3)  
insert into @emp_pct_by_prod values ('abc123',2,'07/12/2015','07/30/2015',100,'Shan',4) 

declare @trans_by_prod table 
( 
 prod_cd varchar(20),
 conf_date datetime,
 buyer_name varchar(100),
 trans_Id INT
) 

insert into @trans_by_prod values ('abc123','07/10/2015','Rich',1) 
insert into @trans_by_prod values ('abc123','07/11/2015','John',2) 
insert into @trans_by_prod values ('abc123','07/12/2015','Mark',3) 
insert into @trans_by_prod values ('abc123','07/13/2015','Shan',5) 
insert into @trans_by_prod values ('abc123','07/29/2015','Shan',4) 

declare @start_date datetime
declare @stop_date datetime 

--The same query can be applied to various cases
--Case 1: 
set @start_date= '07/10/2015'
set @stop_date= '07/29/2015'
SELECT e.* FROM @trans_by_prod t INNER JOIN @emp_pct_by_prod e
									ON t.trans_id=e.trans_id
WHERE t.conf_date BETWEEN @start_date AND @stop_date


--Case 2: 

set @start_date= '07/11/2015'
set @stop_date= '07/11/2015'

SELECT e.* FROM @trans_by_prod t INNER JOIN @emp_pct_by_prod e
									ON t.trans_id=e.trans_id
WHERE t.conf_date BETWEEN @start_date AND @stop_date
 
--Case 3: 

set @start_date= '07/10/2015'
set @stop_date= '07/10/2015'
SELECT e.* FROM @trans_by_prod t INNER JOIN @emp_pct_by_prod e
									ON t.trans_id=e.trans_id
WHERE t.conf_date BETWEEN @start_date AND @stop_date
 

If you have any question, feel free to let me know.