Type25
asked on
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.ReplacementLette rSent, SUM(estItems.EstReplacemen tCost) estCost
FROM dbo.Jobs j INNER JOIN
dbo.LicencesHeld lh ON j.LicID = lh.LicID INNER JOIN
dbo.NationalAccountBranche s 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.ReplacementCompa nyID = 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,acciden talclaimty pe,reporto nly,
rc.AccName,replItems.Repla cementLett erSent
HAVING (j.FinalReport > CONVERT(DATETIME, @date, 102))
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.ReplacementLette
FROM dbo.Jobs j INNER JOIN
dbo.LicencesHeld lh ON j.LicID = lh.LicID INNER JOIN
dbo.NationalAccountBranche
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.ReplacementCompa
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,acciden
rc.AccName,replItems.Repla
HAVING (j.FinalReport > CONVERT(DATETIME, @date, 102))
What criteria do you require?
Max.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER