October 15, 2010 at 10:45 am
We sell tickets to performances. I am writing code to find all records where someone ordered 5+ performances on one order. Performances are grouped into "seasons." All performances but two are in the same season. The other two performances are in a different season. The code I have is finding the orders that all belong to the same season and add up to 5 perfs. But it is also finding orders that have the other two perfs in it, plus perfs that are not in the season I need. Can someone help? This is the code I have:
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
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 = 165 -- All shows in this season PLUS
OR p.perf_no in (6352,6353) ) -- the other show perf_no's
group by o.customer_no
HAVING COUNT(l.perf_no) > 5
October 15, 2010 at 10:50 am
It would help if you could give us some sample data ( in a readily consumable format).
Please read through the link in my signature line and post table defs and sample data as shown in the article.
October 15, 2010 at 10:51 am
If you don't want the other two performances in the query, why does it have, "OR p.perf_no in (6352,6353) " in the Where clause?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 15, 2010 at 11:00 am
I do want the two perfs but only if they are included in an order of 5+ perfs that contains perfs from the other season.
October 15, 2010 at 11:14 am
Change it to:
OR p.perf_no in (6352,6353)
AND exists
(select 1
from t_sub_lineitem s2
join t_lineitem l2 on s2.li_seq_no=l2.li_seq_no
join t_order o2 on l2.order_no=o2.order_no
join t_perf p2 on l2.perf_no = p2.perf_no
where o2.customer_no = o.customer_no
and p2.season = 165)
That will check for there being an order in the season you want. You might need to refine the exists command a bit to narrow it down even more, but this should get you going in the right direction.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 15, 2010 at 11:36 am
You need to include a CASE statement in your HAVING clause.
Select @constituency, o.customer_no
, Count(l.per_no) AS AllPerf
, COUNT(CASE p.season WHEN 165 THEN l.perf_no END) AS CurSeason
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 = 165 -- All shows in this season PLUS
OR p.perf_no in (6352,6353) ) -- the other show perf_no's
group by o.customer_no
HAVING COUNT(CASE p.season WHEN 165 THEN l.perf_no END) > 5
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 15, 2010 at 12:24 pm
I've tried all your suggestions and I'm still getting the wrong results. I created a view with all the relevant perfs in them (this won't change) and STILL getting the wrong # of perfs returned! It's still returning orders that have some of the right perfs and some wrong perfs when it looks for 5+ perfs.
October 15, 2010 at 12:37 pm
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.:cool:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 15, 2010 at 1:01 pm
You need to separate the two query conditions.
Do you have a "customers" table of some sort? I'm assuming the customer_no column in the orders table refers to something like that.
If so, assuming that table is "t_customer", to follow the rest of your naming convention, try this:
SELECT
customer_no,
(SELECT
COUNT(*)
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 = 165
AND o.customer_no = c.customer_no) AS Season165Total,
(SELECT
COUNT(*)
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.perf_no in (6352, 6353)
AND o.customer_no = c.customer_no) AS OutlierTotal,
(SELECT
COUNT(*)
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 o.customer_no = c.customer_no) AS OverallTotal
FROM
dbo.t_customer
WHERE
(SELECT
COUNT(*)
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 = 165
AND o.customer_no = c.customer_no)
+ (SELECT
COUNT(*)
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.perf_no in (6352, 6353)
AND o.customer_no = c.customer_no) >= 5;
You'll have to play with the Select clause to get exactly what you want, but it should give you the rows you're looking for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 15, 2010 at 1:27 pm
I think I found the problem. Your original query was only grouping on the Customer Number, but you want to group on the Order Number as well. Otherwise the calculations are based on ALL of the customer's orders, not any one order.
Select @constituency, o.customer_no
, Count(l.per_no) AS AllPerf
, COUNT(CASE p.season WHEN 165 THEN l.perf_no END) AS CurSeason
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 = 165 -- All shows in this season PLUS
OR p.perf_no in (6352,6353) ) -- the other show perf_no's
group by o.customer_no, o.order_no
HAVING COUNT(CASE p.season WHEN 165 THEN l.perf_no END) > 5
Without sample data to test on, I can't say for certain, but I believe the HAVING clause that includes a CASE statement will perform much better than using a subquery.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 15, 2010 at 1:30 pm
Thank you everyone for your help. A combination of suggestions has worked. This is what I ended up with. I created a view with all the relevant perfs and moved the perf selection inside the EXISTS clause. Plus added customer_no to the GROUP BY clause. Again...thank you!
Select distinct @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 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 )
group by o.customer_no, o.order_no
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply