Posts by José Diz • 6,942 points
305 posts
-
4
votes2
answers229
viewsA: How to use the WHERE clause on top of a column made from row_number() over()
Yes, one way is through the use of CTE. -- código #1 with Sequenciado as ( SELECT *, seq= row_number() over (partition by colA order by colB) from tabA ) SELECT seq, colA, colB from Sequenciado…
-
0
votes1
answer67
viewsA: How to make a query in SQL that brings a count of some results
The cause of the error is that an aggregation function (SUM) was used within another aggregation function (COUNT). Evaluate: -- código #1 v3 ;with Soma_Indisp as ( SELECT sum (case when len ([TEMPO…
-
0
votes1
answer70
viewsA: Add table B row to table A based on conditions
If a line occurs in B and not in A, add it to A. You solve this with set theory. Just do A U (B - A) which is implemented in SQL by -- código #1 SELECT distinct A1, A2, A3, A4 from B minus SELECT…
-
1
votes1
answer108
viewsA: SQL - Logica de Consulta
I want the query return me the days not filled by users, disregarding Saturdays and Sundays, Jorge, the suggestion is that you store in the table Preenchimento only events that occurred and that at…
sql-serveranswered José Diz 6,942 -
1
votes1
answer56
viewsA: View most current sql server 2008 record
Here’s a classic solution: -- código #1 USE banco; with UltMov as ( SELECT *, seq= row_number() over (partition by cli_forn order by dt_mov desc) from tb_movimentos where status_clifor = 'C' and…
-
1
votes1
answer43
viewsA: Problem with Join and sub-consumption
There are some ways to get the result. Considering the code you posted, it seems to me that just review the calculation of the request totalization. The subconsultation SELECT OrderID,…
-
5
votes2
answers335
viewsA: How to return a special character to a query
The character referred to is not called "simple quotation mark" but the apex, although in everyday life we confuse it with the apostrophe. In graphic terms there is a subtle difference between apex…
-
1
votes1
answer267
viewsA: pass external parameters to sqlcmd via batch
Rafael, in SQL Server in the FROM clause it is not possible to use variables for the table denomination (or display); only literal. See documentation from from clause. However, what you request may…
-
2
votes2
answers115
viewsA: Select all customers and check if you are carrier and supplier
In the description of the problem it is stated that the link between the tables of carriers and customers is through the pair {Tracod, Clicod} and that the link between the tables of suppliers and…
-
0
votes2
answers262
viewsA: Set default value when creating an SQL table
CREATE TABLE USUARIO ( ID NUMERIC IDENTITY, LOGIN VARCHAR(25) UNIQUE NOT NULL, SENHA VARCHAR(15) NOT NULL, DTEXPIRACAO DATE NOT NULL default '19000101', CONSTRAINT PK_USUARIO…
-
1
votes1
answer283
viewsA: Select Electronic Point Record (SQL)
From the desired result it seems to me that the date column to be used is pDTAPO. Here is a suggestion to evaluate: -- código #1 SELECT pFUNC, pDTAPO, min(case when right(pTM,1) = 'E' then pHORA…
-
2
votes3
answers3659
viewsA: In Trigger we have INSERTED and DELETED. Why not "UPDATED"?
(...) how I would take the ID of the record being made the UPDATE, and other data to filter some values and make the condition whether the other UPDATE will be performed or not Since the [TG_TEST1]…
-
3
votes1
answer619
viewsA: Distribute values in column, SQL
One option is to use the sort function NTILE to divide the result of the query into blocks, numbering them sequentially, and then associating each value to a block. The column was used as an example…
-
2
votes1
answer321
viewsA: Field condition with subquery
Evaluate the use of CTE to make code readable and easy to maintain. -- código #1 v3 ;with ColetaDados as ( SELECT campo1, t3.quantidade, t2.distancia, (SELECT TOP (1) f.valor FROM frete as f WHERE…
-
5
votes1
answer2755
viewsA: GROUP BY in query with column by subquery
Maybe the use of CTE will solve and even make the code more readable. -- código #1 with CadaCarro as ( SELECT A.CARRO, (SELECT top (1) B.VALOR from tab2 as B where B.DATA <= A.DATA order by…
-
2
votes4
answers4389
viewsA: Select from previous months
To get the data for just one month, try -- código #1 -- informe número de meses a retroagir declare @RetroMes tinyint; set @RetroMes= 1; -- calcula período mensal de consulta declare @DataInicial…
-
3
votes3
answers157
viewsA: Show comments table
There are a few ways to get the extended properties of a table. One of them is through the function fn_listextendedproperty(): -- código #1 SELECT sys.objects.name as [Nome da tabela], EP.name as…
-
2
votes1
answer82
viewsA: How do I turn some rows into columns?
I have this database of questions and answers And I need to create a view that the questions and answers are arranged that way. Lucas, please tell us which database manager you are using. As the…
-
3
votes2
answers730
viewsA: In an SQL query, bring line values in a single line, field
I need to do a query that brings values of 2 lines in one Assuming there is a column seq indicating the order of the lines, by value of id, and that id uniquely identify each pair, here’s a…
-
2
votes2
answers69
viewsA: Match table data on Linkedserver servers
A typical replication solution involves using two commands in the same transaction, one to update existing lines in the remote table and one to add the missing lines in the remote table: -- código…
-
0
votes1
answer71
viewsA: Take only the values that are different in the query
Felipe, if I understood correctly what you need is to know which releases have divergence between the sum of debts and credits. If this is the case, evaluate if the code below meets you. -- código…
-
2
votes2
answers235
viewsA: Case in Where using SQL Server
If the @Option variable is empty, it cannot execute (('{Option}' = ’S') AND Nfdataemis.... If it is S or N it executes (...) Option is a variable that will list the records by Date. If it is S it…
-
0
votes3
answers464
viewsA: drop table with temporary table giving error
In the OBJECT_ID function, for temporary tables it is always necessary to cite the name of the database tempDB: -- código #1 IF Object_ID(N'tempDB..#tempCID', N'U') is not null DROP TABLE #tempCID;…
-
0
votes3
answers658
viewsA: Compare 2 field-to-field tables
I need to compare two sql tables with equal fields but which may have different contents or one have more or less records than the other. (...) would like to show only the (s) field(s) different(s)…
-
2
votes2
answers121
viewsA: Place row data in columns
Here is the suggestion that uses the classic pivot to generate the 4 columns. -- código #1 v3 with R070ACCseq as ( SELECT numcad, datacc, horacc, seq= row_number() over (partition by numcad,…
-
2
votes2
answers85
viewsA: Add rows with the same ID in additional columns
Use the classic pivot: -- código #1 SELECT ID, max(case when SEQUENCIA = 1 then DATA end) as [DATA 1], max(case when SEQUENCIA = 1 then PROFISSAO end) as [PROFISSAO 1], max(case when SEQUENCIA = 2…
-
5
votes1
answer662
viewsA: SQL Server Data Dictionary
In Oracle we have a data dictionary that shows all the options of system views, (...) What is the equivalent in SQL Server to list all system views? This form is a particular implementation of…
-
0
votes2
answers428
viewsA: Cursor problem: SQL Server
In some languages there is the question of scope, in which variables declared within a block (usually even BEGIN END) only exist within the block. But in the case of T-SQL there is no such feature.…
-
2
votes4
answers949
viewsA: Day and month comparison on SQL Server
Considering that the Date birth column is declared as datetime, the suggestion is that the WHERE clause: WHERE cast(dateadd(year, -year(cli.DataNascimento) % 1900, cli.DataNascimento) as date)…
-
1
votes3
answers255
viewsA: SQL bring result with repeated column numbers all only the largest
What I understood is that for each request you must return the line with the latest version. There are some ways to get this result; here is one of them: -- código #1 with PedidoClassificado as (…
-
1
votes2
answers425
viewsA: I need to relate two tables in order to bring me a result within the conditions I want
When cross-referencing the data from two tables I need to assign a spot to that patient who came in contact first. And this vacancy can no longer be attributed to any other patient. The ideal is to…
-
3
votes1
answer477
viewsA: Error trying to add time field(varchar) sql server
This initial suggestion, considering the information provided so far: -- código #1 v3 declare @Mes int; set @Mes= 4; with normalizaHMS as ( SELECT lj, case len(saldo) - len(replace(saldo, ':', ''))…
-
4
votes3
answers4552
viewsA: Use VARCHAR(255) for all fields or choose the best size for each field?
This answer is specific to SQL Server. In the SQL Server documentation for the data type sweep(n) "Storage size is the actual size of the entered data + 2 bytes". That is, if in a given row the size…
-
0
votes2
answers458
viewsA: Sum in a Pivot Table
This is the solution that employs the classic pivot, through the GROUP BY clause: -- código #1 SELECT [ID Rep], sum(case when Item = 'Binder' then 1 else 0 end) as Binder, sum(case when Item =…
-
0
votes2
answers182
viewsA: Higher value comparing lines
Another solution: -- código #1 with tabela2 as ( SELECT *, Seq= row_number() over (partition by NOME order by NUMERO desc) from tabela ) SELECT ID, NUMERO, NOME, MODELO from tabela2 where Seq = 1;…
-
0
votes2
answers128
viewsA: How to separate records into blocks for parallel processing?
The defined solution should consider that new messages can be received in the table at the same time that the process of sending messages is triggered or is running. That is, it is necessary to…
-
1
votes2
answers875
viewsA: About rowspan
Try: <p align="center"> <table border="1" cellspacing="0" cellpadding"2"> <th colspan=3>Tabela de Caracteristicas</th> <tr> <td rowspan=4>Philips</td>…
-
0
votes1
answer40
viewsA: Use LEAD to catch the next date
Considering the information provided so far, here is a suggestion: -- código #1 with Base2 as ( SELECT *, datediff(day, lag(dat_leitura, 1) over (partition by inscricao order by competencia),…
-
0
votes3
answers1155
viewsA: Bring more than one line into a Subquery Sql Server
I need to assemble a query in which I have the values of T1 complemented by the values of T2 using as a comparative factor the field of Data and PF The solution, as already commented in other…
-
0
votes2
answers49
viewsA: How do I count the amount of items in a repeated field?
Mysql and SQL Server tags are marked. Follows solution for SQL Server, but the use of DISTINCT is also valid for Mysql. -- código #1 SELECT count(distinct CtrlCargaNum) as Qtd from CTRL_CARGA where…
-
3
votes4
answers27603
viewsA: SQL - Calculate percentage in the same select
There are some ways. CTE -- código #1 v3 with totVWDADOSFPY as ( SELECT Data, SUM(CASE WHEN Status = 'OK' THEN 1 ELSE 0 END) as Passed, SUM(CASE WHEN Status = 'NOK' THEN 1 ELSE 0 END) as Failed,…
-
0
votes1
answer3477
viewsA: Error: Error Converting data type nvarchar to Numeric
Evaluate the following suggestion: -- código #1 v2 SELECT salario, convert(decimal(10, 2), salario) as [SALÁRIO] from dbo.rendimentos where Isnumeric(salario) = 1; However, if the column values…
sql-serveranswered José Diz 6,942 -
1
votes2
answers116
viewsA: How to return a specific column as an element with FOR XML AUTO clause
This is a suggestion that comes closer to what you request. -- código #1 v3 select 1 as Tag, 0 as Parent, T1.Id as [Table1!1!Id], T1.Nome as [Table1!1!Nome], NULL as [Table2!2!Id], NULL as…
-
1
votes2
answers220
viewsA: Search Dates with Difference Defined in SQL Server
By the result example it is deduced that the query must return all rows where the date difference between the current line and the previous or later line is within the range. Here is the suggestion…
-
0
votes2
answers264
viewsA: Migrate EXTRACT(YEAR_MONTH,data) from Mysql to SQL Server
Here are suggestions that try to simulate the construction used in Mysql: -- código #1 ... where 201705 between (year(treinamentos.data_inicio_treinamento) * 100 +…
-
3
votes2
answers2984
viewsA: Surrogate Key and Natural Key
There’s a great article on natural keys, primary keys, replacement keys (surrogate Keys) etc in Primary Key Primer for SQL Server. Note that this conceptualization is independent of the DW context.…
-
0
votes2
answers1707
viewsA: Table structure in user questions and answers template
The data modeling should be independent of the database manager that will be used. Only in the step of physicalization is that the database structure is defined (tables, columns, primary keys,…
-
1
votes1
answer1249
viewsA: Create a Relationship Table (N:N) that relates to a Normal-Table and another Relationship-Table (N:N) in SQL Server 2014 Management Studio
In declaring the restriction FK_tblInsumosDaComposicao_tblFornecedorInsumo columns need to be declared in the same order. Where is CONSTRAINT FK_tblInsumosDaComposicao_tblFornecedorInsumo FOREIGN…
-
0
votes2
answers1084
viewsA: How to make a dynamic update to all database tables for a specific column
Evaluate the use of the sp_MSforeachtable procedure. -- código #1 USE nome_banco; EXECUTE sp_MSforeachtable 'UPDATE ? set coluna= valor;' In the above code replace coluna by the name of the column…
-
0
votes3
answers2912
viewsA: Create Trigger with update setando field through a function
-- código #1 CREATE TRIGGER dbo.descNF on dbo.tabela1 after INSERT AS begin -- verifica número de linhas a tratar declare @NL int; set @NL= (SELECT count(*) from (SELECT top (2) * from INSERTED) as…