Hallo Olaf,
hier der code der sp. Was ist mit Rückgabe von nur einem Recordset gemeint ?
USE [DB_KAHAINE]
GO
/****** Object: StoredProcedure [dbo].[usp_Tageswerte] Script Date: 18.01.2016 09:03:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Frank ALbers
-- Create date: 2016-01-16
-- Description: Tageswerte aufbereiten
-- =============================================
CREATE PROCEDURE [dbo].[usp_Tageswerte]
-- Add the parameters for the stored procedure here
@Tag int = 1,
@Variablenname varchar(80) = 'ST%_Istwert%'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
/****************************************/
/****** Aufbereiten der Tageswerte ******/
/****************************************/
-- benutze temporäre tabellen für ablage der zwischen ergebnisse
--use tempdb;
--go
Declare @Test int = 1;
If @Test = 0
BEGIN
-- ist nur dummy um in view feldvariablen zu bekommen
SELECT CAST(tw.dt as date) as Datum,det.TagDescription AS Bezeichnung,
det.Units AS Dim ,
mima.MinValue,
mima.MinTime,
mima.MaxValue,
mima.MaxTime,
AVG(tw.Value) AS Mittelwert,
zwstd.[0-2], zwstd.[2-4], zwstd.[4-6], zwstd.[6-8], zwstd.[8-10], zwstd.[10-12], zwstd.[12-14],
zwstd.[12-14], zwstd.[14-16], zwstd.[16-18], zwstd.[18-20], zwstd.[20-22], zwstd.[22-24]
FROM #tmpMiMaTim AS mima
join DB_KAHAINE.dbo.Adr_DBLog_AgentDetails AS det --agent details
ON mima.Tagname = det.TagName
join #tmpDayValue AS tw -- day values
ON mima.Tagname = tw.Tagname
join #tmpZweiStd as zwstd -- two hour values
on mima.Tagname = zwstd.Tagname
GROUP BY CAST(tw.dt as date),det.TagDescription,mima.Tagname,
det.Units,
mima.MinValue,
mima.MinTime,
mima.MaxValue,
mima.MaxTime,
zwstd.[0-2], zwstd.[2-4], zwstd.[4-6], zwstd.[6-8], zwstd.[8-10], zwstd.[10-12], zwstd.[12-14],
zwstd.[12-14], zwstd.[14-16], zwstd.[16-18], zwstd.[18-20], zwstd.[20-22], zwstd.[22-24]
ORDER BY det.TagDescription ;
END
--GO
-- zu beginn temp tabellen löschen, wenn vorhanden
-- temp tabelle für tageswerte
IF OBJECT_ID (N'#tmpDayValue',N'U') IS NOT NULL
DROP TABLE #tmpDayValue;
print 'TagesWerte Tabelle glöscht';
--go
-- temp tabelle für min-max-zeit tageswerte
IF OBJECT_ID (N'#tmpMiMaTim',N'U') IS NOT NULL
DROP TABLE #tmpMiMaTim;
print 'MiMaTim Tabelle glöscht';
--go
IF OBJECT_ID (N'#tmpZweiStd',N'U') IS NOT NULL
DROP TABLE #tmpZweiStd;
print 'ZweiStd Tabelle glöscht';
--go
-- Abfrage Datum-Tag zum suchen im script, ist nur für entwicklung und test
/*
declare @Tag as int = 9
declare @Variablenname as varchar(80) = 'ST%_Istwert%'
*/
print 'Suche nach ' + @variablenname;
-- werte suchen und mit stdunden nummer in temp tabelle speichern
SELECT id,dt,Tagname, Value ,
case -- stundennummer filtern
when (datepart(hour,dt) < 2) then 1
when (datepart(hour,dt) < 4) then 2
when (datepart(hour,dt) < 6) then 3
when (datepart(hour,dt) < 8) then 4
when (datepart(hour,dt) < 10) then 5
when (datepart(hour,dt) < 12) then 6
when (datepart(hour,dt) < 14) then 7
when (datepart(hour,dt) < 16) then 8
when (datepart(hour,dt) < 18) then 9
when (datepart(hour,dt) < 20) then 10
when (datepart(hour,dt) < 22) then 11
when (datepart(hour,dt) < 24) then 12
end as StdNum
into #tmpDayValue
FROM [DB_KAHAINE].[dbo].[Messwerte]
where Tagname like @Variablenname and DATEPART(day,dt) = @Tag ;
print 'TagesWerte Tabelle gefüllt';
--go
-- Suche zu jedem Wert den Min mit Zeit und Max mit Zeit
WITH
Query AS
(
SELECT Tagname,
MIN( Value ) AS MinValue,
MAX( Value ) AS MaxValue
FROM #tmpDayValue
GROUP BY Tagname
),
MinValuesQuery AS
(
SELECT m.Tagname,
q.MinValue,
MIN( dt ) AS MinDt
FROM #tmpDayValue m
INNER JOIN Query q ON m.Tagname = q.Tagname AND m.Value = q.MinValue
GROUP BY m.Tagname,
q.MinValue
),
MaxValuesQuery AS
(
SELECT m.Tagname,
q.MaxValue,
Min( dt ) AS MaxDt
FROM #tmpDayValue m
INNER JOIN Query q ON m.Tagname = q.Tagname AND m.Value = q.MaxValue
GROUP BY m.Tagname,
q.MaxValue
)
SELECT minvq.Tagname,
minvq.MinValue,
Cast(minvq.MinDt as time(0) ) as MinTime,
maxvq.MaxValue,
Cast(maxvq.MaxDt as time(0) ) as MaxTime
INTO #tmpMiMaTim -- temporäre tabelle
FROM MinValuesQuery minvq
CROSS JOIN MaxValuesQuery maxvq
WHERE minvq.Tagname = maxvq.Tagname
ORDER BY Tagname ;
--go
print 'Min-Max-Zeit Werte';
--go
-- zwei Stunden Werte als tabelle ausgeben // Überschriften sind fix als Spaltennamen enthalten
SELECT Tagname ,
[1] AS '0-2' ,[2] AS '2-4',[3] AS '4-6',[4] AS '6-8',[5] AS '8-10',[6] AS '10-12',
[7] AS '12-14',[8] AS '14-16',[9] AS '16-18' ,[10] AS '18-20',[11] AS '20-22',[12] AS '22-24'
into #tmpZweiStd
FROM
(SELECT Tagname, Value, StdNum
FROM #tmpDayValue) AS p
PIVOT
(
AVG(Value)
FOR StdNum IN
( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] )
) AS pvt
ORDER BY Tagname;
--go
print 'Zwei Stunden Werte';
--go
-- abfrage mit Details aus agent details
SELECT CAST(tw.dt as date) as Datum,det.TagDescription AS Bezeichnung,
det.Units AS Dim ,
mima.MinValue,
mima.MinTime,
mima.MaxValue,
mima.MaxTime,
AVG(tw.Value) AS Mittelwert,
zwstd.[0-2], zwstd.[2-4], zwstd.[4-6], zwstd.[6-8], zwstd.[8-10], zwstd.[10-12], zwstd.[12-14],
zwstd.[12-14], zwstd.[14-16], zwstd.[16-18], zwstd.[18-20], zwstd.[20-22], zwstd.[22-24]
FROM #tmpMiMaTim AS mima
join DB_KAHAINE.dbo.Adr_DBLog_AgentDetails AS det --agent details
ON mima.Tagname = det.TagName
join #tmpDayValue AS tw -- day values
ON mima.Tagname = tw.Tagname
join #tmpZweiStd as zwstd -- two hour values
on mima.Tagname = zwstd.Tagname
GROUP BY CAST(tw.dt as date),det.TagDescription,mima.Tagname,
det.Units,
mima.MinValue,
mima.MinTime,
mima.MaxValue,
mima.MaxTime,
zwstd.[0-2], zwstd.[2-4], zwstd.[4-6], zwstd.[6-8], zwstd.[8-10], zwstd.[10-12], zwstd.[12-14],
zwstd.[12-14], zwstd.[14-16], zwstd.[16-18], zwstd.[18-20], zwstd.[20-22], zwstd.[22-24]
ORDER BY det.TagDescription ;
--go
END
GO
Gruß
Frank
SSRS Dataset aus SP keine Feldliste
