July 16, 2012 at 1:53 am
Hi,
I have a table of type name value:
IF OBJECT_ID('tempdb..#Parameters') IS NOT NULL
DROP TABLE #Parameters;
CREATE TABLE #Parameters
(
ParameterID NVARCHAR(8) NOT NULL,
ParameterValue SQL_VARIANT NOT NULL,
ParameterTimestamp DATETIME NOT NULL
);
The data in table looks like:
INSERT INTO #Parameters ( ParameterID, ParameterValue, ParameterTimestamp)
VALUES ( N'A1', 20, '2012-07-16 14:00:00');
INSERT INTO #Parameters ( ParameterID, ParameterValue, ParameterTimestamp)
VALUES ( N'B22', 33, '2012-07-16 15:00:00');
INSERT INTO #Parameters ( ParameterID, ParameterValue, ParameterTimestamp)
VALUES ( N'C24', 40, '2012-07-16 16:00:00');
INSERT INTO #Parameters ( ParameterID, ParameterValue, ParameterTimestamp)
VALUES ( N'A1', 31, '2012-07-16 17:00:00');
INSERT INTO #Parameters ( ParameterID, ParameterValue, ParameterTimestamp)
VALUES ( N'C24', 50, '2012-07-16 17:00:00');
I need a query that propagates the latests value of a ParameterID to the next timestamps that does not have values for this parameter.
The output of the query for the above example should be:
SELECTN'A1' AS ParameterID, 20 AS ParameterValue, '2012-07-16 14:00:00' AS ParameterTimestamp
UNION
SELECTN'B22', 33, '2012-07-16 15:00:00'
UNION
SELECTN'A1', 20, '2012-07-16 15:00:00'
UNION ---
SELECTN'A1', 20, '2012-07-16 16:00:00'
UNION
SELECTN'B22', 33, '2012-07-16 16:00:00'
UNION
SELECTN'C24', 40, '2012-07-16 16:00:00'
UNION ---
SELECTN'A1', 31, '2012-07-16 17:00:00'
UNION
SELECTN'B22', 33, '2012-07-16 17:00:00'
UNION
SELECTN'C24', 50, '2012-07-16 17:00:00'
ORDER BY ParameterTimestamp
For example, for '2012-07-16 15:00:00.000' timestamp paramter "A1" does not have value so it should get from previous timestamp that contains this parameter. In this case from '2012-07-16 14:00:00.000' that has value 33.
For timestamp '2012-07-16 16:00:00' there aren't values for "B22" and "A1" so the values for this parameters should be taken from the previous timestamps: "B22" - 33 and "A1" - 20.
Thanks,
ioani
July 16, 2012 at 5:09 am
SELECT TOP 1 * FROM #Parameters
WHERE
ParameterTimestamp <= '2012-07-16 15:00:00' -- timestamp
AND ParameterID='A1' -- parameterId
ORDER BY ParameterTimestamp DESC
July 16, 2012 at 6:58 am
It is not what I need.
July 16, 2012 at 10:05 am
There must be a better way...
select distinct b.ParameterID,b.ParameterValue,a.ParameterTimestamp
From #Parameters a
Cross apply (select distinct ParameterId from #Parameters) c
Outer Apply (select top 1 * from #Parameters
where ParameterTimestamp <= a.ParameterTimestamp
and ParameterID =c.ParameterID
order by ParameterTimestamp desc) b
where b.ParameterID is not null
order by a.ParameterTimestamp
July 16, 2012 at 11:55 am
Daxesh Patel (7/16/2012)
There must be a better way...
select distinct b.ParameterID,b.ParameterValue,a.ParameterTimestamp
From #Parameters a
Cross apply (select distinct ParameterId from #Parameters) c
Outer Apply (select top 1 * from #Parameters
where ParameterTimestamp <= a.ParameterTimestamp
and ParameterID =c.ParameterID
order by ParameterTimestamp desc) b
where b.ParameterID is not null
order by a.ParameterTimestamp
;WITH cte (rownum, parameterid, parametervalue, parametertimestamp)
AS(
select RANK() OVER(PARTITION BY ParameterID ORDER BY ParameterTimestamp DESC) AS rownum, parameterid, parametervalue, parametertimestamp
From #Parameters
)
SELECT parameterid, parametervalue, parametertimestamp
FROM cte
WHERE rownum = 1
Jared
CE - Microsoft
July 16, 2012 at 11:41 pm
Like this?
DECLARE @MinDate DATETIME
,@MaxDate DATETIME
,@Diff INT
;
SELECT @MinDate = MIN (pms.ParameterTimestamp)
,@MaxDate = MAX (pms.ParameterTimestamp)
FROM #Parameters pms
;
SELECT @Diff = DATEDIFF(HOUR,@MinDate,@MaxDate);
--SELECT @Diff ;
; WITH Numbers AS
(
SELECT TOP 100 N = (ROW_NUMBER() OVER(ORDER BY (SELECT 0))) -1
FROM sys.columns sc1
CROSS JOIN sys.columns sc2
)
, Dates AS
(
SELECT TimeStmp = DATEADD(HH,-1 * N.N, @MaxDate)
, N
FROM Numbers N
WHERE N.N <= @Diff
)
, RowNumbered AS
(
SELECT pms.ParameterID
,pms.ParameterTimestamp
,pms.ParameterValue
,RN_Min = ROW_NUMBER() OVER (PARTITION BY pms.ParameterID ORDER BY pms.ParameterTimestamp ASC)
,RN_Max = ROW_NUMBER() OVER (PARTITION BY pms.ParameterID ORDER BY pms.ParameterTimestamp DESC)
FROM #Parameters pms
)
, MinMax AS
(
SELECT RN_Otr.ParameterID
,MinVal = RN_Otr.ParameterValue
,MaxVal = CrsApp.ParameterValue
,MinTS = RN_Otr.ParameterTimestamp
,MaxTS = CrsApp.ParameterTimestamp
FROM RowNumbered RN_Otr
CROSS APPLY
(
SELECT RN_Inr.ParameterValue
,RN_Inr.ParameterTimestamp
FROM RowNumbered RN_Inr
WHERE RN_Inr.ParameterID = RN_Otr.ParameterID
AND RN_Inr.RN_Max = 1
) CrsApp
WHERE RN_Otr.RN_Min = 1
)
SELECT MM.ParameterID
,ParameterValue = ISNULL( prms.ParameterValue, MM.MinVal)
,OtrApp.TimeStmp
FROM MinMax MM
OUTER APPLY
(
SELECT *
FROM Dates Dts
WHERE Dts.TimeStmp >= MM.MinTS
AND Dts.TimeStmp <= @MaxDate
) OtrApp
LEFT JOIN #Parameters prms
ON prms.ParameterID = MM.ParameterID
AND prms.ParameterTimestamp = OtrApp.TimeStmp
ORDER BY MM.ParameterID , OtrApp.TimeStmp
;
July 17, 2012 at 3:27 am
Thanks for the solutions.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply