Solved

select top1 in subquery - part 2

Posted on 2006-07-11
4
1,143 Views
Last Modified: 2007-12-19
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
Comment
Question by:Type25
  • 2
  • 2
4 Comments
 
LVL 9

Author Comment

by:Type25
Comment Utility
by the way, the query runs, but the results are somewhat random... the EstReplacementCost columns is generally to much.
0
 
LVL 6

Expert Comment

by:maxy88
Comment Utility

What criteria do you require?

Max.
0
 
LVL 9

Author Comment

by:Type25
Comment Utility
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
 
LVL 6

Accepted Solution

by:
maxy88 earned 500 total points
Comment Utility

Try this:

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

Max.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now