Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

SQL Server Set bit value based on sums

Posted on 2016-08-11
Medium Priority
59 Views
I have a table that I want to query once each morning
I want to get a sum of the totalCOunt column
If that total is > 175,000
And then however many ID's there are (Should be 5 to 20
Get the bottom ID's that together SUM(totalCount) <= 125,000
And set outsideBit = 1
So that at the end of the query the ID's will not be in the top when sorted by ID
And the sum of thos ID's is <= 125,000

Here are the three columns
ID   totalCount    outsideBIT
1     12532                0
2     12658                0
etc...
0
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 11

Expert Comment

ID: 41753063
First step is to determine if the sum of totalCount > 175,000. Below I assume that outsideBIT has been set to zero and is not relevant as this stage. If it is, you can always add a WHERE clause to the SELECT statement.

``````dim n as integer
dim rs as DAO.Recordset
dim db as DAO.Database
set db = CurrentDB()
set rs = db.OpenRecordset("select sum(totalCount) from T")
if not rs.eof then
n = rs(0)
if n > 175000 then
'take action
end if
end if
``````
Let me know if the above is going to work for you. If so, we can move on to setting the bit flag. If not we need to adjust things to fit your situation.

-- Craig
0

LVL 49

Accepted Solution

PortletPaul earned 2000 total points
ID: 41753145
``````declare @Table1 table
([id] int, [totalCount] int)
;

INSERT INTO @Table1
([id], [totalCount])
VALUES
(1, 20000),
(2, 20000),
(3, 20000),
(4, 20000),
(5, 20000),
(6, 20000),
(7, 20000),
(8, 20000),
(9, 20000),
(10, 20000)
;

select top(1)
id
from (
select
id
, sum(totalCount) over(order by id DESC) id_low
, sum(totalCount) over(partition by (select 1)) all_count
from @table1
) t
where all_count > 175000 and id_low <= 125000
order by id_low DESC
;

select
id
, sum(totalCount) over(order by id DESC) id_low
, sum(totalCount) over(partition by (select 1)) all_count
from @table1
``````
``````id
--
5

(1 row(s) returned)

(10 row(s) affected)

id id_low all_count
-- ------ ---------
10 20000  200000
9  40000  200000
8  60000  200000
7  80000  200000
6  100000 200000
5  120000 200000
4  140000 200000
3  160000 200000
2  180000 200000
1  200000 200000

(10 row(s) returned)

(10 row(s) affected)
``````
can you illustrate what you wan with sample data and expected result?
0

Author Comment

ID: 41753321
Guys
I will be at my PC in a few hours to check these
0

Author Closing Comment

ID: 41753766
This is exactly what I needed... it returned correctly #5
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backupâ€¦
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses
Course of the Month7 days, 13 hours left to enroll