Post reply

TSQL Question about getting data

  • October 22, 2009 at 10:05 pm

    #1069774

    All right, Brad... I looked at all of this and, although I return what you wanted, I got carried away. If it were me and I saw some of the problems that plague this schedule, I'd want to do a bit more analysis... which I did. I've attached a full working example including your very nice test data... you'll see that the bit of effort on your part was certainly not a waste of time.

    Copy the code from the attachment and run the whole nine-yards all at once to see the final report. The code is fully documented and the variables have very obvious names so that you can see what is going on.

    I had to add some columns to your original temp table. My recommendation would be to add them to the real table but, if you can't, just create a temp table with all the necessary columns, copy the relevent data to it, and let 'er rip. Even if you have to copy a couple of million rows, it will still take a lot less time than a Cursor or While Loop and will come darned close to whipping up on a CLR.

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

  • October 23, 2009 at 8:45 am

    #1070083

    Thank you very much for this. I have not been able to review it yet as we are having other IT issues that are taking my time. But I should be able to look at it today or over this weekend.

  • October 23, 2009 at 4:00 pm

    #1070284

    Understood... been through that a couple of million times. Lemme know how it works out for you when you get the chance.

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

  • October 26, 2009 at 5:53 am

    #1070627

    Again, thanks for the help with this, however I just ran into a couple of issues that I might be able to work through. Unfortunately when I ran this against our database, I get errors. This is because our main database remains to be SQL Server 2000. When I ran this again our "test" database, which is SQL Server 2005 (SP2), it ran fine.

    The results come up with "Short", "Excess", "IsDone", "CompletedDate" and "CompletedShift". Actually what I need to find is the ScheduledDate and ScheduledShift when CurrentGluedPieces falls below the ScheduledTotal. What I need to do from there (and I don't know if doing this in SSRS or the SQL code is better) is to take the DateDiff of NOW() and get the hours from NOW() until the date and shift that is returned.

    Again, I really do appreciate your help.

  • October 26, 2009 at 7:57 am

    #1070707

    Brad,

    Just change CompletedDate and CompletedShift aliases to ScheduledDate and ScheduledShift because those are what you seek.

    As for the other thing, I assumed you were using SQL Server 2005 because this is a 2005 forum. Obviously, we'll need to change the code to work on 2000. I'll take a look at it tonight.

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

  • October 26, 2009 at 8:26 am

    #1070724

    Thanks again. I was not thinking about the version that we are using. When the time comes, I hope it is not going to be a big pain to migrate over to 2005. Here at Kellogg, we work slow. They (corporate IT) just released IE 7 to us. We still work with Office 2003 too.

  • October 26, 2009 at 10:09 am

    #1070803

    Brad Allison (10/26/2009)

    Thanks again. I was not thinking about the version that we are using. When the time comes, I hope it is not going to be a big pain to migrate over to 2005.

    It won't be any pain at all because no changes will be required when that happens.

    Here at Kellogg, we work slow.

    Heh... WHAT???!!!! No Snap, Crackle, or Pop??? 😛

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

  • October 30, 2009 at 10:03 pm

    #1073464

    Apologies, Brad... the new job is keeping me pretty busy... so is the hour commute each way.

    Are you all set with this or do you still need some help in the SQL Server 2000 world?

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

  • October 31, 2009 at 4:56 am

    #1073496

    I still do need help with it. We have had continued IT "fires" at the plant with some fiber networking issues and I have been keeping busy with other things, but I really would like to get this knocked out of the park this upcoming week as I have vacation the following.

    The original data that I sent is still valid. That gets populated by running another stored procedure and putting the data into a table (I truncate the table each time it is run first). I need to select all of that data including this last little piece that you are helping me with which would be just the date and shift that the TotalGluedPieces would fall below the scheduled requirement.

    By the way, if you are curious, what this data is for is a Report in SSRS. The report is to show our Warehouse supervisors how our case stock (the cereal boxes) in inventoried against what is scheduled. So if we are scheduled to produce 150,000 boxes of Rice Krispies and we currently have 120,000 currently inventoried (TotalGluedPieces), they need to know what day and shift that inventory will be depleted. They need the report to show shifts until they run out of the stock. So if I can at least get the day and shift, then I can do a datediff (with a case statement based on shift) to get the number of hours, then divide that by eight. They based on some other parameters, this value will show GREEN (good), YELLOW (coming close), or RED (need to run ASAP). Just a brief summary of why I need this data and what it will do.

    Thanks again.

  • October 31, 2009 at 9:28 am

    #1073509

    Sounds similar to the stock issue on Phil Factor's latest challenge.

    Have a look here and see if you might be able to adapt it to your needs.

    Edit : Although it does make heavy use of cte's which were new in 2005.


    Clear Sky SQL
    My Blog[/url]

  • November 2, 2009 at 5:29 am

    #1073766

    Jeff (or anybody),

    Because the data is being stored in a SQL Server 2000 database, but we also have a working 2005 version on a different server, would there be a way through the main stored procedure to truncate the working table and then insert the data into this table (on the different box) and then perform the main select from that table (on the different box)? If I can get the main stored procedure to do that, then the code that you sent me with the case statements would work. I can do this or move the main store procedure to the 2005 box, but then it would have to do the subquery selections from the 2000 box.

    I know how to get data from a table in a different database on the same box, but is there a way to do it on a different server?

    Thanks,

    Brad

Viewing 11 posts - 16 through 25 (of 25 total)

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