RE/
It is always best to post the DDL (create table statements), example data
(as insert statements), expected output and your current attempts.
I was trying for brevity in hopes of not depressing anybody...-)
Remember, you asked for it....
I've given up on the UNION statements - partially because they create such a
monster and I'm finding that "Divide and conquer" is the best approach for me:
do things in smaller, testable steps and then put the steps together.
Accordingly, I'm replacing the UNION with a work table and a series of queries
that populates it.
Were I'm at now is below. My current problem is coalescing the dates into the
single-record-per-project result. I'm guessing that I'll wind up creating
another temp table with a PeriodID, BeginDate, and EndDate and then, instead of
trying to Sum() the dates (a no-no...) I'll just convert them to a PeriodID and
join to the dates temp table at the very end to recover them.
Don't anybody get *too* indignant about this code: I'm a newbie and still
groping around....
-------------------------------------------------------------------------
--PURPOSE: To return the two recordsets to be used in loading
-- the Estimate edit screen or the work tables behind
-- an Estimate report:
-- > A single header rec with department, person's name, and so-forth
-- > Many records for various projects/estimating periods. These
records
-- are in a sort of pivot table presentation with one column for each
-- date range. i.e. one row contains data from many weeks reported.
--ACCEPTS: - PersonID of person doing the estimating
-- - A flag telling what the data is to be used for:
-- 1 = To Print a report
-- 2 = To load the estimate edit screen
-- - 12 dates: One for each first and last date in each estimating
period
--USED BY: rptEstimate..., frmEstimate
CREATE PROCEDURE spTimeSheet_Load
@PersonID int,
@UsedFor int,
@BeginDate1 datetime,
@EndDate1 datetime,
@BeginDate2 datetime,
@EndDate2 datetime,
@BeginDate3 datetime,
@EndDate3 datetime,
@BeginDate4 datetime,
@EndDate4 datetime,
@BeginDate5 datetime,
@EndDate5 datetime,
@BeginDate6 datetime,
@EndDate6 datetime,
AS
DECLARE @ErrNoData int,
@EstimatesFoundCt int,
-- --------------------------------------------------------------------
-- We don't know how to do figurative constants in TSQL, so we
-- do our own little workaround to avoid "magic numbers"
-- in the code
DECLARE @UsedForPrint int
DECLARE @UsedForLoad int
DECLARE @WeekType_Estimate int
SET @UsedForPrint =1
SET @UsedForLoad =2
SET @ErrNoData =999
SET @WeekType_Estimate =2
-- --------------------------------------------------------------------
-- See if there is any info for the Person/Dates in question
-- Bail out if not
SELECT @EstimatesFoundCt =
(
SELECT COUNT(*) FROM tblWeekReported
WHERE ((tblWeekReported.PersonID=@PersonID) AND
(tblWeekReported.WeekType=@WeekType_Estimate) AND
(
(tblWeekReported.BeginDate=@BeginDate1 AND
tblWeekReported.EndDate=@EndDate1) OR
(tblWeekReported.BeginDate=@BeginDate2 AND
tblWeekReported.EndDate=@EndDate2) OR
(tblWeekReported.BeginDate=@BeginDate3 AND
tblWeekReported.EndDate=@EndDate3) OR
(tblWeekReported.BeginDate=@BeginDate4 AND
tblWeekReported.EndDate=@EndDate4) OR
(tblWeekReported.BeginDate=@BeginDate5 AND
tblWeekReported.EndDate=@EndDate5) OR
(tblWeekReported.BeginDate=@BeginDate6 AND
tblWeekReported.EndDate=@EndDate6)
)
)
)
IF ((@UsedFor = @UsedForPrint) AND (@EstimatesFoundCt = 0))
RETURN @ErrNoData
-- --------------------------------------------------------------------
-- Set a parameter that prevents our work table create/populate operations
-- from appending unwanted recordsets to the output
SET NOCOUNT ON
-- ================================================== ==================
-- Create our work tables
-- ------------------
-- 1) Create a work table that will receive a pivoted version of our
-- data: up to six "WeekReported" periods per line
IF OBJECT_ID('tempdb..#Extract1') IS NOT NULL DROP TABLE #Extract1;
CREATE TABLE #Extract1
(
WeekReportedLineID int,
ProjectID int,
WeekReportedID_Period1 int,
WeekReportedID_Period2 int,
WeekReportedID_Period3 int,
WeekReportedID_Period4 int,
WeekReportedID_Period5 int,
WeekReportedID_Period6 int,
HoursAvailable_Period1 Float,
HoursAvailable_Period2 Float,
HoursAvailable_Period3 Float,
HoursAvailable_Period4 Float,
HoursAvailable_Period5 Float,
HoursAvailable_Period6 Float,
BeginDate_Period1 DateTime,
BeginDate_Period2 DateTime,
BeginDate_Period3 DateTime,
BeginDate_Period4 DateTime,
BeginDate_Period5 DateTime,
BeginDate_Period6 DateTime,
EndDate_Period1 DateTime,
EndDate_Period2 DateTime,
EndDate_Period3 DateTime,
EndDate_Period4 DateTime,
EndDate_Period5 DateTime,
EndDate_Period6 DateTime,
Hours_Period1 Float,
Hours_Period2 Float,
Hours_Period3 Float,
Hours_Period4 Float,
Hours_Period5 Float,
Hours_Period6 Float
);
-- ------------------
-- 2) Create a work table like the first one, whose mission in life
-- is to receive summed data from the first one.
IF OBJECT_ID('tempdb..#Extract2') IS NOT NULL DROP TABLE #Extract2;
CREATE TABLE #Extract2
(
ProjectID int,
WeekReportedID_Period1 int,
WeekReportedID_Period2 int,
WeekReportedID_Period3 int,
WeekReportedID_Period4 int,
WeekReportedID_Period5 int,
WeekReportedID_Period6 int,
HoursAvailable_Period1 Float,
HoursAvailable_Period2 Float,
HoursAvailable_Period3 Float,
HoursAvailable_Period4 Float,
HoursAvailable_Period5 Float,
HoursAvailable_Period6 Float,
BeginDate_Period1 DateTime,
BeginDate_Period2 DateTime,
BeginDate_Period3 DateTime,
BeginDate_Period4 DateTime,
BeginDate_Period5 DateTime,
BeginDate_Period6 DateTime,
EndDate_Period1 DateTime,
EndDate_Period2 DateTime,
EndDate_Period3 DateTime,
EndDate_Period4 DateTime,
EndDate_Period5 DateTime,
EndDate_Period6 DateTime,
Hours_Period1 Float,
Hours_Period2 Float,
Hours_Period3 Float,
Hours_Period4 Float,
Hours_Period5 Float,
Hours_Period6 Float
);
-- --------------------
-- 3) Populate first work table with multiple records for each timesheet
line.
-- We do this with a series of INSERT statements: one for each period.
-- ------------------
-- First of six
INSERT INTO #Extract1
(
ProjectID,
WeekReportedID_Period1,
HoursAvailable_Period1,
BeginDate_Period1,
EndDate_Period1,
Hours_Period1
)
SELECT
tblWeekReportedLine.ProjectID,
tblWeekReportedLine.WeekReportedID,
tblWeekReported.HoursAvailable,
tblWeekReported.BeginDate,
tblWeekReported.EndDate,
tblHour.Hours
FROM (tblWeekReported
INNER JOIN tblWeekReportedLine ON tblWeekReported.WeekReportedID =
tblWeekReportedLine.WeekReportedID)
INNER JOIN tblHour ON tblWeekReportedLine.WeekReportedLineID =
tblHour.WeekReportedLineID
WHERE
(
(tblWeekReported.BeginDate=@BeginDate1) AND
(tblWeekReported.EndDate=@EndDate1) AND
(tblWeekReported.WeekType=@WeekType_Estimate) AND
(tblWeekReported.PersonID=@PersonID)
);
-- ------------------
-- Second of six
INSERT INTO #Extract1
(
ProjectID,
WeekReportedID_Period2,
HoursAvailable_Period2,
BeginDate_Period2,
EndDate_Period2,
Hours_Period2
)
SELECT
tblWeekReportedLine.ProjectID,
tblWeekReportedLine.WeekReportedID,
tblWeekReported.HoursAvailable,
tblWeekReported.BeginDate,
tblWeekReported.EndDate,
tblHour.Hours
FROM (tblWeekReported
INNER JOIN tblWeekReportedLine ON tblWeekReported.WeekReportedID =
tblWeekReportedLine.WeekReportedID)
INNER JOIN tblHour ON tblWeekReportedLine.WeekReportedLineID =
tblHour.WeekReportedLineID
WHERE
(
(tblWeekReported.BeginDate=@BeginDate2) AND
(tblWeekReported.EndDate=@EndDate2) AND
(tblWeekReported.WeekType=@WeekType_Estimate) AND
(tblWeekReported.PersonID=@PersonID)
);
-- ------------------
-- Third of six
INSERT INTO #Extract1
(
ProjectID,
WeekReportedID_Period3,
HoursAvailable_Period3,
BeginDate_Period3,
EndDate_Period3,
Hours_Period3
)
SELECT
tblWeekReportedLine.ProjectID,
tblWeekReportedLine.WeekReportedID,
tblWeekReported.HoursAvailable,
tblWeekReported.BeginDate,
tblWeekReported.EndDate,
tblHour.Hours
FROM (tblWeekReported
INNER JOIN tblWeekReportedLine ON tblWeekReported.WeekReportedID =
tblWeekReportedLine.WeekReportedID)
INNER JOIN tblHour ON tblWeekReportedLine.WeekReportedLineID =
tblHour.WeekReportedLineID
WHERE
(
(tblWeekReported.BeginDate=@BeginDate3) AND
(tblWeekReported.EndDate=@EndDate3) AND
(tblWeekReported.WeekType=@WeekType_Estimate) AND
(tblWeekReported.PersonID=@PersonID)
);
-- ------------------
-- Fourth of six
INSERT INTO #Extract1
(
ProjectID,
WeekReportedID_Period4,
HoursAvailable_Period4,
BeginDate_Period4,
EndDate_Period4,
Hours_Period4
)
SELECT
tblWeekReportedLine.ProjectID,
tblWeekReportedLine.WeekReportedID,
tblWeekReported.HoursAvailable,
tblWeekReported.BeginDate,
tblWeekReported.EndDate,
tblHour.Hours
FROM (tblWeekReported
INNER JOIN tblWeekReportedLine ON tblWeekReported.WeekReportedID =
tblWeekReportedLine.WeekReportedID)
INNER JOIN tblHour ON tblWeekReportedLine.WeekReportedLineID =
tblHour.WeekReportedLineID
WHERE
(
(tblWeekReported.BeginDate=@BeginDate4) AND
(tblWeekReported.EndDate=@EndDate4) AND
(tblWeekReported.WeekType=@WeekType_Estimate) AND
(tblWeekReported.PersonID=@PersonID)
);
-- ------------------
-- Fifth of six
INSERT INTO #Extract1
(
ProjectID,
WeekReportedID_Period5,
HoursAvailable_Period5,
BeginDate_Period5,
EndDate_Period5,
Hours_Period5
)
SELECT
tblWeekReportedLine.ProjectID,
tblWeekReportedLine.WeekReportedID,
tblWeekReported.HoursAvailable,
tblWeekReported.BeginDate,
tblWeekReported.EndDate,
tblHour.Hours
FROM (tblWeekReported
INNER JOIN tblWeekReportedLine ON tblWeekReported.WeekReportedID =
tblWeekReportedLine.WeekReportedID)
INNER JOIN tblHour ON tblWeekReportedLine.WeekReportedLineID =
tblHour.WeekReportedLineID
WHERE
(
(tblWeekReported.BeginDate=@BeginDate5) AND
(tblWeekReported.EndDate=@EndDate5) AND
(tblWeekReported.WeekType=@WeekType_Estimate) AND
(tblWeekReported.PersonID=@PersonID)
);
-- ------------------
-- Sixth of six
INSERT INTO #Extract1
(
ProjectID,
WeekReportedID_Period6,
HoursAvailable_Period6,
BeginDate_Period6,
EndDate_Period6,
Hours_Period6
)
SELECT
tblWeekReportedLine.ProjectID,
tblWeekReportedLine.WeekReportedID,
tblWeekReported.HoursAvailable,
tblWeekReported.BeginDate,
tblWeekReported.EndDate,
tblHour.Hours
FROM (tblWeekReported
INNER JOIN tblWeekReportedLine ON tblWeekReported.WeekReportedID =
tblWeekReportedLine.WeekReportedID)
INNER JOIN tblHour ON tblWeekReportedLine.WeekReportedLineID =
tblHour.WeekReportedLineID
WHERE
(
(tblWeekReported.BeginDate=@BeginDate6) AND
(tblWeekReported.EndDate=@EndDate6) AND
(tblWeekReported.WeekType=@WeekType_Estimate) AND
(tblWeekReported.PersonID=@PersonID)
);
-- --------------------------------------------------------------------
-- Summarize/Total the first work table's rotated/pivoted data into the second
work table
-- Same format, just boiled down to a single record per project.
INSERT INTO #Extract2
(
ProjectID,
WeekReportedID_Period1,
WeekReportedID_Period2,
WeekReportedID_Period3,
WeekReportedID_Period4,
WeekReportedID_Period5,
WeekReportedID_Period6,
HoursAvailable_Period1,
HoursAvailable_Period2,
HoursAvailable_Period3,
HoursAvailable_Period4,
HoursAvailable_Period5,
HoursAvailable_Period6,
BeginDate_Period1,
BeginDate_Period2,
BeginDate_Period3,
BeginDate_Period4,
BeginDate_Period5,
BeginDate_Period6,
EndDate_Period1,
EndDate_Period2,
EndDate_Period3,
EndDate_Period4,
EndDate_Period5,
EndDate_Period6,
Hours_Period1,
Hours_Period2,
Hours_Period3,
Hours_Period4,
Hours_Period5,
Hours_Period6
)
SELECT
ProjectID,
Sum(WeekReportedID_Period1),
Sum(WeekReportedID_Period2),
Sum(WeekReportedID_Period3),
Sum(WeekReportedID_Period4),
Sum(WeekReportedID_Period5),
Sum(WeekReportedID_Period6),
Sum(HoursAvailable_Period1),
Sum(HoursAvailable_Period2),
Sum(HoursAvailable_Period3),
Sum(HoursAvailable_Period4),
Sum(HoursAvailable_Period5),
Sum(HoursAvailable_Period6),
Sum(BeginDate_Period1),
Sum(BeginDate_Period2),
Sum(BeginDate_Period3),
Sum(BeginDate_Period4),
Sum(BeginDate_Period5),
Sum(BeginDate_Period6),
Sum(EndDate_Period1),
Sum(EndDate_Period2),
Sum(EndDate_Period3),
Sum(EndDate_Period4),
Sum(EndDate_Period5),
Sum(EndDate_Period6),
Sum(Hours_Period1),
Sum(Hours_Period2),
Sum(Hours_Period3),
Sum(Hours_Period4),
Sum(Hours_Period5),
Sum(Hours_Period6)
FROM #Extract1
GROUP BY #Extract1.ProjectID;
-- ================================================== =================
-- BEGIN GENERATING OUTPUT
-- Reset a parameter so that subsequent SELECT...; statements will each
-- append a recordset to the output stream
SET NOCOUNT OFF
-- --------------------------------------------------------------------
-- For our first output table return a "Header Info" record that contains
-- information about the person doing the estimating
SELECT
@PersonID,
tlkpDepartment.DepartmentNameLong,
tlkpDepartment.DepartmentNameShort,
tlkpGroup.GroupNameLong,
tlkpGroup.GroupNameShort,
tlkpPerson.EmployeeNumber,
tlkpPerson.NameLast,
tlkpPerson.NameFirst,
tlkpPerson.NameMiddle,
tlkpVendor.VendorName
FROM ((tlkpPerson
LEFT JOIN tlkpVendor ON tlkpPerson.VendorID = tlkpVendor.VendorID)
LEFT JOIN tlkpGroup ON tlkpPerson.GroupID = tlkpGroup.GroupID)
LEFT JOIN tlkpDepartment ON tlkpGroup.DepartmentID =
tlkpDepartment.DepartmentID
WHERE tlkpPerson.PersonID=@PersonID;
-- --------------------------------------------------------------------
-- Produce our second-and-last output table: A copy of the records in Extract#2
-- ------------------------------------------ End
---------------------------------
GO
-------------------------------------------------------------------------
--
PeteCresswell
