HAVING - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

HAVING

Author

Topic

vipinjha
Starting Member

21 Posts

Posted - 2012-03-22 : 06:28:06

Dear All,
I am posting my query in which i want to have only those record which is having same mobile number more than 3 times
need to use group by and having

please tell me proper syntax for same.

SELECT
case when len(PHONENO) > 4 then '******' + RIGHT(PHONENO, 4) else PHONENO end "Cell Number",
PHONENO "CellNumber",
GAM.FORACID Account_Number,
GAM.SCHM_CODE,
CMG.PAN_GIR_NUM "Pan Number",
CMG.CIF_ID

FROM DWH_STAGING..PHONEEMAIL left join DWH_STAGING..CMG(NOLOCK) on CMG.CUST_ID= PHONEEMAIL.ORGKEY
left join DWH_STAGING..GAM(NOLOCK) on GAM.CIF_ID=CMG.CIF_ID

where PHONENO<>'0' and PHONENO is not null
AND PHONEEMAIL.PHONENO LIKE '%9833774765%'
and phoneoremail = 'PHONE' and preferredflag = 'Y'

Thankx & regards,
Vipin jha

Vipin jha

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-22 : 07:00:40

You have two columns that look like cell number. "Cell Number" and "CellNumber". Are you looking for those two columns having the same value, or is it three different rows where one of those columns are the same, or two or more rows where at least one of those is repeated - or something else?

Can you post some sample data and expected output that would cover these cases?

vipinjha
Starting Member

21 Posts

Posted - 2012-03-22 : 07:21:56

bothe coumn are same ,diffrnce is that i dont want to show user mobile number number,
pupose of this report is that suppose one mobile number is assigning to 10 user .
so if i put the mobile number it will show the same number having more than 3 user details

CellNumber Account_Number
9833774765 NULL
9833774765 NULL
9833774765 NULL
9833774765 NULL
9833774765 NULL
+000(000)9833774765 6.73209E+12
9833774765 NULL
9833774765 NULL
9833774765 NULL
9833774765 NULL
9833774765 NULL
9833774765 9.61012E+12
9833774765 NULL
9833774765 NULL

Vipin jha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-22 : 15:42:32

you want values like +000(000)9833774765 to be considered similar to 9833774765 ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources