Link to home
Create AccountLog in
Avatar of whorsfall
whorsfallFlag 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.
Avatar of anillucky31
anillucky31
Flag of India image

can you give more detail on this.
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
Avatar of 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)


Note that for Q2,

The result will be
AA2
AA3 (also have all Targeted = 1 with installed=1)
Avatar of 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
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)
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
Avatar of anillucky31
anillucky31
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account