MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Databases > MS SQL Development
Reload this Page

Stuck on this query

Discuss Stuck on this query in the MS SQL Development forum on Dev Shed.
Stuck on this query MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. SQL Server is Microsoft's enterprise database engine.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old November 11th, 2004, 01:52 PM
lauramccord lauramccord is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Posts: 180 lauramccord User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 9 h 3 m 58 sec
Reputation Power: 5
Stuck on this query
I need the results from the following query to be with the results of the second query. Any ideas?

Code:
SELECT 
                      PR.WBS1, PR.WBS2, PR.WBS3, PR.Fee, PR.ConsultFee, PR.ReimbAllow, PR.LongName, PR.Name, CL.Name AS CLIENTNAME, 
                      CLAddress.Address2 AS CLIENTADDRESS2, CLAddress.Address3 AS CLIENTADDRESS3, CLAddress.Address4 AS CLIENTADDRESS4, 
                      CFGMain.FirmName, CFGMain.Address1, CFGMain.Address2, CFGMain.Address3, CFGMain.Address4, 
                      Contacts.FirstName + ' ' + Contacts.LastName AS CONTACT, LB.AmtBud, LB.BillBud

FROM         PR LEFT OUTER JOIN
                      Contacts ON PR.ContactID = Contacts.ContactID LEFT OUTER JOIN
                      CL ON CL.ClientID = PR.ClientID LEFT OUTER JOIN
                      CLAddress ON CL.ClientID = CLAddress.ClientID LEFT OUTER JOIN
                      LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3                     
 CROSS JOIN
                      CFGMain
Where pr.wbs1 = '001-298' and pr.wbs3 != 'zzz'


and

Code:
SELECT     *
FROM         LD
WHERE     (BilledPeriod = '200408') AND (WBS1 = '001-298')


Thanks.
Reply With Quote
  #2  
Old November 11th, 2004, 02:23 PM
swampBoogie swampBoogie is offline
Contributing User
Dev Shed Intermediate (1500 - 1999 posts)
  
Join Date: Jan 2003
Location: Paris Uppland
Posts: 1,784 swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37
Quote:
I need the results from the following query to be with the results of the second query


Hmmm. Are there some words missing here? Do you mean a join like

Code:
SELECT LD.*,
        PR.WBS1, 
        PR.WBS2, 
        PR.WBS3, 
        PR.Fee, 
        PR.ConsultFee, 
        PR.ReimbAllow, 
        PR.LongName, 
        PR.Name, 
        CL.Name AS CLIENTNAME, 
        CLAddress.Address2 AS CLIENTADDRESS2,
        CLAddress.Address3 AS CLIENTADDRESS3, 
        CLAddress.Address4 AS CLIENTADDRESS4, 
        CFGMain.FirmName, 
        CFGMain.Address1, 
        CFGMain.Address2, 
        CFGMain.Address3, 
        CFGMain.Address4, 
        Contacts.FirstName + ' ' + Contacts.LastName AS CONTACT,
        LB.AmtBud, 
        LB.BillBud
FROM  LD 
 inner join PR on LD.WBS1 = PR.WBS1 
LEFT JOINContacts ON PR.ContactID = contacts.ContactID 
LEFT JOIN CL ON CL.ClientID = PR.ClientID 
LEFT JOIN CLAddress ON CL.ClientID = CLAddress.ClientID 
LEFT JOIN LB ON LB.WBS1 = PR.WBS1 
 AND PR.WBS2 = LB.WBS2 
 AND LB.WBS3 = PR.WBS3                     
 CROSS JOIN CFGMain
Where ld.wbs1 = '001-298' 
   and ld.BilledPeriod = '200408'
  and pr.wbs3 <> 'zzz'


explain more otherwise
Reply With Quote
  #3  
Old November 11th, 2004, 02:40 PM
lauramccord lauramccord is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Posts: 180 lauramccord User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 9 h 3 m 58 sec
Reputation Power: 5
I changed up the query to make it a little more simple.

Basically, I am displaying the results of project code, the amount budgeted for each code, and I need the sum of the billext to be displayed per project code.

However, the billext needs to be based on a certain billing period, '200408' if I put that condition is placed I notice that I do not get all of my results.

This is what I have now:
Code:
SELECT     PR.WBS1, PR.WBS2, PR.WBS3, LB.AmtBud, LD.BillExt
FROM         PR INNER JOIN
                      LD ON PR.WBS1 = LD.WBS1 AND LD.WBS2 = PR.WBS2 AND PR.WBS3 = LD.WBS3 LEFT OUTER JOIN
                      LB ON LB.WBS1 = PR.WBS1 AND LB.WBS2 = PR.WBS2 AND LB.WBS3 = PR.WBS3
WHERE     (PR.WBS1 = '001-298') AND (LD.BilledPeriod = '200408')
ORDER BY PR.WBS2, PR.WBS3


All I want is the results from the following query:
Code:
SELECT     PR.WBS1, PR.WBS2, PR.WBS3, LB.AmtBud
FROM         PR LEFT OUTER JOIN
                      LB ON LB.WBS1 = PR.WBS1 AND LB.WBS2 = PR.WBS2 AND LB.WBS3 = PR.WBS3
WHERE     (PR.WBS1 = '001-298') AND (PR.WBS3 <> 'ZZZ')
ORDER BY PR.WBS2, PR.WBS3


with the summation of LD.billExt where LD.billedPeriod = '200408' beside the AmtBud. Even if the AmtBud does not fall within the LD.BillingPeriod

Hope I explained this clear enough
Reply With Quote
  #4  
Old November 11th, 2004, 02:58 PM
lauramccord lauramccord is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Posts: 180 lauramccord User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 9 h 3 m 58 sec
Reputation Power: 5
Code:
SELECT     PR.WBS1, PR.WBS2, PR.WBS3, LB.AmtBud
FROM         PR LEFT OUTER JOIN
                      LB ON LB.WBS1 = PR.WBS1 AND LB.WBS2 = PR.WBS2 AND LB.WBS3 = PR.WBS3
WHERE     (PR.WBS1 = '001-298') AND (PR.WBS3 <> 'ZZZ')
ORDER BY PR.WBS2, PR.WBS3


Code:
SELECT     PR.WBS1, PR.WBS2, PR.WBS3, SUM(LD.BillExt) AS Expr1
FROM         PR LEFT OUTER JOIN
                      LD ON LD.WBS1 = PR.WBS1 AND PR.WBS2 = LD.WBS2 AND PR.WBS3 = LD.WBS3
WHERE     (PR.WBS1 = '001-298') AND (PR.WBS3 <> 'ZZZ') AND (LD.BilledPeriod = '200408')
GROUP BY PR.WBS1, PR.WBS2, PR.WBS3
ORDER BY PR.WBS2, PR.WBS3


Basically all I need to do is create a column in the first query that will include the results of the second query.

For Example,

WBS1 WBS2 WBS3 AmtBud BillExt
001-298 1217 010 3000 1952.50
001-298 1217 100 4000 <Null>

Any suggestions? It is probably some join that I need to perform
Reply With Quote
  #5  
Old November 12th, 2004, 01:45 AM
r937's Avatar
r937 r937 is offline
SQL Consultant
Click here for more information.
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 17,921 r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level)r937 User rank is General (90000 - 100000 Reputation Level) 
Time spent in forums: 1 Month 4 Weeks 12 h 58 m 55 sec
Reputation Power: 1022
Code:
SELECT PR.WBS1
     , PR.WBS2
     , PR.WBS3
     , LB.AmtBud
     , ( select SUM(LD.BillExt) 
           from LD 
          where WBS1 = PR.WBS1 
            AND WBS2 = PR.WBS2
            AND WBS3 = PR.WBS3 
            and BilledPeriod = '200408' ) as Expr1
  FROM PR 
LEFT OUTER 
  JOIN LB 
    ON LB.WBS1 = PR.WBS1 
   AND LB.WBS2 = PR.WBS2 
   AND LB.WBS3 = PR.WBS3
 WHERE (PR.WBS1 = '001-298') 
   AND (PR.WBS3 <> 'ZZZ')
ORDER 
    BY PR.WBS2
     , PR.WBS3
__________________
r937.com | rudy.ca
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > MS SQL Development > Stuck on this query

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 5 hosted by Hostway. 
Stay green...Green IT