Posts by José Diz • 6,942 points
305 posts
-
2
votes1
answer1350
viewsA: Error "No column name was specified"
It is necessary that the expressions present in the sub-consultations are named. For example: -- código #1 ... (SELECT CONCAT(TT.NUMSERI,TT.PRODUTO ,MAX(TT.DATA)) as SERI_PROD_DATA FROM…
-
1
votes2
answers75
viewsA: Add SUM() to querry results
Here is the code proposed in the topic Exchange WHILE for GROUP in SQL (very similar to this and both of his authorship), modified to add the sum: -- código #1 v2 USE CCILCDatabase; go SELECT…
-
0
votes1
answer114
viewsA: Optimization of SQL-SERVER query
I need to get the EMISSION_DATE field from TLX_VENDA and the B9_DATA field from TLX_ESTOQUE_INI table Therefore, I do not think it is necessary to use APPLY. I suggest that instead of OUTER APPLY (…
sql-serveranswered José Diz 6,942 -
0
votes1
answer335
viewsA: INSER lock trigger with condition and error message
Charles, so I understood it is not to allow inclusion of rows in the FLAN table when the value of the CODTDO column is "00017431". When building trigger procedures it is necessary to be aware that…
sql-serveranswered José Diz 6,942 -
0
votes1
answer79
viewsA: Exchange WHILE for GROUP in SQL
I need to count and group by the first two digits. I need this separated by months... Evaluate -- código #1 v2 USE CCILCDatabase; go SELECT month(DateCreated) as Mes, convert(char(2), CPCCodeID) as…
-
0
votes2
answers217
viewsA: Update item table with stock in any branch
Make sure the following suggestion meets your needs. -- código #1 v2 with Bloquear as ( SELECT Cod_produt from PRxLJ group by Cod_produt having max (Qtd_Saldo) = 0 and max…
-
3
votes2
answers404
viewsA: Get data from all days of the months in the query range
Here’s a solution: -- código #1 v2 -- define periodo de emissão declare @Hoje date, @UmAnoAtras date; set @Hoje= cast(current_timestamp as date); set @UmAnoAtras= dateadd(month, datediff(month, 0,…
sql-serveranswered José Diz 6,942 -
0
votes1
answer33
viewsA: Date choices
If there is a single row with the FLAG column containing "S" and another row containing the value "E", both meeting the filter defined in the WHERE clause, the code below returns what is requested.…
sql-serveranswered José Diz 6,942 -
1
votes2
answers1441
viewsA: Find regular expression record in mssql
In SQL Server there is no native implementation of regular Expression. What comes closer is the definition of pattern (Pattern), used in LIKE and in function SKATEBOARD. The way to store attributes…
-
0
votes1
answer1693
viewsA: Group and add to a query in SQL Server
One way to solve what you need is to build something like -- código #1 SELECT SF2010.F2_DOC, sum(SD2010.D2_TOTAL) as [TOTAL DA NOTA (R$)], max(demais colunas exceto D2_TOTAL) as nome da coluna from…
-
0
votes2
answers68
viewsA: Error 102 in Openquery
Excess of apostrophes in the WHERE clause. If the column v.product is also declared as string, substitute WHERE v.produto = '''''+@CodCai+''''' for WHERE v.produto = ''' + @CodCai + ''' I suggest…
-
3
votes3
answers2090
viewsA: How to add Count output from different tables in SQL Server?
Simple question but at the same time curious. There are some solutions. For example, in addition to the solutions proposed by rLinhares and Maniero, here is another: -- código #1 with C1 as (SELECT…
-
1
votes2
answers1338
viewsA: T SQL Merge Two Lines in One
Considering that the column Untitled2 will contain line ordering, for the same id value, here is suggestion using classic pivot: -- código #1 SELECT id, max(case when C1 = 1 then 1 end) as nC1,…
-
3
votes1
answer279
viewsA: What is the best type in SQL to use with Cryptocurrency?
To store monetary values one should avoid the type of data float (floating point). This goes for any language. As for the type of data money, works correctly, with 4 decimal places fixed. By example…
-
6
votes1
answer16562
viewsA: Regex inside SQL Server
Evaluate the following solution: -- código #1 v2 SELECT colunas from tabela where coluna like '[a-z][0-9][0-9][0-9][/_+-][0-9][0-9][0-9][0-9]'; If the pattern sought can be in any column position,…
-
0
votes2
answers322
viewsA: Procedure delete Join between tables
If a client can have more than one process, then it is necessary to be attentive to that detail. The following suggestion first notes all client processes for only after deleting table rows. --…
-
2
votes1
answer212
viewsA: Optimize module 10 SQL Server
Table variables are stored on disk, in the tempdb database, as well as the temporary tables. Then, it may be that low performance is caused by using the @delta table variable for calculations that…
-
1
votes1
answer135
viewsA: How to migrate data between tables referencing ID?
The OUTPUT clause adds lines at the specified destination; does not update lines. Since it is procedure that will be executed only once, and considering the characteristics of the column to be…
sql-serveranswered José Diz 6,942 -
1
votes1
answer279
viewsA: EXCEPT equivalence | INTERSECT & exists | not exists
The question of performance depends on several factors, such as information to be returned, presence of indexes that facilitate the processing of queries, existing and updated statistics, data…
-
3
votes2
answers979
viewsA: How to get value from a database column using C#
Here is a way to solve that involves deleting the rows of the ITEM_PEDIO table first, following the ORDER table to finally delete the client from the CLIENT table. -- código #1 -- cliente a apagar…
-
1
votes1
answer74
viewsA: Error in running Function SQL Server
In the code of your function, replace the line set @total = (select pr.vl_venda * i.qt_item for set @total = (select sum(pr.vl_venda * i.qt_item)…
-
3
votes2
answers428
viewsA: How to make a query that brings only the count of an uninterrupted sequence?
GAPS and ISLANDS Here is the solution that uses the principle of search for gaps and Islands. -- código #1 v4 with C as ( SELECT Pessoa_Id, Ano, Valor, ilha= (Ano - DENSE_RANK() OVER(partition by…
-
5
votes3
answers158
viewsA: Doubt about sql query
If all lines are true in my query, return all lines. If you have any false, you should not return any. There are some forms. Here’s one that uses the EXISTS function. -- código #1 SELECT…
-
0
votes3
answers1491
viewsA: How to use alter table by modifying an attribute to PK and FK?
So I have an Employee table, where your PK is this Cod_func. After I created this Seller table, I created the attributes manually, I did not put Cod_func from the Seller Table as PK, because this is…
-
0
votes2
answers1443
viewsA: GROUP BY with 2 tables
Repeated lines, when not the expected result, usually occur when the junction is incorrect or incomplete. If a process can have none, one or more progress information, it seems to me that the…
-
-1
votes1
answer711
viewsA: Check the frequency of records in the table
Considering the existence of a column indicating the date of entry of the vehicle into the workshop, one option is to count the number of different entry dates for each plate. -- código #1 v2 SELECT…
-
0
votes2
answers2282
viewsA: Error with Insert in SQL Server: The Conversion of a varchar data type to a datetime data type resulted in an out-of-range value
In order for the execution of the code to be independent of the SQL Server installation language, and as in the INSERT statement the dates are in the dd/mm/yyyy format, one option is to add the…
-
2
votes1
answer888
viewsA: SQL Developer does not run on Ubuntu
To use Oracle SQL Developer 17.3, you must first install the Java Development Kit (JDK) 8, as shown on the page Oracle SQL Developer 17.3 Release Notes. This documentation also states that If SQL…
-
2
votes1
answer200
viewsA: SQL - Search for the names of patients who had more appointments in January 2016
The list of tables does not identify which table is each, but I assume that the first table is the table of consultations (because it contains the date and value of the consultation) and the second…
-
4
votes2
answers8557
viewsA: How to run the INSERT script with one million lines in SSMS?
The table value constructor has limit of maximum number of lines; when this limit is exceeded, the error message 10738 is displayed. In the case in question, the information "INSERT instruction…
sql-serveranswered José Diz 6,942 -
1
votes2
answers320
viewsA: List tables in a single SQL Server query
Considering that in a battle a player may have at most one transgression recorded, here is suggestion: -- código #1 SELECT B.Map_Id, C.Player_Id, count(B.Id) as [Qtd batalhas], sum(C.Time_Played) as…
sql-serveranswered José Diz 6,942 -
-1
votes2
answers838
viewsA: Select picking up previous line
Here is a suggestion for the 2008 version of SQL Server: -- código #1 with Alerta99 as ( SELECT Data, Seq= row_number() over (order by Speed desc) from tbAlerta where alerta = 99 ),…
-
12
votes5
answers8920
viewsA: What is the difference between Visualg and Portugol?
I made contact with portugol in 1977, as support in the learning of structured algorithms. For this reason, I wondered the statement that "Portugol was formally defined in the 1980s". Now, how…
-
0
votes5
answers833
viewsA: How to calculate the date and shift code?
Although it seems a simple case, there is an additional complication that is the fact that the day of the launch should be set to the day before when the turn crosses day and the point was hit the…
-
0
votes3
answers775
viewsA: How can I make a copy of multiple tables in a new table using SQL SERVER?
Suggestions in this reply consider that a new table should be created and that the source tables are declared with columns in the same sequence. (1) It is possible to insert one table at a time…
-
0
votes5
answers2617
viewsA: Select last dates of each SQL month
In Filemaker, the function date(Month;day;year), by displaying 0 in the parameter day, returns the last day of the previous month. For example, date(2;0;2016) returns the last day of January 2016.…
-
0
votes2
answers113
viewsA: Join Tables for Tableau
It seems to me that the query can be mounted with the use of FULL OUTER JOIN type junction between the Initiatives and Goals tables, adding sequencing per pillar in each of these two tables. --…
sql-serveranswered José Diz 6,942 -
-1
votes2
answers1482
viewsA: SQL: Doubt in the Relationship between Tables
As for the PRICE, I must leave in the ITEM table or include also in the ITEM_PEDIDO table? Also include in ITEM_PEDIO table. The ITEMS table contains the prevailing price for each item. For each…
-
2
votes3
answers1110
viewsA: How to find "holes" in SQL Server tables?
Finding the intervals Follow two efficient solutions, obtained in the article Solving Gaps and Islands with Enhanced Window Functions. The first solution caters to various versions of SQL Server. --…
-
-1
votes2
answers337
viewsA: Add WITH query inside another query - SQL Server
Renan, here is the suggestion treating the cases of openings and cancellations, individually. A CTE for each type of case. -- código #2 --> informe datas inicial e final (formato dd/mm/aaaa)…
-
-1
votes3
answers640
viewsA: Grouping by day and by date period
Renan, here is the suggestion that first groups the lines that meet the requirements for only after supplementing the absences. The issuing period is informed at the very beginning of the code,…
-
6
votes3
answers124
viewsA: Selecting Active Batch
Here is the suggestion that uses the product table as a starting point. -- código #1 v3 declare @Hoje datetime; set @Hoje= cast(current_timestamp as date); -- with ctePreços as ( SELECT P.Id,…
-
45
votes5
answers60507
viewsA: DISTINCT and GROUP BY, what is the difference between the two statements?
1. Introduction Clarify the differences and applications of the two statements. The reference is the database manager SQL Server. 2. What appears in the documentation? 2.1 DISTINCT SELECT [ ALL |…
-
0
votes2
answers763
viewsA: select multiple columns group by 1 column
Evaluate -- código #1 with MaisRecente as ( SELECT colunas, Seq= row_number() over (partition by CPF_1 order by DATAEMISSAOAPURADA desc) from tabela ) SELECT colunas from MaisRecente where Seq = 1;…
-
-1
votes2
answers154
viewsA: How to remove Duplicate dates in sql?
What I observed is that there are no repeated lines, but rather that the type of junction used was a Cartesian product, which gave the impression that there were repeated lines. In addition, in the…
-
0
votes2
answers94
viewsA: Adds WITH query within another query
Evaluate the following code. -- código #1 v3 with cteUm as ( SELECT p.projid, tt.tartitulo FROM tarefa tt INNER JOIN projetos p ON p.projid = tt.projid LEFT JOIN tipo c ON c.tipid = tt.tartipid…
-
2
votes4
answers2013
viewsA: Find the best-selling item along with a particular item
I want to find out which item is the most sold along with the salty chicken. The solution proposed in this answer follows the logic defined by the author, using CTE (common table Expressions) to…
-
3
votes1
answer140
viewsA: Query - Bring 5 major records
Evaluate the following code. -- código #1 v3 with cteConsulta as ( SELECT count(distinct case when F.C08 = 3236 then F.ChamadoID end) as CB, count(distinct case when F.C08 = 3237 then F.ChamadoID…
-
1
votes2
answers1654
viewsA: Variable period date SQL Server
In this case, the variable @periodoInicial would have to start in 2017-07-26 and the variable @periodoFinal in 2017-08-25, and ending this period, the variables already changed to 2017-08-26 until…
-
3
votes1
answer4972
viewsA: SELECT in VIEW generates sub-query?
As stated in the BOL documentation regarding exhibitions (views) not indexed, "(...) a display does not exist as a stored set of data values in a database. The rows and columns of the data come from…