Using Joins and Derived tables

Question

Using Joins and Derived tables

beruken

SSC Journeyman

Points: 76
More actions
September 19, 2008 at 7:26 am

#193042

I have a SP that uses temp tables and joins them together based on which result set has returned the most records. I know this isn't optimal and would like to attempt this with derived tables. Here is my SP;
@ProjId int,
@StartDate Datetime
AS
Declare @ELCount int
Declare @SFCount int
--Build temp table of entrylog data
SELECT ISNULL(SUM(TT_EntryLog.Duration), 0.0) AS WorkedSum, YEAR(TT_EntryLog.EntryDate) AS YearNr,
MONTH(TT_EntryLog.EntryDate) AS MonthNr,
TT_EntryLog.UserID, ISNULL(SUM(TT_EntryLog.Duration), 0.00) * TT_Users.CostBasis AS ActualCost
INTO [#EntryLogSummary]
FROM TT_EntryLog RIGHT OUTER JOIN
TT_Users ON TT_EntryLog.UserID = TT_Users.UserID
WHERE (TT_EntryLog.ProjectID = @ProjId) AND (TT_EntryLog.EntryDate BETWEEN @StartDate AND DATEADD(mm, 12, @StartDate)) AND
(TT_Users.Exclude = 0)
GROUP BY YEAR(TT_EntryLog.EntryDate), MONTH(TT_EntryLog.EntryDate), TT_EntryLog.UserID, TT_Users.CostBasis
Set @ELCount = @@ROWCOUNT
--Build temp table of staffing data
SELECT ISNULL(SUM(TT_StaffingPlan.Hours), 0.0) AS Planned, TT_StaffingPlan.DT_StartMonth,
TT_StaffingPlan.YearNr, TT_StaffingPlan.MonthNr,
TT_StaffingPlan.UserID, ISNULL(SUM(TT_StaffingPlan.Hours), 0.0) * (TT_Users.CostBasis + 30) AS PlannedCost
INTO [#StaffingSummary]
FROM TT_StaffingPlan INNER JOIN TT_Users ON TT_StaffingPlan.UserID = TT_Users.UserID
WHERE (TT_StaffingPlan.ProjectID = @ProjId) AND (TT_StaffingPlan.DT_StartMonth BETWEEN @StartDate AND DATEADD(mm, 12, @StartDate))
GROUP BY TT_StaffingPlan.DT_StartMonth, TT_StaffingPlan.YearNr, TT_StaffingPlan.MonthNr, TT_StaffingPlan.UserID, TT_Users.CostBasis
Set @SFCount = @@RowCount
--Combine data to output recordset
-- The following evaluates which recordset has the most results and uses that one to as the master
--so the joins work properly
------------------------------------------------
if @SFCount > @ELCount
Begin
SELECT TT_Users.DisplayName as [Name],
[#StaffingSummary].MonthNr,
[#StaffingSummary].YearNr,
ISNULL([#StaffingSummary].Planned, 0.0) AS PlannedHours,
ISNULL([#StaffingSummary].PlannedCost, 0.00) as PlannedCost,
ISNULL([#EntryLogSummary].WorkedSum, 0.0) AS WorkedHours,
ISNULL([#EntryLogSummary].ActualCost, 0.00) as ActualCost
Into tempdata
FROM [#StaffingSummary]
LEFT OUTER JOIN [#EntryLogSummary] ON [#StaffingSummary].UserId = [#EntryLogSummary].UserId
AND [#StaffingSummary].YearNr = [#EntryLogSummary].YearNr
AND [#StaffingSummary].MonthNr = [#EntryLogSummary].MonthNr
LEFT OUTER JOIN TT_Users ON [#StaffingSummary].UserID = TT_Users.UserID
ORDER BY TT_Users.DisplayName, [#StaffingSummary].YearNr Asc, [#StaffingSummary].MonthNr Asc
Select * from tempdata
Select Distinct [Name], Sum(PlannedCost)as PlannedCostTotalForFY,
Sum(ActualCost) as ActualCostForFY ,
Avg(ActualCost) as AvgBurnRateForTimeWorked
From tempdata Group By [Name]
Drop Table tempdata
END
if @ELCount > @SFCount
Begin
SELECT TT_Users.DisplayName as [Name], [#EntryLogSummary].MonthNr, [#EntryLogSummary].YearNr,
ISNULL([#StaffingSummary].Planned, 0) AS PlannedHours,
ISNULL([#StaffingSummary].PlannedCost, 0.00) as PlannedCost,
ISNULL([#EntryLogSummary].WorkedSum,0.0) AS WorkedHours,
ISNULL([#EntryLogSummary].ActualCost, 0.00) as ActualCost
Into tempdata2
FROM [#EntryLogSummary] LEFT JOIN
[#StaffingSummary] ON [#EntryLogSummary].UserId = [#StaffingSummary].UserId AND
[#EntryLogSummary].YearNr = [#StaffingSummary].YearNr AND
[#EntryLogSummary].MonthNr = [#StaffingSummary].MonthNr
LEFT OUTER JOIN TT_Users ON #EntryLogSummary.UserID = TT_Users.UserID
Order By TT_Users.DisplayName Asc, [#EntryLogSummary].YearNr Asc, [#EntryLogSummary].MonthNr Asc
--Produce second result set
Select * from tempdata2
Select Distinct [Name], Sum(PlannedCost)as PlannedCostTotalForFY,
Sum(ActualCost) as ActualCostForFY ,
Avg(ActualCost) as AvgBurnRateForTimeWorked
From tempdata2 Group By [Name]
Drop Table tempdata2
END
--Drop temp tables
DROP TABLE #EntryLogSummary
DROP TABLE #StaffingSummary
RETURN

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Beruken

Instead of counting how many rows each of the temp tables contains and using the result to determine which is the parent table, it would make sense to use TT_Users as the driving table, and left join both temp tables to it? You could filter it on [UserID from one or the other temp table has to be non-null]. This would give you a consistent query which would then lend itself to substituting the temp tables for derived tables.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

beruken SSC Journeyman Points: 76 More actions · Answer 2

I think I understand that. I was able to build the derived output but still had the problem with master table. IS it possible to keep this result in memory to create a second result set?

Thanks!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Not sure you need to do that. First thing I'd do is check the joins. You've got TT_StaffingPlan INNER JOIN TT_Users

but

TT_EntryLog RIGHT OUTER JOIN TT_Users

You need to find out if either TT_StaffingPlan or TT_EntryLog have UserID not in TT_Users.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

beruken (9/19/2008)
I think I understand that. I was able to build the derived output but still had the problem with master table. IS it possible to keep this result in memory to create a second result set?
Thanks!

[font="Courier New"]-- Here's the original query:

--Build temp table of entrylog data

SELECT ISNULL(SUM(TT_EntryLog.Duration), 0.0) AS WorkedSum,

YEAR(TT_EntryLog.EntryDate) AS YearNr,

MONTH(TT_EntryLog.EntryDate) AS MonthNr,

TT_EntryLog.UserID,

ISNULL(SUM(TT_EntryLog.Duration), 0.00) * TT_Users.CostBasis AS ActualCost

--INTO [#EntryLogSummary]

FROM TT_EntryLog

RIGHT OUTER JOIN TT_Users ON TT_EntryLog.UserID = TT_Users.UserID

WHERE (TT_EntryLog.ProjectID = @ProjId)

AND (TT_EntryLog.EntryDate BETWEEN @StartDate AND DATEADD(mm, 12, @StartDate))

AND (TT_Users.Exclude = 0)

GROUP BY YEAR(TT_EntryLog.EntryDate), MONTH(TT_EntryLog.EntryDate), TT_EntryLog.UserID, TT_Users.CostBasis

-- Test the theory: see of you get the same rowcount...

SELECT ISNULL(SUM(l.Duration), 0.0) AS WorkedSum,

YEAR(l.EntryDate) AS YearNr,

MONTH(l.EntryDate) AS MonthNr,

l.UserID,

ISNULL(SUM(l.Duration), 0.00) * u.CostBasis AS ActualCost

FROM TT_Users u

INNER JOIN l ON l.UserID = u.UserID AND u.Exclude = 0

WHERE (l.ProjectID = @ProjId)

AND (l.EntryDate BETWEEN @StartDate AND DATEADD(mm, 12, @StartDate))

GROUP BY YEAR(l.EntryDate), MONTH(l.EntryDate), l.UserID, u.CostBasis

[/font]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

beruken SSC Journeyman Points: 76 More actions · Answer 5

I reduced it down to the following and got the correct number of records the original SP returned bu the sums were all wrong. I didn't filter on UserID.

SELECT TT_Users.DisplayName,

TT_StaffingPlan.MonthNr,

TT_StaffingPlan.YearNr,

ISNULL(SUM(TT_StaffingPlan.Hours), 0.00) AS PlannedHours,

ISNULL(SUM(TT_EntryLog.Duration), 0.00) AS WorkedSum,

ISNULL(SUM(TT_StaffingPlan.Hours), 0.00) * (TT_Users.CostBasis + 30) AS PlannedCost,

ISNULL(SUM(TT_EntryLog.Duration), 0.00) * TT_Users.CostBasis AS ActualCost

FROM TT_Users

LEFT OUTER JOIN

TT_StaffingPlan ON TT_Users.UserID = TT_StaffingPlan.UserID

LEFT OUTER JOIN

TT_EntryLog ON TT_Users.UserID = TT_EntryLog.UserID

WHERE (TT_StaffingPlan.ProjectID = 18) AND

(TT_EntryLog.EntryDate BETWEEN @StartDate AND DATEADD(mm, 12, @StartDate)) AND

(TT_StaffingPlan.DT_StartMonth BETWEEN @StartDate AND DATEADD(mm, 12, @StartDate))

GROUP BY TT_StaffingPlan.MonthNr, TT_StaffingPlan.YearNr, TT_Users.DisplayName, TT_Users.CostBasis

ORDER BY TT_Users.DisplayName, TT_StaffingPlan.YearNr Desc, MonthNr Desc

Original SP month year planhours plcost workhours actualcost

Crowell, Robert10200717616016202.5012352.500000

Crowell, Robert11200716014560117.507167.500000

New SP

Crowell, Robert102007422402075.003843840126575.000000

Crowell, Robert112007384002075.003494400126575.000000

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

It's too big a step in one go. You need to find out how many rows are returned by each temp table without aggregation, and in particular, how many rows per userID. You will probably need a derived table for at least one of the two child tables, probably both.

Would you have time to set up and post some sample data, for instance "Crowell, Robert"?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

You'd get better assistance if you follow the advice in the following article:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

😎

beruken SSC Journeyman Points: 76 More actions · Answer 8

I've attached the 3 scripted tables and some data. I would remove the category column from the entry log table. Thanks!