Posts by José Diz • 6,942 points
305 posts
-
0
votes2
answers58
viewsA: Import Excel data to table in SQL Server (only data other than that already in the table)
There are some solutions and one of them involves using Linked server to access the spreadsheet in Excel as if it were a table. Then include lines that are in the spreadsheet and that are not part…
-
0
votes2
answers63
viewsA: How to select CNPJ higher billing? - SQL -
My goal is for the query to return the CNPJ address of higher revenue for each economic group. Evaluate if the following sql code returns what you need. -- código #1 SELECT top (1) with ties…
-
1
votes1
answer18
viewsA: Find out what type of Encoding was used
If "encoding" refers to data types, there are several ways. You can use SQL_VARIANT_PROPERTY, when you enter "Basetype" in the parameter Property. -- código #1 CREATE TABLE Super (Nome varchar(50),…
sql-serveranswered José Diz 6,942 -
0
votes1
answer42
viewsA: How to copy data from one column to another column of the same table
A way to make the column NameClear contains the same content as in the column Name, but without the accentuation, is to declare it as calculated column. Something like this: NameClear as…
-
0
votes2
answers141
viewsA: Procedure with CASE in the WHERE clause
It seems that Mysql implementers have offered something more to developers. In SQL Server there are some ways to get what you want. For example: -- código #1 SELECT ... from ... where (@Opcao =…
-
0
votes1
answer114
viewsA: insert the license into SQL Server 2019 Standard Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS)
This is the step by step obtained in the document "SQL Server on Linux Frequently Asked Questions (FAQ)”: When you run mssql-conf setup you are presented with the following options: Choose an…
-
1
votes1
answer63
viewsA: SQL doubt in query
Here’s a possible solution: -- código #1 -- © José Diz (Porto SQL) with Comparecimento as ( SELECT numero, datalineupentrada, horaentrada, cpfespectador from Ingresso where datalineupentrada =…
-
0
votes1
answer79
viewsA: Problems with Insert SQL Server
From the statement I understood that the problem is in the generation of the order number, and for different applications the same order number was used. One solution is to use an order number…
sql-serveranswered José Diz 6,942 -
1
votes1
answer30
viewsA: Concatenate lines into one
Try SELECT id, max (CASE WHEN YEAR(DATA) = 2017 THEN 'S' END) AS [2017], max (CASE WHEN YEAR(DATA) = 2017 AND ORIGEM = 'LOJA' THEN 'S' END) AS [2017 - LOJA], max (CASE WHEN YEAR(DATA) = 2018 THEN…
-
1
votes1
answer69
viewsA: How to treat a field in SQL SERVER
Try SELECT EMP.NOME as NOME_FUNCIONARIO, coalesce (DEP.NOME, 'não possui dependente') as NOME_DEPENDENTE from TB_EMPREGADO as EMP left join TB_DEPENDENTE as DEP on EMP.CODFUN = DEP.CODFUN; If you…
-
1
votes1
answer80
viewsA: How to update via Linked server without using Join / relationship?
When using server linking it is necessary to be aware to reduce traffic between servers. In the sql code you posted the OPENQUERY function is used and in this case I suggest you implement the filter…
-
1
votes1
answer31
viewsA: Quotation Factor
Ronnie Von, try the following sql code: SELECT SIMBOLO as [Símbolo], convert (char(10), DATA, 103) as Data, FATOR as Fator, [Fator dia]= (FATOR - coalesce (lag (FATOR) over (order by DATA), 10000))…
-
0
votes3
answers2051
viewsA: Difference between dates on multiple lines
The expected result is in descending order of date and the TIME column is the difference, in days, between the current row and the previous row. Here is the suggestion using LAG and DATEDIFF, as…
-
1
votes2
answers475
viewsA: Connection error with SQL Server 2012 with Python
It is necessary to check whether the instance with SQL Server 2012 handles the TLS v1.2 protocol. The first step is to find out what the update level of SQL Server software is. One simple way is…
-
1
votes2
answers74
viewsA: Data Conversion in SQL server 2008
If the column C.DATA was declared as datetime and the goal is to display the date in the dd-mm-yyyy format, try SELECT c.Empresa, p.Nome, convert (char(10), c.DATA, 105) as Data, c.NumeroOC as…
-
2
votes1
answer45
viewsA: Is it possible to make an addition with varchar field?
If I didn’t miss the syntax, try something like UPDATE ex set seq_docum_ref= right (('0000' + cast ((cast (seq_docum_ref as int) +1) as varchar(5))), 5), ... But the best thing is to use an…
sql-serveranswered José Diz 6,942 -
0
votes5
answers12287
viewsA: How to perform SQL concatenation
Joana, in the sql code you posted, keep an eye out for the months whose numerical value is less than 10. For example, 12/11/2008 will be "2008 11" but 12/1/2008 will be "2008 1", when it seems to me…
-
0
votes1
answer27
viewsA: Select a date field by extracting only the month and year
SELECT year (Periodo) as Ano, month (Periodo) as [Mês], sum (Quantidade) from Vagas where Periodo >= '20190201' and Periodo < '20190801' group by year (Periodo),…
-
1
votes1
answer79
viewsA: Remove numbers before the comma
The solution I am proposing requires each line to have a unique identification. Therefore, for the purpose of demonstrating the solution, it will be considered that such unique identification…
-
3
votes1
answer212
viewsA: T-SQL - Delete filter "NOT IN" does not work
By multiplying B.[Cod Loja] by 1, the value of B.[Cod Loja] is automatically converted from text value to numeric value. This forces A.[Cod Loja] also be transformed into a numerical value to make…
-
0
votes2
answers156
viewsA: Birthday Ordering - SQL Server
In the article Dominating dates and times in SQL Server you can find information on how to manipulate dates in SQL Server, including with an item dealing precisely with birthday relationships. From…
-
4
votes1
answer47
viewsA: Best Script Performance
I see no difference in performance between constructions -- código #1 IF not exists (SELECT * from tabela where dt_mes_referencia = @mes_referencia) INSERT ... and -- código #2 v2 declare @Var bit;…
-
0
votes1
answer401
viewsA: How to calculate value + sum(value) within a select
What you want is a percentage of a part over a whole. It’s something simple to implement using the SUM window function. To calculate the percentage of each order item over the total order, try --…
-
0
votes1
answer73
viewsA: How do I pass a list of parameters to a precedent?
There are some solutions, one of which is to use table-value Parameters. In the article Using Table-Valued Parameters in SQL Server and . NET, by Erland Sommarskog, you will find the complete…
-
0
votes1
answer85
viewsA: Using the DATEDIFF
Try -- código #1 v2 declare @UmAno int; set @UmAno= cast (convert (char(6), dateadd (year, -1, current_timestamp), 112) as int); DELETE from rvr_tb where ano_mes < @UmAno; The advantage of the…
-
0
votes2
answers400
viewsA: Creation of Trigger Sql Server
The way SQL Server handles events that trigger triggers (Trigger) can become a real trap for those who program the trigger procedure and ignore the specific details of SQL Server, because while…
-
1
votes1
answer188
viewsA: How to not add zeroed values - SQL
OPTION 1 The performance of your query can be improved if there is index with the ano_mes column as the primary key and containing the columns column1 to column7 and also the other identification…
-
1
votes4
answers82876
viewsA: Not IN or Not EXISTS which to use?
The answer depends first on which database manager (sgbd) the internal implementations may not be the same for NOT IN and NOT EXISTS. In the case of SQL Server I recently published the article…
-
1
votes3
answers6516
viewsA: How to convert varchar to Numeric in SQL?
What is occurring is an automatic conversion of data type, called implicit conversion by Microsoft. Details in article "The dangers of automatic data type conversion”. You commented that the CPF…
-
3
votes1
answer81
viewsA: Count in Past Time Intervals
The problem is a bit complex. Evaluate the following suggestion: -- código #1 declare @Inicio_leitura date, @Inicio_mes_atual date; set @Inicio_mes_atual= dateadd (month, datediff (month, 0, cast…
-
0
votes1
answer265
viewsA: How to disable Identity with Linked Server?
The IDENTITY_INSERT instruction must be executed in the instance of the database. It is not possible to define for other instances. -- código #1 SET IDENTITY_INSERT [BD_TESTE].dbo.Produto ON; INSERT…
-
2
votes1
answer461
viewsA: Delete - Which is the fastest?
Great question. The article Delete row set in huge tables treats precisely on how to erase set of rows in huge tables avoiding (or trying to...) stop the other processes that are running…
-
4
votes2
answers420
viewsA: The Datediff function resulted in an Sql Server overflow
If the goal is to calculate the duration in seconds of a phone call, then it looks like someone stayed for years making a phone call... About the error message "The Datediff Function resulted in an…
-
1
votes1
answer216
viewsA: Import CSV into SQL Server
You can import the CSV file to a temporary table and from this temporary table make the inclusion in the definitive table using something like -- código #1 declare @Ultimo int; BEGIN TRANSACTION;…
-
0
votes2
answers87
viewsA: SQL Server Charindex Problem
If you consider the property number as the last element of the address, then using the REVERSE() function makes it easy to obtain only the number. -- código #1 SELECT right (ENDERECO, (charindex ('…
sql-serveranswered José Diz 6,942 -
1
votes3
answers454
viewsA: Replace Sql in the middle of the text
Considering the styles defined in the CONVERT() function, the data is in the 6 style. If the goal is to convert from dd-month-yy to dd-mm-yy, then it is a very simple process: -- código #3 SELECT…
-
1
votes1
answer113
viewsA: store select result in a table
A simple solution is to encapsulate the query SQL code in a common table expression (CTE, common table Expression) and use the INSERT statement to include the query result in the table coleta_query.…
-
2
votes1
answer98
viewsA: Find missing table number
To find the ranges there are a number of suggestions on the web classified as "gaps and Islands". Here is one of the suggestions, adapted from the article "The SQL of Gaps and Islands in Sequences":…
sql-serveranswered José Diz 6,942 -
1
votes2
answers66
viewsA: How to "reset" the other SQLSERVER line
For me there is no duplicity, because in the column "Feature" the information displayed is different. Duplicity would be if the entire contents of the line were the same. In this case, it is…
sql-serveranswered José Diz 6,942 -
1
votes3
answers279
viewsA: use of cursor to update item to item stock
Here is solution using recursive CTE. -- código #1 v2 with Pedidos_seq as ( SELECT *, seq= row_number() over (partition by itemcode order by Docentry) from #tmpPedidos ), Le_Pedido as ( SELECT…
-
0
votes2
answers314
viewsA: Return last entry of each product
Considering the additional information posted in the comments by the author of the topic - we can use instead of input the COD_ENTRADA field which is the sequential number of each note -, here is…
-
1
votes1
answer66
viewsA: Field cumulativity calculated using Sqlserver
There are some forms and one of them is with the use of recursive CTE. -- código #1 v2 with Acumulado as ( SELECT SEQ, VLR, VLR as VLR_ACUMULADO from #T where SEQ = 1 union all SELECT T.SEQ, T.VLR,…
-
2
votes2
answers1604
viewsA: Conversion of a data type scan into a data type datetime resulted in a value outside the yyyy-mm-dd range
If such error message is being issued it is because the PRODUCTODATA column is declared as text (varchar(n), for example). In this case it is necessary to know which format the date is stored in, as…
-
2
votes1
answer981
viewsA: Grouping data by hour intervals in SQL Server
Here is a suggestion to return the averages per hour: -- código #1 v3 SELECT (convert (char(13), E3TimeStamp, 121) + ':00') as hora, avg (TOTALIZADO_FIT300) as campo1, avg (TOTALIZADO_FIT100eFIT300)…
-
4
votes1
answer60
viewsA: Error with another bank’s Foreign key
Marcos, the English text of the 1763 error message does not make clear what the cause is: "No support for foreign key references in all databases" In "all databases"?! -- But here is the original…
-
1
votes1
answer43
viewsA: Id booked in key breach transaction
Mailson, this is one of several problems when using IDENTITY or SEQUENCE. Some tables use a substitute key as a primary key, rather than a natural key. Generally this substitute key is a numerical…
-
0
votes2
answers73
viewsA: Select MAX with the Unix Timestamp field
Levi, in SQL Server the TIMESTAMP data type does not save date/time, as it is a database counter and is incremented for each insert or update operation executed in a table. Details in ROWVERSION.…
-
1
votes1
answer68
viewsA: Identify source of database Locks
Fernando, you can use the procedure sp_whoisactive for monitoring activities, including assisting in detecting the origin of the lock. Reading suggestion: Different Techniques to identify blocking…
-
5
votes1
answer93
viewsA: Data Cleanup - SQL Server
If the SQL Server version is 2016 (or newer), try: -- código #1 SELECT E.ID, rtrim (ltrim (SS.[value])) as Telefone from dbo.tbEmpresa as E cross apply String_Split (E.Telefones, ';') as SS;…
-
1
votes1
answer281
viewsA: Query in an empty table
See @@rowcount to see if you returned any lines in SELECT execution. -- código #1 SELECT Value from AVL_AnalogInput1 where TimeStamp = (select max(TimeStamp) from AVL_AnalogInput1); IF @@rowcount =…
sql-serveranswered José Diz 6,942