Welcome to the Lounge

-- Build the View of the Maximiser data that shows what we want and store it

SELECT     
CASE 
	WHEN c.Record_Type = 1 THEN c.Name
	WHEN c.Record_Type = 31 THEN d.Name + ' - ' + c.First_Name + ' ' + c.Name
	WHEN c.Record_Type = 2 AND len(c.Firm) > 0 THEN c.Firm
	WHEN c.Record_Type = 2 AND len(c.Firm) < 1 THEN c.First_Name + ' ' + c.Name
	WHEN c.Record_Type = 32 THEN 
	(
		CASE 
			WHEN len(d.Firm) > 0 THEN d.Firm + ' - ' + c.First_Name + ' ' + c.Name
			WHEN len(d.Firm) < 1 THEN d.First_Name + ' ' + d.Name + ' - ' + c.First_Name + ' ' + c.Name 
		END
	)
	ELSE c.Name
END AS Company,
CASE
	WHEN c.Address_Id > 0 AND c.Record_Type IN (1, 31) THEN e.Address_Line_1 
	WHEN c.Address_Id < 1 AND c.Record_Type = 31 THEN  g.Address_Line_1
	WHEN c.Address_Id > 0 AND c.Record_Type IN (2, 32) THEN f.Address_Line_1
	WHEN c.Address_Id < 1 AND c.Record_Type = 32 THEN  g.Address_Line_1
	ELSE c.Address_Line_1
END AS Address_1,
CASE 
	WHEN c.Address_Id > 0 AND c.Record_Type IN (1, 31) THEN e.Address_Line_2 
	WHEN c.Address_Id < 1 AND c.Record_Type = 31 THEN  g.Address_Line_2
	WHEN c.Address_Id > 0 AND c.Record_Type IN (2, 32) THEN f.Address_Line_2
	WHEN c.Address_Id < 1 AND c.Record_Type = 32 THEN  g.Address_Line_2
	ELSE  c.Address_Line_2
END AS Address_2,
CASE 
	WHEN c.Address_Id > 0 AND c.Record_Type IN (1, 31) THEN e.City
	WHEN c.Address_Id < 1 AND c.Record_Type = 31 THEN  g.City
	WHEN c.Address_Id > 0 AND c.Record_Type IN (2, 32) THEN f.City
	WHEN c.Address_Id < 1 AND c.Record_Type = 32 THEN  g.City
	ELSE  c.City
END AS City,
CASE 
	WHEN c.Address_Id > 0 AND c.Record_Type IN (1, 31) THEN e.State_Province
	WHEN c.Address_Id < 1 AND c.Record_Type = 31 THEN  g.State_Province
	WHEN c.Address_Id > 0 AND c.Record_Type IN (2, 32) THEN  f.State_Province
	WHEN c.Address_Id < 1 AND c.Record_Type = 32 THEN  g.State_Province
	ELSE  c.State_Province
END AS State,
CASE 
	WHEN c.Address_Id > 0 AND c.Record_Type IN (1, 31) THEN  e.Zip_Code
	WHEN c.Address_Id < 1 AND c.Record_Type = 31 THEN  g.Zip_Code
	WHEN c.Address_Id > 0 AND c.Record_Type IN (2, 32) THEN  f.Zip_Code
	WHEN c.Address_Id < 1 AND c.Record_Type = 32 THEN  g.Zip_Code
	ELSE  c.Zip_Code
END AS Zip,
CASE 
	WHEN c.Address_Id > 0 AND c.Record_Type IN (1, 31) THEN  e.Country
	WHEN c.Address_Id < 1 AND c.Record_Type = 31 THEN  g.Country
	WHEN c.Address_Id > 0 AND c.Record_Type IN (2, 32) THEN  f.Country
	WHEN c.Address_Id < 1 AND c.Record_Type = 32 THEN      g.Country
	ELSE c.Country
END AS Country,
CASE 
	WHEN n.Type = 0 THEN  'Manual Note'
	WHEN n.Type = 1 THEN  'Mail - Out' 
	WHEN n.Type = 2 THEN  'Phone Call'
	WHEN n.Type = 3 THEN  'Timed Note'
	WHEN n.Type = 4 THEN  'Transfer'
	WHEN n.Type = 5 THEN  'Task'
	WHEN n.Type = 6 THEN  'Reserved' 
	WHEN n.Type = 7 THEN  'Reserved'
	WHEN n.Type = 8 THEN  'Opportunity'
	WHEN n.Type = 12 THEN 'Customer Service'
	ELSE  'Unknown'
END AS Activity_Type,
n.DateCol, n.TextCol, n.Owner_Id, n.Client_Id, n.Contact_Number, n.Note_Type,
'    ' AS sndex 
INTO BookerNotes
FROM
	dbo.AMGR_Client_Tbl AS c 
	LEFT OUTER JOIN dbo.AMGR_Client_Tbl AS d ON c.Client_Id = d.Client_Id AND d.Contact_Number = 0 
	LEFT OUTER JOIN dbo.AMGR_Client_Tbl AS e ON c.Client_Id = e.Client_Id AND c.Address_Id = e.Contact_Number 
	LEFT OUTER JOIN dbo.AMGR_Client_Tbl AS f ON c.Client_Id = f.Client_Id AND c.Address_Id = f.Contact_Number 
	LEFT OUTER JOIN dbo.AMGR_Client_Tbl AS g ON c.Client_Id = g.Client_Id AND g.Contact_Number = 0 
	RIGHT OUTER JOIN dbo.AMGR_Notes_Tbl AS n ON c.Client_Id = n.Client_Id AND c.Contact_Number = n.Contact_Number
WHERE c.Record_Type IN (1, 2, 31, 32)
GO

Southmountain wrote:

it has hundreds of fields

Southmountain wrote:

what is the best way to get understanding of this table quickly?

Southmountain wrote:

what is the best way to get understanding of this table quickly?

Quote:

«Il faut imaginer Sisyphe heureux»
Albert Camus