So what do you do then? Use XE to determine which queries are run and how often to determine whether the workload improves or not with the new indexing?

Create a unique index on all the columns and make that unique?

Multiply the result of your sum by 1.000 (adjust the number of zeroes as needed), and it will be converted to a decimal.

use datediff and calc full days first, then subtract, then repeat for hours, and then minutes?

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, 6 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?