SQLTeam.com | Weblogs | Forums

Need Help with Query that is supposed to identify relationships between Account member and creates report

sql2014

#1
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

#2

Please post DDL for the tables and some sample data insert statements.