RE:Resolucion de Tiempo en SQL
Hola Isaías:
antes que nada espero que te encuentres de maravilla...Bueno aqui anexo el codigo para que le des una checada.
Gracias.
--drOP PROCEDURE setSenal
--SELECT * FROM ##resultado SELECT * FROM ##senal SELECT * FROM ##fecha
--exec setSenal '1','A01U1040,A01S1025','05/12/2006 13:16:59','05/12/2006 16:00:00','6'
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE setSenal @centrales nvarchar (300), @senales nvarchar(4000),@fIni nvarchar (20), @fFin nvarchar (20), @Res int
AS
begin
BEGIN
Declare @sql1 nchar(4000)
Declare @fMin DateTime
DECLARE @perIni datetime
DECLARE @perFin datetime
DECLARE @signal nvarchar(8)
DECLARE @fechaX nvarchar(16)
DECLARE @sigval float
DECLARE @sqlOut nvarchar(1000)
DECLARE @message nvarchar(80)
DECLARE @s TABLE (Id_Senal nVarchar(10))
if Exists(Select name from tempdb..sysobjects where name like '##resultado') Drop Table ##resultado
if Exists(Select name from tempdb..sysobjects where name like '##senal') Drop Table ##senal
if Exists(Select name from tempdb..sysobjects where name like '##fecha') Drop Table ##fecha
CREATE TABLE ##senal(Id_Senal Varchar(10), Fecha_Registro DateTime, Valor Float)
CREATE TABLE ##fecha(desde datetime, hasta datetime)
SELECT @sql1 = 'CREATE TABLE ##resultado(Fecha_Registro DateTime,' +
Replace(@senales, ',' , ' nvarchar(40), ') + ' nvarchar(40))'
EXEC sp_executesql @sql1
SET @perIni = @fIni
SELECT @sql1 = 'Insert Into ##senal ' +
'SELECT a.Id_Senal, a.Fecha_Registro, a.Valor ' +
'FROM (Select va.Id_Senal, va.Fecha_Registro, CAST(va.Valor as Float) Valor '+
'From VALORES_ANALOGICOS va ' +
'Union ' +
'Select vb.Id_Senal, vb.Fecha_Registro, CAST(vb.Valor as Float) Valor ' +
'From VALORES_BINARIOS vb) a, VARIABLES_C01 v,TIPOS_DE_SISTEMAS c ' +
'WHERE v.Id_Senal = a.Id_Senal ' +
'AND v.No_Sistema = c.No_Sistema ' +
'AND (CAST(a.Fecha_Registro AS DATETIME) BETWEEN ''' + CAST(@fIni as nvarchar)+''' AND '''+CAST(@fFin as nvarchar)+ ''') ' +
'AND a.Id_Senal in ('''+rtrim(replace(@senales,',',''','''))+''') ' +
'AND c.No_Central IN ('''+rtrim(replace(@centrales,',',''','''))+''') '
EXEC sp_executesql @sql1
IF @Res =0
BEGIN
INSERT INTO ##fecha
SELECT Distinct Fecha_Registro desde, Fecha_Registro hasta
From ##senal
SELECT @sql1 = 'Insert Into ##senal ' +
'SELECT a.Id_Senal, DATEADD(minute, -1 * 720,''' + @fIni + ''') AS Fecha_Registro, -999999.99 As Valor '+
'FROM (Select Distinct x.Id_Senal ' +
'From (Select va.Id_Senal From VALORES_ANALOGICOS va ' +
'Union ' +
'Select vb.Id_Senal From VALORES_BINARIOS vb) x ' +
'Where x.Id_Senal in ('''+rtrim(replace(@senales,',',''','''))+''') '
END
ELSE
BEGIN
SELECT @sql1 = 'Insert Into ##senal ' +
'SELECT a.Id_Senal, DATEADD(minute, -1 * ' + cast(@Res As nvarchar)+ ',''' + @fIni + ''') AS Fecha_Registro, -999999.99 As Valor '+
'FROM (Select Distinct x.Id_Senal ' +
'From (Select va.Id_Senal From VALORES_ANALOGICOS va ' +
'Union ' +
'Select vb.Id_Senal From VALORES_BINARIOS vb) x ' +
'Where x.Id_Senal in ('''+rtrim(replace(@senales,',',''','''))+''') '
WHILE @perIni < @fFin
BEGIN
SET @perFin = dateadd(minute,@Res,@perIni)
INSERT ##fecha
SELECT @perIni, @perFin
SET @perIni = @perFin
END
END
--
DECLARE @cReg int
SELECT @cReg = Count(Distinct Id_Senal)
FROM ##senal
print @cReg
DECLARE @sqlIns nvarchar(4000)
DECLARE @desde datetime, @hasta datetime
DECLARE per_cursor CURSOR FOR
SELECT desde, hasta From ##fecha
OPEN per_cursor
FETCH NEXT FROM per_cursor
INTO @desde, @hasta
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sqlIns = 'Insert ##resultado Select ''' + CAST(@desde as nvarchar) + '''' + REPLICATE(', ''0''',@cReg)
EXEC sp_executesql @sqlIns
FETCH NEXT FROM per_cursor
INTO @desde, @hasta
END
CLOSE per_cursor
DEALLOCATE per_cursor
DECLARE sen_cursor CURSOR FOR
SELECT Id_Senal From ##senal
OPEN sen_cursor
-- Avanzamos un registro y cargamos en las variable el valor encontrado en el primer registro
FETCH NEXT FROM sen_cursor
INTO @signal
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE sen_res CURSOR FOR
SELECT Fecha_Registro From ##resultado
OPEN sen_res
FETCH NEXT FROM sen_res
INTO @fechaX
SELECT @message = 'Fecha '+@fechaX+' Senal '+@signal
print @message
WHILE @@FETCH_STATUS = 0
BEGIN
-- SELECT @sqlOut = 'Select @sigval = (select top 1 isnull( cast(C.Valor as float),0.0)) From ##senal C Where C.Id_Senal=''' + isnull(@signal,'') + '''and C.Fecha_Registro = '+
-- '(Select isnull(Max(C1.Fecha_Registro),getdate()) From ##senal C1 where C.Id_Senal = C1.Id_Senal and C.Fecha_Registro <= ''' + @fechaX + ''') union all select 1'
-- EXEC sp_executesql @sqlOut
Select @sigval = C.Valor
From ##senal C
Where C.Id_Senal=@signal and C.Fecha_Registro = (Select Max(C1.Fecha_Registro) From ##senal C1 where C.Id_Senal = C1.Id_Senal and C.Fecha_Registro <= @fechaX )
--print @sqlOut
SELECT @sqlIns = 'Update ##resultado SET ' + @signal + ' = ' + isnull(cast(@sigval as nvarchar(20)),0)+ ' where cast(Fecha_Registro as datetime) <= ''' + isnull(@fechaX,'0') + ''''
EXEC sp_executesql @sqlIns
FETCH NEXT FROM sen_res
INTO @fechaX
END
/*CLOSE sen_cursor
DEALLOCATE sen_cursor*/
CLOSE sen_res
DEALLOCATE sen_res
FETCH NEXT FROM sen_cursor
INTO @signal
END
CLOSE sen_cursor
DEALLOCATE sen_cursor
END
end