Hola lo que pasa que mi problema es con un store procedure a la hora de ejecutarlo en mi sql2005 sin problemas funciona y se crea, pero ahora quiero crear reportes en el reporting services, pero a la hora de ejecutar lo siguiente:
exec ACF_ReporteDetalleMensualDeControldeCitas
UnitId=1,ServiceId=1, FromDate='01/02/2009',ToDate='01/02/2009'
me marca el siguiente error:
There is an error in the query. Invalid object name '#temp2'.
abajo pego el código de mi store procedure ojala y alguién me pueda ayudar gracias.
Nota: por razones de spam que me marca el foro quite las Arrobas @...
USE [XXXX]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ACF_ReporteDetalleMensualDeControldeCitas]
UnitId INT,
ServiceId INT,
FromDate DATETIME ,
ToDate DATETIME
AS
BEGIN
SET NOCOUNT ON;
SET FromDate = [qf].[GetDatePart]( FromDate )
SET ToDate = [qf].[GetDatePart]( ToDate ) + 1
-- Obtenemos los siguientes campos
-- Módulo, Servicio, Fecha, Slots por día, Slots Ocupados en Agenda, Slots Reservados
SELECT qf.Calendar.CalendarId As CalendarId,
qf.Calendar.Date As CalendarDate,
qf.Calendar.BookedSlotsCount As BookedSlotsCount,
qf.Calendar.ReservedSlotsCount As ReservedSlotsCount ,
qf.Calendar.VacantSlotsCount As VacantSlotsCount,
qf.Unit.UnitId As UnitId,
qf.Unit.UnitName As UnitName,
qf.Service.ServiceName As ServiceName,
qf.Service.ServiceId As ServiceId
INTO #Temp1
FROM qf.Calendar INNER JOIN
qf.Service ON qf.Calendar.ServiceId = qf.Service.ServiceId INNER JOIN
qf.Unit ON qf.Service.UnitId = qf.Unit.UnitId
WHERE qf.Unit.UnitId = UnitId AND qf.Service.ServiceId= ServiceId
AND qf.Calendar.Date BETWEEN FromDate AND ToDate
ORDER BY qf.Calendar.Date
--- Duracion de Slots
SELECT qf.Calendar.CalendarId AS CalendarId,
qf.Calendar.Date AS CalendarDate,
--qf.CalendarSlot.Date as SlotDate,
qf.CalendarSlot.Duration AS Duration,
qf.Unit.UnitId AS UnitId,
qf.Unit.UnitName AS UnitName,
qf.Service.ServiceName AS ServiceName,
qf.Service.ServiceId AS ServiceId
INTO #temp2
FROM qf.Calendar INNER JOIN
qf.CalendarSlot ON qf.Calendar.CalendarId = qf.CalendarSlot.CalendarId INNER JOIN
qf.Service ON qf.Calendar.ServiceId = qf.Service.ServiceId INNER JOIN
qf.Unit ON qf.Service.UnitId = qf.Unit.UnitId
WHERE qf.Unit.UnitId = UnitId AND qf.Service.ServiceId = ServiceId
AND qf.Calendar.Date BETWEEN FromDate AND ToDate
SELECT DISTINCT CalendarDate,
AVG(Duration) AS DuracionSlot
INTO #temp3
FROM #temp2
GROUP BY CalendarDate
ORDER BY CalendarDate
-- Select final
SELECT t1.UnitName AS NombreUnidad,
t1.ServiceName AS NombredeServicio,
t1.CalendarDate AS Fecha,
SUM( t1.BookedSlotsCount + t1.ReservedSlotsCount + t1.VacantSlotsCount) AS SlotsOfertados,
t3.DuracionSlot As DuracionSlot,
t1.BookedSlotsCount AS SlotsOcupadosEnAgenda,
t1.ReservedSlotsCount AS SlotsReservados
FROM #Temp1 as t1 INNER JOIN #Temp3 as t3 ON t1.CalendarDate = t3.CalendarDate
GROUP BY t1.UnitName,
t1.ServiceName,
t1.CalendarDate,
t3.DuracionSlot,
t1.BookedSlotsCount ,
t1.ReservedSlotsCount
ORDER BY t1.CalendarDate
END
RETURN
GO
