August 17, 2009 at 7:57 am
Can anyone give me advice on the simplest way to turn the following separate views into a stored procedures?
Nesting each query into the FROM clause seems messy and makes the code very long and I find it tricky if the view from fall into a linear pattern of execution. Is there a better way. You can see below the type of views/queries I’m working with.
Thanks
Asta
--qryCycleTimesBR_1
SELECT TOP (100) PERCENT tblJobs.Campaign, tblProducts.ProductDescription, tblProducts.Standard, tblProducts.UpperLimit,
tblProducts.LowerLimit, tblJobsBR.Yield, tblJobsBR.JobType, tblJobsBR.EquipOrTrain, tblJobsBR.Plant,
tblJobsBR.ActualStartDate, tblJobsBR.ActualStartTime, tblJobsBR.ActualEndDate, tblJobsBR.ActualEndTime,
tblJobsBR.BRJobIDref, tblJobsBR.PlannedEndDate, tblJobsBR.PlannedStartDate, tblJobsBR.PlannedEndTime,
(CASE WHEN [ActualStartDate] IS NULL THEN (CAST(ActualStartDate AS Varchar)) ELSE CAST(CAST(ActualStartDate AS DATE) AS DATETIME)
+ CAST(ActualStartTime AS TIME) END) AS ActualStart, (CASE WHEN [ActualEndDate] IS NULL THEN (CAST(ActualEndDate AS Varchar))
ELSE CAST(CAST(ActualEndDate AS DATE) AS DATETIME) + CAST(ActualEndTime AS TIME) END) AS ActualEnd, tblJobs.Batch
FROM tblJobs INNER JOIN
tblProducts ON tblJobs.ProductIDref = tblProducts.ProductID RIGHT OUTER JOIN
tblJobsBR ON tblJobs.JobID = tblJobsBR.BRJobIDref
WHERE (tblJobsBR.ActualStartDate IS NOT NULL) AND (tblJobs.Campaign = N'C12009-1141')
--qryCycleTimesBR_2
SELECT Campaign, ProductDescription, Standard, UpperLimit, LowerLimit, Yield, JobType, EquipOrTrain, Plant, ActualStartDate, ActualStartTime,
ActualEndDate, ActualEndTime, BRJobIDref, PlannedEndDate, PlannedStartDate, PlannedEndTime, ActualStart, ActualEnd, DATEDIFF(day,
PlannedStartDate, PlannedEndDate) * 24 AS TheoJobHrs, (CASE WHEN ActualStart IS NULL OR
ActualEnd IS NULL THEN 0 ELSE DATEDIFF(minute, ActualStart, ActualEnd) / 60 END) AS ActualJobHrs, Batch
FROM qryCycleTimesBR_1
--qryCycleTimesBR_3
SELECT ActualJobHrs - TheoJobHrs AS DiffBetweenActualJobHrsAndTheoJobHrs, RANK() OVER (ORDER BY ActualStart) AS Rank, Campaign,
ProductDescription, Standard, UpperLimit, LowerLimit, Yield, JobType, ActualStart, ActualEnd, TheoJobHrs, ActualJobHrs, Batch
FROM qryCycleTimesBR_2
--qryCycleTimesBR_4
SELECT A.Campaign, A.ProductDescription, A.Rank AS RankA, A.Batch AS BatchA, A.ActualStart AS ActualStartA, B.Rank AS RankB, B.Batch AS BatchB,
B.ActualStart AS ActualStartB, CAST(DATEDIFF(minute, A.ActualStart, B.ActualStart) / 60 + CAST(DATEDIFF(minute, A.ActualStart, B.ActualStart)
% 60 AS Decimal(18, 2)) / 60 AS decimal(18, 2)) AS StartToStartHrs
FROM qryCycleTimesBR_3 AS A INNER JOIN
qryCycleTimesBR_3 AS B ON A.Rank + 1 = B.Rank
SELECT A.Standard, A.UpperLimit, A.LowerLimit, A.Rank AS RankA, A.Batch AS BatchA, A.ActualStart AS ActualStartA,
(SELECT SUM(TheoJobHrs) / COUNT(*) AS AverageTheoJobHrs
FROM qryCycleTimesBR_3
WHERE (Rank <= B.Rank)) AS RollingAvgTheoJobHrs,
(SELECT SUM(ActualJobHrs) / COUNT(*) AS AverageActualJobHrs
FROM qryCycleTimesBR_3 AS qryCycleTimesBR_3_1
WHERE (Rank <= B.Rank)) AS RollingAvgActualJobHrs,
(SELECT SUM(Yield) / COUNT(*) AS AverageYield
FROM qryCycleTimesBR_3 AS qryCycleTimesBR_3_1
WHERE (Rank <= B.Rank)) AS RollingAvgYield
FROM qryCycleTimesBR_3 AS A INNER JOIN
qryCycleTimesBR_3 AS B ON A.Rank = B.Rank
--qryCycleTimesBR_6
SELECT qryCycleTimesBR_4.Campaign, qryCycleTimesBR_4.ProductDescription, qryCycleTimesBR_5.RankA, qryCycleTimesBR_5.BatchA,
qryCycleTimesBR_5.ActualStartA, qryCycleTimesBR_4.RankB, qryCycleTimesBR_4.BatchB, qryCycleTimesBR_4.ActualStartB,
qryCycleTimesBR_4.StartToStartHrs, qryCycleTimesBR_5.Standard, qryCycleTimesBR_5.UpperLimit,
qryCycleTimesBR_5.LowerLimit, qryCycleTimesBR_5.RollingAvgTheoJobHrs, qryCycleTimesBR_5.RollingAvgActualJobHrs,
qryCycleTimesBR_5.RollingAvgYield
FROM qryCycleTimesBR_4 RIGHT OUTER JOIN
qryCycleTimesBR_5 ON qryCycleTimesBR_4.RankA = qryCycleTimesBR_5.RankA
SELECT dbo.qryCycleTimesBR_4.Campaign, dbo.qryCycleTimesBR_4.ProductDescription, dbo.qryCycleTimesBR_5.RankA, dbo.qryCycleTimesBR_5.BatchA,
dbo.qryCycleTimesBR_5.ActualStartA, dbo.qryCycleTimesBR_4.RankB, dbo.qryCycleTimesBR_4.BatchB, dbo.qryCycleTimesBR_4.ActualStartB,
dbo.qryCycleTimesBR_4.StartToStartHrs, dbo.qryCycleTimesBR_5.Standard, dbo.qryCycleTimesBR_5.UpperLimit,
dbo.qryCycleTimesBR_5.LowerLimit, dbo.qryCycleTimesBR_5.RollingAvgTheoJobHrs, dbo.qryCycleTimesBR_5.RollingAvgActualJobHrs,
dbo.qryCycleTimesBR_5.RollingAvgYield
FROM dbo.qryCycleTimesBR_4 RIGHT OUTER JOIN
dbo.qryCycleTimesBR_5 ON dbo.qryCycleTimesBR_4.RankA = dbo.qryCycleTimesBR_5.RankA
August 17, 2009 at 8:07 am
Use temp tables and insert into those
something like this
Create Procedure x
as
Create #Tab1 (
Col1 integer)
Create #Tab2 (
Col2 integer)
Insert into #Tab1 Select x from yourtab where a=b
Insert into #tab2 Select y from #tab1 join anothertab on x=y
Select * from #tab2 join yetanothertable on z=a
August 18, 2009 at 1:15 am
thank, thats sound like it would work, I'll give it a try.
Asta
August 18, 2009 at 5:57 am
Is the "qryCycleTimesBR_6" the final result set that you want and the other queries are written just to get the final (qryCycleTimesBR_6) result.
"Keep Trying"
August 18, 2009 at 6:27 am
yes the 6th query is the final result set. but I have it working now using temp tables which I drop at the end of the sp. It works fine.
thank for you help.
Asta
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply