December 22, 2015 at 12:29 pm
Hello,
I am able to get various totals summed up from varying row numbers of this table
CREATE TABLE [dbo].[tb_a]([cl_t][datetime] NOT NULL ,[cl_a][decimal](18,5) NOT NULL) ON [PRIMARY]
like this
SELECT [cl_t], SUM([cl_a]) OVER (ORDER BY [cl_t] DESC ROWS BETWEEN UNBOUNDED PROCEEDING AND CURRENT ROW) AS sums
FROM [tb_a] ORDER BY [cl_t] DESC OFFSET 1 ROWS FETCH NEXT 10 ROWS ONLY
My question is how do I get this set of results, for each row in the table tb_a?
December 22, 2015 at 12:38 pm
Hi...think some scripts / sample data and expected results will help please.
see here for good practice
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
December 22, 2015 at 1:06 pm
stevennjuki (12/22/2015)
Hello,I am able to get various totals summed up from varying row numbers of this table
CREATE TABLE [dbo].[tb_a]([cl_t][datetime] NOT NULL ,[cl_a][decimal](18,5) NOT NULL) ON [PRIMARY]
like this
SELECT [cl_t], SUM([cl_a]) OVER (ORDER BY [cl_t] DESC ROWS BETWEEN UNBOUNDED PROCEEDING AND CURRENT ROW) AS sums
FROM [tb_a] ORDER BY [cl_t] DESC OFFSET 1 ROWS FETCH NEXT 10 ROWS ONLY
My question is how do I get this set of results, for each row in the table tb_a?
Quick question, can you formalize the requirements/problem in a manner that makes it easier to respond to, given what you've already posted can only provoke speculations.
😎
December 22, 2015 at 1:17 pm
Perhaps this is too obvious, but does removing "FETCH NEXT 10 ROWS ONLY" give you the results that you want?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 22, 2015 at 1:18 pm
Okay here is an example
The column cl_a has values (1,2,3,4,5,6)
And for simplicity let's say the total rows to be fetched when computing the sums is only 3 and not 10 as indicated above.
The sums returned by code above for the very first row that has the value 1 will be (1,1+2,1+2+3) which comes to (1,3,6)
Now this I can get by focusing on only one row. My question is how do I return these values for all rows?
Again for example the sums for row 2, if we are fetching from only 3 rows, will be (2,2+3,2+3+4) which will come to (2,5,9)
December 22, 2015 at 1:22 pm
Hi
Well the 10 rows constraint is supposed to help in 'batching', i.e. We focus on the sums of up to 10 rows for each row in the table. But let me look at it again any way. Thx
December 22, 2015 at 2:46 pm
stevennjuki (12/22/2015)
Okay here is an exampleThe column cl_a has values (1,2,3,4,5,6)
And for simplicity let's say the total rows to be fetched when computing the sums is only 3 and not 10 as indicated above.
The sums returned by code above for the very first row that has the value 1 will be (1,1+2,1+2+3) which comes to (1,3,6)
Now this I can get by focusing on only one row. My question is how do I return these values for all rows?
Again for example the sums for row 2, if we are fetching from only 3 rows, will be (2,2+3,2+3+4) which will come to (2,5,9)
Please provide sample data and expected results as has already been requested. That does much more than trying to explain it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 22, 2015 at 4:33 pm
J Livingston SQL (12/22/2015)
Hi...think some scripts / sample data and expected results will help please.see here for good practice
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
Just seen your link!! Will reformat. Thanks.
December 22, 2015 at 11:03 pm
drew.allen (12/22/2015)
Please provide sample data and expected results as has already been requested. That does much more than trying to explain it.
Drew
USE [tempdb]
GO
IF OBJECT_ID('[tempdb].dbo.tb_a', 'U') IS NOT NULL DROP TABLE tb_a
GO
CREATE TABLE [dbo].[tb_a]([cl_t][datetime] NOT NULL,[cl_a] [decimal](18, 0) NOT NULL) ON [PRIMARY]
GO
DELETE FROM [dbo].[tb_a]
INSERT INTO [dbo].[tb_a] ([cl_t],[cl_a]) VALUES('2015-01-01',1)
INSERT INTO [dbo].[tb_a] ([cl_t],[cl_a]) VALUES('2015-01-02',2)
INSERT INTO [dbo].[tb_a] ([cl_t],[cl_a]) VALUES('2015-01-03',3)
INSERT INTO [dbo].[tb_a] ([cl_t],[cl_a]) VALUES('2015-01-04',4)
INSERT INTO [dbo].[tb_a] ([cl_t],[cl_a]) VALUES('2015-01-05',5)
INSERT INTO [dbo].[tb_a] ([cl_t],[cl_a]) VALUES('2015-01-08',6)
INSERT INTO [dbo].[tb_a] ([cl_t],[cl_a]) VALUES('2015-01-09',7)
INSERT INTO [dbo].[tb_a] ([cl_t],[cl_a]) VALUES('2015-01-10',8)
INSERT INTO [dbo].[tb_a] ([cl_t],[cl_a]) VALUES('2015-01-11',9)
INSERT INTO [dbo].[tb_a] ([cl_t],[cl_a]) VALUES('2015-01-12',10)
INSERT INTO [dbo].[tb_a] ([cl_t],[cl_a]) VALUES('2015-01-15',11)
INSERT INTO [dbo].[tb_a] ([cl_t],[cl_a]) VALUES('2015-01-16',12)
INSERT INTO [dbo].[tb_a] ([cl_t],[cl_a]) VALUES('2015-01-17',13)
INSERT INTO [dbo].[tb_a] ([cl_t],[cl_a]) VALUES('2015-01-18',14)
SELECT x.[cl_t] AS times,x.a AS sums
FROM
(
SELECT y.[cl_t],SUM(y.[cl_a]) OVER(ORDER BY y.[cl_t] DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS a
FROM
(
SELECT *
FROM [dbo].[tb_a]
ORDER BY [cl_t] DESC
OFFSET (0) ROWS -- I would like to vary this offset, for each row
)
AS y
)
AS x
ORDER BY x.[cl_t] DESC
OFFSET 0 ROWS
FETCH NEXT (3) ROWS ONLY
This yields
+-------------------------+----+
| 2015-01-18 00:00:00.000 | 14 |
| 2015-01-17 00:00:00.000 | 27 |
| 2015-01-16 00:00:00.000 | 39 |
+-------------------------+----+
However I would like to receive
+-------------------------+----+
| 2015-01-18 00:00:00.000 | 14 |
| 2015-01-18 00:00:00.000 | 27 |
| 2015-01-18 00:00:00.000 | 39 |
| 2015-01-17 00:00:00.000 | 13 |
| 2015-01-17 00:00:00.000 | 25 |
| 2015-01-17 00:00:00.000 | 36 |
| 2015-01-16 00:00:00.000 | 12 |
| 2015-01-16 00:00:00.000 | 23 |
| 2015-01-16 00:00:00.000 | 33 |
+-------------------------+----+
December 23, 2015 at 12:04 am
Well its not about the OFFSET what you have shown in your desired output u need to get CurrentRow and two previous rows data for each 3 dates running total with the partition of these 3 dates.
What i didn't get is this statement. what are you looking for here ?
OFFSET (0) ROWS -- I would like to vary this offset, for each row
December 23, 2015 at 12:28 am
twin.devil (12/23/2015)
Well its not about the OFFSET what you have shown in your desired output u need to get CurrentRow and two previous rows data for each 3 dates running total with the partition of these 3 dates.What i didn't get is this statement. what are you looking for here ?
OFFSET (0) ROWS -- I would like to vary this offset, for each row
Well as I increase that offset parameter I get the the next set of rows. (3 because I am fetching the next 3 rows). For example if it is 1, I get 13,25,36 if it is 2 I get 12,22,33. These values are what I would like to be returned in one select statement.
December 23, 2015 at 12:37 am
Not very dynamic, but here's an attempt. How many rows do you expect for the lowest two dates?
SELECT
ca.n, t.cl_t, t.cl_a,
CASE ca.n
WHEN 1 THEN t.cl_a
WHEN 2 THEN SUM(t.cl_a) OVER (PARTITION BY ca.n ORDER BY t.cl_t ROWS 1 PRECEDING)
WHEN 3 THEN SUM(t.cl_a) OVER (PARTITION BY ca.n ORDER BY t.cl_t ROWS 2 PRECEDING)
END a
FROM
dbo.tb_a t
CROSS APPLY
(
VALUES (1), (2), (3)
) ca(n)
ORDER BY
t.cl_t DESC, ca.n
December 23, 2015 at 3:43 am
Peter Brinkhaus (12/23/2015)
Not very dynamic, but here's an attempt. How many rows do you expect for the lowest two dates?
SELECT
ca.n, t.cl_t, t.cl_a,
CASE ca.n
WHEN 1 THEN t.cl_a
WHEN 2 THEN SUM(t.cl_a) OVER (PARTITION BY ca.n ORDER BY t.cl_t ROWS 1 PRECEDING)
WHEN 3 THEN SUM(t.cl_a) OVER (PARTITION BY ca.n ORDER BY t.cl_t ROWS 2 PRECEDING)
END a
FROM
dbo.tb_a t
CROSS APPLY
(
VALUES (1), (2), (3)
) ca(n)
ORDER BY
t.cl_t DESC, ca.n
Hi,
The number of rows fetched is to be determined by parameter!
December 23, 2015 at 5:07 am
if you mean that number of rows to be fetched of a single date. it would look something like this. You can give a parameter how many rows should be lookup based on the parameter.
Declare @Rows as int = 3
;
Select m.cl_t, xx.cl_t, xx.cl_a
, sum(xx.cl_a) OVER (Partition by m.cl_t Order By m.cl_t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as RunningTotal
from [dbo].[tb_a] m
cross apply
(
Select TOP (@Rows)
a.cl_t
, a.[cl_a]
from [dbo].[tb_a] a
where a.cl_t <= m.[cl_t]
order by a.cl_t DESC
) xx
Order by m.cl_t desc
December 23, 2015 at 5:17 am
stevennjuki (12/23/2015)
...Hi,
The number of rows fetched is to be determined by parameter!
Ok, here's a more dynamic version:
DECLARE @NumberOfRows INT = 4; -- Must be between 1 and 100
WITH Numbers AS -- Gegenerate numbers 1 to 100
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) n
FROM
( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) x1(d)
CROSS JOIN
( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) x2(d)
),
Temp AS -- Compute @NumberRows current and preceding values an using Numbers
(
SELECT
nb.n, t.cl_t, t.cl_a,
LAG(t.cl_a, nb.n - 1) OVER (PARTITION BY nb.n ORDER BY t.cl_t) an
FROM
dbo.tb_a t
CROSS JOIN
Numbers nb
WHERE
nb.n <= @NumberOfRows
)
SELECT -- accumulate an per date
p.n, p.cl_t, p.cl_a, p.an,
SUM(p.an) OVER (PARTITION BY p.cl_t ORDER BY p.n ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) a
FROM
Temp p
-- WHERE -- filter out empty preceding values
-- p.an IS NOT NULL
ORDER BY
p.cl_t DESC, p.n
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply