You're looking for LAG().

SELECT from_set
,prevVal =  LAG(from_set,1) OVER (ORDER BY from_set)
FROM Test;

Found and article that does most of what I want. Now to build it and practice... oh, and break it, just so I know what it looks like when it...

• This reply was modified 4 years, 1 months ago by pietlinden.

Uh oh... so I should learn some basic C# or Powershell (or both).

I guess it's time to look for a good validation tutorial maybe. Thanks!

You would have to modify the bottom part of the query...

SELECT Field1, Field2, Field3
FROM
(SELECT Cast('DQUOTE'+Member4Parent.NAME+'DQUOTE' AS VARCHAR(20)) AS Field1 ,
       ...

Where's b.[DMDPostDate] is your SELECT statement, because it's in your GROUP BY clause?

Do you have an existing OLTP database to build from?

What does each record in your fact table represent? What kind of reporting/summarizing do you want to do?

The fun part is...

Are all the KEN_N## tanks or something? Sounds like a design flaw.  What does the underlying table look like?

Generate CREATE TABLE and INSERT scripts using the data table in your zip file, and then copy and paste the scripts into the window. most folks on here won't go...

If a parent record doesn't have 30 previous periods/records, then you want to exclude it?

Any chance you could translate your question into a Sales kind of question, so I can...

Got some data for us to play with? That's not data.

Can you do PowerQuery inside ADF?

Remove Top N Rows, Remove Bottom N Rows. Done.

a standard rolling window would be something like

SUM([Qty]) OVER (PARTITION BY <columnName> ORDER BY <date> ROWS BETWEEN PREVIOUS 30 ROWS AND CURRENT ROW)

and then you would divide by that.

• This reply was modified 4 years, 1 months ago by pietlinden.
• This reply was modified 4 years, 1 months ago by pietlinden.

Can you join the two source tables in SQL Server, and then create a view that you can use as the source for the dimension?

That's the funny thing about posting on here. I read a ton of questions here, and post some myself. So by now, I've gotten my head around Jeff Moden's article...

Glad it helped!

Good luck on the learning journey! Here's a really good article about posting questions - short and to the point... should be required reading. It's maybe a five...