Select distinct [Name], [Phone], [Address], 
Case when count(left([City],4) > 1 
then left([City],4) + *
Else
[City] end as [City_Name]
from tbl_Customers
group by [Name], 
[Phone], 
[Address],
Case when count(left([City],4) > 1 
then left([City],4) + *
Else
[City] end 
Order by [Name]

Case 	when count(left([City],4) > 1 
		then left([City],4) + *
		Else [City] 
		end as [City_Name]

CASE 
       --4: If the Building CLLI from the "NPA/NXX Assignment table" is different 
        --   than the first 8 characters of the Switch CLLI go to the Remote Units 
        --   and find a remote that is flag switching "True" 
        --   from that CLLI go to "CLLI Hierarchie Table" and display the CLLI Parent
		WHEN COUNT(LEFT(RemoteCLLI, 8) + '*')  = 1
			       AND (viewTheBigPicture.RemoteAccessSwitching = 'Switching') 
				   AND  (LEFT(viewTheBigPicture.SwitchCLLI, 8) + '*' <> dbo.viewTheBigPicture.BuildingCLLI + '*') 
				   AND MAX(RemoteCLLI) is Not Null 
        THEN MAX(RemoteCLLI)
        --4: If the Building CLLI from the "NPA/NXX Assignment table" is different 
        --   than the first 8 characters of the Switch CLLI go to the Remote Units 
        --   and find a remote that is flag switching "True" 
        --   if there is no CLLI Parent then
        --   write the Remote CLLI 11 characters or 8 characters. 
        WHEN COUNT(LEFT(RemoteCLLI, 8) + '*') = 1
			       AND  (LEFT(viewTheBigPicture.SwitchCLLI, 8) + '*' <> dbo.viewTheBigPicture.BuildingCLLI + '*') 
				   AND (viewTheBigPicture.RemoteAccessSwitching = 'Switching') and MAX(RemoteCLLI) is Null
        THEN RemoteCLLI
        --4: If the Building CLLI from the "NPA/NXX Assignment table" is different 
        --   than the first 8 characters of the Switch CLLI go to the Remote Units 
        --   and find a remote that is flag switching "True" 
        --   if there is no CLLI Parent then
        --   write the Remote CLLI 11 characters or 8 characters 
        --   and the * if there is more than one Remote.
		WHEN COUNT(LEFT(RemoteCLLI, 8) + '*') > 1
			       AND  (LEFT(viewTheBigPicture.SwitchCLLI, 8) + '*' <> dbo.viewTheBigPicture.BuildingCLLI + '*') 
				   AND (viewTheBigPicture.RemoteAccessSwitching = 'Switching') and MAX(RemoteCLLI) is Null
        THEN left(RemoteCLLI,8) + '*'
	--3: If there is more than one Switching Remote CLLI per NPA/NXX with the same first 8 caracters then
	--   write only the first 8 characters with an * (eg: vrchpq26*). 
        WHEN COUNT(LEFT(RemoteCLLI, 8) + '*') > 1 
			       AND (viewTheBigPicture.RemoteAccessSwitching = 'Switching')
				   AND  (LEFT(viewTheBigPicture.SwitchCLLI, 8) + '*' = dbo.viewTheBigPicture.BuildingCLLI + '*')  
        THEN LEFT(dbo.viewTheBigPicture.remoteclli, 8) + '*'
		--Else MAX(Remote.remoteclli)
		END AS [Remote]

quote:

---

Originally posted by khtan

you can't use *. You have to explicitly specify the column name there

Case 	when count(left([City],4) > 1 
		then left([City],4) + *
		Else [City] 
		end as [City_Name]

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by madhivanan

<<
But that one is not working for me
>>

What do you mean by it?

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by infodemers


Hi Madhivanan,

Like if I have 4 rows with the same [Name] and the same [City],
then I want the statement when count(left([City],4) > 1 to apply, but it does not. This is the best example I found to demonstrate the problem I have with a huge query. My group by is not working either because it contains aggregator

Thanks!

quote:

---

Originally posted by madhivanan

<<
But that one is not working for me
>>

What do you mean by it?

Madhivanan

Failing to plan is Planning to fail

---

---

quote:

---

Originally posted by infodemers

SELECT DISTINCT 						
viewTheBigPicture.NPANNXID, viewTheBigPicture.SwitchCLLI, viewTheBigPicture.SwitchCompanyName,	  viewTheBigPicture.RemoteAccessSwitching, 
CASE 						
WHEN COUNT(LEFT(RemoteCLLI, 8) + '*') = 1 
AND viewTheBigPicture.RemoteAccessSwitching = 'Switching') 
AND (LEFT(viewTheBigPicture.SwitchCLLI, 8) + '*' <> dbo.viewTheBigPicture.BuildingCLLI + '*') 
AND MAX(RemoteCLLI) IS NOT NULL 
THEN MAX(RemoteCLLI) 
WHEN COUNT(LEFT(RemoteCLLI, 8) + '*') = 1 
AND (LEFT(viewTheBigPicture.SwitchCLLI, 8)  + '*' <> dbo.viewTheBigPicture.BuildingCLLI + '*') AND  (viewTheBigPicture.RemoteAccessSwitching = 'Switching') 
AND MAX(RemoteCLLI) IS NULL 
THEN RemoteCLLI WHEN COUNT(LEFT(RemoteCLLI, 8) + '*') > 1 
AND (LEFT(dbo.viewTheBigPicture.SwitchCLLI, 8)  + '*' <> dbo.viewTheBigPicture.BuildingCLLI + '*') 
AND (viewTheBigPicture.RemoteAccessSwitching = 'Switching') 
AND MAX(RemoteCLLI) IS NULL
THEN LEFT(RemoteCLLI, 8) + '*' 
ELSE MAX(RemoteCLLI) 
END AS [Remote], 	
viewTheBigPicture.BuildingCLLI					
FROM viewTheBigPicture
 						
LEFT OUTER JOIN dbo.CLLIHierarchie AS b ON b.CLLIParent = dbo.viewTheBigPicture.remoteclli						
WHERE(viewTheBigPicture.NPANNXID = '514-215') AND (viewTheBigPicture.SwitchCompanyName IN ('ANONYMOUS', 'ANONYMOUS2'))AND (viewTheBigPicture.NPANNXAssignmentEndDate IS NULL OR viewTheBigPicture.NPANNXAssignmentEndDate > GETDATE())						
						
GROUP BY viewTheBigPicture.NPANNXID, viewTheBigPicture.SwitchCLLI, viewTheBigPicture.SwitchCompanyName, viewTheBigPicture.RemoteAccessSwitching, RemoteCLLI, LEFT(RemoteCLLI, 8) + '*', viewTheBigPicture.BuildingCLLI

The results is :
						
[npannx][SwitchCLLI][Company][RemoteAccessSwitching][Remote][Building]	
514-215	ABCDEF23DS4	ANONYMOUS	Access	ABCDEFBDRL1	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Access	ABCDEFBDRS1	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Access	ABCDEFBDRS2	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Access	ABCDEFBDRS3	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Switching	ABCDEF41R10	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Switching	ABCDEF41R11	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Switching	ABCDEF41RL1	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Switching	ABCDEF41RS1	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Switching	ABCDEF41RS2	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Switching	ABCDEF41RS4	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Switching	ABCDEF41RS5	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Switching	ABCDEF41RS6	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Switching	ABCDEF41RS7	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Switching	ABCDEF41RS8	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Switching	ABCDEF41RS9	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Switching	ABCDEF41RSA	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Switching	ABCDEF41RSC	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Switching	ABCDEF41RSE	ABCDEF41	
514-215	ABCDEF23DS4	ANONYMOUS	Switching	ABCDEF41RSG	ABCDEF41	
I wish to only show onw row only in my results based on my case statement.						

quote:

---

Originally posted by infodemers


Hi Madhivanan,

Like if I have 4 rows with the same [Name] and the same [City],
then I want the statement when count(left([City],4) > 1 to apply, but it does not. This is the best example I found to demonstrate the problem I have with a huge query. My group by is not working either because it contains aggregator

Thanks!

quote:

---

Originally posted by madhivanan

<<
But that one is not working for me
>>

What do you mean by it?

Madhivanan

Failing to plan is Planning to fail

---

---

---