By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

How to wrap a UNION query in a totals query?

P: n/a
I've got some SQL that works as far as returning a recordset from a series of
UNION statements.

viz:

SELECT whatever
UNION this
UNION that
UNION other

Now I want to group and sum on it's results.

Started out tying:

SELECT * FROM
(
union stuff
)

....but couldn't even get past the syntax check.
Where I'm headed is a sort of pivot table presentation of some hours data
associated with various projects with a column for each of six date ranges.

Bottom line: can somebody give me a pointer to the syntax needed to wrap those
UNION statements and then select/group/sum their results?
--
PeteCresswell
Jul 20 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Hi Pete

It is always best to post the DDL (create table statements), example data
(as insert statements), expected output and your current attempts.

There are loads of posts regarding Pivot tables/CrossTabs such as
http://tinyurl.com/i9mt

At a guess you are also trying to create a derived table such as

SELECT Col1, COUNT(*)
FROM ( SELECT 1 as Col1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 3 ) A
GROUP BY Col1

John

"(Pete Cresswell)" <x@y.z> wrote in message
news:o0********************************@4ax.com...
I've got some SQL that works as far as returning a recordset from a series of UNION statements.

viz:

SELECT whatever
UNION this
UNION that
UNION other

Now I want to group and sum on it's results.

Started out tying:

SELECT * FROM
(
union stuff
)

...but couldn't even get past the syntax check.
Where I'm headed is a sort of pivot table presentation of some hours data
associated with various projects with a column for each of six date ranges.
Bottom line: can somebody give me a pointer to the syntax needed to wrap those UNION statements and then select/group/sum their results?
--
PeteCresswell

Jul 20 '05 #2

P: n/a
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
Jul 20 '05 #3

P: n/a
Here's the current final product (that returns what I want - albiet maybe not as
elegantly as it could...)
=============================
--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 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 estimat edit screen
-- - 12 dates: One for each first and last date in each estimating
period
--USED BY: rptEstimate..., frmEstimate

CREATE PROCEDURE spEstimate_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 @EstimatesFoundCt int

-- --------------------------------------------------------------------
-- We don't know how to do figurative constants in TSQL, so we
-- do our own little workaround to document avoid "magic numbers"
-- in the code

DECLARE @ErrNoData int,
@UsedForPrint int,
@UsedForLoad int,
@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,

HoursAvailable_Period1 Float,
HoursAvailable_Period2 Float,
HoursAvailable_Period3 Float,
HoursAvailable_Period4 Float,
HoursAvailable_Period5 Float,
HoursAvailable_Period6 Float,

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,

HoursAvailable_Period1 Float,
HoursAvailable_Period2 Float,
HoursAvailable_Period3 Float,
HoursAvailable_Period4 Float,
HoursAvailable_Period5 Float,
HoursAvailable_Period6 Float,

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,
HoursAvailable_Period1,
Hours_Period1
)

SELECT
tblWeekReportedLine.ProjectID,
tblWeekReported.HoursAvailable,
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,
HoursAvailable_Period2,
Hours_Period2
)

SELECT
tblWeekReportedLine.ProjectID,
tblWeekReported.HoursAvailable,
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,
HoursAvailable_Period3,
Hours_Period3
)

SELECT
tblWeekReportedLine.ProjectID,
tblWeekReported.HoursAvailable,
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,
HoursAvailable_Period4,
Hours_Period4
)

SELECT
tblWeekReportedLine.ProjectID,
tblWeekReported.HoursAvailable,
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,
HoursAvailable_Period5,
Hours_Period5
)

SELECT
tblWeekReportedLine.ProjectID,
tblWeekReported.HoursAvailable,
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,
HoursAvailable_Period6,
Hours_Period6
)

SELECT
tblWeekReportedLine.ProjectID,
tblWeekReported.HoursAvailable,
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,

HoursAvailable_Period1,
HoursAvailable_Period2,
HoursAvailable_Period3,
HoursAvailable_Period4,
HoursAvailable_Period5,
HoursAvailable_Period6,

Hours_Period1,
Hours_Period2,
Hours_Period3,
Hours_Period4,
Hours_Period5,
Hours_Period6
)

SELECT
ProjectID,

Sum(HoursAvailable_Period1),
Sum(HoursAvailable_Period2),
Sum(HoursAvailable_Period3),
Sum(HoursAvailable_Period4),
Sum(HoursAvailable_Period5),
Sum(HoursAvailable_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 AS 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
with dates
-- recovered via a join on DateID

SELECT
*,
@BeginDate1 AS BeginDate_Period1,
@BeginDate2 AS BeginDate_Period2,
@BeginDate3 AS BeginDate_Period3,
@BeginDate4 AS BeginDate_Period4,
@BeginDate5 AS BeginDate_Period5,
@BeginDate6 AS BeginDate_Period6,

@EndDate1 AS EndDate_Period1,
@EndDate2 AS EndDate_Period2,
@EndDate3 AS EndDate_Period3,
@EndDate4 AS EndDate_Period4,
@EndDate5 AS EndDate_Period5,
@EndDate6 AS EndDate_Period6

FROM #Extract2

-- ------------------------------------------ End
---------------------------------
GO

=============================
--
PeteCresswell
Jul 20 '05 #4

P: n/a
(Pete Cresswell) (x@y.z) writes:
Here's the current final product (that returns what I want - albiet
maybe not as elegantly as it could...)
Since you are crazy enough to post your code, permit me just two comments,
both a bit on the style side, but nevertheless:
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)
)
)
)
Use aliases. This makes your queries more consice, and easier to read:

