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_Loa d
@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,
@EstimatesFound Ct 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_Estim ate int
SET @UsedForPrint =1
SET @UsedForLoad =2
SET @ErrNoData =999
SET @WeekType_Estim ate =2
-- --------------------------------------------------------------------
-- See if there is any info for the Person/Dates in question
-- Bail out if not
SELECT @EstimatesFound Ct =
(
SELECT COUNT(*) FROM tblWeekReported
WHERE ((tblWeekReport ed.PersonID=@Pe rsonID) AND
(tblWeekReporte d.WeekType=@Wee kType_Estimate) AND
(
(tblWeekReporte d.BeginDate=@Be ginDate1 AND
tblWeekReported .EndDate=@EndDa te1) OR
(tblWeekReporte d.BeginDate=@Be ginDate2 AND
tblWeekReported .EndDate=@EndDa te2) OR
(tblWeekReporte d.BeginDate=@Be ginDate3 AND
tblWeekReported .EndDate=@EndDa te3) OR
(tblWeekReporte d.BeginDate=@Be ginDate4 AND
tblWeekReported .EndDate=@EndDa te4) OR
(tblWeekReporte d.BeginDate=@Be ginDate5 AND
tblWeekReported .EndDate=@EndDa te5) OR
(tblWeekReporte d.BeginDate=@Be ginDate6 AND
tblWeekReported .EndDate=@EndDa te6)
)
)
)
IF ((@UsedFor = @UsedForPrint) AND (@EstimatesFoun dCt = 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 "WeekReport ed" periods per line
IF OBJECT_ID('temp db..#Extract1') IS NOT NULL DROP TABLE #Extract1;
CREATE TABLE #Extract1
(
WeekReportedLin eID 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_Perio d1 DateTime,
BeginDate_Perio d2 DateTime,
BeginDate_Perio d3 DateTime,
BeginDate_Perio d4 DateTime,
BeginDate_Perio d5 DateTime,
BeginDate_Perio d6 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('temp db..#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_Perio d1 DateTime,
BeginDate_Perio d2 DateTime,
BeginDate_Perio d3 DateTime,
BeginDate_Perio d4 DateTime,
BeginDate_Perio d5 DateTime,
BeginDate_Perio d6 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_Perio d1,
EndDate_Period1 ,
Hours_Period1
)
SELECT
tblWeekReported Line.ProjectID,
tblWeekReported Line.WeekReport edID,
tblWeekReported .HoursAvailable ,
tblWeekReported .BeginDate,
tblWeekReported .EndDate,
tblHour.Hours
FROM (tblWeekReporte d
INNER JOIN tblWeekReported Line ON tblWeekReported .WeekReportedID =
tblWeekReported Line.WeekReport edID)
INNER JOIN tblHour ON tblWeekReported Line.WeekReport edLineID =
tblHour.WeekRep ortedLineID
WHERE
(
(tblWeekReporte d.BeginDate=@Be ginDate1) AND
(tblWeekReporte d.EndDate=@EndD ate1) AND
(tblWeekReporte d.WeekType=@Wee kType_Estimate) AND
(tblWeekReporte d.PersonID=@Per sonID)
);
-- ------------------
-- Second of six
INSERT INTO #Extract1
(
ProjectID,
WeekReportedID_ Period2,
HoursAvailable_ Period2,
BeginDate_Perio d2,
EndDate_Period2 ,
Hours_Period2
)
SELECT
tblWeekReported Line.ProjectID,
tblWeekReported Line.WeekReport edID,
tblWeekReported .HoursAvailable ,
tblWeekReported .BeginDate,
tblWeekReported .EndDate,
tblHour.Hours
FROM (tblWeekReporte d
INNER JOIN tblWeekReported Line ON tblWeekReported .WeekReportedID =
tblWeekReported Line.WeekReport edID)
INNER JOIN tblHour ON tblWeekReported Line.WeekReport edLineID =
tblHour.WeekRep ortedLineID
WHERE
(
(tblWeekReporte d.BeginDate=@Be ginDate2) AND
(tblWeekReporte d.EndDate=@EndD ate2) AND
(tblWeekReporte d.WeekType=@Wee kType_Estimate) AND
(tblWeekReporte d.PersonID=@Per sonID)
);
-- ------------------
-- Third of six
INSERT INTO #Extract1
(
ProjectID,
WeekReportedID_ Period3,
HoursAvailable_ Period3,
BeginDate_Perio d3,
EndDate_Period3 ,
Hours_Period3
)
SELECT
tblWeekReported Line.ProjectID,
tblWeekReported Line.WeekReport edID,
tblWeekReported .HoursAvailable ,
tblWeekReported .BeginDate,
tblWeekReported .EndDate,
tblHour.Hours
FROM (tblWeekReporte d
INNER JOIN tblWeekReported Line ON tblWeekReported .WeekReportedID =
tblWeekReported Line.WeekReport edID)
INNER JOIN tblHour ON tblWeekReported Line.WeekReport edLineID =
tblHour.WeekRep ortedLineID
WHERE
(
(tblWeekReporte d.BeginDate=@Be ginDate3) AND
(tblWeekReporte d.EndDate=@EndD ate3) AND
(tblWeekReporte d.WeekType=@Wee kType_Estimate) AND
(tblWeekReporte d.PersonID=@Per sonID)
);
-- ------------------
-- Fourth of six
INSERT INTO #Extract1
(
ProjectID,
WeekReportedID_ Period4,
HoursAvailable_ Period4,
BeginDate_Perio d4,
EndDate_Period4 ,
Hours_Period4
)
SELECT
tblWeekReported Line.ProjectID,
tblWeekReported Line.WeekReport edID,
tblWeekReported .HoursAvailable ,
tblWeekReported .BeginDate,
tblWeekReported .EndDate,
tblHour.Hours
FROM (tblWeekReporte d
INNER JOIN tblWeekReported Line ON tblWeekReported .WeekReportedID =
tblWeekReported Line.WeekReport edID)
INNER JOIN tblHour ON tblWeekReported Line.WeekReport edLineID =
tblHour.WeekRep ortedLineID
WHERE
(
(tblWeekReporte d.BeginDate=@Be ginDate4) AND
(tblWeekReporte d.EndDate=@EndD ate4) AND
(tblWeekReporte d.WeekType=@Wee kType_Estimate) AND
(tblWeekReporte d.PersonID=@Per sonID)
);
-- ------------------
-- Fifth of six
INSERT INTO #Extract1
(
ProjectID,
WeekReportedID_ Period5,
HoursAvailable_ Period5,
BeginDate_Perio d5,
EndDate_Period5 ,
Hours_Period5
)
SELECT
tblWeekReported Line.ProjectID,
tblWeekReported Line.WeekReport edID,
tblWeekReported .HoursAvailable ,
tblWeekReported .BeginDate,
tblWeekReported .EndDate,
tblHour.Hours
FROM (tblWeekReporte d
INNER JOIN tblWeekReported Line ON tblWeekReported .WeekReportedID =
tblWeekReported Line.WeekReport edID)
INNER JOIN tblHour ON tblWeekReported Line.WeekReport edLineID =
tblHour.WeekRep ortedLineID
WHERE
(
(tblWeekReporte d.BeginDate=@Be ginDate5) AND
(tblWeekReporte d.EndDate=@EndD ate5) AND
(tblWeekReporte d.WeekType=@Wee kType_Estimate) AND
(tblWeekReporte d.PersonID=@Per sonID)
);
-- ------------------
-- Sixth of six
INSERT INTO #Extract1
(
ProjectID,
WeekReportedID_ Period6,
HoursAvailable_ Period6,
BeginDate_Perio d6,
EndDate_Period6 ,
Hours_Period6
)
SELECT
tblWeekReported Line.ProjectID,
tblWeekReported Line.WeekReport edID,
tblWeekReported .HoursAvailable ,
tblWeekReported .BeginDate,
tblWeekReported .EndDate,
tblHour.Hours
FROM (tblWeekReporte d
INNER JOIN tblWeekReported Line ON tblWeekReported .WeekReportedID =
tblWeekReported Line.WeekReport edID)
INNER JOIN tblHour ON tblWeekReported Line.WeekReport edLineID =
tblHour.WeekRep ortedLineID
WHERE
(
(tblWeekReporte d.BeginDate=@Be ginDate6) AND
(tblWeekReporte d.EndDate=@EndD ate6) AND
(tblWeekReporte d.WeekType=@Wee kType_Estimate) AND
(tblWeekReporte d.PersonID=@Per sonID)
);
-- --------------------------------------------------------------------
-- 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_Perio d1,
BeginDate_Perio d2,
BeginDate_Perio d3,
BeginDate_Perio d4,
BeginDate_Perio d5,
BeginDate_Perio d6,
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(WeekReporte dID_Period1),
Sum(WeekReporte dID_Period2),
Sum(WeekReporte dID_Period3),
Sum(WeekReporte dID_Period4),
Sum(WeekReporte dID_Period5),
Sum(WeekReporte dID_Period6),
Sum(HoursAvaila ble_Period1),
Sum(HoursAvaila ble_Period2),
Sum(HoursAvaila ble_Period3),
Sum(HoursAvaila ble_Period4),
Sum(HoursAvaila ble_Period5),
Sum(HoursAvaila ble_Period6),
Sum(BeginDate_P eriod1),
Sum(BeginDate_P eriod2),
Sum(BeginDate_P eriod3),
Sum(BeginDate_P eriod4),
Sum(BeginDate_P eriod5),
Sum(BeginDate_P eriod6),
Sum(EndDate_Per iod1),
Sum(EndDate_Per iod2),
Sum(EndDate_Per iod3),
Sum(EndDate_Per iod4),
Sum(EndDate_Per iod5),
Sum(EndDate_Per iod6),
Sum(Hours_Perio d1),
Sum(Hours_Perio d2),
Sum(Hours_Perio d3),
Sum(Hours_Perio d4),
Sum(Hours_Perio d5),
Sum(Hours_Perio d6)
FROM #Extract1
GROUP BY #Extract1.Proje ctID;
-- =============== =============== =============== =============== =======
-- 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. DepartmentNameL ong,
tlkpDepartment. DepartmentNameS hort,
tlkpGroup.Group NameLong,
tlkpGroup.Group NameShort,
tlkpPerson.Empl oyeeNumber,
tlkpPerson.Name Last,
tlkpPerson.Name First,
tlkpPerson.Name Middle,
tlkpVendor.Vend orName
FROM ((tlkpPerson
LEFT JOIN tlkpVendor ON tlkpPerson.Vend orID = tlkpVendor.Vend orID)
LEFT JOIN tlkpGroup ON tlkpPerson.Grou pID = tlkpGroup.Group ID)
LEFT JOIN tlkpDepartment ON tlkpGroup.Depar tmentID =
tlkpDepartment. DepartmentID
WHERE tlkpPerson.Pers onID=@PersonID;
-- --------------------------------------------------------------------
-- Produce our second-and-last output table: A copy of the records in Extract#2
-- ------------------------------------------ End
---------------------------------
GO
-------------------------------------------------------------------------
--
PeteCresswell
