Jumps in a field of a table

Question

Jumps in a field of a table

Navigation

#1 by (0 votes)

2

Good morning, I am new to this site, I would like to know if someone could help me or guide me with this doubt.

I have a table in MYSQL call Facturas , have a field called INVOICE NUMBER ( NUMFAC ), among others, the field saves a character that identifies the status of the invoice, for the invoice paid the first character is a letter P , now what I want is to know if through a select something can be done, it can be obtained if there are invoice jumps in a range of dates.

I'll leave you how the table is structured more or less.

+------------------------------------+  
|..Fecha...|Hora|.NumFac.|  
|2017-07-25|0801|P0000001|  
|2017-07-25|0802|P0000002|  
|2017-07-25|0810|P0000003|  
|2017-07-25|0811|P0000004|  
|2017-07-25|0812|P0000005|  
|2017-07-25|0813|P0000007|  
|2017-07-26|0802|P0000008|  
|2017-07-26|0806|P0000009|  
|2017-07-26|0807|P0000010|  
|2017-07-26|0808|P0000011|  
|2017-07-26|0810|P0000012|  
|2017-07-26|0811|P0000013|  
|2017-07-26|0812|P0000014|

mysql

asked by Danny Ferrer 25.07.2017 в 17:26

source

1 answer

Insert data in the database without refreshing the page Know if a file has changed its content with Node.js

score 0 · Answer 1

This query would return all the invoice numbers that do not exist (including the next one to the last one, this could be deleted if necessary). The query works in SqlServer , but I imagine that for MySql it will be similar and easily adaptable, and it can give you an idea of a way to achieve what you are looking for:

select 'P' + replicate('0', 7 - len(substring(NumFac,2,7) +1)) + cast (substring(NumFac,2,7) +1 as varchar) From Facturas t1
where not exists (select * from Facturas t2 where substring(t1.NumFac,2,7) +1 = substring(t2.NumFac,2,7));