SQL looping [xx(] - 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 2000 Forums

Transact-SQL (2000)

SQL looping [xx(]

Author

Topic

manjeet_kaur81
Starting Member

4 Posts

Posted - 2005-10-18 : 02:34:49

We are a call center and calls records have been stored in Table named 'Ticket'. 'Ticket type' is based on 3 things. Category , Sub Category and finally ticket name. All 3 are in tables named respectivaley Cat, SubCat & TktType
For example : Category-GPRS, Sub Category-COMPLAINT, Ticket Name-Problem on Handset

Now problem is that I want mobile nos(ticket table has mobile no) of all those customers who has called up for same day again for same tkt type.

eg. if a customer 'ABC' has called up for a tkt type GPRS-COMPLAINT-PROBLEM ON HANDSET on 10-15-2005(mm-dd-yr) then I want mobile no of customer who has called up again for similar problem. In this example answer will be 'ABC's mobile no' if we assume he called up next day for similar problem.

I have tried many things. But couldn't succssed. Any help would be appericated.

Thanks

Manjeet

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-18 : 03:28:39

Hey..
Can you the post some sample data for the same so that we can work out on it.. ??

Are you looking for somthing like this ..

Select MobileName From TktType Where [Date] = '10-15-2005' And [COMPLAINT_PROBLEM] = 'HANDSET'???

Complicated things can be done by simple thinking

manjeet_kaur81
Starting Member

4 Posts

Posted - 2005-10-18 : 05:38:49

Following is the query for getting repeated callers of a Location. But this query givs me generic view of all types of problems. So even if caller has called up 2nd time for some other problem, it includes that caller.


Select cu.mobile, cu.Cust_name, z.rcvd_datetime, 
a.catname, b.subcatnm,c.tktname, z.problem_reported, z.engg_remarks
from 
Tkt_Cat_Info a 
INNER JOIN  Tkt_Cat_Master d ON a.catid = d.catid 
INNER JOIN  Tkt_Type_Master e ON e.catmstrid = d.catmstrid 
INNER JOIN  Tkt_Type_Info c   ON c.tktid = e.tktid 
INNER JOIN   Tkt_Subcat_Info b ON b.subcatid = d.subcatid
INNER JOIN   Ticket z ON e.tkt_typeid = z.Tkt_TypeID 
AND e.tkt_typeid = z.Tkt_TypeID
Inner Join Customer cu On cu.custid=z.custid
Inner Join Location l On l.locid=cu.locid
Inner Join Circle ci On ci.cirid=l.cirid
Where ci.cirid=3 /* and c.tktid!=59 and c.tktname!='ABUSIVE CALLER'  */
and day(rcvd_datetime) between 15 and 17
and month(rcvd_datetime)=10
and year(rcvd_datetime)=2005
/*and a.catname!='NOT APPLICABLE' 
and b.subcatnm!='OTHERS'  */
group by cu.mobile,cu.cust_name,a.catname, b.subcatnm,c.tktname,z.rcvd_datetime,z.problem_reported, z.engg_remarks
having count(cu.cust_name)between 1 and 2
order by mobile, Cust_name, rcvd_datetime,a.catname, b.subcatnm, c.tktname

Thanks

Manjeet

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources