Styjun

Find values of x so that the matrix is invertible

Does Google Maps take into account hills/inclines for route times?

What would be the ideal melee weapon made of "Phase Metal"?

Military Weapon System

As a DM, how to avoid unconscious metagaming when dealing with a high AC character?

What are some symbols representing peasants/oppressed persons fighting back?

In which ways do anagamis still experience ignorance?

does ability to impeach an expert witness on science or scholarship go too far?

QGIS Linestring rendering curves between vertex

CPU overheating in Ubuntu 18.04

how can draw a kiviat diagram?

Modeling, view and projection transformation using vector and point in homogenous form

Why did my rum cake turn black?

Ambiguous sentences: How to tell when they need fixing?

nginx serves wrong domain site. It doenst shows default site if no configuration applies

How does one stock fund's charge of 1% more in operating expenses than another fund lower expected returns by 10%?

Is purchasing foreign currency before going abroad a losing proposition?

TikZ Can I draw an arrow by specifying the initial point, direction, and length?

What is temperature on a quantum level?

What is this welding tool I found in my attic?

To accent or not to accent in Greek

How might the United Kingdom become a republic?

Create dashed intersections with labels using pgfplots and tikz

Are lithium batteries allowed in the International Space Station?

t-sql function like “filter” for sum(x) filter(condition) over (partition by

Function vs. Stored Procedure in SQL ServerSQL Server: Difference between PARTITION BY and GROUP BYSum across partitions with window functionsWindow function issue - max over partitionPostgreSQL window function: row_number() over (partition col order by col2)Dynamic row range when calculating moving sum/average using window functions (SQL Server)SQL Server 2008 version of OVER(… Rows Unbounded Preceding)T-SQL- Sum over Partition by in combination with group bySQL over clause PARTITION BYMax() Over Partition By with date conditions

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I'm trying to sum a window with a filter. I saw something similar to
sum(x) filter(condition) over (partition by...)
but it does not seem to work in t-sql, SQL Server 2017.

Essentially, I want to sum the last 5 rows that have a condition on another column.

I've tried
sum(case when condition...) over (partition...)
and sum(cast(nullif(x))) over (partition...).

I've tried left joining the table with a where condition to filter out the condition.

All of the above will add the last 5 from the starting point of the current row with the condition.

What I want is from the current row. Add the last 5 values above that meet a condition.

Date| Value | Condition | Result
1-1 10 1 
1-2 11 1 
1-3 12 1
1-4 13 1
1-5 14 0
1-6 15 1
1-7 16 0
1-8 17 0 sum(15+13+12+11+10)
1-9 18 1 sum(18+15+13+12+11)
1-10 19 1 sum(19+18+15+13+12)

In the above example the condition I would want would be 1, ignoring the 0 but still having the "window" size be 5 non-0 values.

tsql window-functions

share|improve this question

asked Mar 26 at 5:55

ifeelstupidifeelstupid

2511 silver badge44 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is date column, is it date or values which you have given?
  
  – PSK
  Mar 26 at 6:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The date column is a date.
  
  – ifeelstupid
  Mar 26 at 6:11
  

  
  
  
  

add a comment | 

0

I'm trying to sum a window with a filter. I saw something similar to
sum(x) filter(condition) over (partition by...)
but it does not seem to work in t-sql, SQL Server 2017.

Essentially, I want to sum the last 5 rows that have a condition on another column.

I've tried
sum(case when condition...) over (partition...)
and sum(cast(nullif(x))) over (partition...).

I've tried left joining the table with a where condition to filter out the condition.

All of the above will add the last 5 from the starting point of the current row with the condition.

What I want is from the current row. Add the last 5 values above that meet a condition.

Date| Value | Condition | Result
1-1 10 1 
1-2 11 1 
1-3 12 1
1-4 13 1
1-5 14 0
1-6 15 1
1-7 16 0
1-8 17 0 sum(15+13+12+11+10)
1-9 18 1 sum(18+15+13+12+11)
1-10 19 1 sum(19+18+15+13+12)

In the above example the condition I would want would be 1, ignoring the 0 but still having the "window" size be 5 non-0 values.

tsql window-functions

share|improve this question

asked Mar 26 at 5:55

ifeelstupidifeelstupid

2511 silver badge44 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is date column, is it date or values which you have given?
  
  – PSK
  Mar 26 at 6:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The date column is a date.
  
  – ifeelstupid
  Mar 26 at 6:11
  

  
  
  
  

add a comment | 

I'm trying to sum a window with a filter. I saw something similar to
sum(x) filter(condition) over (partition by...)
but it does not seem to work in t-sql, SQL Server 2017.

Essentially, I want to sum the last 5 rows that have a condition on another column.

I've tried
sum(case when condition...) over (partition...)
and sum(cast(nullif(x))) over (partition...).

I've tried left joining the table with a where condition to filter out the condition.

All of the above will add the last 5 from the starting point of the current row with the condition.

What I want is from the current row. Add the last 5 values above that meet a condition.

Date| Value | Condition | Result
1-1 10 1 
1-2 11 1 
1-3 12 1
1-4 13 1
1-5 14 0
1-6 15 1
1-7 16 0
1-8 17 0 sum(15+13+12+11+10)
1-9 18 1 sum(18+15+13+12+11)
1-10 19 1 sum(19+18+15+13+12)

In the above example the condition I would want would be 1, ignoring the 0 but still having the "window" size be 5 non-0 values.

tsql window-functions

share|improve this question

asked Mar 26 at 5:55

ifeelstupidifeelstupid

2511 silver badge44 bronze badges

Date| Value | Condition | Result
1-1 10 1 
1-2 11 1 
1-3 12 1
1-4 13 1
1-5 14 0
1-6 15 1
1-7 16 0
1-8 17 0 sum(15+13+12+11+10)
1-9 18 1 sum(18+15+13+12+11)
1-10 19 1 sum(19+18+15+13+12)

share|improve this question

asked Mar 26 at 5:55

ifeelstupidifeelstupid

2511 silver badge44 bronze badges

share|improve this question

asked Mar 26 at 5:55

ifeelstupidifeelstupid

2511 silver badge44 bronze badges

asked Mar 26 at 5:55

ifeelstupidifeelstupid

2511 silver badge44 bronze badges

asked Mar 26 at 5:55

ifeelstupidifeelstupid

2511 silver badge44 bronze badges

1 Answer
1

active

oldest

votes

0

This can easily be achieved using a correlated sub query:

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
 [Date] Date, 
 [Value] int, 
 Condition bit
)
INSERT INTO @T ([Date], [Value], Condition) VALUES
('2019-01-01', 10, 1),
('2019-01-02', 11, 1),
('2019-01-03', 12, 1),
('2019-01-04', 13, 1),
('2019-01-05', 14, 0),
('2019-01-06', 15, 1),
('2019-01-07', 16, 0),
('2019-01-08', 17, 0),
('2019-01-09', 18, 1),
('2019-01-10', 19, 1)

