Solved

SQL question

Posted on 2011-09-28
5
180 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
  • 3
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
nice
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Copy Database Wizard 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.
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now