Posts by Sorack • 27,430 points
864 posts
-
2
votes1
answer1501
viewsA: Uncaught Error: [$injector:modulerr] for angular-material.js
The version of his angular-material imported was very outdated (0.11). I performed the correct import for your code to work as below. The use of the module in the creation of its controller was…
-
0
votes2
answers1525
viewsA: Browse previous record - SQL Server 2012
You can use a WITH to sort by request and link to the previous and the next. WITH timeline (SolID, ChamadoIntegradoID, Ordem, VencimentoPausado) AS ( SELECT s.SolID, s.ChamadoIntegradoID,…
-
2
votes2
answers94
viewsA: Adds WITH query within another query
If you want to use the first concatenation query to return the records of the second you would have to do the following: WITH tabela (projeto, operadora, compdesc, usuuf) AS ( SELECT pp.projdesc,…
-
3
votes1
answer69
viewsA: Doubt query - SQL Server 2012
I created an example with a variable table based on the result of your first query: DECLARE @tabela TABLE(id INT IDENTITY, projeto VARCHAR(100), operadora VARCHAR(100)); INSERT INTO @tabela SELECT…
-
4
votes1
answer55
viewsA: Multiple Fault Handling in Angular
You can use the method $q.all to solve an array of promises: var promessas = []; promessas.push(promessa1); promessas.push(promessa2); promessas.push(promessa3); $q.all(promessas).then(function() {…
-
5
votes4
answers2013
viewsA: Find the best-selling item along with a particular item
You can use the aggregate function SUM grouping by product only those items that have note together with the description "salted chicken": SELECT TOP(1) p.descricao, SUM(vi.qtde) AS quantidade_total…
-
3
votes1
answer219
viewsA: Query in date column with current week filter
Use the function YEARWEEK together with the CURDATE: SELECT * FROM tabela WHERE YEAR(data) = YEAR(CURDATE()) AND YEARWEEK(data, 1) = YEARWEEK(CURDATE(), 1) YEAR Returns the year for date, in the…
-
5
votes3
answers1206
viewsA: Statement "Return" does not work within foreach. Why?
The return works only for the scope of the function being called, in which case is the forEach. To interrupt it you must make one for conventional: for (var i = 0; i < $scope.items.length; i++) {…
javascriptanswered Sorack 27,430 -
5
votes2
answers1634
views -
6
votes1
answer438
viewsA: How to join 2 SQL queries into a single one (one is a mean value calculation and another search criteria with BETWEEN
Use the clause NOT EXISTS in the second query using the values of the first: SELECT AVG(virtual.valor) AS valor, virtual.id FROM (SELECT fp.valor, (6371 * ACOS(COS(RADIANS(-23.5427450)) *…
-
1
votes2
answers63
viewsA: SQL query to identify available hourly providers
Use the clause NOT EXISTS: SELECT p.* FROM prestador p WHERE NOT EXISTS(SELECT 1 FROM prestadores_pedidos pp WHERE pp.id_prestador = p.id AND (pp.hora_entrada BETWEEN '2017-08-10 08:00:00' AND…
-
2
votes2
answers1654
viewsA: Variable period date SQL Server
If you are using the 2012 or higher version of SQL Server the following code will meet the specification: DECLARE @dia INT; DECLARE @inicio DATE; DECLARE @fim DATE; SET @dia = 26; SET @inicio =…
-
2
votes3
answers55
viewsA: I tried a Union consultation but did not get the expected result
You have an extra parenthesis in your JOIN with the table pessoa. It’s like this: ... LEFT OUTER JOIN pessoa AS vendedor ON vendedor.cd_pessoa = F.cd_pessoa_fun) ... The correct is: ... LEFT OUTER…
-
4
votes8
answers48170
viewsA: How to calculate a person’s age in SQL Server?
The most accurate method of calculating age no SQL Server is to consider the day of birth and the base day of the calculation and calculate the difference of years with DATEDIFF. The function below…
-
1
votes4
answers2146
viewsA: SQL - Draw random lines
One solution is to use a subquery with ROW_NUMBER using PARTITION BY by the column you want to be the grouping and sorting by NEW_ID which will ensure randomisation: SELECT x.* FROM (SELECT…
-
3
votes2
answers50
viewsA: doubt with query in sql
Use the clause EXISTS together with the NOT SELECT * FROM vendedor t WHERE NOT EXISTS(SELECT 1 FROM vendedor t2 WHERE t2.cargo = t.cargo AND t2.nome <> t.nome AND t2.salario > t.salario);…
-
2
votes1
answer494
viewsA: Doubt - Query (Do not bring a particular record)
You can use the clause EXISTS together with the NOT to discover customers who have not called unlocking: SELECT DISTINCT * FROM Tarefa T WHERE T.TarTitulo = 'Bloquear Cliente - Inadimplente' AND…
-
0
votes1
answer706
viewsA: Split date ranges into weeks
You can use the clause WITH to recursively obtain the dates for the weeks desired: WITH semanas AS (SELECT 1 AS ordem, CAST('2017-07-01' AS DATE) AS inicio, DATEADD(DAY, -1, DATEADD(WEEK, 1,…
-
3
votes2
answers7633
viewsQ: How to format a full date?
How to convert a Date for the full date? I would like to show today’s date in the following format: Thursday, 20 July 2017 The example code you would like to complete to get the above result in a…
javascriptasked Sorack 27,430 -
1
votes1
answer134
viewsA: How to return driver names in SQL table?
You can use the clause NOT EXISTS to verify the occurrences for that pilot in tests in Brazil: SELECT p.nome FROM piloto p WHERE NOT EXISTS(SELECT 1 FROM resultado r INNER JOIN prova p ON p.id =…
-
1
votes1
answer3060
viewsA: Error - Query: The sub-query returned more than 1 value
The problem is that your subquery to return the field faturado is returning more than one value. You can use a DISTINCT if it ensures that the outcome of query that is correct: ... (SELECT DISTINCT…
-
3
votes2
answers6058
viewsA: How do I clean fields with angular?
You can clear the object as follows, so the ng-model will mirror it to the view: (function() { angular .module('MinhaApp', []); angular .module('MinhaApp') .controller('MeuController',…
-
5
votes2
answers74
viewsA: how to make this query sql
You just need to use the JOIN to link between tables as below: SELECT COUNT(n.codigo) FROM cliente c INNER JOIN notafiscal n ON n.cod_cliente = c.codigo WHERE c.municipio = 10;…
-
1
votes2
answers505
viewsA: how to specify values to sort sql server
You can create an order column with CASE: SELECT CASE t.campo WHEN 'DIAMANTE' THEN 0 WHEN 'OURO' THEN 1 WHEN 'PRATA' THEN 2 WHEN 'BRONZE' THEN 3 END AS ordem FROM tabela t ORDER BY 1…
-
1
votes2
answers4957
viewsA: How to search records from the previous month?
You can use the following syntax in your WHERE: SELECT SUM(DIFERENÇA) AS mesanterior FROM TOTALIZADOR WHERE NID = 252 AND DATEPART(MONTH, datahora) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE()))…
-
3
votes3
answers873
viewsA: Do not return a field value when it is zero
Use the clause CASE: SELECT CASE t.valor WHEN 0 THEN NULL ELSE t.valor END AS valor FROM tabela t CASE The CASE expression is used to evaluate various conditions and return a unique value for each…
-
1
votes2
answers311
viewsA: Bring the count of the word "DATA" from a column calculated in SQL Server
Use the GROUP BY with the function COUNT: SELECT CONSCM.CAM_PF AS CAM, COUNT(CASE WHEN ([CAM_DT_REGISTRO] < LAG([CAM_DT_REGISTRO],1) OVER (PARTITION BY [CAM_PF] ORDER BY [CAM_CD] ASC)) OR…
-
2
votes1
answer506
viewsA: Insert the result of a Procedure into a new SQL table
You just need to put the INSERT before the EXECUTE: ... SET @sql_insert = 'SELECT ''' + @database_name + ''', ''' + @table_name + ''', ''' + @elemento + ''', count(*) as n_foto, FORMAT…
-
1
votes1
answer152
viewsA: How to allocate transactions or group by day?
You can use the WITH to generate the available days of the month and link them with your query original: WITH dias AS ( SELECT CAST('2017-05-01' AS DATE) AS dia, 1 AS nivel UNION ALL SELECT…
-
0
votes2
answers284
viewsA: Query that only returns the result if a field is in another table
You can perform the query using the clause HAVING along with a JOIN on the table tbl_extras: SELECT tc.id FROM tbl_carros tc INNER JOIN tbl_extras te ON te.id_car = tc.id_car WHERE te.extra IN…
-
3
votes2
answers2375
viewsA: Sum the total value of two columns, subtract, and return even if negative Mysql
The problem is that if you don’t have revenue the value returned will be NULL. Use the function IFNULL to get around: SELECT IFNULL(FORMAT(SUM(receitas_dia), 0) - IFNULL(SUM(IFNULL(despesas_dia,…
-
0
votes1
answer632
viewsA: SQL to check skipped number
You can remove the letters from the code and then compare it to the same table using NOT EXISTS: SELECT codigo FROM tabela t WHERE NOT EXISTS(SELECT FROM tabela t2 WHERE…
-
2
votes3
answers5803
viewsA: Draw numbers without repeating in javascript
You can insert the numbers from 1 to 16 within a array and use the method sort of array randomly sorting with the function Math.random()); function _sortear(quantidade, maximo) { var numeros = [];…
javascriptanswered Sorack 27,430 -
1
votes1
answer2401
viewsA: How to convert a varchar type from a column to datetime within a view using SQL?
Everything will depend on the format of your date. With format 101 you are specifying which format is mm/dd/aaaa. The code below returns the date 21/06/2017: DECLARE @data varchar(10) =…
-
7
votes3
answers12428
viewsA: What’s the difference between Cast and Convert?
CONVERT is a specific function of SQL Server and CAST is standard ANSI. There is a conversion table that can be used to decide whether or not to use some conversion function for each case: Following…
-
1
votes1
answer105
viewsA: How to find a word in 10 tables in Mysql
One way is to use the clause UNION and join search results: SELECT 'TABELA 1' AS origem t1.campo1, t1.campo2 FROM tabela1 t1 WHERE t1.campo1 like '%texto%' OR t1.campo2 like '%texto%' UNION SELECT…
-
1
votes2
answers33
views -
2
votes2
answers178
viewsA: Group data from a table
whereas his SGBD be the SQL Server you can gather the data in a subquery and carry out the PIVOT on top of that data: SELECT a.alunonome AS aluno, m.materianome AS disciplina, y.bim1, y.bim2,…
-
0
votes3
answers193
viewsA: Select only first place in the first half - SQL Server Code
You can obtain such information by linking the information with JOIN and restricting in WHERE: SELECT Nm.Nome, COUNT(epp.PosicaoFinal) AS quantidade FROM Prova P INNER JOIN EquipaParticipaProva epp…
-
0
votes1
answer69
viewsA: SQL Server Code for Question
Just use a COUNT with DISTINCT grouped by Marca: SELECT car.Marca, COUNT(DISTINCT epp.IdPiloto) AS quantidade FROM carro car INNER JOIN EquipaParticipaProva epp ON epp.IdCarro = car.IdCarro GROUP BY…
-
0
votes1
answer699
viewsA: Sum hours worked Mysql
You can group by formatted date and then use the function UNIX_TIMESTAMP to get the difference from the lowest date to the highest date in seconds: SELECT caminhao, DATE_FORMAT(data, '%d/%m/%Y') AS…
-
6
votes5
answers16830
viewsA: How do I list all tables with their respective databases?
The following query: DECLARE @tabelas TABLE(nome_database SYSNAME, nome_schema SYSNAME, nome_tabela SYSNAME); DECLARE @database SYSNAME; SET NOCOUNT ON; DECLARE bases CURSOR LOCAL FAST_FORWARD FOR…
-
8
votes1
answer2181
viewsA: Identify if a string is capitalized in sql server!
You can use the COLLATE LATIN1_GENERAL_CS_AI in its validation as follows: DECLARE @texto_normal VARCHAR(100); DECLARE @texto_minusculo VARCHAR(100); SET @texto_normal = 'Normal'; SET…
-
9
votes2
answers2120
viewsQ: What’s the difference between "this" and "$Scope"?
In the AngularJS we use $scope to share information between the domains of a controller. There is also the syntax Controller as which allows us to use the this to the same end. With $scope: function…
-
5
votes1
answer341
viewsA: Check if String has '+' character
The character + is reserved in regular expressions, so it is necessary to escape it, however in the Java the character \ is used to escape expressions within the String, then there is the need to…
-
1
votes3
answers899
viewsA: NOT EXIST ? How to use
In your case the result would be as follows: SELECT E.* FROM EMPRESAS E WHERE E.PLANO IN ('A', 'B', 'C', 'D', 'E') AND NOT EXISTS(SELECT 1 FROM FRANQUIAS F WHERE F.ID_EMPRESA = E.ID_EMPRESA)…
-
16
votes6
answers32941
viewsA: Convert varchar to date in SQL
If your column dt_item be as varchar in format dd/MM/yyyy: SELECT * FROM TAB_FATURAMENTO WHERE cd_cliente LIKE '%' AND CONVERT(DATE, dt_item, 103) BETWEEN CONVERT(DATE, '15/05/2017', 103) AND…
-
3
votes1
answer222
viewsA: CASE WITH IN does not work
You may not use a IN as a result of a CASE. Replace with a structure with OR: WHERE E.numcad = 241 AND ((@codccu IN ('911003','63') AND E.codepi IN (1,6,23,24)) OR (E.codepi IN (1,23))) AND P.codepi…
-
2
votes2
answers45
viewsA: Error in SQL output
The problem is that you must isolate the clause OR of his query: SELECT * FROM `tabela` WHERE `data` BETWEEN 'dataInicio' AND 'dataFim' AND (`usuarioID` = 0 OR `adminID` = 2)…
-
1
votes3
answers741
viewsA: Compare data from two tables
In the case of a table merge you should use a JOIN: SELECT tb1.id, tb1.bairro FROM Tabela1 tb1 INNER JOIN Tabela2 tb2 ON tb2.idBairro = tb1.id WHERE tb1.idCidade = 17500 AND tb2.logradouro = 'Av…