Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    Nuernberg, Germany
    Posts
    36

    Unanswered: Startdate depending on quantiy

    Hi,

    my customer wants a report to show quantities sold after a period of time.
    3, 5 or ten days, etc.
    I started by getting the MininumDate of every article in a seperate table.
    Very easy!
    Now he tells me: no, no, not the first time ever the article shows up
    in the facttable is the startdate but the date when
    for example 500 pieces are sold. :-(((

    So the facttable looks like:
    Article, Date,Quantity, Price, ....

    I can imagine a procedure to go through all the factdata
    and would develop this.
    The point is:
    Factdata is now 230 Million records, with 160.000 article in 3 years.
    So I'm not shure how long this procedure will run.

    Question:
    Is there any procedure code I can use?
    Is there sql syntax - perhaps using subqueries - I can try?

    Any suggestions are very welcomed!

    thank you in advance

    Michael

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT article, date
       FROM FactTable AS a
       WHERE 500 <= (SELECT Sum(z.Quantity)
          FROM FactTable AS z
          WHERE  z.Article = a.Article
             AND z.date <= a.date)
    Note that if you are running either PDW or SQL 2012 there are more efficient ways to do this using the enhanced OVER and RANGE features.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2003
    Location
    Nuernberg, Germany
    Posts
    36
    Hi Pat,

    looks very good!
    I will give this a try tomorrow and let you know.

    Thank you very much!

    Michael

  4. #4
    Join Date
    Sep 2003
    Location
    Nuernberg, Germany
    Posts
    36
    Hi Pat,

    your query gives me:

    article date date_as_int
    1515508 2010-01-02 00:00:00.000 40178
    1515508 2010-01-03 00:00:00.000 40179
    1515508 2010-01-04 00:00:00.000 40180
    1515508 2010-01-05 00:00:00.000 40181

    not too bad, but I only need the first (min) value.

    Do you know the HAVING clause?
    I'm using 2008 R2.

    I start with the workaround, build new table and second script
    select article,min(date), -- or min(date_as_int)
    from hlp_mindatetable
    group by article

    Thanks for helping!

    Michael

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use the min() function and GROUP BY article if you only need the first date. Use my code if you want to build it into an UPDATE statement.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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