October 29, 2003 at 9:31 am
When trying to compile this sp there is an error that the variable @startdate must be declared. I can't for the life of me figure out why since the parameter is declared. Before I pull out any more hair I thought a few more pairs of eyes might find something obvious that I am missing.
Also, if anyone can offer a method of accomplishing this without the use of the temp table I would be interested in seeing it.
stored preocedure:
CREATE PROC uspRptWorkerHoursSummary
@startdate smalldatetime,
@enddate smalldatetime,
@areaprogram int
AS
SELECT
worker_ID,
SUM
(CASE WHEN service_date between @startdate and @enddate and
sh_toplevel_defn = 'Direct' and area_pgm_ID = @areaprogram
THEN hours
ELSE 0
END)
AS [Direct],
SUM
(CASE WHEN service_date between @startdate and @enddate and
sh_toplevel_defn = 'Indirect' and area_pgm_ID = @areaprogram
THEN hours
ELSE 0
END)
AS [Indirect],
SUM
(CASE WHEN service_date between @startdate and @enddate and
sh_toplevel_defn = 'Other' and area_pgm_ID = @areaprogram
THEN hours
ELSE 0
END)
AS [Other]
INTO #testxx
FROM
vwRptXWorkerHoursSummary
GROUP BY
worker_ID
GO
SELECT
a.worker_ID, a.direct, a.indirect, a.other, b.recording
FROM
#testxx a join
(select worker, SUM(hours) as recording
from vwRecordingHoursWorker
where date between @startdate and @enddate and
area = @areaprogram
group by worker) b
ON a.worker_ID = b.worker
GO
Thanks!
Tim
October 29, 2003 at 9:36 am
I should add that the select stmt alone works fine with values in place of the parameters
TH
Tim
October 29, 2003 at 9:44 am
Drop the Middle "GO".
I did the same thing recently pasting together a bunch of code. The go apparently acts like an END for the SP, so the code following is a new SP ???
Don't understand, just know it works.
KlK, MCSE
KlK
October 29, 2003 at 9:58 am
"go" is a batch directive and is not used within stored procedures. It creates a new batch, which means your local parameters are out of scope because everything following the "go" is a separate batch that stands on its own. The CREATE PROC batch is ended by the first "go". Here's a SP using derived tables instead of a temporary table:
CREATE PROC uspRptWorkerHoursSummary
@StartDate smalldatetime,
@EndDate smalldatetime,
@AreaProgram int
AS
SET NOCOUNT ON
SELECT s.Worker_Id, s.Direct, s.Other, r.Recording
FROM
(SELECT Worker_Id,
SUM(CASE sh_TopLevel_Defn WHEN 'Direct' THEN Hours ELSE 0 END) Direct,
SUM(CASE sh_TopLevel_Defn WHEN 'Indirect' THEN Hours ELSE 0 END) Indirect,
SUM(CASE sh_TopLevel_Defn WHEN 'Other' THEN Hours ELSE 0 END) Other
FROM vwRptXWorkerHoursSummary
WHERE Service_Date BETWEEN @StartDate AND @EndDate
AND Area_Pgm_ID = @AreaProgram
GROUP BY Worker_Id) s
JOIN
(SELECT Worker, SUM(Hours) Recording
FROM vwRecordingHoursWorker
WHERE [Date] BETWEEN @StartDate AND @EndDate
AND Area = @AreaProgram
GROUP BY Worker) r ON r.Worker = s.Worker_Id
--Jonathan
--Jonathan
October 29, 2003 at 10:12 am
Thanks - I managed to figure that out just after posting this issue.
Tim
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply