Solved

SQL question

Posted on 2011-09-28
5
185 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]
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 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

809 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