duplicate rows - loan number
Not great at T-SQL.
I need help figuring out why there is row duplication.
For example, Loan Number 8074** appears twice.
Also, please share how to troubleshoot this.
Results:
result-failedsales.png
I need help figuring out why there is row duplication.
For example, Loan Number 8074** appears twice.
Also, please share how to troubleshoot this.
USE [PP]
GO
/****** Object: StoredProcedure [dbo].[Report_FailedSales_Get] Script Date: 01/23/2013 11:31:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Report_FailedSales_Get]
@LoggedInUserID int,
@LoggedInUserSID int,
@ClientID int = null,
@ClientSID int = null,
@PoolID int = null,
@PoolSID int = null,
@FirmID int = null,
@FirmSID int = null,
@localClosingMerchantID int = null,
@localClosingMerchantSID int = null,
@BrokerageID int = null,
@BrokerageSID int = null,
@RoleID int = null,
@UserID int = null,
@UserSID int = null,
@State varchar(2) = null,
@FileReceivedFromDt datetime = null,
@FileReceivedToDt datetime = null,
@OrigListFromDt datetime = null,
@OrigListToDt datetime = null,
@UCFromDt datetime = null,
@UCToDt datetime = null,
@ReportAsOfDt datetime = null,
@ListingAgentUserID int = null,
@ListingAgentUserSID int = null,
@InvestName varchar(35) = NULL,
@FSFromDt datetime = null,
@FSToDt datetime = null
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Declare @RoleCode varchar(10)
select @RoleCode = Code from Roles where RoleID = @RoleID
declare @InvestorID int, @InvestorSID int
set @InvestorID = null
set @InvestorSID = null
select @InvestorID = Merchant.MerchantID
,@InvestorSID = Merchant.MerchantSID
from merchant
join [User] u on u.merchantid = merchant.merchantid and u.merchantsid = merchant.merchantsid
where
u.userid = @LoggedInUserID and u.usersid = @LoggedInUserSID
and merchant.typeid = dbo.getlookupid('Mchnt_Inv')
select
Asset.LoanNumber,
dbo.CreateIdSid(Asset.AssetID, Asset.AssetSID) as AssetIdSid,
Merchant.Name as Client,
Pool.PoolName as Pool,
AMF.Name as AMFirm,
Asset.Address,
Asset.City,
Asset.State,
Asset.Zip,
CC.UserName as Closing_Coordinator,
AM.UserName as AssetMgr,
DATEDIFF(dd,Closing.UnderContractDT,getdate()) as DUC,
Closing.UnderContractDt,
Closing.ContractClosed as Contract_Close_Dt,
Closing.ScheduledClose as Scheduled_Close_Dt,
Closing.ExtendedClose as Extended_Close_Dt,
vClosingContact.MerchantName as Local_Closing_Office,
Closing.FinanceType as FinanceType,
MaxOffer.OfferPrice as Gross_Sales_Price,
MaxOffer.SC_ClosingCost + MaxOffer.SC_LoanPoints + MaxOffer.SC_Repairs + MaxOffer.SC_PestTermite + MaxOffer.SC_FhaVaNA + MaxOffer.SC_HomeWarranty + MaxOffer.SC_Other as Seller_Concessions,
(MaxOffer.OfferPrice - (coalesce(MaxOffer.SC_ClosingCost,0) +coalesce(MaxOffer.SC_LoanPoints,0)+coalesce(MaxOffer.SC_Repairs,0)+coalesce(MaxOffer.SC_PestTermite,0)+coalesce(MaxOffer.SC_FhaVaNA,0)
+coalesce(MaxOffer.SC_HomeWarranty,0)+coalesce(MaxOffer.SC_Other,0)) ) as Net_Sales_Price,
Buyer.BuyerFName,
Buyer.BuyerLName,
Agt.UserName as Listing_Agent,
dbo.DateFormat(MaxOffer.OfferDt) as FailedSaleDate,
p1.Description as FailedSaleReason,
dbo.GetComment(Buyer.FailCommentID, Buyer.FailCommentSID) as FailedSaleComments
FROM Asset
Left join vAssetContact AM on Asset.AssetID = AM.AssetID AND Asset.AssetSID = AM.AssetSID AND AM.RoleCode = 'ASETMGR'
Left join vAssetContact Agt on Asset.AssetID = Agt.AssetID AND Asset.AssetSID = Agt.AssetSID AND Agt.RoleCode = 'AGT'
Left join vAssetContact CC on Asset.AssetID = CC.AssetID AND Asset.AssetSID = CC.AssetSID AND CC.RoleCode = 'CLSCRD'
Left join vAssetContact CLAM on Asset.AssetID = CLAM.AssetID AND Asset.AssetSID = CLAM.AssetSID AND CLAM.RoleCode = 'CL1'
Left join vAssetContact SM on Asset.AssetID = SM.AssetID AND Asset.AssetSID = SM.AssetSID AND SM.RoleCode = 'SLSMGR'
left join vAssetContact as vClosingContact on Asset.AssetID = vClosingContact.AssetID and Asset.AssetSID = vClosingContact.AssetSID and vClosingContact.RoleCode = 'CC'
left join Closing on Asset.AssetID = Closing.AssetID AND Asset.AssetSID = Closing.AssetSID
INNER JOIN Pool ON Asset.PoolID = pool.PoolID AND Asset.PoolSID = pool.PoolSID
left join vpoolinvestor vpi on vpi.poolID = pool.PoolID and vpi.poolsid = pool.PoolSID
and vpi.AMFirmID = asset.AMFirmID and vpi.AMFirmSID = asset.AMFirmSID
and vpi.ClientID = Pool.MerchantID and vpi.ClientSID = Pool.MerchantSID
LEFT JOIN Merchant ON pool.MerchantID = merchant.MerchantID AND pool.MerchantSID = merchant.MerchantSID
INNER JOIN Buyer ON Asset.AssetID = buyer.AssetID AND Asset.AssetSID = buyer.AssetSID
INNER JOIN Status ON Asset.StatusID = status.StatusID
join merchant AMF on AMF.merchantid = asset.amfirmid and AMF.merchantsid = asset.amfirmsid
INNER JOIN Lookup p1 ON buyer.BuyerStatusID = p1.LookupID
AND p1.Description = 'Failed'
INNER JOIN Lookup p2 ON buyer.FailReasonID = p2.LookupID
AND p2.Description NOT IN ('Unable To Obtain Contract')
INNER JOIN (SELECT
MAX(OfferDt) OfferDt,
BuyerId,
BuyerSID,
OfferPrice,
SC_ClosingCost,
SC_LoanPoints,
SC_Repairs,
SC_PestTermite,
SC_FhaVaNA,
SC_HomeWarranty,
SC_Other
FROM Offer
INNER JOIN Lookup ON Offer.OfferTypeID = Lookup.LookupID AND LookupCode = 'Offer_FL'
WHERE ((@FSFromDt is null) or (dbo.DateFormat(Offer.OfferDt) >= @FSFromDt))
and ((@FSToDt is null) or (dbo.DateFormat(Offer.OfferDt) <= @FSToDt))
GROUP BY
BuyerId,
BuyerSID,
OfferPrice,
SC_ClosingCost,
SC_LoanPoints,
SC_Repairs,
SC_PestTermite,
SC_FhaVaNA,
SC_HomeWarranty,
SC_Other
)MaxOffer ON buyer.BuyerID = MaxOffer .BuyerID AND buyer.BuyerSID = MaxOffer .BuyerSID
LEFT JOIN (
select Buyer.AssetID, Buyer.AssetSID, tblLastFailedOffer.LasFailedOfferDt, tblFailReason.Description as FailReason
from Offer
inner join
(
SELECT Buyer.AssetID,Buyer.AssetSID,MAX(Offer.OfferDt) as LasFailedOfferDt
FROM Asset
inner join Buyer on Asset.AssetID = Buyer.AssetID and Asset.AssetSID = Buyer.AssetSID
inner join Offer on Buyer.BuyerID = Offer.BuyerID and Buyer.BuyerSID = Offer.BuyerSID
WHERE Buyer.BuyerStatusID = 366 --Failed
AND ((@FSFromDt is null) or (dbo.DateFormat(Offer.OfferDt) >= @FSFromDt))
and ((@FSToDt is null) or (dbo.DateFormat(Offer.OfferDt) <= @FSToDt))
GROUP BY Buyer.AssetID,Buyer.AssetSID
) as tblLastFailedOffer on Offer.AssetID = tblLastFailedOffer.AssetID and Offer.AssetSID = tblLastFailedOffer.AssetSID
and Offer.OfferDt = tblLastFailedOffer.LasFailedOfferDt
inner join Buyer on Offer.BuyerID = Buyer.BuyerID and Offer.BuyerSID = Buyer.BuyerSID
left join Lookup as tblFailReason on Buyer.FailReasonID = tblFailReason.LookupID
) tblLastFailReason ON Asset.AssetID = tblLastFailReason.AssetID AND Asset.AssetSID = tblLastFailReason.AssetSID
WHERE AM.UserID = @LoggedInUserID and AM.UserSID = @LoggedInUserSID
and ((@ClientID is null) or (Pool.MerchantID = @ClientID and Pool.MerchantSID = @ClientSID))
and ((@PoolID is null) or (Pool.PoolID = @PoolID and Pool.PoolSID = @PoolSID))
and ((@FirmID is null) or (Asset.AMFirmID = @FirmID and Asset.AMFirmSID = @FirmSID))
and ((@localClosingMerchantID is null) or (vClosingContact.merchantID = @localClosingMerchantID and vClosingContact.merchantSID = @localClosingMerchantSID))
and ((@ListingAgentUserID is null) or (Agt.UserID = @ListingAgentUserID and Agt.UserSID = @ListingAgentUserSID))
and ((@BrokerageID is null) or (Agt.MerchantID = @BrokerageID and Agt.MerchantSID = @BrokerageSID))
and ((@RoleID is null) or (@UserID is null)
or (@RoleCode = 'ASETMGR' and AM.UserID = @UserID and AM.UserSID = @UserSID)
or (@RoleCode = 'SLSMGR' and SM.UserID = @UserID and SM.UserSID = @UserSID)
or (@RoleCode = 'CLSCRD' and CC.UserID = @UserID and CC.UserSID = @UserSID)
)
and ((@State is null) or (Asset.State = @State))
and ((@FileReceivedFromDt is null) or (Asset.FileReceiveDate >= @FileReceivedFromDt))
and ((@FileReceivedToDt is null) or (Asset.FileReceiveDate <= @FileReceivedToDt))
and ((@OrigListFromDt is null) or (Asset.IniListDt >= @OrigListFromDt))
and ((@OrigListToDt is null) or (Asset.IniListDt <= @OrigListToDt))
and ((@UCFromDt is null) or (Closing.UnderContractDT >= @UCFromDt))
and ((@UCToDt is null) or (Closing.UnderContractDT <= @UCToDt))
and ((@FSFromDt is null) or (dbo.DateFormat(MaxOffer.OfferDt) >= @FSFromDt))
and ((@FSToDt is null) or (dbo.DateFormat(MaxOffer.OfferDt) <= @FSToDt))
and (@InvestorID is null or (vpi.InvestorID = @InvestorID and vpi.InvestorSID = @InvestorSID ))
and (@InvestName is null or @InvestName = '' OR Asset.InvestName = @InvestName)USE [PP]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[Report_FailedSales_Get]
@LoggedInUserID = 23543,
@LoggedInUserSID = 1
SELECT 'Return Value' = @return_value
GOResults:
result-failedsales.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>>For example, can you have multiple buyers per loan?
I believe you can.
In fact, as I look at the rows, that is where they are often differing - buyer names!
Thanks!
I believe you can.
In fact, as I look at the rows, that is where they are often differing - buyer names!
Thanks!
Thanks for the split. Good luck with your project. -Jim
ASKER
Let me think about this a while.