December 17, 2015 at 10:21 pm
I am trying to select data from this table
CREATE TABLE [dbo].[tb_value](
[NameID] [tinyint] NOT NULL,
[Times] [datetime] NOT NULL,
[Value] [real] NOT NULL,
CONSTRAINT [PK_tb_value] PRIMARY KEY CLUSTERED
(
[NameID] ASC,
[Times] ASC
)
) ON [PRIMARY];
like this
WITH
te_basis AS
(
SELECT NameID,Times,Value,ROW_NUMBER() OVER (PARTITION BY tb_value.NameID ORDER BY Times) r
FROM tb_value
WHERE NameID = 21
ORDER BY Times DESC
OFFSET (0) ROWS
FETCH NEXT (11) ROWS ONLY
),
te_quote_1 AS
(
SELECT NameID,Times,Value,ROW_NUMBER() OVER (PARTITION BY tb_value.NameID ORDER BY Times) r
FROM tb_value
WHERE NameID = 31
ORDER BY Times DESC
OFFSET (0) ROWS
FETCH NEXT (11) ROWS ONLY
)
SELECT
(
SELECT DATEDIFF(MINUTE,a.Times,ISNULL(b.Times,a.Times)) AS basis,a.Value - ISNULL(b.Value,0) AS delta
FROM te_basis a JOIN te_basis b ON a.r+1 = b.r
ORDER BY a.Times DESC
OFFSET (1) ROWS
FETCH NEXT (10) ROWS ONLY
),
(
SELECT DATEDIFF(MINUTE,c.Times,ISNULL(d.Times,c.Times))
FROM te_quote_1 c JOIN te_quote_1 d ON c.r+1 = d.r
ORDER BY c.Times DESC
OFFSET (1) ROWS
FETCH NEXT (10) ROWS ONLY
);
however I keep getting the error
"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
I have not used this predicate before but my understanding is it returns one row, indiscriminately.
I need to insert all rows that meet my conditions not just one row.
However, this seems to work but it selects for one column only.
WITH
te_basis AS
(
SELECT NameID,Times,Value,ROW_NUMBER() OVER (PARTITION BY tb_value.NameID ORDER BY Times) r
FROM tb_value
WHERE NameID = 31
ORDER BY Times DESC
OFFSET (0) ROWS
FETCH NEXT (11) ROWS ONLY
)
--SELECT
--(
SELECT DATEDIFF(MINUTE,a.Times,ISNULL(b.Times,a.Times)) AS basis,a.Value - ISNULL(b.Value,0) AS delta
FROM te_basis a JOIN te_basis b ON a.r+1 = b.r
ORDER BY a.Times DESC
OFFSET (1) ROWS
FETCH NEXT (10) ROWS ONLY
--)
How can I go about this?
December 18, 2015 at 2:45 am
I'm not sure about what output is desired. Anyway 2014 has LED/LAG. So most probably you needn't ROW_NUMBERing CTEs here at all.
December 29, 2015 at 3:09 am
Please share sample data and desired output
December 29, 2015 at 8:08 am
I was going to provide an alternative using LAG, but I didn't want to spend much time testing and guessing the exact requirements. Especially with those offsets.
WITH
te_basis AS
(
SELECT NameID,Times,Value,ROW_NUMBER() OVER (PARTITION BY tb_value.NameID ORDER BY Times) r
FROM tb_value
WHERE NameID = 21
ORDER BY Times DESC
OFFSET (0) ROWS
FETCH NEXT (11) ROWS ONLY
),
te_quote_1 AS
(
SELECT NameID,Times,Value,ROW_NUMBER() OVER (PARTITION BY tb_value.NameID ORDER BY Times) r
FROM tb_value
WHERE NameID = 31
ORDER BY Times DESC
OFFSET (0) ROWS
FETCH NEXT (11) ROWS ONLY
),
times_basis AS
(
SELECT DATEDIFF(MINUTE,a.Times,ISNULL(b.Times,a.Times)) AS basis,
a.Value - ISNULL(b.Value,0) AS delta,
a.r
FROM te_basis a
JOIN te_basis b ON a.r+1 = b.r
ORDER BY a.Times DESC
OFFSET (1) ROWS
FETCH NEXT (10) ROWS ONLY
),
times_quotes AS
(
SELECT DATEDIFF(MINUTE,c.Times,ISNULL(d.Times,c.Times)) quote,
c.r
FROM te_quote_1 c
JOIN te_quote_1 d ON c.r+1 = d.r
ORDER BY c.Times DESC
OFFSET (1) ROWS
FETCH NEXT (10) ROWS ONLY
)
SELECT b.basis, b.delta, q.quote
FROM times_basis b
JOIN times_quotes q ON b.r = q.r;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply