newbie needs help. Thanks - 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)

newbie needs help. Thanks

Author

Topic

xzyan
Starting Member

8 Posts

Posted - 2006-11-22 : 09:58:38

Hi I am new to sql . my job give me this project >> I need help Thanks

We need to figure out a way to automate SQL database to capture and use data displayed and break it down to a disposition code level for East and West operations.

|ISPTicket |Customer TN | Start Date/Time | Closed Date/Time |Disposition Code| Disposition Sub-Code |
1 111111111 11/21/2006 2:26:00 AM 11/21/2006 2:26:00 AM test ok test ok
2 222222222 11/21/2006 2:26:00 AM 11/21/2006 2:26:00 AM bad mdm mdm issue
3 333333333 11/21/2006 2:26:00 AM 11/21/2006 2:26:00 AM cable pair cp issue

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 10:39:30

How do you distinguish between East and West operations in the supplied test data?

Peter Larsson
Helsingborg, Sweden

xzyan
Starting Member

8 Posts

Posted - 2006-11-22 : 10:54:41

there to databases on called east and the other one called west and there are both webbased you click on the west then you get a table w/ all the in put ...

this are the Disposition Code

*TEST OK
*CORE CENTRAL OFFICE
*CORE OUTSIDE PLANT
*CPE
*DSNO CENTRAL OFFICE
*ISP
*OTHER TELCO

this are Disposition Sub-Code

*TEST OK
*DNOC CA/CUSTOMER ADVISED
*BILLING/RECORD ISSUES
*FRAME WIRING
*PUBLIC ATM SWITCH
*PUBLIC FRAME RELAY SWITCH
*OPTICAL CONCENTRATOR (RT)

Here is what I have in mind what you think

select Disposition_Code
from voleast
where Disposition_Code = ('TEST OK''CORE CENTRAL OFFICE''CORE OUTSIDE PLANT''CPE''DSNO CENTRAL OFFICE''ISP''OTHER TELCO')
order by Disposition_Code

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 11:01:19

Something like this?


select	'East',
		Disposition_Code
from	<east operations database>
where	Disposition_Code IN ('TEST OK', 'CORE CENTRAL OFFICE', 'CORE OUTSIDE PLANT', 'CPE', 'DSNO CENTRAL OFFICE', 'ISP', 'OTHER TELCO')

union all

select	'West',
		Disposition_Code
from	<west operations database>
where	Disposition_Code IN ('TEST OK', 'CORE CENTRAL OFFICE', 'CORE OUTSIDE PLANT', 'CPE', 'DSNO CENTRAL OFFICE', 'ISP', 'OTHER TELCO')

Peter Larsson
Helsingborg, Sweden

xzyan
Starting Member

8 Posts

Posted - 2006-11-22 : 11:08:38

Thanks a lot I will try it and left you know .. Thanks so mutch

xzyan
Starting Member

8 Posts

Posted - 2006-11-22 : 14:30:51

I was told that Disposition codes are dynamic and can change by center, it will be cleaner not to hard code by using something like select count for the ticket number field as a unique identifier and simply group by disposition code,

do you know what that means ? Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 14:34:55

???

select		'East',
		Disposition_Code
from		<east operations database> AS q
inner join	DispCodes AS w ON w.Disposition_Code = q.Disposition_Code

union all

select		'West',
		Disposition_Code
from		<west operations database> AS z
inner join	DispCodes AS x ON x.Disposition_Code = z.Disposition_Code

Peter Larsson
Helsingborg, Sweden

xzyan
Starting Member

8 Posts

Posted - 2006-11-22 : 14:44:08

can you pls explain what that means, I kind of lost over this one.. Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 14:48:24

It means that Central can change the codes whenever they want.
You just bind your <East Operations Database> with the DispositionsCode database.

Peter Larsson
Helsingborg, Sweden

xzyan
Starting Member

8 Posts

Posted - 2006-11-22 : 14:50:39

ok Thanks I will study this statement.. to get more knowlege about it

Thanks Man.

xzyan
Starting Member

8 Posts

Posted - 2006-12-03 : 13:10:11

here is the flow up on the question . To the point we need to try to make efficient script that run daily (Getdate or sysdate - 1 for example), count tn, and group by disposition code and sub code.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 04:12:34

Yesterday.
SELECT DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)

Peter Larsson
Helsingborg, Sweden

xzyan
Starting Member

8 Posts

Posted - 2006-12-06 : 17:54:03

Thanks Man I will try it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources