FWIW, here's the worked solution, so folks at home can play along...

use tempdb;
go

/* setup script */CREATE TABLE LineItem(
LineNum int not null,
ItemNo char not null,
Qty tinyint not null);
GO

INSERT...

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

right-click on the table, and "script table as"... then "Create to..."?

SELECT ...

FROM query1

UNION ALL

SELECT...

FROM query2

If they're dates, use something like >= @StartDate AND < @EndDate.

No need to do all that expensive conversion.

use a windowing function with SUM and UNBOUNDED PRECEDING.

Oh good, because I was going to add that (as far as I know) there's no way to pivot the data you have without explicitly adding a column for week...

SELECT u.AGG_TYPE
, u.PRODUCT_GROUP
, u.PRODUCT_NAME
,u.product_code
,u.FORMAT_NAME
,u.store_region
,u.store_code
, u.weekno
, SUM(u.sales) AS TotalSales
, SUM(u.volume) AS TotalVolume
FROM 
(SELECT d.agg_type,
d.PRODUCT_GROUP,
d.PRODUCT_NAME,
d.product_code,
d.FORMAT_NAME,
d.store_region,
d.store_code,
d.week1_sales AS Sales,
d.Week1_Volume AS Volume,
1 as weekno
FROM dbo.extra_data d
UNION ALL
SELECT d.agg_type,
d.PRODUCT_GROUP,
d.PRODUCT_NAME,
d.product_code,
d.FORMAT_NAME,
d.store_region,
d.store_code,
d.week2_sales,
d.Week2_Volume,
2 as weekno
FROM dbo.extra_data d)...

How are week_1 and week_2 related to Load_Date?

This looks like you're making it a whole lot more complicated than it needs to be. This is my start at it, but...

So you query and find all the components of the stuff in the work order that have no children - because that's the definition of the lowest part on the...

Should be. What do you need the recursion for, getting component parts?

The source data table is as follow...

Okay, how about a create table script?

Why not go crazy and use a WHERE clause to find the records that are different and only update those?

SELECT DATEDIFF(day, [ExpiredDate], GETDATE() )

FROM MyTable

use tempdb;
go

SELECT x.ExpiredDate
, isfuture = iif(x.ExpiredDate>getdate(),'future','past')
,  daysAgo = DATEDIFF(day, getdate(),x.ExpiredDate)
, PassTest = IIF(DATEDIFF(day, getdate(),x.ExpiredDate) = x.ExpectedDaysAgo,1,0)
FROM (VALUES
('2021-07-25 15:29:09.000', 6)
,('2021-07-28 17:31:22.000', 9)
,('2021-07-19 04:02:54.000', 0)
,('2021-07-18 12:01:31.000', -1) 
)...

A big part of your problem is that you data is pivoted, and you need to unpivot it to store it. Unpivoting something like this using PowerQuery is child's play. Basically,...

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