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

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

Hi Pat,

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

Thank you very much!

Michael

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

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