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 ")".
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?
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of David Woodall Sent: Friday, October 15, 2010 10:55 AM To: Gloria Ormsby Subject: Re: [Tessitura Technical Forum] SQL Help 2
OR p.perf_no in (1,2) -- the other show perf_no's
From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com> Sent: 10/15/2010 8:48:40 AM
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!
I believe you will also need to group by order_no for this case.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Gloria Ormsby Sent: Friday, October 15, 2010 11:30 AM To: Guy, Jackie Subject: RE: [Tessitura Technical Forum] SQL Help 2
I believe the following will work, I highlighted the changes.
Select distinct @constituency, o.customer_no
OR p.perf_no in (1,2) ) -- the other show perf_no's
group by o.customer_no, o.order_no
HAVING COUNT(l.perf_no) >= 5
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Gloria Ormsby Sent: Friday, October 15, 2010 12:30 PM To: llindvall@cfl.rr.com Subject: RE: [Tessitura Technical Forum] SQL Help 2
I didn't realize the performances all had to be ordered in a single order. If that is the case, then yes, it would be necessary to group by order_no as well.
David
It’s not respecting the number of perfs requirement. I’m getting orders that have just 4 perfs.
One other thing I thought might be easier is to create a view that has all the season shows AND the other two perfs. Then I can write the procedure around that view. What do you think of that idea?
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Lisa Lindvall Sent: Friday, October 15, 2010 1:25 PM To: Gloria Ormsby Subject: RE: [Tessitura Technical Forum] SQL Help 2
Try this:
-- begin code
declare @perfs table (perf_no int)
declare @season int
declare @order_start datetime
declare @order_end datetime
declare @constituency int
-- initialize variables
select @season = 40
select @order_start = '2010-07-01 00:00:00'
select @order_end = '2010-10-01 00:00:00'
select @constituency = 8
-- get all of the performances for the season
insert into @perfs (perf_no)
select perf_no
from t_perf with (nolock)
where season = @season
-- add any rogue performances to the list
select 849 -- rogue perf
union
select 856 -- rogue perf
select 909 -- rogue perf
-- Using a common Table Expression, pull a list of all of the customers who have any
-- of the performances we care about
-- distinct gives us one row per performance
;with SummaryCTE (customer_no, perf_no) as (
select distinct
o.customer_no,
s.perf_no
from t_sub_lineitem s with (nolock)
join t_order o with (nolock) on s.order_no = o.order_no
join @perfs p on s.perf_no = p.perf_no
where
o.order_dt between @order_start and @order_end
and s.sli_status in (3, 6, 12) -- seated, paid; unseated, paid; ticketed, paid
insert tx_const_cust (constituency, customer_no)
-- Pull anyone from our *temp table* that has five or more
-- performances
select
@constituency,
customer_no
from SummaryCTE
group by customer_no
having count(perf_no) >= 5
-- end code
I’m teaching myself Common Table Expressions after seeing a couple of postings on them on SQLServerCentral.com. They are temporary tables/views that exist for a single select, and can be used to do all sorts of interesting things.
-steve carlock
Information Technology Manager
The Granada
(805) 899-3000 x 111 (phone)
(805) 899-3081 (fax)
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Gloria Ormsby Sent: Friday, October 15, 2010 10:45 AM To: Steve Carlock Subject: RE: [Tessitura Technical Forum] SQL Help 2
Thanks everyone for your help. I ended up creating a view that has all the relevant perfs. I had to move the perf selection on that view inside the EXISTS clause and I had to add the order_no to the GROUP BY clause. Thanks again and have a great weekend.
AND EXISTS(select 1 from dbo.T_LINEITEM l (NOLOCK)
Where l.order_no = o.order_no AND perf_no IN (SELECT perf_no FROM lv_season_perfs)
HAVING count(distinct perf_no) >= 5 )
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of David Woodall Sent: Friday, October 15, 2010 1:40 PM To: Gloria Ormsby Subject: RE: [Tessitura Technical Forum] SQL Help 2
From: Lisa Lindvall <bounce-lisalindvall1897@tessituranetwork.com> Sent: 10/15/2010 12:22:49 PM