Solved

SQL Server Set bit value based on sums

Posted on 2016-08-11
4
39 Views
Last Modified: 2016-08-12
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
Comment
Question by:lrbrister
  • 2
4 Comments
 
LVL 11

Expert Comment

by:CraigYellick
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

Open in new window

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 48

Accepted Solution

by:
PortletPaul earned 500 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

Open in new window

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)

Open in new window

can you illustrate what you wan with sample data and expected result?
0
 

Author Comment

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

Author Closing Comment

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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now