how to delete rows if they are in consecutive series of 50 numbers

Question

0.00/5 (No votes)

See more:

I have to check whether the rows are in sequence of 50 numbers based on condition the ending number last digit is either 50 or 00

Example

   01-50   
   51-100
   101-150
   151-200
   201-250
   

If I found the consecutive series of 50 is in between these numbers . I have to delete those numbers.


supose if found numbers starting from 201-250 then i have to delete those series
 if i found 211-260  i should not delete these rows.

I need a sql server query or C# gridview.

Here is my code. to fectch the missing records.

SQL

;WITH Missing (missdcno, maxid)
AS
(
 SELECT 1 AS missdcno, (
select max(cast(R.dcno as int))  from   K_RT_WarehouseDetails D
inner join K_RT_DailyentryRetail R on D.sno=R.branchdate where  len(DCNO) !>5   )
 UNION ALL
 SELECT missdcno + 1, maxid FROM Missing
 WHERE missdcno < maxid
)
SELECT missdcno
FROM Missing
LEFT OUTER JOIN K_RT_DailyentryRetail tt on tt.dcno = Missing.missdcno
WHERE tt.dcno is NULL and missdcno not between 1 and 100
OPTION (MAXRECURSION 0);

Here output comes as a sub result .From this result i have to write Main query.I am not getting any Idea.

Posted 4-Nov-14 23:56pm

Bhagavan Raju M

Updated 5-Nov-14 0:17am

v4

Add a Solution

Comments

Tomas Takac 5-Nov-14 6:10am

What have you tried so far? Can you show as your code?

Bhagavan Raju M 5-Nov-14 6:16am

I post My code .

Tomas Takac 5-Nov-14 7:54am

Honestly, I don't understand what you are trying to do. First you generate a sequence 1..maxid. Then you select those which do not have matching record in K_RT_DailyentryRetail. Then what? You want to look for the series of 50 within these missing records? And you want to delete those from a different table than K_RT_DailyentryRetail, right?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

andyharman · Answer 1 · 2014-11-05T10:51:00

Solution 1

The following SQL allows me to find consecutive invoices on my database:

SQL

select inv.InvoiceId as StartInvoiceId, inv.InvoiceId + 49 as EndInvoiceId
from accounting.invoice as inv
where (inv.InvoiceId - 1) % 50 = 0
and 50 = (select count(*) from accounting.invoice
	where invoiceId between inv.InvoiceId and inv.InvoiceId + 49)
order by inv.InvoiceId

You should be able to convert it to your needs.

Posted 5-Nov-14 10:51am

andyharman

Comments

andyharman 6-Nov-14 8:01am

I just checked with my production DBA colleague. He says that if you are running this on a server that has lots of CPUs then you should consider using the "maxdop = 1" hint when you do sub-queries in this way. If SQL-Server decides to split this single query over multiple processors then it is technically possible for the processes to lock each other.

Bhagavan Raju M 7-Nov-14 2:31am

Ok.