I am building a query to show me all people who ordered 5+ perfs during our subscription period. Two of the perfs in our subscriptions are in a different season because they are co-pros. I want the query to pull orders that have 5+ perfs from our Flynn season but if the order also contains those two other perfs as part of the 5+ then I want it to pull those too. I've got my "and/or" logic messed up because I'm getting orders that have those two rogue perfs on orders of 5+ shows but the other shows on the order are not in our season. This is what I have. Can anybody help?
Insert
tx_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.season = 165 OR p.perf_no IN(6352,6353)
)
Gloria,
What happens if you move that last AND statement into the WHERE instead of the HAVING clause? I thnk that is where your trouble lies. My old nemesis the parenthesis also look to be rearing their head here. Try something like:
WHERE o.order_dt BETWEEN ....... AND (l.perf_no In (select p.perf_no from vs_perf p where p.season = 165) OR l.perf_no in (6352,6353))
And for my own question, since I've never tried it. Can you include a select statement as part of IN?
For instance:
l.perf_no IN ((select p.perf_no from vs_perf where p.season = 165), 6352,6353)
Hmmm.... Now I want to go try that out.
EDIT: Dang it. Forgot parenthesis again. Went back and added them. Some days I'm amazed they give me access to the DB.
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=@constituencyInsert tx_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_nojoin t_order o on l.order_no=o.order_noWHERE o.order_dt BETWEEN '2010/07/01' AND '2010/09/08' AND l.tot_pur_amt > 0AND 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 pWHERE 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
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
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 (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
EDIT - Fixed a, yes you guessed it, missing ")".