Avatar of whorsfall
whorsfall
Flag for Australia 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.
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
anillucky31

8/22/2022 - Mon
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)


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

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)
⚡ 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

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

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.