CODEWITHSUNDEEP
sqlsql-server
shailendra kushwah
shailendra kushwah

Reputation: 117

How to get data from sql server with condition?

I am working with Sql Server!

My question is: I have 15000 records in my customer table , And I want to process first 5000 records in one day, next day I process on next 5000 records on daily basis. Every day operation is perform in limited number of records, Data of customer table changes frequently. And also get number of pending records which are not processed. Please give your helpful suggestions how to do this . Thanks

Further Details:

  1. datetime stamp using in table
  2. Fields: [first_name] ,[middle_name] ,[last_name] ,[created] ,[created_by] ,[customer_number]

Upvotes: 0

Views: 51

Answers (2)

JohnHC
JohnHC

Reputation: 11195

I'm going to assume you have some form of ID in your table...

So you set a start date in your procedure, and compare to that (I have used '2016-01-01'):

with CTE as
(
select t1.*, row_number() over(order by customer_id) as r_ord
from Mytable t1
)
select CTE.*
from CTE
where (mod(datediff(day, '2016-01-01', getdate()),3) = 0 and r_ord <= 5000)
or  (mod(datediff(day, '2016-01-01', getdate()),3) = 1 and r_ord between 5001 and 10000)
or  (mod(datediff(day, '2016-01-01', getdate()),3) = 2 and r_ord > 10000)

Upvotes: 1

Nishant Kumar
Nishant Kumar

Reputation: 2169

The simplest way can be by adding two column (if not exist). updated_at and processed_at. updated_at column will be updated on update of row. processed_at column will be updated when you started process that row by your daily job. Now your query will be something like.

select * from your_table where updated_at > processed_at limit 5000;

Upvotes: 1

Related Questions