Solved

select top1 in subquery - part 2

Posted on 2006-07-11
4
1,144 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
ID: 17081121
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
ID: 17081309

What criteria do you require?

Max.
0
 
LVL 9

Author Comment

by:Type25
ID: 17081317
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
ID: 17082481

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

911 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

22 Experts available now in Live!

Get 1:1 Help Now