Microsoft 2005 T-SQL Help

whorsfall
whorsfall used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
can you give more detail on this.

Commented:
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
F IgorDeveloper

Commented:
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)


Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

F IgorDeveloper

Commented:
Note that for Q2,

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

Author

Commented:
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)

Author

Commented:
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.
select distinct SiteCode from table1 where  Targetd = 1 and Installed = 1

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial