Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    105

    Unanswered: Need a suggestion on how to revise this query

    This is what I have:

    Code:
    Select
     PR.WBS1, 
     PR.WBS2, 
     PR.WBS3, 
     PR.LongName, 
     PR.Name, 
     CL.Name as CLIENTNAME, 
     CFGMain.FirmName, 
     CFGMain.Address1, 
     CFGMain.Address2, 
     CFGMain.Address3, 
     CFGMain.Address4, 
     Contacts.FirstName + ' ' + Contacts.LastName as CONTACT, 
     isnull(LB.AmtBud, '0.00') as AmtBud, 
     Sum(LD.BillExt) as BillExt 
     From PR 
     left join Contacts ON PR.ContactID = Contacts.ContactID 
     left join CL ON CL.ClientID = PR.ClientID 
     left join LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 
     left join LD ON LD.WBS1 = PR.WBS1 AND PR.WBS2 = LD.WBS2 AND LD.WBS3 = PR.WBS3  And LD.BilledPeriod =  '200408',
    CFGMain 
    Where (PR.WBS3 <> 'ZZZ') and (PR.WBS2 <> 'ZZZ')
    Group By  PR.WBS1, PR.WBS2, PR.WBS3, PR.LongName, PR.Name, CL.Name, CFGMain.FirmName, 
    CFGMain.Address1, CFGMain.Address2, CFGMain.Address3, 
    CFGMain.Address4, Contacts.FirstName, Contacts.LastName, 
    LB.AmtBud
    Right now I have a column that takes the sum of LD.BilledPeriod when equaled to '200408'. The problem is that I need to include another column that also calculates the sum for BillExt where LD.BilledPeriod <= '200408'. Ordinarily I would have done a subquery which I have tested and it does work. But, I have to find another way of doing this because when I add subqueries other functionality in the software I using does not work. So, I am hoping that someone might know another route for me to take. I was thinking about using a UNION but this will not create a new column that I need.

    Thank You,
    Laura

  2. #2
    Join Date
    May 2004
    Posts
    105
    Ok, I think I might have solved it by using CASE.

    Code:
    SELECT     PR.WBS1, PR.WBS2, PR.WBS3, PR.LongName, PR.Name, ISNULL(LB.AmtBud, '0.00') AS AmtBud, 
                          SUM(CASE WHEN LD.BilledPeriod <= '200408' THEN LD.BillExt ELSE '0' END) AS BTD, 
                          SUM(CASE WHEN LD.BilledPeriod = '200408' THEN LD.BillExt ELSE '0' END) AS BillExt
    FROM         PR LEFT OUTER JOIN
                          LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 LEFT OUTER JOIN
                          LD ON LD.WBS1 = PR.WBS1 AND PR.WBS2 = LD.WBS2 AND LD.WBS3 = PR.WBS3
    WHERE     (PR.WBS3 <> 'ZZZ') AND (PR.WBS2 <> 'ZZZ') AND (PR.WBS1 = '001-298')
    GROUP BY PR.WBS1, PR.WBS2, PR.WBS3, PR.LongName, PR.Name, LB.AmtBud
    Thanks,
    Laura

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That was painful...good luck

    Code:
    Select * FROM (
    SELECT
     PR.WBS1, 
     PR.WBS2, 
     PR.WBS3, 
     PR.LongName, 
     PR.Name, 
     CL.Name as CLIENTNAME, 
     CFGMain.FirmName, 
     CFGMain.Address1, 
     CFGMain.Address2, 
     CFGMain.Address3, 
     CFGMain.Address4, 
     Contacts.FirstName + ' ' + Contacts.LastName as CONTACT, 
     isnull(LB.AmtBud, '0.00') as AmtBud, 
     Sum(LD.BillExt) as BillExt_200408 
     From PR 
     left join Contacts ON PR.ContactID = Contacts.ContactID 
     left join CL ON CL.ClientID = PR.ClientID 
     left join LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 
     left join LD ON LD.WBS1 = PR.WBS1 AND PR.WBS2 = LD.WBS2 AND LD.WBS3 = PR.WBS3  And LD.BilledPeriod =  '200408',
    CFGMain 
    Where (PR.WBS3 <> 'ZZZ') and (PR.WBS2 <> 'ZZZ')
    Group By  PR.WBS1, PR.WBS2, PR.WBS3, PR.LongName, PR.Name, CL.Name, CFGMain.FirmName, 
    CFGMain.Address1, CFGMain.Address2, CFGMain.Address3, 
    CFGMain.Address4, Contacts.FirstName, Contacts.LastName, 
    LB.AmtBud) AS A
    JOIN (
    SELECT
     PR.WBS1, 
     PR.WBS2, 
     PR.WBS3, 
     PR.LongName, 
     PR.Name, 
     CL.Name as CLIENTNAME, 
     CFGMain.FirmName, 
     CFGMain.Address1, 
     CFGMain.Address2, 
     CFGMain.Address3, 
     CFGMain.Address4, 
     Contacts.FirstName + ' ' + Contacts.LastName as CONTACT, 
     isnull(LB.AmtBud, '0.00') as AmtBud, 
     Sum(LD.BillExt) as BillExt_LT_200408 
     From PR 
     left join Contacts ON PR.ContactID = Contacts.ContactID 
     left join CL ON CL.ClientID = PR.ClientID 
     left join LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 
     left join LD ON LD.WBS1 = PR.WBS1 AND PR.WBS2 = LD.WBS2 AND LD.WBS3 = PR.WBS3  And LD.BilledPeriod <  '200408',
    CFGMain 
    Where (PR.WBS3 <> 'ZZZ') and (PR.WBS2 <> 'ZZZ')
    Group By  PR.WBS1, PR.WBS2, PR.WBS3, PR.LongName, PR.Name, CL.Name, CFGMain.FirmName, 
    CFGMain.Address1, CFGMain.Address2, CFGMain.Address3, 
    CFGMain.Address4, Contacts.FirstName, Contacts.LastName, 
    LB.AmtBud) AS B
    ON a.PR.WBS1 = b.PR.WBS1 AND a.WBS2 = b.WBS2 AND a.WBS3 = b.WBS3 AND a.LongName AND a.LongName AND 
    AND a.Name = a.Name AND  a.CLIENTNAME = b.CLIENTNAME AND a.FirmName = b.FirmName AND
    a.Address1 = b.Address1 AND a.Address2 = a.Address2 AND a.Address3 = b.Address3 AND
    a.Address4 = b.Address4 AND a.FirstName = b.FirstName AND a.LastName = b.LastName AND 
    a.AmtBud = b.AmtBud
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Damn...I like the CASE...I seem to forget that...

    The results should be the same though...are they?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    May 2004
    Posts
    105
    Hmm.., actually I couldn't get yours to work. There were a few error that I couldn't resolve. But that's ok because the case idea worked.

    Thanks,
    Laura

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •