Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Posts
    19

    Unanswered: Interesting Problem...any SQL gurus?

    Hi

    I've got the following data set from a couple of join & union queries
    Code:
    Order Qty   Plant   Type Start Date  End Date
    1     80    1000    AA    9/6/2007   9/13/2007
    2     10    200     SP    9/10/2007  9/11/2007
    What i need to do is figure out if its week 37 (9/10/2007 - 9/16/2007), whats part of each order per plant lies in that week i.e. Order 1 will have 4 days in week 36 and 4 days in week 37. Order 2 will have the whole order in week 37.

    My required output is something like this

    Plant Type Week Sum(Qty_week)
    1000 AA 37 40


    I have thinking about views, case statements, self-joins but its not making much sense since the dates & row data are at different granularity. Any ideas? Thanks for your help.
    Last edited by gvee; 09-11-07 at 11:04. Reason: [CODE] tags added to preserve formatting.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    The reason why you have not received any responses to your post is because, quite simply, what you have written does not make any degree of sense. The grammar and spelling is terrible and the question is devoid of any degree of coherence.

    Outside of these shortcomings, your actual question is logically flawed in itself. Reading this sort of nonsense makes me feel embarrassed to be a human. To help you improve your English ability, the following is a list of mistakes that relate to both the grammar and the intent of your question:

    1. out if its week 37 (9/10/2007 - 9/16/2007),: The correct usage is It's, short for It Is, and not its.
    2. week 37 (9/10/2007 - 9/16/2007): Here you state the end date as being 9/16/2007, but your sample data contains a different end date for the same start date. Pathetic!
    3. whats part of each order per plant lies in that week: That sentence does not make sense to any degree, even if I try to amend it in my mind, I'm unable to extract any meaning from it!
    4. whats: As with a previous mistake that I pointed out, please do try and understand how to use an apostrophe.
    5. dates & row data are at different granularity: As with a previous sentence of yours, this one does not make sense. Please explain, oh please do, just explain exactly what you were trying to say here.
    6. but its :Again, please understand how to use an apostrophe.
    7. I have thinking about views, case statementsorry, was that written in English?

    After you have addressed all of the above failings in your question, the kind readers of this forum may be in a better position to assist you, as they should be able to understand exactly what is it that you are seeking help with.

    Regards,
    Last edited by r123456; 09-11-07 at 15:32.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2004
    Posts
    19
    Thanks for your input, you're on the wrong forums. This is a DB forum and quite frankly i don't give a $at's a$$ to how many apostrophes you'd like to see. The ESL tutor forums must be somewhere else!

    You obviously are devoid of any SQL know how otherwise you would have figured out in an instant i am looking for dates WITHIN week 37 so the end date is obviously BEFORE 9/16/2007.

    Please avoid posting irrelevant yakking, it's not helping the post or doing more than making you look like a fool.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    If you take week 37 (start_week = to_date('9/10/2007', 'mm/dd/yyyy'),end_week = to_date('9/16/2007', 'mm/dd/yyyy') ) as the only week, there is no need for joins, just simple arithmetics:
    nr_days = least(end_week, end_date) - greatest(start_week, start_date) + 1
    if nr_days is negative, set it to zero.

    Why the second row is not included in the required output?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    robert: defo wrong forum for that

    choo: is 9/16 part of week 37 or not?

    flyboy: best question so far
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Now that I've thought about it, for the first time and for no more than a few seconds, it is indeed clear as to what you are trying to achieve. Flyboy has correctly pointed out a solution for you, a trivial one at that. My goodness the irony present in your reply makes me laugh, it really does.

    This does not, however, alter the fact that your inability or refusal to write in English on an English speaking forum contributed to the delayed response in answering your question and also the confusion that many readers experienced in reading the question.

    The underlying notion that I'm advocating here is that little twats like yourself, who feel they have a right to disgrace the English language, should not be permitted to interact with those of us who do take the time to write properly.

    If you were to speak or write in a way similar to that of your initial post for a job interview or any professional business document for any reputable firm, you would find yourself fired so quickly that you wouldn't know the time of day.

    Yes we all make mistakes in our communication and on occasions we may choose to adopt a more informal tone. For if one forgets to use grammar correctly in a spot here and there or fails to use a comma then many people, myself included, would simply read over it and continue. In the case of your writing, however, you chose to show a complete disregard for the English language and communication.

    Rudy: defo wrong forum for that. Could somebody please translate those series of words into a sentence that someone from outside of Canada can understand? I certainly have a few moments spare to understand the English meaning!

    I hope this is clear for you.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    robert, here's the translation: you are definitely in the wrong forum if you feel that it is somehow okay to point out grammatical errors and belittle another member

    and if you persist in your attitude and name calling, you will find yourself banned from this one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Posts
    19
    The second row is definitely included..i was only looking at the first order.

    nr_days: how would you know which days are in week 37 and which in week 36? if it was just week 37, i wouldnt even have posted this.

  9. #9
    Join Date
    Feb 2004
    Posts
    19
    r123456: i'm sorry if your boss abuses you at work every day..i'm sure there's a forum somewhere out there for retards...please leave this post alone

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    atl-choozang, same warning goes for you

    no disrespect towards other members
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2007
    Posts
    623
    atl-choozang: Could you clearly specify, what you really want?
    From your first post I assumed, you want number of days belonging to intersection of two given date intervals (stored in table and given week) as other parts are trivial (number of days in table date interval and multiply/division other column value).

    Now it seems you want to generate (American?) ISO? week calendar (or just get current year's ISO? week 37 bounds)?
    Just have a look at Date range per week thread on AskTom; however it contains ISO calendar generation example, you should derive the second info easily from it (as each week contains 7 days, it is just a matter of adding constant number of days to the start of the first week).

  12. #12
    Join Date
    Feb 2004
    Posts
    19
    Figured out how to do it:

    http://www.adp-gmbh.ch/ora/sql/table_cast.html

    This UDF allows you to get a table of dates based on your date range. I did a cross product of that with my table and viola! I get equal number of rows for each date range, do a group by and get my partial week order quantities.

    Thanks for everyone's input.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •