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!
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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)
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)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial