• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

SQL Server Select on LT running Totals

In the sql below I get a count  by CampaignID

What I would like to do is set an update and set vendorServer = 1 on all rows where the running total < 125,000

SQL Server version is 2012


SELECT CampaignID, CurrentCount, vendorServer
FROM   dbo.Campaigns
WHERE  schedStatus = 'ACTIVE' AND stat = 0 

Open in new window

0
lrbrister
Asked:
lrbrister
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define 'running total' in the context of the above T-SQL statement, preferably with some sample data.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
CurrentCount is the column that has the running total?
If so, then you can try this:
UPDATE Campaigns
SET vendorServer=1
WHERE CurrentCount < 125000

Open in new window

NOTE: Test it before in a development environment before running the code in Production.
0
 
lrbristerAuthor Commented:
Here ...
SP1
And this
SP2
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this solution:
UPDATE Campaigns
SET SendGrid=1
FROM Campaigns T
INNER JOIN (SELECT TOP 100 PERCENT c.campagainId, c.CurrentCount, c.SendGrid, SUM(c2.CurrentCount) RunningTotal
		FROM Campaigns c,
			Campaigns c2 
		WHERE c2.campagainId <= c.campagainId
		GROUP BY c.campagainId, c.CurrentCount, c.SendGrid
		HAVING SUM(c2.CurrentCount)<125000
		ORDER BY c.campagainId) T2
	ON T.campagainId = T2.campagainId

Open in new window

0
 
SharathData EngineerCommented:
try this.
;with cte as (select *,sum(CurrentCount) over (order by CampaignId) RunningTotal from Campaigns)
update cte set SendGrid = case when RunningTotal< 125000 then 1 else 0 end

Open in new window


Here is the example
declare @Campaigns table(CampaignId int, CurrentCount int, SendGrid bit)
insert @Campaigns values (96988, 21618, 0),(96989, 28477, 0),(96990, 24, 0),(96991, 58000, 0),(96992, 11255, 0),(96993, 35035, 0)
;with cte as (select *,sum(CurrentCount) over (order by CampaignId) RunningTotal from @Campaigns)
update cte set SendGrid = case when RunningTotal< 125000 then 1 else 0 end

select * from @Campaigns

/*
CampaignId	CurrentCount	SendGrid
96988	21618	1
96989	28477	1
96990	24	1
96991	58000	1
96992	11255	1
96993	35035	0
*/

Open in new window

0
 
lrbristerAuthor Commented:
Very nice guys... both work
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now