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, 3 months ago by pietlinden.
• This reply was modified 4 years, 3 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...

Lisa,

Welcome! Since you're new, I created the CREATE and INSERT scripts for your question. (Posting screenshots doesn't help the folks on here recreate your scenario, so it makes it less...

Don't think you can use LAG(). I think it was introduced in SQL Server 2012.

I only suggested it because you posted in a 2019 forum.

So use C#.

Something like this?

--return the previous record where type is F as well.
SELECT tl1.ID, tl1.[type] as ClientType, tl1.ClientName, tl1.completedOn, tl1.CustName, tl1.CustID, tl1.PreviouslyCompletedID,
prev.id, prev.clienttype, prev.clientname, prev.CompletedOn
FROM #temp_Lookback tl1
CROSS APPLY...

Use LAG() to look at a previous record

Upgrade and use STRING_AGG?

use tempdb;
go

 create table #PartsFeature
 (
 PartId int,
 Featurekey nvarchar(200),
 FeatureValue nvarchar(200),
 );
 go

 insert into #PartsFeature(PartId,Featurekey,FeatureValue)
 values
 (1550,'Botato','Yellow'),
 (1550,'Mango','Red'),
 (1550,'dates','Black'),
 (1600,'Rice','white'),
 (1600,'macrona','Red'),
 (1600,'chicken','Yellow'),
...

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

Calculate week numbers for each sale record, then you can do something like

SELECT WeekNo = t.N, ProductID, ca.Total_Sales, ca.rnk 
FROM Tally t
CROSS APPLY (SELECT TOP 10 ProductID,...

DECLARE @MyDate DATE = '10/9/2021';

SELECT DATEPART(week,@MyDate);

DATEPART(week,[DateColumn]) will return the weeknumber of the year. Just group on that.

Help yourself. Introduce yourself to the help files on SQL Server.