Microsoft 2005 T-SQL Help

Hi,

I am after a T-SQL (Microsoft SQL 2005) that can do the following.

Ok Table1 Contains

SiteCode Package ID Targeted Installed
--------- ---------- -------- ---------
AA1 P1 1 0
AA1 P2 1 1
AA1 P3 1 0
AA1 P4 1 1

AA2 P1 1 1
AA2 P2 0 0
AA2 P3 1 1

AA3 P1 1 1
AA3 P3 1 1
AA4 P4 1 1

Ok I would like to work out how to do SQL that does the following:

1. Return the sitecode where there any of the result set is Targetd = 1 and Installed = 0.
So the result set I would back is a single row being:

AA1

2. Return the sitecode where only if every record that every Row that is Targeted = 1
also has Installed = 1. (So if there is anything that is Targeted = 1 and Installed = 0
it would be eliminated). So the result I would get back here would be

AA2

3. Return the sitecode where only where every row must match Targeted = 1 and Installed = 1
So the result returned would be:

AA3

Thanks,

Ward.

Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008

Last Comment

anillucky31

8/22/2022 - Mon

anillucky31

5/13/2011

can you give more detail on this.

Ebcidic

5/14/2011

hi,
I have created a query instead of table to try this out.

QUESTION 1:


SELECT Q.SITECODE FROM 
(
SELECT 'AA1' AS SITECODE, 'P1' AS PACKAGEID, 1 AS TARGETED, 0 AS INSTALLED
UNION ALL
SELECT 'AA1' AS SITECODE, 'P2' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA1' AS SITECODE, 'P3' AS PACKAGEID, 1 AS TARGETED, 0 AS INSTALLED
UNION ALL
SELECT 'AA1' AS SITECODE, 'P4' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA2' AS SITECODE, 'P1' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA2' AS SITECODE, 'P2' AS PACKAGEID, 1 AS TARGETED, 0 AS INSTALLED
UNION ALL
SELECT 'AA2' AS SITECODE, 'P3' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA3' AS SITECODE, 'P1' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA3' AS SITECODE, 'P3' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA4' AS SITECODE, 'P4' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
)Q

WHERE Q.TARGETED=1 AND Q.INSTALLED=0
GROUP BY SITECODE



QUESTION 2:

SELECT Q.SITECODE FROM 
(
SELECT 'AA1' AS SITECODE, 'P1' AS PACKAGEID, 1 AS TARGETED, 0 AS INSTALLED
UNION ALL
SELECT 'AA1' AS SITECODE, 'P2' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA1' AS SITECODE, 'P3' AS PACKAGEID, 1 AS TARGETED, 0 AS INSTALLED
UNION ALL
SELECT 'AA1' AS SITECODE, 'P4' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA2' AS SITECODE, 'P1' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA2' AS SITECODE, 'P2' AS PACKAGEID, 0 AS TARGETED, 0 AS INSTALLED
UNION ALL
SELECT 'AA2' AS SITECODE, 'P3' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA3' AS SITECODE, 'P1' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA3' AS SITECODE, 'P3' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA4' AS SITECODE, 'P4' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
)Q
GROUP BY Q.SITECODE
HAVING COUNT(1)>SUM(Q.TARGETED)



QUESTION 3:


SELECT Q.SITECODE FROM 
(
SELECT 'AA1' AS SITECODE, 'P1' AS PACKAGEID, 1 AS TARGETED, 0 AS INSTALLED
UNION ALL
SELECT 'AA1' AS SITECODE, 'P2' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA1' AS SITECODE, 'P3' AS PACKAGEID, 1 AS TARGETED, 0 AS INSTALLED
UNION ALL
SELECT 'AA1' AS SITECODE, 'P4' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA2' AS SITECODE, 'P1' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA2' AS SITECODE, 'P2' AS PACKAGEID, 0 AS TARGETED, 0 AS INSTALLED
UNION ALL
SELECT 'AA2' AS SITECODE, 'P3' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA3' AS SITECODE, 'P1' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA3' AS SITECODE, 'P3' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
UNION ALL
SELECT 'AA4' AS SITECODE, 'P4' AS PACKAGEID, 1 AS TARGETED, 1 AS INSTALLED
)Q 
GROUP BY Q.SITECODE
HAVING COUNT(1)=SUM(Q.TARGETED) AND COUNT(1)=SUM(Q.INSTALLED)

Open in new window

Please try and let me know

Francisco Igor

5/14/2011

Q1:

select distinct SiteCode
from table1
where Targetd = 1 and Installed = 0

Q2:

select distinct SiteCode
from table1
where Targetd = 1 and Installed = 1

Q3:
select SiteCode
from table1
group by SiteCode
having count(*)=sum(Targetd) and count(*)=sum(Installed)

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

Francisco Igor

5/14/2011

Note that for Q2,

The result will be
AA2
AA3 (also have all Targeted = 1 with installed=1)

whorsfall

5/15/2011

ASKER

faigor,

Thanks for that it almost all works.
The only issue is for Q2:

select distinct SiteCode from table1 where Targetd = 1 and Installed = 1

This returns:

AA1 <-- Bad
AA2
AA3
AA4

So based on the data I would hope it would return AA2,AA3 and AA4. (Opps, I had an error in my expected results). The query you had does not eliminate where Targeted = 1 and Installed = 0 which is why AA1 is still coming up.

Thanks,

Ward.

Thanks,

Ward

anillucky31

5/15/2011

For Q2 use this

select distinct SiteCode from table1 where Targetd = 1 and Installed = 1

where siteCode not in (select SiteCode from table1 where Targetd = 1 and Installed = 0)

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

whorsfall

5/15/2011

ASKER

Hi,

Thanks for that are u sure the synatx is correct I got an error

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'where'.

thanks,

Ward.

ASKER CERTIFIED SOLUTION

anillucky31

5/15/2011

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.