Posts by José Diz • 6,942 points
305 posts
-
0
votes2
answers712
viewsA: Monitor performed SQL SERVER 2014 Insert/delete/update
The change data capture (CDC) is an option; you had the opportunity to read the article SQL Server Security Audit Basics? The subject is extensive to address in forum topic, but there are several…
sql-serveranswered José Diz 6,942 -
-1
votes3
answers2405
viewsA: Export query output as Insert scripts
You can export/import directly between databases, without the need for intermediate files, and by delimiting in the WHERE clause the restrictions to obtain "only a few records". Just have access to…
sql-serveranswered José Diz 6,942 -
3
votes3
answers14296
viewsA: Export SQL Server query to CSV file
The utility BCP makes it possible to export data to text file, CSV format. Through the -t option it is possible to define the field separator. I recommend the previous reading of the document…
-
0
votes1
answer110
viewsA: Grouping data by period
Evaluate the following suggestion, which accounts for production per day/hour: -- código #1 v2 -- informa o período de emissão (formato dd/mm/aaaa) declare @dataInicial datetime, @dataFinal…
sql-serveranswered José Diz 6,942 -
1
votes1
answer252
viewsA: Changing a table
There are a few approaches to what you request. I suggest backing up the table (or database) before starting the procedures. The procedure that follows is an outline and has not been tested. I…
sql-serveranswered José Diz 6,942 -
0
votes1
answer1324
viewsA: Select the line with the oldest date within this query between the repeated values
Considering the complete code you posted on this topic, here’s another suggestion: -- código #2 v5 with tbOPR as ( SELECT r.dt_uso_inicio as DATA_REMESSA, o.dt_entorc_oficina as DATA_ABERTURA,…
sql-serveranswered José Diz 6,942 -
0
votes4
answers3265
viewsA: Select only 1st row of table
(...) I need to take only the oldest data among the repeated. Jander, considering the code you posted at the beginning of this topic, it seems to me that only the GROUP BY clause needs to be…
-
3
votes1
answer91
viewsA: Group results in single line according to sequential
Try -- código #1 v2 SELECT COD, Cast(DATA as date) as DATA, ST, BATIDA1= max(case when SEQ_BAT = 1 then Cast(BATIDA as time(0)) end), BATIDA2= max(case when SEQ_BAT = 2 then Cast(BATIDA as time(0))…
-
3
votes1
answer669
viewsA: occurrence of word in sql query
There is a way to count the events of ana (...) using only the sql query? my select returns only a row and a column and in this field comes this string literally as content "ANA ANA JOAO ANA ALBERTO…
-
4
votes2
answers1333
viewsA: Set default value SQL Server column
Luiz, you can use the restriction CHECK. -- código #1 CREATE TABLE XPTO( ID INT IDENTITY(1,1), NOME VARCHAR(100) NOT NULL, ATIVO CHAR(1) NOT NULL check (ATIVO in ('S','N')) ); -- código #2 CREATE…
-
1
votes1
answer1224
viewsA: Install Full-text in SQL Server
This is a feature that should be downloaded, or is already in my SQL Server and just need to activate it? Full text search (full-text search) is an optional component of the Database Mechanism…
-
1
votes2
answers2319
viewsA: How to use like in a comparison of fields in different tables?
Thiago, considering that the column D3_EMISSAO is probably declared as varchar(8), and with the value stored in the format yyyymmdd, it seems unnecessary, and perhaps inefficient, the use of the…
-
1
votes1
answer116
viewsA: Create select to return last time something was non-zero
"CA_93", there are some approaches to meet what you need. The most traditional is the use of the function row_number() to number the stock lines of each product by date, but in reverse order, where…
-
0
votes2
answers6454
viewsA: How to use Sum with subquery
How can I use sum with a subquery inside? Documentation of the function Sum() indicates that the parameter must be an expression. And, in another part, it states that "(...) Aggregation functions…
-
1
votes2
answers379
viewsA: Birthday listing in a date range
In this case: -- código #1 v5 declare @DataInicial date, @DataFinal date; set @DataInicial = cast(Current_timestamp as date); set @DataFinal = @DataInicial; -- verifica se é sexta-feira set…
sql-serveranswered José Diz 6,942 -
3
votes5
answers1923
viewsA: Is it possible to update the first 100 lines in sql(server)?
If what you need is only to change product code 3 to 1 in table rows tbl_sell, as it is in the code you transcribed for this topic, then the solution is simple. -- código #1 declare @Continua int,…
-
1
votes2
answers473
viewsA: Insert 1000 records into table with uniqueidentifier as primary key
Here is a suggestion to include the lines using a single INSERT execution in the table. In general, it is more efficient. -- código #1 INSERT into client (col1, col2, col3) SELECT NewId(), ('jack '…
-
0
votes2
answers394
viewsA: Compare a null bit column
Rafael, there are several factors that determine the performance of a SELECT construction, including whether there are indices and, in this case, what are the characteristics of the existing…
sql-serveranswered José Diz 6,942 -
2
votes3
answers634
viewsA: Like bringing in salesmen who made and didn’t make sales yesterday?
This is a suggestion considering the information available. -- código #1 v3 -- obtém horários de limite do dia declare @Data1 datetime, @Data2 datetime; set @Data1= Cast(Current_timestamp as date);…
-
3
votes4
answers1225
viewsA: How do I write a store that uses current and previous line values?
Bruno, the problem you propose seems to me similar (but not identical) to the issue of bank account statement, where to calculate the balance of the day you must get the balance of the previous day,…
-
1
votes3
answers1584
viewsA: Merge the result of a query on the same line from the sequential
Considering that the number of rows per person/date is variable, the ideal solution should employ the use of pivoted with dynamic SQL command. Here is a solution that works with 80 compatibility…
-
-2
votes5
answers1818
viewsA: Group records per day
Each entry table row contains the ID of an object, a date range, and the number of recorded hours. It is also observed that there are overlapping dates in the intervals; that is, the intervals…
-
2
votes3
answers150
viewsA: Database modeling, when it is possible to denormalize
In my opinion, data modeling should be carried out independently of sgbd. After finishing the data modeling comes the physical implementation, when then it may be necessary to adapt the data model…
-
2
votes2
answers2406
viewsA: Single row and column result for multi-row column
Felipe, your request contains two of the most frequently asked questions on SQL forums: (1) break a character string containing several fields separated by a delimiter (split string) and (2) turn…
-
0
votes3
answers75
viewsA: Doubt with registration grouping
This is the first proposal to combine applications for the same product on the same date. Only after this grouping is the query executed. -- código #1 v3 with Pedidos_Produto as ( SELECT…
-
6
votes3
answers324
viewsA: "Translate" query from Mysql to SQL Server 2012
To have action similar to construction REPLACE INTO (mysql SQL extension) mentioned in this topic, here is code outline: -- código #1 BEGIN TRANSACTION; DELETE from [schedule] where jobname =…
-
0
votes2
answers4550
viewsA: Conversion from VARCHAR to SMALLDATETIME
The above filter does nothing but, starting from the current date, subtract 1 day, and after add 18 hours "from the current date" To= Cast (Current_timestamp as date) "subtract a day" B= Dateadd…
sql-serveranswered José Diz 6,942 -
2
votes3
answers737
viewsA: Field comparison problem with date and time
@itasouza, the form you use is reliable, because it has previously converted datetime for date. That is, the comparison will always be between two operands of the type date. Causes no problem in…
sql-serveranswered José Diz 6,942 -
2
votes2
answers1381
viewsA: How to create a Rigger to save updates from two tables?
André, it is not possible to associate a Rigger procedure to more than one table. Only to a single table. I suggest you implement the update of the history table in the application itself. Tabela3…
-
2
votes1
answer65
viewsA: Query with SQL Server query
If there are repeated localities, then it seems to me that the first step is to remove them from the table. To find possible cases of repetition in the same unit of the federation, the code below…
-
0
votes1
answer191
viewsA: TRIGGER and ASSERTION sql server
The trigger creation syntax allows you to create independent procedures for the INSERT, UPDATE and DELETE instructions. It is also possible in a single procedure to handle the three types of event.…
sql-serveranswered José Diz 6,942 -
0
votes1
answer41
viewsA: Doubt when changing fields automatically subSelect
Carlos, some sgbd implement "calculated column", whose value is usually calculated at run time, that is, when the contents of the column are consulted, using column(s) of the same table. In…
-
1
votes2
answers99
viewsA: Procedure for storing in backup tables?
Before modifying the database structure, and its impact on applications, I suggest an analysis of what is causing the slowness. They may be outdated statistics, some query that needs to be…
-
2
votes2
answers74
viewsA: How to use @in SQL SERVER?
Try -- código #1 v2 DECLARE @W_P1 int; DECLARE @W_P2 int; DECLARE @W_NUM int; set @W_P1= 0; set @W_P2= 10; set @W_NUM= 0; WHILE (@W_P2 < 300) BEGIN SET @W_NUM= (SELECT COUNT(P_CODE) FROM PRODUCTT…
sql-serveranswered José Diz 6,942 -
2
votes2
answers1514
viewsA: Calculate length of a Sqlserver field
Eduardo, there are two functions in SQL Server that return the size: Len() and Datalength(). And when you look at the documentation of the two functions, you’ll notice that there’s a slight…
-
1
votes1
answer3303
viewsA: SQL server 2014 installation error
Guilherme, what you are installing is SQL Server 2014 Express with SP1, SQLEXPRWT_X64 package. There is a more recent compilation, already with service pack 2: SQL Server 2014 Service Pack 2 (SP2)…
sql-serveranswered José Diz 6,942 -
2
votes3
answers75
viewsA: How can I exclude the penultimate number from a period?
To convert a date to aaaamm, numeric format, there are a few shapes. -- código #1 SELECT (YEAR(Periodo_Mensal) * 100 + MONTH(Periodo_Mensal)) as PERIODO_MENSAL from ... If you need the result to be…
-
3
votes2
answers55
viewsA: Returning only one select at a time multiuser
Gustavo, there’s a difference between multi-user and multitasking. The symptom you report in the comments, "2 customers get the same ID", probably due to the competition of proceedings of a…
-
2
votes2
answers95
viewsA: Doubt SQL Server
“Write a command that shows the name of each employee and the name of each dependant that each employee you have depended on” The above statement seemed a little ambiguous. What I understood is…
sql-serveranswered José Diz 6,942 -
0
votes2
answers605
viewsA: Field format for saving time
To store a time count that is less than 24 hours, you can use TIME. But if the accumulated exceeds 24 hours, as you quote, then you can use either datetime or int. When using the datetime type, you…
-
-2
votes3
answers7027
viewsA: Is Sql Server Agent (SQLEXPRESS) on Local Computer started stopped?
SQL Server Agent does not work in SQL Server Express editions. Although the service is listed as installed, it can never be activated. That’s in the documentation Features supported in SQL Server…
sql-serveranswered José Diz 6,942 -
2
votes2
answers957
viewsA: How to compare set of numbers with other set?
Whereas: the order of the elements (numbers) in the set does not matter; there are no repeated elements (that is, a same number cannot occur more than once in the same set); and the values of the…
-
-1
votes2
answers1894
viewsA: Create total sales (value) per customer (SQL)
Felipe, regarding the use of procedure Trigger, I suggest you avoid as much as possible their use for needs that can be resolved in the app. But if what you posted is an academic exercise, and it is…
-
2
votes1
answer584
viewsA: Create Precedent to adjust price as per conditions (SQL Server)
Considering that the readjustment parameter is reported as a real numerical value. For example, if the adjustment is 12.5%, the value passed as parameter is in the form 12.5 To calculate the new…
-
1
votes3
answers2086
viewsA: Return an sql command in a Function
In the example you posted, it would be like creating a subtable within each row of the main table. It seems to me that this is not possible in SQL Server. The way you built the main code, the result…
-
-1
votes2
answers1894
viewsA: Create total sales (value) per customer (SQL)
If what you need is a stored procedure that should be called by the app at the end of each sale (that is, after all the items from the sale have been registered), here is a suggestion: -- código #1…
-
2
votes1
answer334
viewsA: How to merge 8 query returns into one?
One possibility is to turn each query into a CTE and then join the result of the 8 queries. ORDER BY should be removed when entering the code of each query as CTE. -- código #1 with Consulta1 as (…
-
1
votes1
answer167
viewsA: Query condition to display only records that contain the same 3x consecutive number
Octavio, here’s a possibility: -- código #1 v5 IF Object_ID('tempDB..#TabBatidas', 'U') is not null DROP TABLE #TabBatidas; -- set DATEFIRST 7; -- domingo -- with Consulta as ( SELECT V.CHAPA,…
sql-serveranswered José Diz 6,942 -
1
votes1
answer293
viewsA: How do I search with a text variable containing apostrophes?
Marcelo, to assign OLHO D'AGUA DO CASADO to a variable, you can use some ways to define constant, of which I transcribe two: -- código #1 -- apóstrofo como delimitador de constante declare @cidade…
-
-1
votes1
answer63
viewsA: INSTEAD OF (Deleted table)
A procedure Trigger may be triggered even if no modification has occurred (in this case, line deletion). Therefore, it is recommended that at the beginning of the code, the number of rows in the…