?
Solved

SQL question

Posted on 2011-09-28
5
Medium Priority
?
195 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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

840 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