Post reply

how to do duration in hours with ragged data

  • April 2, 2014 at 11:40 am

    #285298

    Is there any convention for how to get from the left hand column (Hours) to the right hand column (Hours per Week)?

    I am working in a place where I do not have create table rights, so that's a potential fly in the ointment.

    The good news, if there is a ray of sunshine here, is that the increments are in half hours, and so far, I haven't run across any individuals who report in fifteen minute intervals, but in a table of about 30K records, there are about 3500 distinct rows that are sampled in the left hand column.

    So there are a lot of wrinkles here, in that the days of the week are all over the lot (M or Mon or Monday or Mondays) sometimes the times have colons and sometimes not, and the am and pm influence the calc too...so im over my head.

    thanks a lot

    Hours Hours per Week

    F 8:30a-12:30p 4

    Tues 1p-8p 7

    Mondays & Tuesdays 1-5PM, Wednesdays 9-5PM 16

    M - 8:00 am - 12:00 pm; Tu - 8:00 am - 12:00 pm 8

    Tu - 4:30 pm - 8:30 pm; F - 8:00 am - 12:00 pm 8

    M,W,F 8a-4p, T,Th 4p-8p 32

    Tues: 8:30a-11:30p 3

    M-F 9am to 5pm 40

    Mon & Thurs 8:30am-7pm Tues, Wed Fri, 8:30am-4:30pm Sat 9:30am-1pm 40.5

    Tuesday and Thursday 1pm to 4pm 6

    M 9a-12p 1p-5p, T1p-5p, W 9a-12 1p-5p, Th 9a-12p, F 1p-5p 25 (there are no repeated days between days with lunch hours!)

    drew

  • April 2, 2014 at 1:25 pm

    #1702608

    This is a text split problem. There are several good splitting fuctions talked about on this site.

  • April 2, 2014 at 4:05 pm

    #1702655

    Thanks very much for the nudge...

    I found this http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

    which was great summation of the blizzard of solutions I found here, which was kind of an embarrassment of riches :-P.

    I appreciate the clue

    best

    drew

  • April 2, 2014 at 4:10 pm

    #1702656

    To me, splitting it is the trivial part. Analyzing all the possible variations will be vastly more complex.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • April 2, 2014 at 4:20 pm

    #1702661

    Holy good night...I just read the entire article and the comments, and that brought me right back here to look at DelimtedSplitN4K or DelimitedSplit8K from Jeff Moden, so I spoke too soon and apologize for creating the wrong idea initially.

    thanks again

  • April 2, 2014 at 4:25 pm

    #1702662

    Yes, it will be a gnarly mess...I have a little time to mull though, but it is in preparation for a conversion.

    The business problem I am trying to solve is to confirm that the hours recorded in that text field match a total hours requirement for a provider to be at a particular site, so there are a lot of holes in the boat to plug, like spelling out the range of days, accounting for T for Tuesday versus T for Thursday, figuring out dashes from commas etc. I have a little time to figure it out, but it will be no day at the beach.

    Thanks very much for your observation just the same.

    best

    drew

  • April 2, 2014 at 4:55 pm

    #1702667

    drew.georgopulos (4/2/2014)

    Thanks very much for the nudge...

    I found this http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

    which was great summation of the blizzard of solutions I found here, which was kind of an embarrassment of riches :-P.

    I appreciate the clue

    best

    drew

    Be careful, Drew. The author of that article didn't pick up on the latest and greatest for the DelimitedSplit8K function (for starters).

    --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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 2, 2014 at 5:52 pm

    #1702675

    Thanks for the heads up Jeff...actually, I got straightened out by Lynn's comment in the first article's comment section, (and yours too) which brought be back to here

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    I think that is the correct version.

    Not for nothing, but for me anyway, finding the right stuff here at SCC is really hit or miss. I don't know if its because of the volume of material or the way its stored or searched, but there must be a better way to organize the content (separating the wheat from the chaff). Its true the content is ranked by stars, but for some reason, I found it necessary to delve into many articles before I gave up, googled and found the article you are warning me about, and once I knew what I was looking for, found and read your update.

    Damn Sam, I'm knocked out for today!

    Thanks again for warning me...I have a feeling i'll be back in the not-too-distant once I get my arms around it.

    best to you and thanks again.

  • April 2, 2014 at 6:05 pm

    #1702680

    To avoid those search problems once you've found something interesting, I would suggest to use the bookmarks on your browser (Chrome will synchronize them within all your devices) or the briefcase from SSC.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • April 2, 2014 at 10:38 pm

    #1702721

    drew.georgopulos (4/2/2014)

    Not for nothing, but for me anyway, finding the right stuff here at SCC is really hit or miss.

    I agree. It seems like it's always been a problem even if you're familiar with tags.

    I normally insist that people give Google a shot before posting but if you know what you want, most of the heavy hitters here have quite the library of links for articles and you only need to ask the question.

    Good to "see" you, again. It's been a while.

    --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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply