WITH SubQuery AS
(
SELECTjh.Company, jh.JobNum, ja.AssemblySeq, jo.OprSeq, jo.OpCode, 
            CAST(CASE WHEN NOT jh.Date03 IS NULL THEN jh.Date03...

I have removed all the 'reformatting' from the Views and removed the NoLock and added the Outer Apply.

SELECT        jh.Company, jh.JobNum...

Instead of using multiple nested views - what technique should I adopt to build my more complex views?

I also, for readability, tend to create multiple smaller SQL Views then reuse them to build larger more complex views.

I always use NoLock as I was getting a number of...

I tend to write my code outwith the gui then when I save it as a view SQL reformats and adds the TOP(100) etc.

The first SQL snippet is the original sql.

The second SQL snippet is the new sql.

The third snippet is the Function.

The JobHead table has an Index of Company, JobNum but I...

WITH SubQuery AS (SELECT        TOP (100) PERCENT jh.Company, jh.JobNum AS ParentJob, jh.JobFirm, jh.JobReleased, ja.AssemblySeq, jh.PartNum AS ParentPart, jm.MtlSeq, jm.PartNum AS...

I have created a Scalar-valued Function and called this from within my view - basically the join that I perform on the non-index fields I have removed from my Sql...

The first view holds circa 1500 records and returns circa 2200 through a second cte view.

The data is consistently refreshed throughout the working day - eg. every 5 minutes and...

Jeff:

Thanks for the response.

There are about 16,000 to 20,000 rows in the data set in total currently.
I want the running total to start over on...

Steve:

Would you be available for a remote session in a couple of hours or some other time suitable for yourself - possibly a TeamViewer session.

I can...

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable
CREATE TABLE #mytable (
ParentWO varchar(50),
PProduction_Qty float,
PQty_Complete float,
ChildWO varchar(50),
IssueQty int,
Production_Qty float,
Qty_Complete float)
INSERT INTO...

Steve

Unfortunately Child Work Orders will be produced and issued to more than one Parent Work Order - the thoughts behind this is that some Child Manufactured Parts are...