?
Solved

SQL question

Posted on 2011-09-28
5
Medium Priority
?
192 Views
Last Modified: 2012-06-27
I have enclosed an SQL for the most part works well. It should be returning a project record from each database where feild stat = 'A'. The problem I'm running into is that in a set of projects there could be one or two that have a stat value of 'C' for closed instead of 'A' for active.

If just one record for a project returns 'A' I need all three - one from each database

Current example data:

Company   |    Project     |     stat
MF                    AI123             C
AI                     AI123             A
AC                   AI123             C

In my current script only the AI record would show - I need all 3.

SELECT   'MF' as company, *, 
                                                   (SELECT     SUM(amount)  
                                                    FROM          MF_App.dbo.projtran AS pt 
                                                     WHERE      (pt.proj_num = MF_App.dbo.proj.proj_num) AND pt.cost_code <> 'INT') AS proj_sum 
                         FROM         MF_App.dbo.proj LEFT OUTER JOIN 
                                               AGRAMaster.dbo.ACS_ProjectHeader ON MF_App.dbo.proj.proj_num = AGRAMaster.dbo.ACS_ProjectHeader.ProjectID 
                         WHERE     (MF_App.dbo.proj.stat = 'A')
                         UNION ALL 
                         SELECT    'AI' as company, *, 
                                                   (SELECT     SUM(amount)  
                                                     FROM          AI_App.dbo.projtran AS pt 
                                                    WHERE      (pt.proj_num = AI_App.dbo.proj.proj_num) AND pt.cost_code <> 'INT') AS proj_sum 
                         FROM         AI_App.dbo.proj LEFT OUTER JOIN 
                                               AGRAMaster.dbo.ACS_ProjectHeader ON AI_App.dbo.proj.proj_num = AGRAMaster.dbo.ACS_ProjectHeader.ProjectID 
                         WHERE     (MF_App.dbo.proj.stat = 'A') 
                         UNION ALL 
                         SELECT    'AC' as company, *, 
                                                   (SELECT     SUM(amount)  
                                                     FROM          AC_App.dbo.projtran AS pt 
                                                     WHERE      (pt.proj_num = AC_App.dbo.proj.proj_num) AND pt.cost_code <> 'INT') AS proj_sum 
                         FROM         AC_App.dbo.proj LEFT OUTER JOIN " & _
                                               AGRAMaster.dbo.ACS_ProjectHeader ON AC_App.dbo.proj.proj_num = AGRAMaster.dbo.ACS_ProjectHeader.ProjectID 
                         WHERE     (MF_App.dbo.proj.stat = 'A')

Open in new window

0
Comment
Question by:thenrich
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36719534
in short/simplified form, you would do this:
select t.*
  from yourtable t
 where exists ( select null from yourtable x where x.Project = t.Project and x.stat = 'A' )

Open in new window

hope this helps
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36814606
could you post the actual code used ...

the one you've posted is correct ... (the second union tests againt the wrong database status...)

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36814746
you probably meant it like this....

but please don't use * in production code ... specify each individual column required.
;with cte as (select proj_num from mf_app.dbo.proj where STAT='A'
              union select proj_num from ai_app.dbo.proj where STAT='A'
              union select proj_num from ac_app.dbo.proj where STAT='A'
              )
   
SELECT   'MF' as company, *, 
                       (SELECT     SUM(case cost_code when 'INT' then 0 else amount end)  
                        FROM          MF_App.dbo.projtran AS pt 
                         WHERE      (pt.proj_num = P.proj_num) 
                           ) AS proj_sum 
FROM         MF_App.dbo.proj as P
LEFT OUTER JOIN AGRAMaster.dbo.ACS_ProjectHeader 
ON P.proj_num = AGRAMaster.dbo.ACS_ProjectHeader.ProjectID 
WHERE   exists (select proj_num from cte as x where x.proj_num = p.proj_num)  
UNION ALL 
SELECT    'AI' , *, 
                       (SELECT     SUM(case cost_code when 'INT' then 0 else amount end)  
                         FROM          AI_App.dbo.projtran AS pt 
                        WHERE      (pt.proj_num = P.proj_num) 
                        ) AS proj_sum 
FROM         AI_App.dbo.proj as P
LEFT OUTER JOIN AGRAMaster.dbo.ACS_ProjectHeader 
  ON P.proj_num = AGRAMaster.dbo.ACS_ProjectHeader.ProjectID 
WHERE  exists (select proj_num from cte as x where x.proj_num = p.proj_num) 
UNION ALL 
SELECT    'AC' , *, 
                       (SELECT     SUM(case cost_code when 'INT' then 0 else amount end)  
                         FROM          AC_App.dbo.projtran AS pt 
                         WHERE      (pt.proj_num = P.proj_num)
                          )  
FROM         AC_App.dbo.proj as P
LEFT OUTER JOIN  AGRAMaster.dbo.ACS_ProjectHeader 
  ON P.proj_num = AGRAMaster.dbo.ACS_ProjectHeader.ProjectID 
WHERE   exists (select proj_num from cte as x where x.proj_num = p.proj_num)

Open in new window

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 36814810
but i thinks its easier to read if you do it more like this...

but the * does need to be replaced by the column list as this will double up the proj_num column

ps

you probably also need an ORDER BY clause

order by projectid,company ?
;with cte as (select proj_num from mf_app.dbo.proj where STAT='A'
              union select proj_num from ai_app.dbo.proj where STAT='A'
              union select proj_num from ac_app.dbo.proj where STAT='A'
              )
   ,cte1 as (select proj_num,h.*
               from cte as p
               left outer join AGRAMaster.dbo.ACS_ProjectHeader as H
                 ON P.proj_num = h.ProjectID 
            )
SELECT   'MF' as company, *, 
                       (SELECT     SUM(case cost_code when 'INT' then 0 else amount end)  
                        FROM          MF_App.dbo.projtran AS pt 
                         WHERE      (pt.proj_num = P.proj_num) 
                           ) AS proj_sum 
FROM         MF_App.dbo.proj as P
inner join cte1 as x where x.proj_num = p.proj_num  
UNION ALL 
SELECT    'AI' , *, 
                       (SELECT     SUM(case cost_code when 'INT' then 0 else amount end)  
                         FROM          AI_App.dbo.projtran AS pt 
                        WHERE      (pt.proj_num = P.proj_num) 
                        ) AS proj_sum 
FROM         AI_App.dbo.proj as P
inner join cte1 as x where x.proj_num = p.proj_num 
UNION ALL 
SELECT    'AC' , *, 
                       (SELECT     SUM(case cost_code when 'INT' then 0 else amount end)  
                         FROM          AC_App.dbo.projtran AS pt 
                         WHERE      (pt.proj_num = P.proj_num)
                          )  
FROM         AC_App.dbo.proj as P
inner join cte1 as x where x.proj_num = p.proj_num

Open in new window

0
 
LVL 5

Author Closing Comment

by:thenrich
ID: 36814892
nice
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question