He's called Brian! And the advice he has provided is sound: if the ETL solution is provided by a third party and there are...

With >1,700 points, you should know better. As already requested by others, please post consumable DDL, sample data in the form of INSERT statements and desired results based on your...

Untested, but something like this. Use a CTE to pre-aggregate the values in Cost Breakdown and then JOIN to that from Sales Line.

With Cost_Breakdown_Totals as 
(SELECT cb.sl_entry_no,...

Hi Des

thanks for the answer it works well except for values which are zero then it doesn’t list them. how to list the zero values?

For example if I include...

If you provide the following, there is a chance that you will get useful assistance. Without it, there's not much we can say.

1. DDL for the tables involved in the...

Ok, how about

TempAtMaxConductivity = temp

?

I imagine that if you replace this

CASE WHEN TagName = 'WFI_LP01_Temp.PV' THEN value END AS TempAtMaxConductivity

with this

TempAtMaxConductivity = value

it will solve the problem.

If you are referring to my code, that's called a SEMI JOIN and is...

Untested, but should get you closer.

select c.FirstName
from dbo.customers c
where not exists (select 1 from dbo.orders o where o.cust_id = c.id and o.order_date BETWEEN '20190201' AND '20190301')

Change your ORDER BY clause to use numeric (rather than alphabetic) ordering:

ORDER BY (x.ComToTal + y.Totals) DESC

What sort of 'updates' do you need to look for? INSERTS, UPDATES and/or DELETES?

Do your tables have DateCreated and DateModified columns? If they do, this exercise probably becomes easier and...

Yes. You've been here long enough to know this.

Please provide sample DDL, INSERT statements with test data and desired output based on the data provided.

Just extend what you are already doing

Select * from
(
 SELECT Stuff.Example1
Stuff.Example2
Stuff.Example3
Stuff.Example4
 FROM   Table1) As FirstSet
 INNER JOIN
 (
  SELECT This.Example5
 This.Example6
 This.Example7
 This.Example8
 FROM...

• This reply was modified 5 years ago by Phil Parkin.
• This reply was modified 5 years ago by Phil Parkin.

SELECT TOP (1) DateTime, 
TOC = value
FROM runtime.dbo.History
WHERE TagName = 'WFI_LP01_TOC.PV'
AND DateTime >= '2018-01-01'
AND DateTime <= '2018-01-08'
AND wwRetrievalMode = 'Cyclic'
ORDER BY value desc

So you want the TOC column to show the same value for all the rows within the selected range?

• This reply was modified 5 years ago by Phil Parkin.