USE [IT] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- /*select * from NAME ------ ----select * from RSUDependNew..CP_Connections where KeyMemberNameID in (select name_id from NAME where name_account='804961') ----select * from RSUDependNew..Cp_member join nameid on NAME_ID = nameid_name where NAME_ACCOUNT = 804958 and Name_sequence != 1 and name_ID != 456146*/ /*select * from NAME join nameid on NAME_ID = nameid_name where NAME_ACCOUNT = /*150420*/ 804958*/ --select * from name where NAME_ACCOUNT = 804958 and name_sequence != 1 exists (select * from nameid where name_id = nameid_name) --select * from account where Account_ID =804961 --select replace('100-265-995','-','') as newtrn --select * from RSUDependnew..CP_Member where Member_ACCOUNT ='804961' /* select * from NAMEID left join name on nameid_name = name.name_id where NAMEID_VALUE = '100981496'*/ --select MEMBER_TRN from RSUDepend..CP_Member -- --select * from [dbo].[CFUNCTION_CONNECTED_PARTY]('JME201600017','2016-07-31') /*where account='8049587'*/order by account --SELECT * FROM [dbo].[CFUNCTION_CONNECTED_PARTY_TRN] ('JME201600017','2016-07-31','100981496') ALTER function [dbo].[CFUNCTION_CONNECTED_PARTY] ( @ISIN_CODE nvarchar(12) ,@RECORD_DATE date ) returns table as return ( --select * from RSUDepend..CP_MEMBER where = '100981496' SELECT DISTINCT KeyMember.NAME_TEXT AS Name ,COALESCE(ConnectedMember.NAME_TEXT,KeyMember.NAME_TEXT) AS PARTY ,A.ACCOUNT_REFERENCE AS ACCOUNT ,ConnectedMember.NAME_ID AS CNI ,KeyMember.NAME_ID AS KNI ,case /*when c1.Relationship is null then 'Self'*/ when (c1.Relationship is null and ConnectedMember.NAME_ID!= KeyMember.NAME_ID )then 'Unknown' when (c1.Relationship is null AND ConnectedMember.NAME_ID = KeyMember.NAME_ID) then 'Self' else c1.Relationship end as Relationship ,ISI.ISIN_CODE AS ISIN ,ISI.ISIN_SHORT_NAME AS Symbol ,ISI.ISIN_FULL_NAME AS Symbol_Name ,ISIN_SHARES AS ISSUED_SHARES ,CASE WHEN c1.Relationship ='self' OR c1.Relationship IS NULL THEN ISNULL(BAL.Volume,0) ELSE NULL END AS Volume ,case when c1.Relationship is null THEN 1 ELSE c1.SortOrder END AS SortCol ,CASE WHEN c1.Relationship ='self' OR c1.Relationship IS NULL THEN ISNULL(BAL.percentage,0) ELSE NULL END AS percentage ,CASE WHEN KeyMember.NAME_ID_CODE IS NOT NULL THEN KeyMember.NAME_ID_CODE WHEN Keymember.NAME_NI IS NOT NULL THEN KeyMember.NAME_NI ELSE KeyMemberNID.NAMEID_VALUE END AS TRN ,DIRECTOR_STATUS.DIRECTOR_STATUS_DESCRIPTION AS Position ,ISNULL(CASE WHEN KeyMember.NAME_ID_CODE IS NOT NULL THEN KeyMember.NAME_ID_CODE WHEN KeyMember.NAME_NI IS NOT NULL THEN KeyMember.NAME_NI ELSE KeyMemberNID.NAMEID_VALUE END , A.ACCOUNT_ID) AS Mask_TRN ,(SELECT TOP(1)NAME_TEXT FROM NAME AS JOINTMEMBERA WHERE(NAME_ACCOUNT = A.ACCOUNT_ID) AND (NAME_TYPE = 'O')AND (NAME_SEQUENCE = 2)) AS JointMember1 ,(SELECT TOP(1)NAME_TEXT FROM NAME AS JOINTMEMBERB WHERE(NAME_ACCOUNT = A.ACCOUNT_ID) AND (NAME_TYPE = 'O')AND (NAME_SEQUENCE = 3)) AS JointMember2 ,(SELECT TOP(1)NAME_TEXT FROM NAME AS JOINTMEMBERC WHERE(NAME_ACCOUNT = A.ACCOUNT_ID) AND (NAME_TYPE = 'O')AND (NAME_SEQUENCE = 4)) AS JointMember3 ,@RECORD_DATE as RecordDate FROM RSUDependNew..CP_Member KM JOIN CONFIG_DIRECTOR_STATUS DIRECTOR_STATUS ON DIRECTOR_STATUS.DIRECTOR_STATUS_CODE = KM.MEMBER_DIRECTOR COLLATE Latin1_General_CI_AS JOIN ISIN ISI ON ISI.ISIN_ID = KM.MEMBER_ISIN AND ISI.ISIN_CODE = ISNULL(@ISIN_CODE,(SELECT MAX(ISIN_CODE) FROM ISIN WHERE ISIN_CODE LIKE 'J%')) LEFT JOIN NAMEID KeyMemberNID ON ((KeyMemberNID.NAMEID_VALUE COLLATE Latin1_General_CI_AS = KM.MEMBER_TRN AND KeyMemberNID.NAMEID_TYPE = 'TRN' AND ISI.ISIN_ID = KM.MEMBER_ISIN AND ISI.ISIN_CODE = ISNULL(@ISIN_CODE,(SELECT MAX(ISIN_CODE) FROM ISIN WHERE ISIN_CODE LIKE 'J%' )))) JOIN NAME KeyMember ON (KeyMember.NAME_ID = KeyMemberNID.NAMEID_NAME AND KeyMember.NAME_SEQUENCE = 1) left join (select c.*,r.Relationship,r.SortOrder from [RSUDependNew]..CP_Connections c /*c on c.keyMemberNameID = N.NAME_ID*/ left join RSUDependNew..CP_Relationships r on c.RelationshipID = r.RelationshipID) c1 on c1.keyMemberNameID = KeyMember.NAME_ID JOIN Name ConnectedMember on ((c1.ConnectedPartyNameID = ConnectedMember.NAME_ID OR (ConnectedMember.NAME_ID = KeyMemberNID.NAMEID_NAME and ConnectedMember.NAME_ID <> c1.KeyMemberNameID))) OR(ConnectedMember.NAME_ID != KeyMember.NAME_ID AND ConnectedMember.NAME_ACCOUNT = KeyMember.NAME_ACCOUNT AND ConnectedMember.NAME_SEQUENCE != 1) --LEFT JOIN NAMEID ConnectedMemberNID ON (ConnectedMember.NAME_ID = ConnectedMemberNID.NAMEID_NAME and ConnectedMemberNID.NAMEID_VALUE COLLATE Latin1_General_CI_AS != KM.MEMBER_TRN) JOIN Account A ON A.ACCOUNT_ID =ISNULL(ConnectedMember.NAME_ACCOUNT,KM.MEMBER_ACCOUNT) LEFT JOIN (SELECT A.ACCOUNT_ID ,I.ISIN_SHORT_NAME as Symbol ,B.BALTIME_BALANCE as Volume ,CASE WHEN I.ISIN_SHARES <> 0 THEN B.BALTIME_BALANCE/I.ISIN_SHARES ELSE 0 END as percentage from BALTIME B JOIN ACCOUNT A ON A.ACCOUNT_ID = B.BALTIME_ACCOUNT JOIN NAME N ON N.NAME_ACCOUNT = A.ACCOUNT_ID AND N.NAME_TYPE = 'O' --and N.NAME_SEQUENCE = 1 JOIN ISIN I ON I.ISIN_ID = B.BALTIME_ISIN WHERE I.ISIN_CODE = isnull(@ISIN_CODE,(SELECT max(ISIN_CODE) FROM ISIN WHERE ISIN_CODE like 'J%')) AND B.BALTIME_START<= isnull(@RECORD_DATE,GETDATE()) AND isnull(@RECORD_DATE,GETDATE()) < B.BALTIME_END ) BAL ON BAL.ACCOUNT_ID = A.ACCOUNT_ID WHERE isnull(@RECORD_DATE,GETDATE()) between [MEMBER_JOINED] and [MEMBER_RESIGNED]) --AND KeyMember.NAME_ACCOUNT = '804958' The query above is supposed to identify the relationships of person's in the account based on custom tables but some records are being returned with the wrong relationship
Need Help with Query that is supposed to identify relationships between Account member and creates report
Please post DDL for the tables and some sample data insert statements.