Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678»»»

Counting the characters in a string before a space Expand / Collapse
Author
Message
Posted Saturday, June 14, 2014 2:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
cbrammer1219 (6/14/2014)
Tell me about it I have told them before any projects are started the data needs cleaned up, these tables don't even have Pk or fk, they b Ave me joining on tables with no relation at all and I try to tell them they are reporting incorrectly. They won't listen. You should see the customer and contact tables, there is no data validation when input to the database. It's a nightmare. ...


The thing I learned SQL on was CDRs so I feel your pain on this. Have they provided you with a Record Layout that you could share? I think it's really important because you missed the data in the file that starts at character 165. I also think that the record layout would contain more information about what the infamous Col05 contains so that we could pull off the perfect split for you. It would also help us define what the PK needs to be so that you can check for duplicate calls, which AT&T and most of the other "Biggees" are nortorious for providing.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1580870
Posted Saturday, June 14, 2014 3:43 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 2,533, Visits: 7,098
cbrammer1219 (6/12/2014)
Yes, I am not looking to count the spaces, I want the count of characters before each space, I can find the spaces, but I am needing for example 777 8888 99999 ---> count of first set of chars before the space(3)....count of second bf space(4) and then (5)...final count of chars.


Is this coming from specs or is it a observed pattern? Have the feeling you might be chasing a red herring here, any CDR format should contain these information explicitly, not implied as this.

Understanding the logic of these record is mandatory for processing them, such as call types A B C D, Transfers, Trunks etc.
Post #1580878
Posted Sunday, June 15, 2014 1:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:57 PM
Points: 105, Visits: 240
I don't need all of the data, I am inserting the data from my original post to create a SSRS report, the data is coming from the flatfile which I am loading into SQL 2012 table. The other fields that aren't being inserted aren't needed for my report, this is something the 3rd party system is doing. Called Mettel...
Post #1580953
Posted Sunday, June 15, 2014 1:22 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 1,943, Visits: 20,179
cbrammer1219 (6/14/2014)
I will give this a try in a little while, I appreciate all that have stuck through this post. I will mark answered if this is a solution or even close.


and the answer is...???


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1580954
Posted Sunday, June 15, 2014 2:37 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 2,533, Visits: 7,098
cbrammer1219 (6/15/2014)
I don't need all of the data, I am inserting the data from my original post to create a SSRS report, the data is coming from the flatfile which I am loading into SQL 2012 table. The other fields that aren't being inserted aren't needed for my report, this is something the 3rd party system is doing. Called Mettel...


Just step a way from counting characters and spaces for a moment, could you tell us what you want to report on? If it is something like incoming call/outgoing call/transferred call, originating/terminating numbers, then you are indeed chasing ghosts and red herrings in flocks as the fields being ignored would hold all the information you are after.

And there is more, a CDR will hold multiple records for a single call, A/B record for the call initiation, another for the pick-up/answer etc. Without knowing this logic and how to interpret the different record types this data is not going to make much sense.
Post #1580959
Posted Sunday, June 15, 2014 2:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:57 PM
Points: 105, Visits: 240
Yes that is what I am reporting on, for each extension. The count of incoming and out going call for each extension displaying customer contact and call duration.
Post #1580961
Posted Sunday, June 15, 2014 3:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:57 PM
Points: 105, Visits: 240
Here's the file being processed.
Post #1580963
Posted Sunday, June 15, 2014 3:49 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 2,533, Visits: 7,098
cbrammer1219 (6/15/2014)
Here's the file being processed.


Now we are getting somewhere, looks like an utility extract from a CDR, possibly MiTel (hence the table name in the earlier post), will look into it.
Post #1580967
Posted Tuesday, June 17, 2014 1:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:57 PM
Points: 105, Visits: 240
Just curious could I create a lookup table with all the extension in it and then insert that into the CallTrace table where the extension and do the same for the Customer table, because right now there are no PK, FK, it is the worst DB structure I have ever worked with, and yet they want miracles; After this I am going to insist on taking the time to work with my peer to begin creating a datamart, any suggestion for a beginning SQL to accomplish this?
Post #1582574
Posted Tuesday, June 17, 2014 2:08 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 2,533, Visits: 7,098
cbrammer1219 (6/17/2014)
Just curious could I create a lookup table with all the extension in it and then insert that into the CallTrace table where the extension and do the same for the Customer table, because right now there are no PK, FK, it is the worst DB structure I have ever worked with, and yet they want miracles; After this I am going to insist on taking the time to work with my peer to begin creating a datamart, any suggestion for a beginning SQL to accomplish this?

Welcome to the real world, and mind you it is not going to get better, when ever you think you've seen the worst, it gets worse. That's the business and one better get used to it.

I'm trying to find specs on the format, MiTel isn't to common any more, will let you know.

You shouldn't need an extension table unless you can relate/tie the extension to a business entity/value. But if so, in a data-mart/dw feeding into a cube, that would probably become a dimension.

The sql part is tertiary, first business requirements then data models. Only after those steps one starts talking sql.
Post #1582587
« Prev Topic | Next Topic »

Add to briefcase «««45678»»»

Permissions Expand / Collapse