November 24, 2015 at 4:58 pm
Hello, I created a stored procedure that returns financial information for a business. The stored procedure receives a companyID parameter
There are five types a record can have:
Business Accounts
Loans
Lines of Credit
Leases
Cash Management Services
The last Type "Cash Management Services", can also have sub-types:
Credit Card
One Card
ACH
Positive Pay
Debit Card
Merchant Services
Online Banking
Remote Deposit Capture
Sweeps/Zero Balance
Dom./Int. Wires
LockBox
Other
When I execute the stored procedure, I get duplicate records for each one of the sub-types of Cash Management Services. For example, the row with data has sub type One Card, and I get that row, plus 11 more representing each one of the sub types for cash management services
I do need to be able to get the row with data for this type, but also to list all its sub-types without data.
The output ideally should be like the file named "output_desired"
The current output looks like the file "current _output"
Does this make sense?
This is my stored procedure:
/*
EXEC [dbo].[sp_Card_Company_BusinessFinancialInformation] 5
*/
ALTER PROCEDURE dbo.sp_Card_Company_BusinessFinancialInformation
@CompanyID INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT
[ModifyProcedure]
, [PrimaryKeyName]
, [PrimaryKeyValue]
, [ForeignKeyName]
, [ForeignKeyValue]
, [CompositeKeyName]
, [CompositeKeyValue]
, [rt].[RelationshipTypeID]
, [rt].[RelationshipTypeName]
, [rst].[RelationshipSubTypeID]
, [rst].[RelationshipSubTypeName]
, [Location]
, [Quantity]
, [FollowUpDate]
, [Details]
, [DocsInNautilus]
, [HasSuccessionPlan]
, [HasBuySellAgreement]
, [IsFunded]
, [RetirementPlanType]
, [PlanAssets]
, [PlanProvider]
, [LastPersonalFinancialPlanReviewDate]
, [LastFinancialStatementReceivedDate]
FROM
(
SELECT
'sp_CUD_BusinessFinancialInformation' [ModifyProcedure]
, 'BusinessFinancialInformationID' [PrimaryKeyName]
, [fi].[BusinessFinancialInformationID] [PrimaryKeyValue]
, 'CompanyID' [ForeignKeyName]
, [cfi].[CompanyID] [ForeignKeyValue]
, NULL [CompositeKeyName]
, NULL [CompositeKeyValue]
, [fi].[RelationshipTypeID]
, [fi].[RelationshipType]
, [fi].[BusinessFinancialInformationID]
, [cfi].[CompanyID]
, [fi].[Location]
, [fi].[Quantity]
, [fi].[FollowUpDate]
, [fi].[Details]
, [fi].[DocsInNautilus]
, [fi].[HasSuccessionPlan]
, [fi].[HasBuySellAgreement]
, [fi].[IsFunded]
, [fi].[RetirementPlanType]
, [fi].[PlanAssets]
, [fi].[PlanProvider]
, [fi].[LastPersonalFinancialPlanReviewDate]
, [fi].[LastFinancialStatementReceivedDate]
FROM dbo.vw_tblBusinessFinancialInformation fi
LEFT OUTER JOIN dbo.vw_tblCompany_BusinessFinancialInformation AS cfi
ON cfi.BusinessFinancialInformationID = fi.
BusinessFinancialInformationID
WHERE [cfi].[CompanyID] = @CompanyID
) cc_bfi
LEFT OUTER JOIN dbo.tblRelationshipType rt
ON cc_bfi.RelationshipTypeID = rt.RelationshipTypeID
LEFT OUTER JOIN dbo.tblRelationshipSubType rst
ON rt.RelationshipTypeID = rst.RelationshipTypeID;
END;
November 25, 2015 at 1:27 am
Please post create view/table scripts along with sample data for the following tables / views
vw_tblBusinessFinancialInformation - and all referenced tables in the view
vw_tblCompany_BusinessFinancialInformation - and all referenced tables in the view
tblRelationshipType
tblRelationshipSubType
November 25, 2015 at 2:48 am
Both output_desired.txt and current_output.txt show 16 rows and have the same projection on (RelationshipTypeName, RelationshipSubTypeName ). What's wrong?
November 25, 2015 at 8:03 am
The current output record for type Cash Management Services and sub type One Card is the one actual record, all the other records for type Cash Management Services, are a duplication of that record data, what the out put should present is the records for cash management services sub type one card, and all the other column populated, and all other cash management rows, should only have the sub type populated with the rest of the column NULL.
November 25, 2015 at 9:48 am
itortu (11/25/2015)
The current output record for type Cash Management Services and sub type One Card is the one actual record, all the other records for type Cash Management Services, are a duplication of that record data, what the out put should present is the records for cash management services sub type one card, and all the other column populated, and all other cash management rows, should only have the sub type populated with the rest of the column NULL.
Can't find any data in cc_bfi subquery which could be possibly matched to single
dbo.tblRelationshipSubType row. How one should select exactly 'One Card' row ?
November 25, 2015 at 10:02 am
anthony.green (11/25/2015)
Please post create view/table scripts along with sample data for the following tables / viewsvw_tblBusinessFinancialInformation - and all referenced tables in the view
vw_tblCompany_BusinessFinancialInformation - and all referenced tables in the view
tblRelationshipType
tblRelationshipSubType
In addition to sample data the expected results based on the sample data.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply