How to insert 4 records for every one found?

This SQL will insert one record into #Pivot1 for every one
record returned by the SELECT statement:
---
INSERT INTO #Pivot1
(
PersonID,
ProjectID,
Estimate5,
Available5
)
SELECT
wr.PersonID,
ln.ProjectID,
hr.Hours,
wr.HoursAvailable
FROM (tblWeekReported wr
INNER JOIN tblWeekReportedLine ln ON wr.WeekReportedID = ln.WeekReportedID)
INNER JOIN tblHour hr ON ln.WeekReportedLineID = hr.WeekReportedLineID
WHERE
(
(wr.BeginDate=@.BeginDate5) AND
(wr.EndDate=@.EndDate5) AND
(wr.WeekType=2) -- 2=Monthly
);
---
What I need to do, however, is to split each of the incoming records up
into 4 inserts to #Pivot1, dividing 'Hours' and 'HoursAvailable' by 4
for each record.
I guess the brute force method would be to just run that snippet of SQL
four times... but is there a "right" way?
--
PeteCresswellOops! I think I may have something:
---
INSERT INTO #Pivot1
(
PersonID,
ProjectID,
Estimate5,
Available5
)
SELECT
wr.PersonID,
ln.ProjectID,
hr.Hours/4,
wr.HoursAvailable/4
FROM (tblWeekReported wr
INNER JOIN tblWeekReportedLine ln ON wr.WeekReportedID = ln.WeekReportedID)
INNER JOIN tblHour hr ON ln.WeekReportedLineID =hr.WeekReportedLineID
WHERE
(
(wr.BeginDate=@.BeginDate5) AND
(wr.EndDate=@.EndDate5) AND
(wr.WeekType=2) -- 2=Monthly
);
INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
---
Am I on the right track?
--
PeteCresswell|||RE/
>INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
>INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
>INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
"Oops" again: strike that last INSERT - it would
have given us 8 copies of each rec instead of 4...
--
PeteCresswell|||That wouldn't work, you'd end up with far too many records.
--
HTH
Ryan Waight, MCDBA, MCSE
"(Pete Cresswell)" <x@.y.z> wrote in message
news:0k3vqv0fr0s2763bmgfma27re6e4lbglhc@.4ax.com...
> Oops! I think I may have something:
> ---
> INSERT INTO #Pivot1
> (
> PersonID,
> ProjectID,
> Estimate5,
> Available5
> )
> SELECT
> wr.PersonID,
> ln.ProjectID,
> hr.Hours/4,
> wr.HoursAvailable/4
> FROM (tblWeekReported wr
> INNER JOIN tblWeekReportedLine ln ON wr.WeekReportedID =ln.WeekReportedID)
> INNER JOIN tblHour hr ON ln.WeekReportedLineID => hr.WeekReportedLineID
> WHERE
> (
> (wr.BeginDate=@.BeginDate5) AND
> (wr.EndDate=@.EndDate5) AND
> (wr.WeekType=2) -- 2=Monthly
> );
> INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
> INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
> INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
> ---
> Am I on the right track?
> --
> PeteCresswell|||On Mon, 10 Nov 2003 13:05:03 GMT, "(Pete Cresswell)" <x@.y.z> wrote:
>This SQL will insert one record into #Pivot1 for every one
>record returned by the SELECT statement:
>...<snip>...
>What I need to do, however, is to split each of the incoming records up
>into 4 inserts to #Pivot1, dividing 'Hours' and 'HoursAvailable' by 4
>for each record.
>I guess the brute force method would be to just run that snippet of SQL
>four times... but is there a "right" way?
I don't know about right way, but here's a couple of ways:
a) after inserting these rows, select them and insert again (thus
doubling them). Then select them and insert them once more (thus
doubling the double, i.e. four rows for each original one)
b) create a temp table, populate with four records, and join to it in
the select part of your statement. A cartesian join (i.e. no criteria
for the join so that all rows from your temp table are selected and
joined with all rows from the rest of your query) will mean that each
row will appear four times in your result set. This would be my
preference, as it involves a single insert (except for the temp table :)
Of course, if you did this regularly enough, you might want to leave the
table of four rows around permanently, ready to join with. Kind of like
the DUAL table in Oracle these days, that provides a hack method for
returning results from functions without referencing a "real" table.
cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander

How to Include months not in the records?

Hi,
I have 2 tables created.
One is the Sale Persons (PersonID, PersonName) and the other is the Sales Detail (PersonID, Month, Year, TotalSales)
PersonID | PersonName PersonID | Month | Year | Total Sales
================== ==================================
ID1 | Sally ID1 | 1 | 2005 | 1000
ID2 | David ID2 | 2 | 2005 | 1500
Is it possible to write in a SQL statement to return all the sales person & the total sales for the 12 months for a particular year (even though some month data are not in the table)
I would like the result to be like the following:
PersonID | Month | Year | Total Sales
===================================
ID1 | 1 | 2005 | 1000
ID1 | 2 | 2005 | 0
ID1 | 3 | 2005 | 0
(For month 4 - 12) Total Sales will be 0 too as no records exist in the first place
ID2 | 1 | 2005 | 0
ID2 | 2 | 2005 | 1500
........

Do you have to do it in SQL or can you do it in code?|||

Create a temporary table using a sql proc or in code that contains the year(s) and months that you do want to include. Then you just have to OUTER join it to your existing tables to fill in the missing gaps.
HTH.