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, 4 months ago by pietlinden.

That will work. The basic idea is that you have to have the first query execute, and then the second work on the first... and that's essentially what a CTE...

Sorry, some days I'm dumber than a freaking rock. I think your problem is that FundVariance doesn't have a value when you're trying to filter it. It's evaluated after the...

• This reply was modified 4 years, 5 months ago by pietlinden. Reason: me mordio la estupidez!

Why not create a fiscal calendar? It's a whole one record per day. They're super helpful if you're doing stuff like sales analysis. Then this is ridiculous easy.

            
July 3, 2021 at 3:49 am
            
#3903064

            
            
                                                
        

Put all your queries and stored procedures as close to the data (back end) as you can. Especially if it's in Azure.

I think I can put multiple Access append queries...

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

You've got that many points and you can't post create table and insert scripts? =(

Anyway, do everyone a favor and post them so they don't have to waste their time...