T-SQL скриптUSE [ASKU]
GO
/****** Object: StoredProcedure [dbo].[GetAirValuesTwo] Script Date: 11/01/2011 10:38:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetAirValuesTwo] (@Date DATETIME, @GroupId INT)
AS
BEGIN
DECLARE @NAMES NVARCHAR(max);
DECLARE @T_NAMES NVARCHAR(max);
DECLARE @SQL NVARCHAR(max);
DECLARE @CNT INT;
DECLARE @STP INT;
SET @GroupId = 12001 --Потом удалить
IF DATEPART(YEAR,GETDATE()) = DATEPART(YEAR,@Date)
BEGIN
IF DATEPART(MONTH,GETDATE()) = DATEPART(MONTH,@Date)
BEGIN
IF DATEPART(DAY,GETDATE()) = DATEPART(DAY,@Date)
BEGIN
SET @CNT = DATEPART(HOUR,GETDATE()) - 1
END
ELSE
BEGIN
SET @CNT = 23
END
END
ELSE
BEGIN
SET @CNT = 23
END
END
ELSE
BEGIN
SET @CNT = 23
END
SET @STP = @CNT
WHILE @CNT >= 0
BEGIN
IF @CNT = @STP
BEGIN
SET @NAMES = '[' + CONVERT(NVARCHAR,@CNT) + ']'
SET @T_NAMES = '[' + CONVERT(NVARCHAR,@CNT) + '] AS [' + CONVERT(NVARCHAR,@CNT) + ' ч.]'
END
IF @CNT < @STP
BEGIN
SET @NAMES = '[' + CONVERT(NVARCHAR,@CNT) + '],' + @NAMES
SET @T_NAMES = '[' + CONVERT(NVARCHAR,@CNT) + '] AS [' + CONVERT(NVARCHAR,@CNT) + ' ч.],' + @T_NAMES
END
SET @CNT = @CNT - 1
END
SELECT @SQL =
'
WITH SELRES ([OBJ_PARAMETER_ID], [NAME], [VALUE], [TIMESTAMP]) AS
(
SELECT par.[OBJ_SKAGNAME_ID], par.[NAME] + '''+', '+ '''+lu.[NAME], val.[VALUE], val.[TIMESTAMP]
FROM [dbo].[OBJ_SKAGNAME] par
INNER JOIN [dbo].[LINK_PARGROUP] gr
ON gr.[OBJ_PARAMETER_ID]=par.[OBJ_SKAGNAME_ID]
INNER JOIN [dbo].[OBJ_PARAMETER] o_par
ON par.[OBJ_SKAGNAME_ID] = o_par.[OBJ_PARAMETER_ID]
INNER JOIN [dbo].[LIST_UNIT] lu
ON o_par.[LIST_UNIT_ID] = lu.[LIST_UNIT_ID]
INNER JOIN [dbo].[OBJ_GROUP] ogr
ON ogr.[OBJ_GROUP_ID]=gr.[OBJ_GROUP_ID]
INNER JOIN [dbo].[OBJ_PARAMETER_VALUE] val
ON val.OBJ_PARAMETER_ID = par.OBJ_SKAGNAME_ID
WHERE gr.[OBJ_GROUP_ID] = CONVERT(INT,@GroupId)
AND val.[TIMESTAMP] >= @Date AND val.[TIMESTAMP] < DATEADD(DAY,1,@Date)
)
SELECT
[NAME] AS [Параметр], [OBJ_PARAMETER_ID]
,'+@T_NAMES+'
FROM (SELECT [NAME], [OBJ_PARAMETER_ID], [VALUE], x = cast(datepart(hour,[TIMESTAMP]) as varchar) from SELRES) T
PIVOT (
AVG ( VALUE )
FOR [x] IN ('+@NAMES+')
) PVT
'
--PRINT @SQL
EXEC sp_executesql @SQL, N'@Date datetime, @GroupId int', @Date, @GroupId
END