SQL Query logic issue

Need help with an sql query
Stuck on logic :(

Current output is like this
Config_Name	Survey_Name	ASN	ASNType		Status_Name	Company_ID		Contact_Name	Contact_Number
ASN		Network ASN	12345	Public		Active		A			User1		" "
ASN		Network ASN	12345	Public		Active		A			User1		(212) 123-4567
ASN		Network ASN	12345	Public		Active		A			User1		Direct
ASN		Network ASN	54321	Private		Active		B			User2		NULL
ASN		Network ASN	98765	Private		Active		C			User3		NULL
ASN		Network ASN	98765	Private		Active		C			User3		(800) 123-4567
ASN		Network ASN	98765	Private		Active		C			User3		Direct

Open in new window


Need output to look like this

Config_Name	Survey_Name	ASN	ASNType		Status_Name	Company_ID		Contact_Name	Contact_Number
ASN		Network ASN	12345	Public		Active		A			User1		(212) 123-4567
ASN		Network ASN	54321	Private		Active		B			User2		NULL
ASN		Network ASN	98765	Private		Active		C			User3		(800) 123-4567

Open in new window


Here is my current sql query

SELECT DISTINCT a.[Config_Name], a.[Survey_Name], a.[ASN], a.[ASNType],a.[Status_Name],a.[Company_ID],a.[Contact_Name],a.[Contact_Number]
		  FROM 
		(SELECT DISTINCT ccc.[Config_Name],ccc.[Survey_Name],asncrd.[Answer] as [ASN],asntypecrd.[Answer] as [ASNType],ccc.[Status_Name],ccc.[Company_ID], ccc.[Contact_Name],cav.[Audit_Value] as [Contact_Number]
		  ,ccc.[Device_ID],ccc.[Serial_Number],ccc.[Model_Number],ccc.[Tag_Number],ccc.[Date_Purchased],ccc.[Date_Installed],ccc.[Installed_By],ccc.[Date_Expiration],ccc.[Vendor_Notes]
		  ,ccc.[Notes],ccc.[MAC_Address],ccc.[Last_Login],ccc.[IP_Address],ccc.[Default_Gateway],ccc.[Os_Type],ccc.[Os_Info],ccc.[Cpu_Speed],ccc.[RAM],ccc.[Local_Hard_Drives],ccc.[Active_Flag]
		  ,ccc.[Last_Update_UTC],ccc.[Updated_By],ccc.[Company_Name],ccc.[Vendor_Company_Name],ccc.[Installed_By_RecID],ccc.[SR_SLA_RecID],ccc.[SLA_Name],ccc.[Installed_By_Name],ccc.[Display_Vendor_Flag]
		  ,ccc.[Entered_By],ccc.[Date_Entered_UTC]
			FROM [CWPROD].[cwwebapp_infohedge].[dbo].[v_api_collection_company_configuration] ccc WITH (NOLOCK)
			LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[v_api_collection_company_configuration_type_question] asn WITH (NOLOCK) ON asn.[CS_Survey_RecID] = ccc.[CS_Survey_RecID] AND asn.question LIKE 'Enter the Vendor''s ASN number:'
			LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[v_api_collection_company_configuration_type_question] asntype WITH (NOLOCK) ON asntype.[CS_Survey_RecID] = ccc.[CS_Survey_RecID] AND asntype.question ='ASN Type Public/Private:'
			LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[CS_Result_Detail] asncrd WITH (NOLOCK) ON asncrd.[CS_Survey_Detail_RecID] = asn.[CS_Survey_Detail_RecID] AND asncrd.CS_Result_RecID = ccc.Config_RecID
			LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[CS_Result_Detail] asntypecrd WITH (NOLOCK) ON asntypecrd.[CS_Survey_Detail_RecID] = asntype.[CS_Survey_Detail_RecID] AND asntypecrd.CS_Result_RecID = ccc.Config_RecID
			LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[Contact] c WITH (NOLOCK) ON c.[Company_RecID] = ccc.[Company_RecID]
			LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[Contact_Audit] ca WITH (NOLOCK) ON ca.Contact_RecID = c.[Contact_RecID]
			LEFT JOIN (SELECT [Contact_Audit_RecID] FROM [CWPROD].[cwwebapp_infohedge].[dbo].[Contact_Audit_Value] WHERE [Audit_Value] = 'Direct') as t ON t.Contact_Audit_RecID = ca.Contact_Audit_RecID
			LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[Contact_Audit_Value] cav WITH (NOLOCK) ON cav.Contact_Audit_RecID = t.Contact_Audit_RecID
			where ccc.[Survey_Name] = 'Network ASN' ) as a

Open in new window

LVL 4
Mauro CazabonnetSenior Software EngineerAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
In this case an extended JOIN predicate should do it:

LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[Contact_Audit_Value] cav WITH (NOLOCK) ON cav.Contact_Audit_RecID = t.Contact_Audit_RecID AND cav.Type = 'main'

Open in new window

0
 
ste5anSenior DeveloperCommented:
Well, what is your rule for picking the correct value in Contact_Number? What is the cardinality between Contact_Audit_Value and the other tables? Or in other words, why do you have three rows?
0
 
Mauro CazabonnetSenior Software EngineerAuthor Commented:
Ahh yes
There are direct, fax and main contact numbers

I have this so far with a temp table I was trying to avoid

DECLARE @TEMP TABLE
		(
			ASN_CNT int,
			ASN varchar(10)
		)

		INSERT INTO @TEMP
		SELECT DISTINCT COUNT (CASE WHEN a.[Contact_Number] IS NULL THEN 'NA' ELSE a.[Contact_Number] END) as [Contact_Cnt], a.[ASN]
				  FROM 
				(SELECT DISTINCT ccc.[Config_Name],ccc.[Survey_Name],asncrd.[Answer] as [ASN],asntypecrd.[Answer] as [ASNType],ccc.[Status_Name],ccc.[Company_ID], ccc.[Contact_Name],cav.[Audit_Value] as [Contact_Number]
					FROM [CWPROD].[cwwebapp_infohedge].[dbo].[v_api_collection_company_configuration] ccc WITH (NOLOCK)
					LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[v_api_collection_company_configuration_type_question] asn WITH (NOLOCK) ON asn.[CS_Survey_RecID] = ccc.[CS_Survey_RecID] AND asn.question LIKE 'Enter the Vendor''s ASN number:'
					LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[v_api_collection_company_configuration_type_question] asntype WITH (NOLOCK) ON asntype.[CS_Survey_RecID] = ccc.[CS_Survey_RecID] AND asntype.question ='ASN Type Public/Private:'
					LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[CS_Result_Detail] asncrd WITH (NOLOCK) ON asncrd.[CS_Survey_Detail_RecID] = asn.[CS_Survey_Detail_RecID] AND asncrd.CS_Result_RecID = ccc.Config_RecID
					LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[CS_Result_Detail] asntypecrd WITH (NOLOCK) ON asntypecrd.[CS_Survey_Detail_RecID] = asntype.[CS_Survey_Detail_RecID] AND asntypecrd.CS_Result_RecID = ccc.Config_RecID
					LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[Contact] c WITH (NOLOCK) ON c.[Company_RecID] = ccc.[Company_RecID]
					LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[Contact_Audit] ca WITH (NOLOCK) ON ca.Contact_RecID = c.[Contact_RecID]
					LEFT JOIN (SELECT [Contact_Audit_RecID] FROM [CWPROD].[cwwebapp_infohedge].[dbo].[Contact_Audit_Value] WHERE [Audit_Value] = 'Direct') as t ON t.Contact_Audit_RecID = ca.Contact_Audit_RecID
					LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[Contact_Audit_Value] cav WITH (NOLOCK) ON cav.Contact_Audit_RecID = t.Contact_Audit_RecID
					where ccc.[Survey_Name] = 'Network ASN') as a
					GROUP BY a.[Config_Name], a.[Survey_Name], a.[ASN], a.[ASNType],a.[Status_Name],a.[Company_ID],a.[Contact_Name]

		SELECT * FROM (
		SELECT DISTINCT a.ASN_CNT, a.[Config_Name], a.[Survey_Name], a.[ASN], a.[ASNType],a.[Status_Name],a.[Company_ID],a.[Contact_Name], CASE WHEN a.ASN_CNT = 1 AND PATINDEX('%[0-9]%',a.[Contact_Number]) = 0 THEN a.[Contact_Number] WHEN a.ASN_CNT > 1 AND PATINDEX('%[0-9]%',a.[Contact_Number]) > 0 THEN a.[Contact_Number] END as [Contact_Number]
				  ,a.[Device_ID],a.[Serial_Number],a.[Model_Number],a.[Tag_Number],a.[Date_Purchased],a.[Date_Installed],a.[Installed_By],a.[Date_Expiration],a.[Vendor_Notes]
				  ,a.[Notes],a.[MAC_Address],a.[Last_Login],a.[IP_Address],a.[Default_Gateway],a.[Os_Type],a.[Os_Info],a.[Cpu_Speed],a.[RAM],a.[Local_Hard_Drives],a.[Active_Flag]
				  ,a.[Last_Update_UTC],a.[Updated_By],a.[Company_Name],a.[Vendor_Company_Name],a.[Installed_By_RecID],a.[SR_SLA_RecID],a.[SLA_Name],a.[Installed_By_Name],a.[Display_Vendor_Flag]
				  ,a.[Entered_By],a.[Date_Entered_UTC] 
				  FROM 
				(SELECT DISTINCT tmp.ASN_CNT, ccc.[Config_Name],ccc.[Survey_Name],asncrd.[Answer] as [ASN],asntypecrd.[Answer] as [ASNType],ccc.[Status_Name],ccc.[Company_ID], ccc.[Contact_Name],CASE WHEN cav.[Audit_Value] IS NULL THEN 'NA' ELSE cav.[Audit_Value] END as [Contact_Number]
				  ,ccc.[Device_ID],ccc.[Serial_Number],ccc.[Model_Number],ccc.[Tag_Number],ccc.[Date_Purchased],ccc.[Date_Installed],ccc.[Installed_By],ccc.[Date_Expiration],ccc.[Vendor_Notes]
				  ,ccc.[Notes],ccc.[MAC_Address],ccc.[Last_Login],ccc.[IP_Address],ccc.[Default_Gateway],ccc.[Os_Type],ccc.[Os_Info],ccc.[Cpu_Speed],ccc.[RAM],ccc.[Local_Hard_Drives],ccc.[Active_Flag]
				  ,ccc.[Last_Update_UTC],ccc.[Updated_By],ccc.[Company_Name],ccc.[Vendor_Company_Name],ccc.[Installed_By_RecID],ccc.[SR_SLA_RecID],ccc.[SLA_Name],ccc.[Installed_By_Name],ccc.[Display_Vendor_Flag]
				  ,ccc.[Entered_By],ccc.[Date_Entered_UTC]
					FROM [CWPROD].[cwwebapp_infohedge].[dbo].[v_api_collection_company_configuration] ccc WITH (NOLOCK)
					LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[v_api_collection_company_configuration_type_question] asn WITH (NOLOCK) ON asn.[CS_Survey_RecID] = ccc.[CS_Survey_RecID] AND asn.question LIKE 'Enter the Vendor''s ASN number:'
					LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[v_api_collection_company_configuration_type_question] asntype WITH (NOLOCK) ON asntype.[CS_Survey_RecID] = ccc.[CS_Survey_RecID] AND asntype.question ='ASN Type Public/Private:'
					LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[CS_Result_Detail] asncrd WITH (NOLOCK) ON asncrd.[CS_Survey_Detail_RecID] = asn.[CS_Survey_Detail_RecID] AND asncrd.CS_Result_RecID = ccc.Config_RecID
					LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[CS_Result_Detail] asntypecrd WITH (NOLOCK) ON asntypecrd.[CS_Survey_Detail_RecID] = asntype.[CS_Survey_Detail_RecID] AND asntypecrd.CS_Result_RecID = ccc.Config_RecID
					LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[Contact] c WITH (NOLOCK) ON c.[Company_RecID] = ccc.[Company_RecID]
					LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[Contact_Audit] ca WITH (NOLOCK) ON ca.Contact_RecID = c.[Contact_RecID]
					LEFT JOIN (SELECT [Contact_Audit_RecID] FROM [CWPROD].[cwwebapp_infohedge].[dbo].[Contact_Audit_Value] WHERE [Audit_Value] = 'Direct') as t ON t.Contact_Audit_RecID = ca.Contact_Audit_RecID
					LEFT JOIN [CWPROD].[cwwebapp_infohedge].[dbo].[Contact_Audit_Value] cav WITH (NOLOCK) ON cav.Contact_Audit_RecID = t.Contact_Audit_RecID
					LEFT JOIN @TEMP tmp ON tmp.ASN = asncrd.[Answer]
					where ccc.[Survey_Name] = 'Network ASN') as a) as b
		WHERE b.Contact_Number IS NOT NULL

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mauro CazabonnetSenior Software EngineerAuthor Commented:
Pulling direct only otherwise Not available
0
 
ste5anSenior DeveloperCommented:
???

btw, you know, that NOLOCK can return terrible wrong results?
0
 
Mauro CazabonnetSenior Software EngineerAuthor Commented:
Yes I'm aware about dirty data, want to avoid table locks
0
 
Mauro CazabonnetSenior Software EngineerAuthor Commented:
Thx for the assist

Regards,
M
0
 
ste5anSenior DeveloperCommented:
Not only dirty data. When table reorg happens, you can miss entire pages. Thus many rows missing. Better indices also avoid locks and increase performance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.