473,382 Members | 1,526 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

How to wrap a UNION query in a totals query?

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
9 5429
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
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
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
(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
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
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
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
(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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Steve | last post by:
I have a products table where the PK is ProductID. Also have the standard Orders table and OrderDetails table. I created a query that joins the Orders table and OrderDetails table. The query...
5
by: Alicia | last post by:
Hello everyone based on the data, I created a union query which produces this. SELECT ,,, 0 As ClosedCount FROM UNION SELECT ,, 0 AS OpenedCount, FROM ORDER BY , ;
4
by: stephena | last post by:
Hello all, hope someone can help. I recently built a report showing the distribution of money in and out of the company. Simplified version of which looks like: DECEMBER 2004 TELEPHONE...
15
by: Rolan | last post by:
There must be a way to enhance the performance of a query, or find a plausible workaround, but I seem to be hitting a wall. I have tried a few tweaks, however, there has been no improvement. ...
2
by: mattytee123 | last post by:
I have about 20 tables, of which I would like to do a union query and count of how many of each different code there is? The simplified verson of the table is structured like this. Code ...
7
by: Mark Carlyle via AccessMonster.com | last post by:
I have this update query that I am trying to run. I know the syntax is messed up but do not know how to correct it. Select 'UPDATE', Transactions,'Set = where = ' From "Get Daily Balances" ...
4
by: New Guy | last post by:
I'm trying to work with a system that somebody else built and I am confounded by the following problem: There is a table of payments and a table of charges. Each client has charges and payments...
4
by: lorirobn | last post by:
Hi, I have a report displaying items that are missing from a room. I created 2 queries, the first getting the items IN the room, and the second being an "unmatched" query that references the...
2
by: sara | last post by:
I am helping a non-profit track their elder clients and care given to the clients. The organization would like a report that shows various info on ALL clients, such as: # in each town, # and...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.