August 1, 2005 at 7:35 pm
I have a table with 3 columns. They are QuoteID, Sector Number & Country. For each QuoteID there can be any number of sectors and the country can be any valid country in the datbase. I need a view to return 'Plus VAT' if all the countries for a given QuoteID are England. For Example for the following data,
QuoteID Sector Country
1 1 England
1 2 England
2 1 England
2 2 France
3 1 Germany
The view should look something like this,
QuoteID Vat
1 Plus Vat
2 No
3 No
I just cant think how to get this view to work. Is it possible?! I just cant think how to work it given the fact that there can be any number of sectors for each quote.
Any help greatly appreciated,
Chris Evans
August 1, 2005 at 11:51 pm
Here's one way....(from what I can understand the # of sectors shouldn't really matter) - it's only the quoteid and country that is being checked...
let me know if this is what you're looking for:
Create Table tblQuotes
(QuoteID int,
Sector int,
Country varchar(50))
insert into tblQuotes values(1,1,'England')
insert into tblQuotes values(1,2,'England')
insert into tblQuotes values(2,1,'England')
insert into tblQuotes values(2,2,'France')
insert into tblQuotes values(3,1,'Germany')
SELECT DISTINCT QuoteID, 'Vat' =
CASE
WHEN COUNT(DISTINCT(Country)) = 1 AND COUNT(QuoteID) > 1 AND Country = 'England' THEN 'Plus Vat'
ELSE 'No'
END
FROM tblQuotes
GROUP BY QuoteID, Country
**ASCII stupid question, get a stupid ANSI !!!**
August 2, 2005 at 8:56 am
Took a little bit of modification to get it working but it was pretty much there. Never would have thought of that in a million years though so thanks very much for your help.
Regards,
Chris
August 2, 2005 at 9:02 am
glad you got it working Chris - would you mind posting the modified version ?!
**ASCII stupid question, get a stupid ANSI !!!**
August 2, 2005 at 11:47 am
This is what i'm using. not sure if its the most effective way of working it but does the job.
SELECT DISTINCT QuoteID, 'Plus VAT' AS VAT
FROM (SELECT DISTINCT QuoteID, Country
FROM dbo.tblSchedule
GROUP BY QuoteID, Country) tblVAT
GROUP BY QuoteID
HAVING (COUNT(Country) = 1) AND Country = 'England')
Cheers
Chris
August 3, 2005 at 10:24 am
SELECT DISTINCT QuoteID, Country
FROM dbo.tblSchedule
GROUP BY QuoteID, Country
You don't need the DISTINCT, since you are using GROUP BY.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply