Help pls? Out of my depth... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help pls? Out of my depth...

Author

Topic

dakeyras
Starting Member

4 Posts

Posted - 2007-03-22 : 09:59:25

Hi there, I'm new here and quite green when it comes to SQL in general - I havent used it in any depth for some years and, in the absence of our DBA I've been asked to produce a report... I'm just using query analyser to extract some data as I don't have access to Crystal or anything similar...

I have a table laid out as follows - the PK is REC_ID. It basically stores all the contract start dates for each client we deal with.

RECID------Company-----Product-----------Date

445as------ABCLtd------ICT-Hardware------2007-01-14 00:00:00.000
253s1------ABCLtd------SOFT-Mainstream---2007-01-18 00:00:00.000
567o8------ABCLtd------SOFT-Maintenance--2007-01-18 00:00:00.000
809b9------Gen4Ltd-----ICT-Hardware------2007-01-14 00:00:00.000
098xc------Gen4Ltd-----SOFT-Maintenance--2007-01-18 00:00:00.000
551df------SteetsPLC---ICT-Hardware------2007-01-14 00:00:00.000
919sd------SteetsPLC---SOFT-Upgrade------2007-01-18 00:00:00.000
010qr------SteetsPLC---SOFT-Maintenance--2007-01-18 00:00:00.000
124vv------SteetsPLC---PERS-Allocations--2007-01-18 00:00:00.000

I can easily extract, for example, how many companies have taken up individual services, or many times individual services have been employed... what I'm having difficulty with is trying to extract, for example, all those companies who have had say taken up ICT AND SOFT services, whether explicit or using a LIKE statement.

For instance, if I wanted to find all those companies that had taken up both 'ICT-Hardware' AND 'Soft-Maintenance' and literally just get back the results...

Company

ABCLtd
SteetsPLC

Or a numeric result(in this case 2) would be ok...

How would I do that?

Sorry, this may seem to be very obvious to you but I'm at a loss...

Hope you can help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 10:04:51

[code]
select Company
from yourtable
where Product in ('ICT-Hardware', 'SOFT-Mainstream')
group by Company
having count(*) = 2
[/code]

dakeyras
Starting Member

4 Posts

Posted - 2007-03-22 : 10:06:41

Hey Khtan! Thanks for the fast response!

if I wanted to find those that are like '%SOFT%' for example, would I still be able to use the same syntax? (as in the IN statement)?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 10:13:20

[code]
select Company
from yourtable
where Product = 'ICT-Hardware'
or Product like '%SOFT%'
group by Company
having count(*) = 2
[/code]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-22 : 10:15:00

quote:

Originally posted by khtan


select Company
from   yourtable
where  Product = 'ICT-Hardware'
or     Product like '%SOFT%'  -- If SOFT is going to be prefix
group by Company
having count(*) = 2

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dakeyras
Starting Member

4 Posts

Posted - 2007-03-22 : 10:28:54

Hmmm, they seem to be bringing back innacuracies.

For example - in the original post, I want to isolate all companies that have been provided with BOTH services, not either / or. It's essential that I bring back those that have only had ALL the services specified in the query.

The initial query is bringing back companies that have sometimes had only one of these services? Any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-22 : 11:22:57

select company from (
select 1 as t, Company from yourtable where Product = 'ICT-Hardware'
union
select 0, Company from yourtable where Product = 'SOFT-Mainstream'
) AS x
group by Company
having count(*) = 2

Peter Larsson
Helsingborg, Sweden

dakeyras
Starting Member

4 Posts

Posted - 2007-03-22 : 13:23:54

Thank you Peso!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources