December 5, 2007 at 5:29 am
Hi
I cannot seem to get a query that works with what I need to do.
If I could try and split it down simply...
I have a table with alot of fields...the five I am interested in are
DiscountMatrix *(e.g. CIDG, TID, BIDG)
CustomerGroup *(e.g. BENCH, TURNB)
DiscountGroup *(e.g. BA)
ProductCode *(e.g. AA001, YY999)
ProductGroup *(e.g. BAA, HGG)
Discount *(e.g. 65.00, 34.00, 30.00)
I need to get data from the table based on two criteria...
select Discount from table where DiscountMatrix = 'CIDG' and CustomerGroup = 'BENCH'
and
select Discount from table where DiscountMatrix = 'BIDG' and DiscountGroup = 'BA'
but I only want discounts from the second query where they were not returned by the first query.
It's quite a bit more complicated than that because there are other joined tables and calculated fields, but essentially, that's what I need to do.
Does anyone know how to achieve this pls?
Thank you
BR
December 5, 2007 at 5:57 am
select Discount from table where DiscountMatrix = 'CIDG' and CustomerGroup = 'BENCH'
union
select Discount from table where DiscountMatrix = 'BIDG' and DiscountGroup = 'BA'
N 56°04'39.16"
E 12°55'05.25"
December 5, 2007 at 5:58 am
select Discount from table where DiscountMatrix = 'CIDG' and CustomerGroup = 'BENCH'
select Discount from table where DiscountMatrix = 'BIDG' and DiscountGroup = 'BA'
where not exists (select * from table as x where x.DiscountMatrix = 'CIDG' and x.CustomerGroup = 'BENCH' and x.discount = table.discount)
N 56°04'39.16"
E 12°55'05.25"
December 5, 2007 at 8:21 am
Thx for your replies.
Glad to see I was using the correct examples....Union and not exists doesn't work and here's why...
The first query works on ProductCode (AA001, YY999)
The second query works on ProductGroup (BAA, YYZ)
To further complicate issues, the ProductCodes returned WILL HAVE a ProductGroup.
Also, the table will have data like so...
DiscountMatrix CustomerGroup DiscountGroup ProductCode ProductGroup Discount
CGID BENCH AA001 AAA 64.99
BIDG BA AA001 AAA 50.99
CGID BENCH AA002 AAA 62.99
BIDG BA AA002 AAA 50.99
CGID BENCH AA192 AAA 59.99
BIDG BA AA192 AAZ 40.99
BIDG BA AA193 AAZ 30.99
BIDG BA AA194 AAZ 30.99
And I need to extract ALL the DiscountMatrix = CGID and CustomerGroup = BENCH (these will ALWAYS be higher than where DiscountMatrix = BIDG and DiscountGroup = BA) and, where there are other products not returned in DiscountMatrix = CGID and CustomerGroup = BENCH, then I want those also...so out of the above, I would want the following returned...
DiscountMatrix CustomerGroup DiscountGroup ProductCode ProductGroup Discount
CGID BENCH AA001 AAA 64.99
CGID BENCH AA002 AAA 62.99
CGID BENCH AA192 AAA 59.99
BIDG BA AA193 AAZ 30.99
BIDG BA AA194 AAZ 30.99
Sorry if this is as clear as mud, but I've been racking my brains trying to get something to work, and short of storing all the data in a temporary table and updating each record if I find a discount higher than the one stored, I don't know what else to do...
Thx again for your replies
December 5, 2007 at 8:54 am
All due respect - but what you've described in 4 paragraphs IS what a UNION syntax would do. It would return the DISTINCT values (or in your wording, all of the rows from query 1 and those not previously returned from 2).
If you're looking to find the highest discount of the two - then perhaps UNION the qualifying details, and then run aggregate functions against it.
select ProductCode,
max(discount) as MaxDiscount
from
(
select
productcode,
Discount
from table
where DiscountMatrix = 'CIDG' and CustomerGroup = 'BENCH'
union
select
productcode,
Discount
from table
where DiscountMatrix = 'BIDG' and DiscountGroup = 'BA'
) U
group by productCode
This won't scale incredibly well, but should do okay if you keep it to relatively small datasets. Otherwise, you might do better with temp tables.
[/code]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 6, 2007 at 12:48 am
Thx for the reply Matt
Well, maybe I'm not explaining the data I'm working with very well...it is quite messy (crap in crap out?). Still, it should be do-able.
As for the union, it didn't work. When I read up on it and tried it (before I posted here), it seemed like what I wanted, but I expect the data in the system is in such a way as to need more than a simple union.
I haven't tried you example yet (the select max with the union in the middle) so I will test that and let you know. It may well be what I'm looking for.
To the others who replied here. Thx for taking the time and apologies if I didn't make my requirements as clear as I could've.
Thx to all.
December 6, 2007 at 1:48 am
Is this what you're looking?
SELECTDISTINCT Discount
FROM
WHERE( DiscountMatrix = 'CIDG' and CustomerGroup = 'BENCH' )
OR ( DiscountMatrix = 'BIDG' and DiscountGroup = 'BA' )
--Ramesh
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply