• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1157
  • Last Modified:

select top1 in subquery - part 2

Next problem....

I need to join on the items table again, but this time i need to sum on a column but only on certain criteria (look at the last join, is that the correct way of doing it?) - the column in question is EstReplacementCost


declare @date datetime
set @date = '2006-07-01 00:00:00'

SELECT j.JobNo, j.ClaimNo, nab.NatAccBranch, j.CoverID, j.NewForOld, j.AccidentalClaimType, dc.DamageDescription, c.Cause, j.ReportOnly,
                      j.ContactsEntered, j.CustDirectContact, j.CustLicContact, j.VisitedOn, j.FinalReport, cd.CustName, cd.CustPostcode, j.RoomCount, j.Excess,
             sum(ia.chnet) totalInvoice, rc.AccName, replItems.ReplacementLetterSent, SUM(estItems.EstReplacementCost) estCost
FROM         dbo.Jobs j INNER JOIN
                      dbo.LicencesHeld lh ON j.LicID = lh.LicID INNER JOIN
                      dbo.NationalAccountBranches nab ON j.NatBranchID = nab.NatAccBranchID INNER JOIN
                      dbo.DamageCategories dc ON j.DamageCatID = dc.DamageCategoryID INNER JOIN
                      dbo.Causes c ON j.CauseID = c.CauseID INNER JOIN
                      dbo.Items i ON j.JobNo = i.JobNo INNER JOIN
                      dbo.ItemActions ia ON i.ItemID = ia.ItemID INNER JOIN
                      dbo.CustomerDetails cd ON j.CustomerID = cd.CustomerID LEFT JOIN
                  Items replItems ON j.JobNo = replItems.JobNo AND replItems.itemid =
                  (select top 1 t.itemid from items t where j.JobNo = t.JobNo and replacementcompanyid is not null and replacementletter is not null order by Replacementlettersent desc ) LEFT JOIN
                  ReplacementCompanies rc ON replItems.ReplacementCompanyID = rc.ReplCoID LEFT JOIN
                  items estItems ON j.JobNo = estItems.JobNo AND estItems.ItemID in
                  (select t.itemid from items t where j.JobNo = t.JobNo and replacementrequired = 0)

WHERE     (lh.LiveUser = 1) AND (j.BUID = 2759)
GROUP BY j.JobNo, j.ClaimNo, nab.NatAccBranch, j.CoverID, dc.DamageDescription, c.Cause, j.ContactsEntered, j.CustDirectContact, j.CustLicContact,
                      j.VisitedOn, j.FinalReport, cd.CustName, cd.CustPostcode, j.RoomCount, j.Excess,newforold,accidentalclaimtype,reportonly,
                  rc.AccName,replItems.ReplacementLetterSent
HAVING      (j.FinalReport > CONVERT(DATETIME, @date, 102))




0
Type25
Asked:
Type25
  • 2
  • 2
1 Solution
 
Type25Author Commented:
by the way, the query runs, but the results are somewhat random... the EstReplacementCost columns is generally to much.
0
 
maxy88Commented:

What criteria do you require?

Max.
0
 
Type25Author Commented:
This join:
LEFT JOIN
               items estItems ON j.JobNo = estItems.JobNo AND estItems.ItemID in
               (select t.itemid from items t where j.JobNo = t.JobNo and replacementrequired = 0)

I need the sum of EstReplacementCost  where replacementrequired = 0

as well as the rest of the query.

For the time being i've done it as a seperate query
0
 
maxy88Commented:

Try this:

LEFT JOIN
               items estItems ON j.JobNo = estItems.JobNo AND estItems.ItemID in
               (select itemid from items where replacementrequired = 0)

Max.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now