The query:

SELECT [Date], [Value], Condition,
 (
 SELECT Sum([Value])
 FROM 
 (
 SELECT TOP 5 [Value] 
 FROM @T AS t1
 WHERE Condition = 1
 AND t1.[Date] <= t0.[Date]
-- If you want the sum to appear starting from a specific date, unremark the next row
 --AND t0.[Date] > '2019-01-07'
 ORDER BY [Date] DESC 
 ) As t2
 HAVING COUNT(*) = 5 -- there are at least 5 rows meeting the condition
 ) As Result
FROM @T As T0

Results:

Date Value Condition Result
2019-01-01 10 1 
2019-01-02 11 1 
2019-01-03 12 1 
2019-01-04 13 1 
2019-01-05 14 0 
2019-01-06 15 1 61
2019-01-07 16 0 61
2019-01-08 17 0 61
2019-01-09 18 1 69
2019-01-10 19 1 77

share|improve this answer

edited Mar 26 at 6:43

answered Mar 26 at 6:32

Zohar PeledZohar Peled

59.8k77 gold badges3636 silver badges7878 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55350656%2ft-sql-function-like-filter-for-sumx-filtercondition-over-partition-by%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

This can easily be achieved using a correlated sub query:

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
 [Date] Date, 
 [Value] int, 
 Condition bit
)
INSERT INTO @T ([Date], [Value], Condition) VALUES
('2019-01-01', 10, 1),
('2019-01-02', 11, 1),
('2019-01-03', 12, 1),
('2019-01-04', 13, 1),
('2019-01-05', 14, 0),
('2019-01-06', 15, 1),
('2019-01-07', 16, 0),
('2019-01-08', 17, 0),
('2019-01-09', 18, 1),
('2019-01-10', 19, 1)

The query:

SELECT [Date], [Value], Condition,
 (
 SELECT Sum([Value])
 FROM 
 (
 SELECT TOP 5 [Value] 
 FROM @T AS t1
 WHERE Condition = 1
 AND t1.[Date] <= t0.[Date]
-- If you want the sum to appear starting from a specific date, unremark the next row
 --AND t0.[Date] > '2019-01-07'
 ORDER BY [Date] DESC 
 ) As t2
 HAVING COUNT(*) = 5 -- there are at least 5 rows meeting the condition
 ) As Result
FROM @T As T0

Results:

Date Value Condition Result
2019-01-01 10 1 
2019-01-02 11 1 
2019-01-03 12 1 
2019-01-04 13 1 
2019-01-05 14 0 
2019-01-06 15 1 61
2019-01-07 16 0 61
2019-01-08 17 0 61
2019-01-09 18 1 69
2019-01-10 19 1 77

share|improve this answer

edited Mar 26 at 6:43

answered Mar 26 at 6:32

Zohar PeledZohar Peled

59.8k77 gold badges3636 silver badges7878 bronze badges

add a comment | 

0

