whorsfall
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.
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.
can you give more detail on this.
hi,
I have created a query instead of table to try this out.
Please try and let me know
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)
Please try and let me know
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)
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)
The result will be
AA2
AA3 (also have all Targeted = 1 with installed=1)
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
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)
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)
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.