none
Locations List: Substring, Location Name, Location Address... RRS feed

  • Question

  • Hello,

    I have a query :

    SELECT DISTINCT
    -- System Name
    	CPU.SystemName0 AS 'Hostname', 
    -- Operating System
    CASE
    	WHEN SYS.Operating_System_Name_and0 LIKE '%Windows%' THEN 'Windows'
    	ELSE 'Unknown'
    END AS 'OS',
    -- Host 
    	SYS.Virtual_Machine_Host_Name0 AS 'Hostname of the Physical Parent',
    -- Serial Number
    	PC.SerialNumber0 AS 'Serial #',
    -- Manufacturer
    	CS.Manufacturer0 AS 'Server Make',
    -- Model
    	CS.Model0 AS 'Server Model',
    -- Physical Processor 
    CASE	
    	WHEN SYS.Is_Virtual_Machine0 = 1 THEN ' '
    	ELSE COUNT(CPU.ResourceID)
    END AS [Physical CPU Quantity],
    	CPU.NumberOfCores0 AS '# Cores per Physical CPU',
    -- Processor type and Model
    	CPU.Name0 as 'Processor Type and Model', 
    -- Vitual Machine Type
    CASE	
    	WHEN SYS.Is_Virtual_Machine0 = 1 THEN
    								-- Manufacturer
    									CASE
    										WHEN CS.Manufacturer0 Like '%VMWare%' THEN 'VMWare'
    										WHEN CS.Manufacturer0 Like '%Microsoft%' THEN 'Hyper-V'
    										ELSE 'Unknown'
    									END
    	ELSE ' '
    END AS 'Virtualization technology',
    -- Virtual Processor
    CASE	
    	WHEN SYS.Is_Virtual_Machine0 = 1 THEN COUNT(CPU.ResourceID)
    	ELSE ' '
    END AS 'Virtual CPU (VMWare)',
    -- Cluster Name
    CASE
    	WHEN CSE.DisplayName0 = 'Cluster Service' and CSE.StartMode0 = 'Auto' THEN CSE.Name0
    	ELSE ' '
    END AS 'Cluster Group',
    -- Insertion 6 AIX Columns
    	NULL as Placeholder, -- for column having null value LPAR Sharing Mode (IBM Power Systems)
    	NULL as Placeholder, -- for column having null value Entitled Capacity (AIX)
    	NULL as Placeholder, -- for column having null value Online Virtual CPUs (AIX)
    	NULL as Placeholder, -- for column having null value Shared Pool ID (AIX)
    	NULL as Placeholder, -- for column having null value Active CPUs in Pool (AIX)
    	NULL as Placeholder, -- for column having null value Active Physical CPUs in System (AIX)
    --'Installation Date'
    FORMAT(OPSYS.[InstallDate0],'MM/dd/yyyy') AS 'Install Date',
    	NULL as Placeholder, -- for column having null value Bundling
    	NULL as Placeholder, -- for column having null value Disaster Recovery Type (Hot. Warm, Cold)
    -- Server: 3rd character of the name P=Prod, T=Test, D=Dev
    -- Desktop
    --		ad.medctr.ucla.edu/MC/MCCS/Desktop Lab Test/desk-patch-testing
    --		Faysal’s team handles the UAT and patch testing each month using machines in the above OU. 
    --
    --		(ISS CTO & Pilot Groups)
    --		Desktop Support: 
    --		ad.medctr.ucla.edu/MC/MCCS/Computers/ISS Desktop N
    --		Customer Care:
    --		ad.medctr.ucla.edu/MC/MCCS/Computers/Help Desk N
    --
    --		All of ISS are located somewhere in these OUs:
    --		ad.medctr.ucla.edu/MC/MCCS/Computers (root)
    --		•	Computers Main (win7)
    --		•	Computers limited Policies
    --		•	Computers N (win10)
    --		•	EHR & EHR N
    -- Windows Servers 
    CASE
    	WHEN SYS.Operating_System_Name_and0 LIKE '%Server%' THEN 
    																	CASE 
    																		WHEN SUBSTRING(CPU.SystemName0,3,1) = 'P' THEN 'Prod'
    																		WHEN SUBSTRING(CPU.SystemName0,3,1) = 'D' THEN 'Dev'
    																		WHEN SUBSTRING(CPU.SystemName0,3,1) = 'T' THEN 'Test'
    																		WHEN SUBSTRING(CPU.SystemName0,3,1) = 'S' THEN 'Staging'
    																		ELSE ' '
    																	END
    -- Windows Workstation 
    	WHEN	SYS.Operating_System_Name_and0 LIKE '%Workstation%' 
    	OR 		SYS.Operating_System_Name_and0 = 'Windows Developer Preview 6.2'
    	OR		SYS.Operating_System_Name_and0 = 'Windows Embedded Standard 6.1'
    	OR		SYS.Operating_System_Name_and0 = 'Windows Technical Preview for Enterprise 6.4'
    																THEN 
    																	CASE 
    																		WHEN OUN.System_OU_Name0 = 'ad.medctr.ucla.edu/MC/MCCS/Desktop Lab Test/desk-patch-testing' THEN 'Test'
    																		WHEN OUN.System_OU_Name0 = 'ad.medctr.ucla.edu/MC/MCCS/Computers/ISS Desktop N' THEN 'Pilot'
    																		WHEN OUN.System_OU_Name0 = 'ad.medctr.ucla.edu/MC/MCCS/Computers/Help Desk N' THEN 'Pilot'
    																		ELSE 'PROD'
    																	END
    	ELSE 'Unknown'
    END AS 'Environment',
    -- Offline / Not in Use
    	NULL as Placeholder, -- for column having null value 
    -- Location
    -- Server: 2nd character of the name R=Ronald Regan Hospital, I=Irvine Data Center, S=San Diego DataCenter, O=Oppenheimer, M=Santa Monica, 
    -- Desktop
    CASE 
    	WHEN SUBSTRING(CPU.SystemName0,2,1) = 'R' THEN 'RRH'
    	WHEN SUBSTRING(CPU.SystemName0,2,1) = 'I' THEN 'SDC'
    	WHEN SUBSTRING(CPU.SystemName0,2,1) = 'S' THEN 'SDSC'
    	WHEN SUBSTRING(CPU.SystemName0,2,1) = 'O' THEN 'MCO'
    	WHEN SUBSTRING(CPU.SystemName0,2,1) = 'M' THEN 'SMH'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'CHS' THEN 'CHS'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'MP1' THEN 'MP1'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'MP2' THEN 'MP2'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'MP3' THEN 'MP3'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'OISS' THEN 'OPP'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'OPP' THEN 'OPP'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'BOA' THEN '924 Westwood Blvd.'
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'BW' THEN '11980 Brentwood Plaza, Los Angeles'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'CHS' THEN '10833 LeConte Ave Los Angeles 90095'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'CPN' THEN ''
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'WS' THEN '300 Stein Plaza Driveway'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'FAC' THEN '700 Tiverton Dr. '
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'JS' THEN '100 Stein Plaza Driveway, Los Angeles'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'MOC' THEN '2211 Michigan Ave. Santa Monica Ca 90404'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'MP1' THEN 'Vatche and Tamar Medical Building – 100 UCLA Medical Plaza'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'MP2' THEN '200 UCLA Medical Plaza'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'MP3' THEN '300 UCLA Medical Plaza'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'NPH' THEN 'Semel Institue for Neuroscience and Human Behavior 760 Westwood Plaza'
    	WHEN SUBSTRING(CPU.SystemName0,1,1) = 'O' THEN '10880 Wilshire Blvd., Westwood'
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'UB' THEN '10945 Le Conte Ave., Los Angeles Ca 90024'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'PVUB' THEN '10945 Le Conte Ave., Los Angeles Ca 90024'
    	WHEN SUBSTRING(CPU.SystemName0,1,1) = 'R' THEN '757 Westwood Plaza, Los Angeles Ca'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'REED' THEN 'Reed Neurological Center 710 Westwood Plaza'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'RHB' THEN '1000 Veteran Ave.'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'S12' THEN '1131 Wilshire Blvd., Santa Monica'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'S15' THEN '1260 15th St. Santa monica Ca'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'S16' THEN '1245 16th St. Santa Monica Ca'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'SAOB' THEN '1336 16th St. Santa Monica'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'S18' THEN '1801, 1811, 1821, 1831 Wilshire Blvd Santa Monica'
    	WHEN SUBSTRING(CPU.SystemName0,1,1) = 'S' THEN '1250 16th st., Santa Monica Ca'
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'SO' THEN '1225 15th St. Santa Monica hospital'
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'SC' THEN '1223 16th st., Santa Monica, '
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'SH' THEN '1317 15th St. Santa Monica Ca'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'TIV' THEN '900 Tiverton Ave., Los Angeles'
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'WC' THEN '10920 Wilshire Blvd., los Angeles'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'HOF' THEN '1530 Arizona Blvd, Santa Monica'
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'TS' THEN '936 Westwood Blvd'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'CVS' THEN '1001 Westwood'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'GLY' THEN '1145 Gayley Blvd'
    	WHEN SUBSTRING(CPU.SystemName0,1,6) = 'GA1001' THEN '1001 Gayley'
    	WHEN SUBSTRING(CPU.SystemName0,1,6) = 'GA1072' THEN '1072 Gayley'
    	WHEN SUBSTRING(CPU.SystemName0,1,7) = '1045GLY' THEN '1045 Gayley'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'S20' THEN '2020 Santa Monica Blvd'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'S19' THEN '1919 Santa Monica Blvd'
    	WHEN SUBSTRING(CPU.SystemName0,1,5) = 'S2001' THEN '2001 Santa Monica Blvd'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SWZ' THEN '1328 16th st Santa Monica'
    	WHEN SUBSTRING(CPU.SystemName0,1,7) = 'STANFRD' THEN '1717 Stanford St., Santa Monica Ca'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'STRL' THEN '1344 16th St Santa Monica'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'TELE' THEN 'All work from home'
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'LC' THEN 'Quote needed for Suite 305'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'WFCC' THEN '10250 Santa Monica Blvd'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'WLA' THEN '11318 National Blvd., Los Angeles Ca 90064'
    	WHEN SUBSTRING(CPU.SystemName0,1,5) = 'S1304' THEN '1304 15th St. Santa Monica'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'WSHC' THEN '1950 Sawtelle Blvd., Los Angeles Ca 90025'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'W24' THEN '2424 Wilshire Blvd., Santa Monica 90404'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = 'Old: SPS, PS2428SMB, CNPS2428SMB; New: S2428' THEN '2428 Santa Monica Blvd., '
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SMP' THEN '2825 Santa Monica Blvd.,'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'BHHC' THEN '335 N La Brea Ave., Los Angeles'
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'PP' THEN '881 Alma Real Dr., Pacific Palisades ca 90272'
    	WHEN SUBSTRING(CPU.SystemName0,1,5) = 'S2336' THEN '2336 Santa Monica Blvd'
    	WHEN SUBSTRING(CPU.SystemName0,1,9) = 'DWHC1000F' THEN '1000 Flower St, Glendale, CA 91201'
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNDWHC1000F' THEN '1000 Flower St, Glendale, CA 91201'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '1111 S. Figueroa St, Los Angeles, CA 90015'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'BBS191BV / CNBBS191BV' THEN '191 S. Buena Vista, Suite 415, Burbank, CA 91505'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'BPT2211MA / CNBPT2211MA' THEN '2211 Magnolia Ave, #160, Burbank, CA 91506'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'TLHC4343RD / CNTLHC4323RD' THEN '4323 Riverside Drive, Burbank, CA  91505'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'PRN19950R / CNPRN19950R' THEN '19950 Rinaldi St, Suite 300, Porter Ranch, CA 91326'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'AW23388MD / CNAW23388MD' THEN '23388 Mulholland Drive - Lodge building, Woodland Hills, CA  91364'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'WHPTP23388 / CNWHPTP23388' THEN '23388 Mulholland Drive - Saban Pool, Woodland Hills, CA  91364'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'WHPT23388 / CNWHPT23388' THEN '23388 Mulholland Drive - Saban PT building, Woodland Hills, CA  91364'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'JSHC23388 / CNJSHC23388' THEN '23388 Mulholland Drive, Woodland Hills, CA  91364'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SCOB23861/CNSCOB23861' THEN '23861 McBean Pkwy, Suite E24, Valencia, CA  91355'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '250 W Arminta St., Panorama City, CA 91402'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SCPT25751 / CNSCPT25751' THEN '25751 McBean Parkway, Suite 200, Valencia, CA  91355'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SCHC25751 / CNSCHC25751' THEN '25751 McBean Parkway, Suite 210, Valencia, CA  91355'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SCRC' THEN '27235 Tourney Rd., Suite 1500, Santa Clarita, CA 91355'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SCRC2111' THEN '27235 Tourney Rd., Suite 2100, Santa Clarita, CA 91355'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SCRC2306' THEN '27235 Tourney Rd., Suite 2306, Santa Clarita, CA 91355'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SCRC2315' THEN '27235 Tourney Rd., Suite 2315, Santa Clarita, CA 91355'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'BHA6344TC / CNBHA6344TC' THEN '6344 Topanga Canyon Blvd., Suite 2040 Woodland Hills, CA 91367'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'WH6344TCB / CNWH6344TCB' THEN '6344 Topanga Canyon Blvd., Suite 2040/2060, Woodland Hills, CA 91367'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'WHOB7345 / CNWHOB7345' THEN '7345 Medical Center Drive, Suite 420, West Hills, CA 91307'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '1250 La Venta Dr., Suite 205, Westlake Village, CA 91361'
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'WOB1250LV' THEN '1250 La Venta Drive, Suite 105, Westlake Village, CA 91361'
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNWOB1250LV' THEN '1250 La Venta Drive, Suite 105, Westlake Village, CA 91361'
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'TON2100LR' THEN '2100 Lynn Road, Suite 230, Thousand Oaks, CA  91360'
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNTON2100LR' THEN '2100 Lynn Road, Suite 230, Thousand Oaks, CA  91360'
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'M23815SRR' THEN '23815 Stuart Ranch Road, Suite 300, Malibu, CA  90265'
    	WHEN SUBSTRING(CPU.SystemName0,1,10) = 'CNM23815SRR' THEN '23815 Stuart Ranch Road, Suite 300, Malibu, CA  90265'
    	WHEN SUBSTRING(CPU.SystemName0,1,8)  = 'BHA23815' THEN '23815 Stuart Ranch Road, Suite 300, Malibu, CA  90265'
    	WHEN SUBSTRING(CPU.SystemName0,1,10) = 'CNBHA23815' THEN '23815 Stuart Ranch Road, Suite 300, Malibu, CA  90265'
    	WHEN SUBSTRING(CPU.SystemName0,1,3)  = 'PCC30301' THEN '30301 Agoura Rd, Suite 100, Agoura Hills, CA  91301'
    	WHEN SUBSTRING(CPU.SystemName0,1,3)  = 'BILL30301AR' THEN '30301 Agoura Rd, Suite 100, Agoura Hills, CA  91301'
    	WHEN SUBSTRING(CPU.SystemName0,1,3)  = 'TIHN30301AR' THEN '30301 Agoura Rd, Suite 100, Agoura Hills, CA  91301'
    	WHEN SUBSTRING(CPU.SystemName0,1,3)  = 'PCC30601' THEN '30601 Agoura Rd, Suite 230, Agoura Hills, CA  91301'
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'WW11303WB' THEN '11303 Washington Blvd., Suite 100, Los Angeles, CA  90006'
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNWW11303WB' THEN '11303 Washington Blvd., Suite 100, Los Angeles, CA  90006'
    	WHEN SUBSTRING(CPU.SystemName0,1,8)  = 'BHA11303' THEN '11303 Washington Blvd., Suite 100, Los Angeles, CA  90006'
    	WHEN SUBSTRING(CPU.SystemName0,1,10) = 'CNBHA11303' THEN '11303 Washington Blvd., Suite 100, Los Angeles, CA  90006'
    	WHEN SUBSTRING(CPU.SystemName0,1,2)  = 'MB' THEN '2200 N Sepulveda Blvd, Manhattan Beach, CA 90266'
    	WHEN SUBSTRING(CPU.SystemName0,1,3)  = '' THEN '2275 E. Mariposa Ave., El Segundo, CA 90245'
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'MBA2501SB' THEN '2501 N Sepulveda Blvd., Suite 100, Manhattan Beach, CA  90266, '
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNMBA2501SB' THEN '2501 N Sepulveda Blvd., Suite 100, Manhattan Beach, CA  90266, '
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'MBP1000SB' THEN '2501 N Sepulveda Blvd., Suite 101, Manhattan Beach, CA  90266, '
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNMBP1000SB' THEN '2501 N Sepulveda Blvd., Suite 101, Manhattan Beach, CA  90266, '
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'OPHC2701O' THEN '2701 Ocean Park Blvd., Suite 130, Santa Monica, CA  90405'
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNOPHC2701EX10' THEN '2701 Ocean Park Blvd., Suite 130, Santa Monica, CA  90405'
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'TOB4201TB' THEN '4201 Torrance Blvd, Suite 660, Torrance, CA 90503'
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNTOB4201TB' THEN '4201 Torrance Blvd, Suite 660, Torrance, CA 90503'
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'MDR4560AW' THEN '4560 Admiralty Way, Suite 100, Marina Del Rey, CA  90292'
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNMDR4560AW' THEN '4560 Admiralty Way, Suite 100, Marina Del Rey, CA  90292'
    	WHEN SUBSTRING(CPU.SystemName0,1,5)  = 'PVWIC' THEN '501 N. Deep Valley Dr., Suite 300, Rolling Hills Estates, CA 90274'
    	WHEN SUBSTRING(CPU.SystemName0,1,4)  = 'PVIC' THEN '501 N. Deep Valley Dr., Suite 400, Rolling Hills Estates, CA 90274'
    	WHEN SUBSTRING(CPU.SystemName0,1,6)  = 'LAX400' THEN '5757 Century Blvd., Suite 425, Los Angeles, CA  90045'
    	WHEN SUBSTRING(CPU.SystemName0,1,6)  = 'LAX100' THEN '5767 Century Blvd, Suite 102, 200, 400, Los Angeles, CA  90045'
    	WHEN SUBSTRING(CPU.SystemName0,1,6)  = 'LAX200' THEN '5767 Century Blvd., Suite 100, Los Angeles, CA  90045'
    	WHEN SUBSTRING(CPU.SystemName0,1,6)  = 'LAX400' THEN '5767 Century Blvd., Suite 200, Los Angeles, CA  90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 200, Los Angeles, CA  90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 400, Los Angeles, CA 90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 400, Los Angeles, CA 90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 400, Los Angeles, CA 90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 400, Los Angeles, CA 90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 400, Los Angeles, CA 90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 400, Los Angeles, CA 90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 400, Los Angeles, CA 90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 400, Los Angeles, CA 90045'
    	WHEN SUBSTRING(CPU.SystemName0,1,8)  = 'WFCC2660' THEN '6000 Sepulveda Blvd, Suite 2660, Culver City, CA 90230'
    	WHEN SUBSTRING(CPU.SystemName0,1,10) = 'CNWFCC2660' THEN '6000 Sepulveda Blvd, Suite 2660, Culver City, CA 90230'
    	ELSE '-- To be Checked --'
    END AS 'Location',
    -- Comments
    	NULL as Placeholder -- for column having null value 
    FROM [dbo].[v_R_System] SYS
    INNER JOIN [dbo].[v_GS_PROCESSOR] CPU			on CPU.ResourceID   = SYS.ResourceID
    INNER JOIN [dbo].[v_GS_COMPUTER_SYSTEM] CS		on CS.ResourceID    = SYS.ResourceID
    INNER JOIN [dbo].[v_GS_SERVICE] CSE				on CSE.ResourceID   = SYS.ResourceID
    INNER JOIN [dbo].[v_GS_SYSTEM_ENCLOSURE] SE		on SE.ResourceID    = SYS.ResourceID 
    INNER JOIN [dbo].[v_GS_PC_BIOS] PC				on PC.ResourceID	= SYS.ResourceID
    INNER JOIN [dbo].[v_RA_System_SystemOUName] OUN on OUN.ResourceID   = SYS.ResourceID
    INNER JOIN [dbo].[v_GS_OPERATING_SYSTEM] OPSYS  on OPSYS.ResourceID = SYS.ResourceID
    join	   [dbo].[v_CM_RES_COLL_UCP00B27] COL	on COL.ResourceID   = SYS.ResourceID
    ---WHERE SYS.Name0 Like '%SCCMDP%'
    GROUP BY
    SYS.Operating_System_Name_and0,
    SYS.Virtual_Machine_Host_Name0,
    PC.SerialNumber0,
    SYS.Is_Virtual_Machine0,
    CS.Manufacturer0,
    CS.Model0,
    CPU.SystemName0,
    CPU.Name0,
    CPU.NumberOfCores0,
    CPU.NumberOfLogicalProcessors0,
    CSE.DisplayName0,
    CSE.StartMode0,
    CSE.Name0,
    OUN.System_OU_Name0,
    OPSYS.InstallDate0

    Is there a better way to do the search of location:

    -- Desktop
    CASE 
    	WHEN SUBSTRING(CPU.SystemName0,2,1) = 'R' THEN 'RRH'
    	WHEN SUBSTRING(CPU.SystemName0,2,1) = 'I' THEN 'SDC'
    	WHEN SUBSTRING(CPU.SystemName0,2,1) = 'S' THEN 'SDSC'
    	WHEN SUBSTRING(CPU.SystemName0,2,1) = 'O' THEN 'MCO'
    	WHEN SUBSTRING(CPU.SystemName0,2,1) = 'M' THEN 'SMH'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'CHS' THEN 'CHS'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'MP1' THEN 'MP1'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'MP2' THEN 'MP2'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'MP3' THEN 'MP3'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'OISS' THEN 'OPP'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'OPP' THEN 'OPP'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'BOA' THEN '924 Westwood Blvd.'
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'BW' THEN '11980 Brentwood Plaza, Los Angeles'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'CHS' THEN '10833 LeConte Ave Los Angeles 90095'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'CPN' THEN ''
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'WS' THEN '300 Stein Plaza Driveway'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'FAC' THEN '700 Tiverton Dr. '
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'JS' THEN '100 Stein Plaza Driveway, Los Angeles'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'MOC' THEN '2211 Michigan Ave. Santa Monica Ca 90404'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'MP1' THEN 'Vatche and Tamar Medical Building – 100 UCLA Medical Plaza'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'MP2' THEN '200 UCLA Medical Plaza'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'MP3' THEN '300 UCLA Medical Plaza'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'NPH' THEN 'Semel Institue for Neuroscience and Human Behavior 760 Westwood Plaza'
    	WHEN SUBSTRING(CPU.SystemName0,1,1) = 'O' THEN '10880 Wilshire Blvd., Westwood'
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'UB' THEN '10945 Le Conte Ave., Los Angeles Ca 90024'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'PVUB' THEN '10945 Le Conte Ave., Los Angeles Ca 90024'
    	WHEN SUBSTRING(CPU.SystemName0,1,1) = 'R' THEN '757 Westwood Plaza, Los Angeles Ca'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'REED' THEN 'Reed Neurological Center 710 Westwood Plaza'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'RHB' THEN '1000 Veteran Ave.'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'S12' THEN '1131 Wilshire Blvd., Santa Monica'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'S15' THEN '1260 15th St. Santa monica Ca'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'S16' THEN '1245 16th St. Santa Monica Ca'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'SAOB' THEN '1336 16th St. Santa Monica'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'S18' THEN '1801, 1811, 1821, 1831 Wilshire Blvd Santa Monica'
    	WHEN SUBSTRING(CPU.SystemName0,1,1) = 'S' THEN '1250 16th st., Santa Monica Ca'
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'SO' THEN '1225 15th St. Santa Monica hospital'
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'SC' THEN '1223 16th st., Santa Monica, '
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'SH' THEN '1317 15th St. Santa Monica Ca'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'TIV' THEN '900 Tiverton Ave., Los Angeles'
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'WC' THEN '10920 Wilshire Blvd., los Angeles'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'HOF' THEN '1530 Arizona Blvd, Santa Monica'
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'TS' THEN '936 Westwood Blvd'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'CVS' THEN '1001 Westwood'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'GLY' THEN '1145 Gayley Blvd'
    	WHEN SUBSTRING(CPU.SystemName0,1,6) = 'GA1001' THEN '1001 Gayley'
    	WHEN SUBSTRING(CPU.SystemName0,1,6) = 'GA1072' THEN '1072 Gayley'
    	WHEN SUBSTRING(CPU.SystemName0,1,7) = '1045GLY' THEN '1045 Gayley'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'S20' THEN '2020 Santa Monica Blvd'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'S19' THEN '1919 Santa Monica Blvd'
    	WHEN SUBSTRING(CPU.SystemName0,1,5) = 'S2001' THEN '2001 Santa Monica Blvd'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SWZ' THEN '1328 16th st Santa Monica'
    	WHEN SUBSTRING(CPU.SystemName0,1,7) = 'STANFRD' THEN '1717 Stanford St., Santa Monica Ca'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'STRL' THEN '1344 16th St Santa Monica'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'TELE' THEN 'All work from home'
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'LC' THEN 'Quote needed for Suite 305'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'WFCC' THEN '10250 Santa Monica Blvd'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'WLA' THEN '11318 National Blvd., Los Angeles Ca 90064'
    	WHEN SUBSTRING(CPU.SystemName0,1,5) = 'S1304' THEN '1304 15th St. Santa Monica'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'WSHC' THEN '1950 Sawtelle Blvd., Los Angeles Ca 90025'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'W24' THEN '2424 Wilshire Blvd., Santa Monica 90404'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = 'Old: SPS, PS2428SMB, CNPS2428SMB; New: S2428' THEN '2428 Santa Monica Blvd., '
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SMP' THEN '2825 Santa Monica Blvd.,'
    	WHEN SUBSTRING(CPU.SystemName0,1,4) = 'BHHC' THEN '335 N La Brea Ave., Los Angeles'
    	WHEN SUBSTRING(CPU.SystemName0,1,2) = 'PP' THEN '881 Alma Real Dr., Pacific Palisades ca 90272'
    	WHEN SUBSTRING(CPU.SystemName0,1,5) = 'S2336' THEN '2336 Santa Monica Blvd'
    	WHEN SUBSTRING(CPU.SystemName0,1,9) = 'DWHC1000F' THEN '1000 Flower St, Glendale, CA 91201'
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNDWHC1000F' THEN '1000 Flower St, Glendale, CA 91201'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '1111 S. Figueroa St, Los Angeles, CA 90015'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'BBS191BV / CNBBS191BV' THEN '191 S. Buena Vista, Suite 415, Burbank, CA 91505'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'BPT2211MA / CNBPT2211MA' THEN '2211 Magnolia Ave, #160, Burbank, CA 91506'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'TLHC4343RD / CNTLHC4323RD' THEN '4323 Riverside Drive, Burbank, CA  91505'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'PRN19950R / CNPRN19950R' THEN '19950 Rinaldi St, Suite 300, Porter Ranch, CA 91326'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'AW23388MD / CNAW23388MD' THEN '23388 Mulholland Drive - Lodge building, Woodland Hills, CA  91364'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'WHPTP23388 / CNWHPTP23388' THEN '23388 Mulholland Drive - Saban Pool, Woodland Hills, CA  91364'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'WHPT23388 / CNWHPT23388' THEN '23388 Mulholland Drive - Saban PT building, Woodland Hills, CA  91364'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'JSHC23388 / CNJSHC23388' THEN '23388 Mulholland Drive, Woodland Hills, CA  91364'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SCOB23861/CNSCOB23861' THEN '23861 McBean Pkwy, Suite E24, Valencia, CA  91355'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '250 W Arminta St., Panorama City, CA 91402'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SCPT25751 / CNSCPT25751' THEN '25751 McBean Parkway, Suite 200, Valencia, CA  91355'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SCHC25751 / CNSCHC25751' THEN '25751 McBean Parkway, Suite 210, Valencia, CA  91355'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SCRC' THEN '27235 Tourney Rd., Suite 1500, Santa Clarita, CA 91355'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SCRC2111' THEN '27235 Tourney Rd., Suite 2100, Santa Clarita, CA 91355'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SCRC2306' THEN '27235 Tourney Rd., Suite 2306, Santa Clarita, CA 91355'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'SCRC2315' THEN '27235 Tourney Rd., Suite 2315, Santa Clarita, CA 91355'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'BHA6344TC / CNBHA6344TC' THEN '6344 Topanga Canyon Blvd., Suite 2040 Woodland Hills, CA 91367'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'WH6344TCB / CNWH6344TCB' THEN '6344 Topanga Canyon Blvd., Suite 2040/2060, Woodland Hills, CA 91367'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = 'WHOB7345 / CNWHOB7345' THEN '7345 Medical Center Drive, Suite 420, West Hills, CA 91307'
    	WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '1250 La Venta Dr., Suite 205, Westlake Village, CA 91361'
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'WOB1250LV' THEN '1250 La Venta Drive, Suite 105, Westlake Village, CA 91361'
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNWOB1250LV' THEN '1250 La Venta Drive, Suite 105, Westlake Village, CA 91361'
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'TON2100LR' THEN '2100 Lynn Road, Suite 230, Thousand Oaks, CA  91360'
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNTON2100LR' THEN '2100 Lynn Road, Suite 230, Thousand Oaks, CA  91360'
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'M23815SRR' THEN '23815 Stuart Ranch Road, Suite 300, Malibu, CA  90265'
    	WHEN SUBSTRING(CPU.SystemName0,1,10) = 'CNM23815SRR' THEN '23815 Stuart Ranch Road, Suite 300, Malibu, CA  90265'
    	WHEN SUBSTRING(CPU.SystemName0,1,8)  = 'BHA23815' THEN '23815 Stuart Ranch Road, Suite 300, Malibu, CA  90265'
    	WHEN SUBSTRING(CPU.SystemName0,1,10) = 'CNBHA23815' THEN '23815 Stuart Ranch Road, Suite 300, Malibu, CA  90265'
    	WHEN SUBSTRING(CPU.SystemName0,1,3)  = 'PCC30301' THEN '30301 Agoura Rd, Suite 100, Agoura Hills, CA  91301'
    	WHEN SUBSTRING(CPU.SystemName0,1,3)  = 'BILL30301AR' THEN '30301 Agoura Rd, Suite 100, Agoura Hills, CA  91301'
    	WHEN SUBSTRING(CPU.SystemName0,1,3)  = 'TIHN30301AR' THEN '30301 Agoura Rd, Suite 100, Agoura Hills, CA  91301'
    	WHEN SUBSTRING(CPU.SystemName0,1,3)  = 'PCC30601' THEN '30601 Agoura Rd, Suite 230, Agoura Hills, CA  91301'
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'WW11303WB' THEN '11303 Washington Blvd., Suite 100, Los Angeles, CA  90006'
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNWW11303WB' THEN '11303 Washington Blvd., Suite 100, Los Angeles, CA  90006'
    	WHEN SUBSTRING(CPU.SystemName0,1,8)  = 'BHA11303' THEN '11303 Washington Blvd., Suite 100, Los Angeles, CA  90006'
    	WHEN SUBSTRING(CPU.SystemName0,1,10) = 'CNBHA11303' THEN '11303 Washington Blvd., Suite 100, Los Angeles, CA  90006'
    	WHEN SUBSTRING(CPU.SystemName0,1,2)  = 'MB' THEN '2200 N Sepulveda Blvd, Manhattan Beach, CA 90266'
    	WHEN SUBSTRING(CPU.SystemName0,1,3)  = '' THEN '2275 E. Mariposa Ave., El Segundo, CA 90245'
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'MBA2501SB' THEN '2501 N Sepulveda Blvd., Suite 100, Manhattan Beach, CA  90266, '
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNMBA2501SB' THEN '2501 N Sepulveda Blvd., Suite 100, Manhattan Beach, CA  90266, '
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'MBP1000SB' THEN '2501 N Sepulveda Blvd., Suite 101, Manhattan Beach, CA  90266, '
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNMBP1000SB' THEN '2501 N Sepulveda Blvd., Suite 101, Manhattan Beach, CA  90266, '
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'OPHC2701O' THEN '2701 Ocean Park Blvd., Suite 130, Santa Monica, CA  90405'
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNOPHC2701EX10' THEN '2701 Ocean Park Blvd., Suite 130, Santa Monica, CA  90405'
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'TOB4201TB' THEN '4201 Torrance Blvd, Suite 660, Torrance, CA 90503'
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNTOB4201TB' THEN '4201 Torrance Blvd, Suite 660, Torrance, CA 90503'
    	WHEN SUBSTRING(CPU.SystemName0,1,9)  = 'MDR4560AW' THEN '4560 Admiralty Way, Suite 100, Marina Del Rey, CA  90292'
    	WHEN SUBSTRING(CPU.SystemName0,1,11) = 'CNMDR4560AW' THEN '4560 Admiralty Way, Suite 100, Marina Del Rey, CA  90292'
    	WHEN SUBSTRING(CPU.SystemName0,1,5)  = 'PVWIC' THEN '501 N. Deep Valley Dr., Suite 300, Rolling Hills Estates, CA 90274'
    	WHEN SUBSTRING(CPU.SystemName0,1,4)  = 'PVIC' THEN '501 N. Deep Valley Dr., Suite 400, Rolling Hills Estates, CA 90274'
    	WHEN SUBSTRING(CPU.SystemName0,1,6)  = 'LAX400' THEN '5757 Century Blvd., Suite 425, Los Angeles, CA  90045'
    	WHEN SUBSTRING(CPU.SystemName0,1,6)  = 'LAX100' THEN '5767 Century Blvd, Suite 102, 200, 400, Los Angeles, CA  90045'
    	WHEN SUBSTRING(CPU.SystemName0,1,6)  = 'LAX200' THEN '5767 Century Blvd., Suite 100, Los Angeles, CA  90045'
    	WHEN SUBSTRING(CPU.SystemName0,1,6)  = 'LAX400' THEN '5767 Century Blvd., Suite 200, Los Angeles, CA  90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 200, Los Angeles, CA  90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 400, Los Angeles, CA 90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 400, Los Angeles, CA 90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 400, Los Angeles, CA 90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 400, Los Angeles, CA 90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 400, Los Angeles, CA 90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 400, Los Angeles, CA 90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 400, Los Angeles, CA 90045'
    	--WHEN SUBSTRING(CPU.SystemName0,1,3) = '' THEN '5767 Century Blvd., Suite 400, Los Angeles, CA 90045'
    	WHEN SUBSTRING(CPU.SystemName0,1,8)  = 'WFCC2660' THEN '6000 Sepulveda Blvd, Suite 2660, Culver City, CA 90230'
    	WHEN SUBSTRING(CPU.SystemName0,1,10) = 'CNWFCC2660' THEN '6000 Sepulveda Blvd, Suite 2660, Culver City, CA 90230'
    	ELSE '-- To be Checked --'
    END AS 'Location',
    

    as this list is not exhaustive and could be changed anytime by addition or removal of locations...?

    Is it possible to have all this locations in a table, spreadsheet, etc... and query it from the current statement?

    Also the length of the Location is not constant from 1 to 13 characters...

    I am looking for advices.

    Thanks,

    Dom


    Security / System Center Configuration Manager Current Branch / SQL

    Saturday, December 7, 2019 10:26 PM

Answers

  • you could have a dimension table that maps the codes to the full name

    ex:

    code, location

    LAX400, 5767 Century Blvd., Suite 200, Los Angeles, CA  90045


    jchang

    • Marked as answer by Felyjos Saturday, December 7, 2019 10:43 PM
    Saturday, December 7, 2019 10:37 PM