SQL Help 2

Hi David,

Thanks for your reply. That is giving me people who have several orders adding up to 5 perfs, not just one order with 5 perfs. What do you think?

Gloria,

What I would do is focus on getting the correct set of performance lines for each customer. That is shows in the target season OR one of the two other shows. Then look for more than 5 of that set. 

For Example:

Select @constituency, o.customer_no 

from t_sub_lineitem s 

join t_lineitem l on s.li_seq_no=l.li_seq_no 

join t_order o on l.order_no=o.order_no

join t_perf p on l.perf_no = p.perf_no

WHERE o.order_dt BETWEEN '2010/07/01' AND '2010/09/08' 

AND l.tot_pur_amt > 0

AND (p.season = @season -- All shows in this season PLUS

         OR  p.perf_no in (1,2)   -- the other show perf_no's

group by o.customer_no

HAVING COUNT(l.perf_no) > 5

Hey Levi, the answer is no, that didn't work but it was a good thought! 

I also tried to include the prod_season_no instead of season 165 and including the perf numbers of those two other shows. This almost works but I"m getting people who have 5+ shows if they include the two other perfs but not the perfs in season 165. So a correct order would be 5+ Flynn Season shows, that 5+ can include those two perfs but the Flynn shows have to make up the other shows included in the 5+. What I'm getting are orders that have the two perfs included in a total count of 5+perfs, but the other shows on the order are not Flynn shows.

This is what I have now. Anyone have any other ideas?

delete from tx_const_cust where constituency=@constituency
Inserttx_const_cust(constituency, customer_no)
Select@constituency, o.customer_no from t_sub_lineitem s 
join t_lineitem l on s.li_seq_no=l.li_seq_no and l.order_no=s.order_no
join t_order o on l.order_no=o.order_no
WHERE o.order_dt BETWEEN '2010/07/01' AND '2010/09/08' 
AND l.tot_pur_amt > 0
AND EXISTS(select 1 from dbo.T_LINEITEM l (NOLOCK)  
Where l.order_no = o.order_no   
HAVING count(distinct convert(char(12), perf_no)) BETWEEN 5 AND 99 )
AND l.perf_no IN (SELECT p.perf_no FROM dbo.VS_PERF p
WHERE p.prod_season_no IN (6068,6069,6337))
and o.customer_no not in(select customer_no from tx_const_cust where 
 constituency = @constituency) and customer_no>0  group by customer_no

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Hi David,

Thanks for your reply. That is giving me people who have several orders adding up to 5 perfs, not just one order with 5 perfs. What do you think?

Gloria,

What I would do is focus on getting the correct set of performance lines for each customer. That is shows in the target season OR one of the two other shows. Then look for more than 5 of that set. 

For Example:

Select @constituency, o.customer_no 

from t_sub_lineitem s 

join t_lineitem l on s.li_seq_no=l.li_seq_no 

join t_order o on l.order_no=o.order_no

join t_perf p on l.perf_no = p.perf_no

WHERE o.order_dt BETWEEN '2010/07/01' AND '2010/09/08' 

AND l.tot_pur_amt > 0

AND (p.season = @season -- All shows in this season PLUS

         OR  p.perf_no in (1,2)   -- the other show perf_no's

group by o.customer_no

HAVING COUNT(l.perf_no) > 5

Hey Levi, the answer is no, that didn't work but it was a good thought! 

I also tried to include the prod_season_no instead of season 165 and including the perf numbers of those two other shows. This almost works but I"m getting people who have 5+ shows if they include the two other perfs but not the perfs in season 165. So a correct order would be 5+ Flynn Season shows, that 5+ can include those two perfs but the Flynn shows have to make up the other shows included in the 5+. What I'm getting are orders that have the two perfs included in a total count of 5+perfs, but the other shows on the order are not Flynn shows.

This is what I have now. Anyone have any other ideas?

delete from tx_const_cust where constituency=@constituency
Inserttx_const_cust(constituency, customer_no)
Select@constituency, o.customer_no from t_sub_lineitem s 
join t_lineitem l on s.li_seq_no=l.li_seq_no and l.order_no=s.order_no
join t_order o on l.order_no=o.order_no
WHERE o.order_dt BETWEEN '2010/07/01' AND '2010/09/08' 
AND l.tot_pur_amt > 0
AND EXISTS(select 1 from dbo.T_LINEITEM l (NOLOCK)  
Where l.order_no = o.order_no   
HAVING count(distinct convert(char(12), perf_no)) BETWEEN 5 AND 99 )
AND l.perf_no IN (SELECT p.perf_no FROM dbo.VS_PERF p
WHERE p.prod_season_no IN (6068,6069,6337))
and o.customer_no not in(select customer_no from tx_const_cust where 
 constituency = @constituency) and customer_no>0  group by customer_no

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!