Posts by imex • 1,387 points
52 posts
-
-1
votes1
answer23
viewsA: Operation of datiff in SQL 2014
Try this suggestion using the operator Between: where Data between dateadd(dd, -60, cast(getdate() as date)) and getdate() I hope it helps
-
1
votes1
answer33
views -
1
votes2
answers52
viewsA: Delete all items that have at least one Null value
Arthur, I think you can use not exists. Here is a suggestion for testing: select contrato.IdContrato as ID, parcela.ValorParcela as Parcela left join parcelas on parcelas.IdContrato =…
-
0
votes2
answers90
viewsA: SQL - Integer and Decimal Number Conversion
I don’t know if I understand the question correctly, but try changing the condition used in IIF as follows:: ,IIF(qtfornecedor = floor(qtfornecedor), In this suggestion it is being checked whether…
-
1
votes1
answer53
viewsA: Insert values x into table A if exist on table B
Good morning, Here is a suggestion for testing: Insert into HeartRate (TimeStamp, Value, MacAddress) Select top(1) @TimeStamp, @Value, @MacAddress From banda where MacAddress = @MacAddress Select…
-
0
votes1
answer51
viewsA: LEFT JOIN with more than one condition in ON - SQL Server
Good night, I think the way you put it won’t work because IIF expects one value for when the condition is true and another for when it isn’t, and not another condition. And the same goes for Case.…
-
0
votes2
answers48
viewsA: Mysql - Add values from a column and also bring value from each row
I think you can use "group by" with "with rollup" to get that sum at the end. Ex: SELECT SUM(val_resgate) AS resgate FROM ETAPA WHERE fk_servico=39 GROUP BY id_etapa WITH ROLLUP I hope it helps…
-
1
votes1
answer68
viewsA: CONDITIONAL ROW NUMBER
Here is a suggested test using a recursive CTE to get the "Row": WITH CTE_RN AS ( SELECT PLACA, DATHOR, LATITU, LONGIT, ROW_NUMBER() OVER(PARTITION BY PLACA ORDER BY DATHOR) AS RN FROM POSICOES ),…
-
1
votes1
answer42
viewsA: I would like to type in Edit and the values of the letters appear in theTMemo using this function format
Follow a code suggestion for studies and tests: function m(x:string): string; var y: string; w: integer; begin Result := ''; for w := 1 to Length(x) do begin if (x[w] = 'A') or (x[w] = 'a') or (x[w]…
-
1
votes1
answer61
viewsA: Date sum with condition?
Here is a suggested test using a recursive CTE: WITH CTE_RN AS ( SELECT DATA, LATITU, LONGIT, ROW_NUMBER() OVER(ORDER BY DATA) AS RN FROM TABELA_LATLONG ), CTE_REC AS ( SELECT DATA, LATITU, LONGIT,…
sql-serveranswered imex 1,387 -
0
votes1
answer31
viewsA: How to restrict minutes in a time interval using Mysql
Here is a suggestion for testing: AND dd.hour * 10000 + dd.minute * 100 + dd.second between 80000 and 110000 I hope it helps
-
3
votes1
answer41
viewsA: MYSQL Operation subtraction fields from the same table with different WHERE?
Here is an example for testing where the Case command within the Sum function is used to multiply the value by -1 when the type is spent, and thus subtract: select sum(valor * case when tipo =…
-
0
votes1
answer58
viewsA: How to count the amount of distinct occurrences in a BD and return
If you are using Postgresql 9.5 or later you can use the Count function with a "window Function" to get the amount per author: with CTE_Count as ( SELECT l.editora, l.autores, count(*)…
-
0
votes1
answer49
viewsA: SQL - How to bring only rows (ID and Date/time) with less than 1 hour
Here is a suggested test using the Lag function to get a column value from a previous row: select t.* from Tabela as t where datediff (minute, lag(DataHora, 1, t.DataHora) over(partition by id_omega…
-
1
votes2
answers54
viewsA: How do I select some lines of different audiences?
Juliana, follows a suggestion for tests using a CTE to obtain the number of lines of the groups to then use this amount in the Where clause: with CTE_Count as ( select count(case when Grupo = 2 then…
-
0
votes1
answer32
viewsA: Doubt in SELECT when creating a VIEW
Good afternoon, I believe you can use the function Coalesce to get the value of the column that is populated between columns Cpf and cnpj: SELECT coalesce(tabela1.cpf, tabela1.cnpj) as cpfcnpj FROM…
postgresqlanswered imex 1,387 -
0
votes2
answers40
views -
1
votes1
answer77
viewsA: Select TOP 3 of every month
Here is a suggestion to test using the Row_number function to number the lines per month and in descending order of quantity: with CTE_RN as ( select mes, rubrica, count(*) qtd, row_number()…
-
4
votes3
answers283
viewsA: How to delete duplicates between multiple tables
Gabriel, follows a suggestion using 3 Deletes, one for each table: delete t from itens_banco as t left join itens_banco as ba on t.numero_serie = ba.numero_serie and t.id > ba.id left join…
-
2
votes1
answer73
viewsA: Relate groups among Researchers with at least 2 common areas in MYSQL
I did not test, I may have some errors, but follow a suggestion for tests: with CTE_UsuarioAreas as ( select ra1.idAreaAtuacao as idAreaAtuacao1, ra2.idAreaAtuacao as idAreaAtuacao2,…
-
1
votes1
answer269
viewsA: How to convert Datetime to timestamp? (SQL SERVER)
Try some tests with the script below: declare @DataIni datetime; declare @Tempo int; declare @Data datetime; set @DataIni = dateadd(hour, datediff(hour, getutcdate(), getdate()), '19700101'); set…
-
0
votes2
answers54
viewsA: Query duplicate numeric field in sql server
Good afternoon, Here is a suggestion for testing using the function Count with the trunks Over and Partition By to obtain the number of rows with the same value in the columns total value, numpdv…
-
0
votes2
answers47
viewsA: sql search filter - exception
Junior, here is a suggestion for tests using the claushula Having with the functions Min and Max: SELECT P.COD_ALUNO, P.NOME, P.ID_TIPO_PESSOAS FROM TB_PESSOAS P INNER JOIN TB_VOUCHER V ON…
-
1
votes1
answer45
viewsA: How to make a select where the order "Group" is shown after "Child Groups"
Here is a suggested test using a recursive CTE: with CTE_Rec as ( select idGrupoProduto, grpDescGrupo, grpGrupoProdutoPai, cast(grpDescGrupo as varchar(1000)) as DescOrdem from GrupoProduto where…
-
1
votes1
answer38
viewsA: how to consult database informed by variable?
I think in this case you will have to use a dynamic query: declare @SQL nvarchar(max); set @SQL = N'select A.name from [' + @nomeBanco + '].sys.procedures as A'; execute sp_executesql @SQL; I hope…
sql-serveranswered imex 1,387 -
1
votes2
answers74
viewsA: Mysql - Catch minor record in a 1:N ratio
Good morning, André, from what I’ve seen if you’re using Mysql 8.0.14 or later you can use a Side derived table: SELECT produto.titulo, mod.sku, mod.preco_de, mod.preco_por FROM produto, LATERAL (…
-
1
votes1
answer51
viewsA: Concatenate values with condition (case)
Good night, Here is a suggestion for testing: WITH CTE_COUNT AS ( SELECT IDPS AS ID, IDAREAINTERESSE, COUNT(*) AS QTDE FROM INSCRITO WHERE IDAREAINTERESSE IN (51, 52, 53) GROUP BY IDPS,…
-
2
votes1
answer120
viewsA: Subconsulta returns more than one value
Good morning, Almino, if you want to get the highest Count of Emailpromotion per person, try doing some tests this way: WITH CTE_Count AS ( SELECT FirstName, COUNT(CASE WHEN EmailPromotion = 0 THEN…
-
2
votes1
answer21
viewsA: How to select records that have no relationship between 3 tables
Lucas, follow a suggestion using a Cross Join between the tables of companies and users to generate all possible combinations between these tables, and a filter with the operator Exists to obtain in…
-
1
votes1
answer73
viewsA: Doubt with LEFT or INNER JOIN and WHERE SQL Access
Fabio, the value of the Id_clifor column of the table tbl_special precos_will be null when there is no row in this table for the product since it is being used Left Join, so try leaving Where as…
-
1
votes1
answer31
views -
1
votes1
answer103
viewsA: How to search for data from one column in another column in SAS using Proc-SQL?
If the missing digits are the 2 on the right, try using the Left function to get only the 12 digits on the left: ON A.CPF_CLIENTE = LEFT(B.NUM_CPF_CNPJ_CLI, 12) I hope it helps…
-
6
votes1
answer84
viewsA: What would be the code to simulate the inverse of the tab
Do you want to simulate Shift+Tab? Try it this way: keybd_event(VK_SHIFT, 0, 0, 0); // pressiona o Shift keybd_event(VK_TAB, 0, 0, 0); // pressiona o Tab keybd_event(VK_TAB, 0, KEYEVENTF_KEYUP, 0);…
-
1
votes2
answers141
viewsA: Procedure with CASE in the WHERE clause
Jessica, as far as I know it is not possible to put a condition inside Case in SQL Server, I believe it is possible to only return a value. For the case you posted, I suggest the version below,…
-
0
votes1
answer42
viewsA: Do not bring duplicates with a certain condition in a SELECT
Fernando, I think you can leave Group By with the column res and use Case to add another column that does not duplicate if the GRU column is different from 212. Example allowing the column emp does…
-
1
votes1
answer43
viewsA: Is it possible to use the return of a query with GROUP_CONCAT in the WHERE clause?
Lucas, I think it would be more common to return users' Ids in separate lines in CTE to then merge CTE with the user table, but considering your example I believe that CTE is not necessary because…
-
2
votes2
answers84
views -
1
votes2
answers205
viewsA: How to use IN in Filter from a Clientdataset?
Good morning, Diego, there are some particularities according to the type of Dataset, and some options are only available for some Datasets. If you notice correctly, the excerpt you quoted is in…
-
2
votes1
answer33
viewsA: Multiple condition C#
Paul, try to change the operator Or (||) to And (&&). As you are comparing the same element with more than one value, it will surely always be different from at least one of them. I hope it…
-
1
votes2
answers133
viewsA: How to copy a. txt encoded file to another decoded file using Assignfile?
Nelson, if you want to create a file with the content displayed by Showmessage, then I think you should create that file at the beginning, before looping, and run Writeln at the same point where you…
-
0
votes1
answer69
viewsA: MYSQL returns OK
I agree with the comment regarding the return of more than one date in the subselect, and for the call count to be made, which I believe is the goal, I think you should refer to the working day…
-
2
votes1
answer42
viewsA: Query a column using the output from another column
If the database you are using supports CTE (Common Table Expression), I believe it is an interesting option because it facilitates the visualization of the SQL statement: with aux as ( SELECT…
-
1
votes1
answer51
viewsA: Add separate lines of a child table with values of a parent table without duplicating the values of the parent table
Thiago, follow a suggestion for testing using the operator Apply to count the documents of the sale, and to divide the value between the documents: select ca.Id, ca.Tarifa + ca.Taxa + v.OutroValor /…
-
1
votes1
answer279
viewsA: Sql Server - Time Conversion (HH:mm) to Decimal
Richard, follow a suggestion for testing, make sure the expected results are obtained: DECLARE @HorasAtendidas VARCHAR(5) = '60:59', @HorasAtendidasInt int, @CargaHoraria DECIMAL(18, 2); SET…
-
1
votes2
answers95
viewsA: Subconsulta with MIN AND MAX together
Here is a suggestion using a CTE with Min and Max functions, for later use in a Join: with CTE_M as ( select MIN(Size) as SizeMin, MAX(Size) as SizeMax from Production.Product ) select p.Name,…
-
1
votes2
answers59
viewsA: Select record between the first day of month X and the last day of month X
Brenno, follow a suggestion using the function STR_TO_DATE to convert the parameters month and year from string to date, and the function LAST_DAY to get the last day of the month: SELECT * FROM…
-
1
votes1
answer44
viewsA: Doubts about if clause
For this type of situation you can use the Case command. Ex: where case when D1_NFORI NOT LIKE '000%' then CONCAT('000', D1_NFORI) else D1_NFORI end I hope it helps…
-
1
votes1
answer120
viewsA: System.Data.Sqlclient.Sqlexception: 'Incorrect syntax near 'messaging'. '
I believe the error is occurring in the Updatejson method, where the Myupdate parameter is added in the SQL statement without being delimited by quotes, although I think the best option would be to…
-
2
votes1
answer92
viewsA: Average and count for each SQL product
Try a test by doing a Join with a derived table where the average is calculated. Ex: UPDATE product INNER JOIN ( SELECT product_id, AVG(product_review.rating) as review_rating FROM product_review…
-
1
votes1
answer50
viewsA: Add a column where its values are already the result of another sum
Good afternoon, I have no experience with Mysql, but if the version you are using allows you to use CTE (Common Table Expressions), you can try it in a similar way to the one below: with CTE_Origem…