T-SQL Calculate threshold from a recordset

Question

I have the following result set :

    ContentSendId ContentId   NewsletterId Position    IsConditionMatch SendDate                NumberOfSends IsSendValid
    ------------- ----------- ------------ ----------- ---------------- ----------------------- ------------- -----------
    1             100001      51           1           0                2011-05-14 00:00:00.000 200           0
    2             100001      51           1           0                2011-05-13 00:00:00.000 300           0
    3             100001      51           1           0                2011-05-14 00:00:00.000 100           0
    4             100001      51           1           0                2011-05-13 00:00:00.000 200           0

I need to run a calculation in T-SQL where if given a threshold value a record should get inserted (into a temp table) and any values outside the threshold should be ignored

So in this example lets say the threshold value is 500, the first record and the second record should get inserted.

EDIT : Running total is something to take care of in this scenario, so for example (above example updated) in the above scenario the temp table should insert 1st and 2nd record and stop since the threshold of 500 has been met.

SQL Server 2005, 2008 or 2012? It could influence how the solution is formulated. An answer catering for SQL 2008 might not be optimal for SQL 2012, as newer versions has first-class construct for some stuff, e.g. running total, while with the older versions you have to come up creatively with an optimal solution — Michael Buen, Oct 04 '12 at 16:23

podiluska · Accepted Answer · 2012-10-04T15:20:34.457

1

select t1.ContentSendId, t1.ContentId, t1.NewsletterId, t1.Position, t1.IsConditionMatch, t1.SendDate, t1.NumberOfSends, t1.IsSendValid 
into #t
from yourtable t1
    inner join yourtable t2 on t1.ContentSendId>=t2.ContentSendId
group by t1.ContentSendId, t1.ContentId, t1.NewsletterId, t1.Position, t1.IsConditionMatch, t1.SendDate, t1.NumberOfSends, t1.IsSendValid 
having SUM(t2.NumberOfSends) < @threshold

edited Oct 04 '12 at 15:20

answered Oct 04 '12 at 15:07

podiluska

49,221
7
86
96

Updated question with more details. – Murtaza Mandvi Oct 04 '12 at 15:13
1

Updated answer with more SQL :) – podiluska Oct 04 '12 at 15:20

Kermit · Answer 2 · 2012-10-04T15:17:18.817

0

It seems you are just trying to filter the result set.

SELECT * FROM t1 
GROUP BY SendDate
HAVING SUM(NumberOfSends) >= 500

edited Oct 04 '12 at 15:17

answered Oct 04 '12 at 15:08

Kermit

32,563
10
80
117

Sorry added more explanation, and enhanced my example resultset, running total is important to consider. – Murtaza Mandvi Oct 04 '12 at 15:13

T-SQL Calculate threshold from a recordset

2 Answers2