SELECT @EstimatesFoundCt =
(SELECT COUNT(*)
FROM tblWeekReported wr
WHERE ((wr.PersonID=@PersonID) AND
(wr.WeekType=@WeekType_Estimate) AND
((wr.BeginDate=@BeginDate1 AND wr.EndDate=@EndDate1) OR
(wr.BeginDate=@BeginDate2 AND wr.EndDate=@EndDate2) OR
(wr.BeginDate=@BeginDate3 AND wr.EndDate=@EndDate3) OR
(wr.BeginDate=@BeginDate4 AND wr.EndDate=@EndDate4) OR
(wr.BeginDate=@BeginDate5 AND wr.EndDate=@EndDate5) OR
(wr.BeginDate=@BeginDate6 AND wr.EndDate=@EndDate6) )
)
)

Since this query only accesses one table, many programmers would use any
prefix, and I have to count myself amoung those. However, as soon there
is more than tables in a query, you always use prefixes. Even if a column
name is not unambiguous today, it may not be tomorrow.

CREATE TABLE #Extract1
(
WeekReportedLineID int,
ProjectID int,

HoursAvailable_Period1 Float,
HoursAvailable_Period2 Float,
HoursAvailable_Period3 Float,
HoursAvailable_Period4 Float,
HoursAvailable_Period5 Float,
HoursAvailable_Period6 Float,
...


In my opinon you should always include explicit NULL/NOT NULL indicators.
If you don't specify, there is a default, but whether that default is
NULL or NOT NULL depends on SET commands set at run-time, so you can
get surprises if you don't tell what you want.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

P: n/a
Hi Erland,

Looks like the correspondence course fron the John Bell school or dyslexic
posting is working!! :)
Since this query only accesses one table, many programmers would use any
Since this query only accesses one table, many programmers would not use
any
prefix, and I have to count myself amoung those. However, as soon there
is more than tables in a query, you always use prefixes. Even if a column
is more than one table in a query, you should always use prefixes. Even if a
column
name is not unambiguous today, it may not be tomorrow.


name is not ambiguous today, it may not be tomorrow.

Looks like I better start planning the graduation ceremony!

John
Jul 20 '05 #6

P: n/a
RE/
Use aliases. This makes your queries more consice, and easier to read:


I like it. Will change over ASAP.

What about the "NULLS" biz? Does that refer to whether-or-not Null is allowed
as a value?
--
PeteCresswell
Jul 20 '05 #7

P: n/a
The nulls are to do do with the ANSI_NULL_DEFAULT setting for the database
which can be different from database to database.

When this option is set to ON, all user-defined data types or columns that
are not explicitly defined as NOT NULL during a CREATE TABLE or ALTER TABLE
statement default to allowing null values. See:
http://msdn.microsoft.com/library/de...asp?frame=true

John

"(Pete Cresswell)" <x@y.z> wrote in message
news:qq********************************@4ax.com...
RE/
Use aliases. This makes your queries more consice, and easier to read:
I like it. Will change over ASAP.

What about the "NULLS" biz? Does that refer to whether-or-not Null is

allowed as a value?
--
PeteCresswell

Jul 20 '05 #8

P: n/a
(Pete Cresswell) (x@y.z) writes:
What about the "NULLS" biz? Does that refer to whether-or-not Null is
allowed as a value?


Yes.

nisse int NOT NULL
kalle int NULL

nisse does permits NULL, but kalle does. But in:

nisse int,
kalle int,

You don't know whether NULL are accepted or not. Well, at least I don't.
There are two settings which controls the default, at it is all very
confusing.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #9

P: n/a
Did you alias the virtual table?

SELECT * FROM
(
SELECT whatever
UNION this
UNION that
UNION other
) AS MYBIGUNION -- ALIAS TO UNION
"(Pete Cresswell)" <x@y.z> wrote in message
news:o0********************************@4ax.com...
I've got some SQL that works as far as returning a recordset from a series of UNION statements.

viz:

SELECT whatever
UNION this
UNION that
UNION other

Now I want to group and sum on it's results.

Started out tying:

SELECT * FROM
(
union stuff
)

...but couldn't even get past the syntax check.
Where I'm headed is a sort of pivot table presentation of some hours data
associated with various projects with a column for each of six date ranges.
Bottom line: can somebody give me a pointer to the syntax needed to wrap those UNION statements and then select/group/sum their results?
--
PeteCresswell

Jul 20 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.