This can easily be achieved using a correlated sub query:

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
 [Date] Date, 
 [Value] int, 
 Condition bit
)
INSERT INTO @T ([Date], [Value], Condition) VALUES
('2019-01-01', 10, 1),
('2019-01-02', 11, 1),
('2019-01-03', 12, 1),
('2019-01-04', 13, 1),
('2019-01-05', 14, 0),
('2019-01-06', 15, 1),
('2019-01-07', 16, 0),
('2019-01-08', 17, 0),
('2019-01-09', 18, 1),
('2019-01-10', 19, 1)

The query:

SELECT [Date], [Value], Condition,
 (
 SELECT Sum([Value])
 FROM 
 (
 SELECT TOP 5 [Value] 
 FROM @T AS t1
 WHERE Condition = 1
 AND t1.[Date] <= t0.[Date]
-- If you want the sum to appear starting from a specific date, unremark the next row
 --AND t0.[Date] > '2019-01-07'
 ORDER BY [Date] DESC 
 ) As t2
 HAVING COUNT(*) = 5 -- there are at least 5 rows meeting the condition
 ) As Result
FROM @T As T0

Results:

Date Value Condition Result
2019-01-01 10 1 
2019-01-02 11 1 
2019-01-03 12 1 
2019-01-04 13 1 
2019-01-05 14 0 
2019-01-06 15 1 61
2019-01-07 16 0 61
2019-01-08 17 0 61
2019-01-09 18 1 69
2019-01-10 19 1 77

share|improve this answer

edited Mar 26 at 6:43

answered Mar 26 at 6:32

Zohar PeledZohar Peled

59.8k77 gold badges3636 silver badges7878 bronze badges

add a comment | 

This can easily be achieved using a correlated sub query:

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
 [Date] Date, 
 [Value] int, 
 Condition bit
)
INSERT INTO @T ([Date], [Value], Condition) VALUES
('2019-01-01', 10, 1),
('2019-01-02', 11, 1),
('2019-01-03', 12, 1),
('2019-01-04', 13, 1),
('2019-01-05', 14, 0),
('2019-01-06', 15, 1),
('2019-01-07', 16, 0),
('2019-01-08', 17, 0),
('2019-01-09', 18, 1),
('2019-01-10', 19, 1)

The query:

SELECT [Date], [Value], Condition,
 (
 SELECT Sum([Value])
 FROM 
 (
 SELECT TOP 5 [Value] 
 FROM @T AS t1
 WHERE Condition = 1
 AND t1.[Date] <= t0.[Date]
-- If you want the sum to appear starting from a specific date, unremark the next row
 --AND t0.[Date] > '2019-01-07'
 ORDER BY [Date] DESC 
 ) As t2
 HAVING COUNT(*) = 5 -- there are at least 5 rows meeting the condition
 ) As Result
FROM @T As T0

Results:

Date Value Condition Result
2019-01-01 10 1 
2019-01-02 11 1 
2019-01-03 12 1 
2019-01-04 13 1 
2019-01-05 14 0 
2019-01-06 15 1 61
2019-01-07 16 0 61
2019-01-08 17 0 61
2019-01-09 18 1 69
2019-01-10 19 1 77

share|improve this answer

edited Mar 26 at 6:43

answered Mar 26 at 6:32

Zohar PeledZohar Peled

59.8k77 gold badges3636 silver badges7878 bronze badges

DECLARE @T AS TABLE
(
 [Date] Date, 
 [Value] int, 
 Condition bit
)
INSERT INTO @T ([Date], [Value], Condition) VALUES
('2019-01-01', 10, 1),
('2019-01-02', 11, 1),
('2019-01-03', 12, 1),
('2019-01-04', 13, 1),
('2019-01-05', 14, 0),
('2019-01-06', 15, 1),
('2019-01-07', 16, 0),
('2019-01-08', 17, 0),
('2019-01-09', 18, 1),
('2019-01-10', 19, 1)

SELECT [Date], [Value], Condition,
 (
 SELECT Sum([Value])
 FROM 
 (
 SELECT TOP 5 [Value] 
 FROM @T AS t1
 WHERE Condition = 1
 AND t1.[Date] <= t0.[Date]
-- If you want the sum to appear starting from a specific date, unremark the next row
 --AND t0.[Date] > '2019-01-07'
 ORDER BY [Date] DESC 
 ) As t2
 HAVING COUNT(*) = 5 -- there are at least 5 rows meeting the condition
 ) As Result
FROM @T As T0

Date Value Condition Result
2019-01-01 10 1 
2019-01-02 11 1 
2019-01-03 12 1 
2019-01-04 13 1 
2019-01-05 14 0 
2019-01-06 15 1 61
2019-01-07 16 0 61
2019-01-08 17 0 61
2019-01-09 18 1 69
2019-01-10 19 1 77

share|improve this answer

edited Mar 26 at 6:43

answered Mar 26 at 6:32

Zohar PeledZohar Peled

59.8k77 gold badges3636 silver badges7878 bronze badges

answered Mar 26 at 6:32

Zohar PeledZohar Peled

59.8k77 gold badges3636 silver badges7878 bronze badges

answered Mar 26 at 6:32

Zohar PeledZohar Peled

59.8k77 gold badges3636 silver badges7878 bronze badges