Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL question

Posted on 2011-09-28
5
Medium Priority
?
194 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

618 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