asked on

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.

anillucky31

can you give more detail on this.

Ebcidic

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

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)

Francisco Igor

Note that for Q2,

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

whorsfall

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

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)

whorsfall

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial