Solved

duplicate rows - loan number

Posted on 2013-01-23
5
356 Views
Last Modified: 2013-01-30
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.



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)

Open in new window



USE [PP]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[Report_FailedSales_Get]
		@LoggedInUserID = 23543,
		@LoggedInUserSID = 1

SELECT	'Return Value' = @return_value

GO

Open in new window





Results:

res failed salesresult-failedsales.png
0
Comment
Question by:knowlton
  • 2
  • 2
5 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 300 total points
Comment Utility
Since I don't see any DISTINCT or GROUP BY in your T-SQL that would ensure duplicates are deleted from the return recordset, explain for us how you would expect that there would not be duplicates.

Also, there are a couple of INNER JOINs here, so if there is a one-to-many relationship between your main table and the joined table, if the joined table has multiple rows where the foreign key is related to the main table's key, it would return multiple rows.
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
I think I see your point about one to many relationships...

Let me think about this a while.
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 200 total points
Comment Utility
I agree with Jim. One way to troubleshoot is to look at the elements that are different on the duplicated rows. If there are no differences, start by considering what in the data can appear more than once. For example, can you have multiple buyers per loan? Questions, such as the aforementioned one, will help you find the source of the duplication in the JOINs. If there is a difference, look at the source table for those columns. This will help you figure out why and how to correct the duplication. If the value is a number for example, you may need to aggregate the numbers. For example, a listing of different closing costs. I see you are already aggregating that data, but possibly there is something else of that nature.

Best regards, Kevin
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
>>>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!
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Thanks for the split.  Good luck with your project.  -Jim
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

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