Posts by Ricardo Souza • 459 points
25 posts
-
1
votes3
answers694
viewsA: how do I make a row_number keeping the document number with different plots?
See if in my example below meets your need: create table #temp (doc int,cliente int) insert into #temp values ('10','15') ,('10','15') ,('10','15') ,('23','59') ,('23','59') ,('23','59') select * ,…
-
0
votes2
answers972
viewsA: How to Use an UPDATE within IF ELSE SQL Server
After the ALTER TABLE it won’t work to realize the UPDATE, follows an alternative that can help you, as in my example below: IF OBJECT_ID('TempDB.dbo.#MinhaTabela') IS NOT NULL drop table…
-
1
votes3
answers1332
viewsA: Format DATETIME SQL Server
EXEMPLO1 - Direct format, important that the month has to be with upper case, because the result may appear minutes instead of the month EXEMPLO2 - Displays in the format you need, but the result is…
-
2
votes1
answer187
viewsA: SQL server error to create table
For Fks to work in create it is necessary that the referenced table exists, that the referenced field exists and that this field is PK (primary key). Based on your create I built a script that will…
-
1
votes2
answers878
viewsA: How to get the number of columns of a temporary table
Hello, see if the code below meets your need. -- ============================== -- exibindo qtd de colunas -- ============================== SELECT COUNT(*) AS QTD FROM sys.sysobjects AS T (NOLOCK)…
-
0
votes2
answers69
viewsA: Help with handling employees' schedules
My interim solution was to POG below, suits me for now but I believe it can be improved or even have a new solution to the case. declare @func as table (id int , nome varchar(255) , h_entrada…
-
1
votes2
answers69
viewsQ: Help with handling employees' schedules
I have a situation regarding company employees' lunch check-in and check-out times; I need to limit the number of employees who can have lunch at the same time, at least together in the 15-minute…
-
0
votes0
answers969
viewsQ: Decrypt Procedures and Functions in SQL Server
I need to decrypt some routines they’re using "WITH ENCRYPTION" and would like to know if there is any way by SQL Server itself, without needing to use third party tools, to do this.…
sql-serverasked Ricardo Souza 459 -
0
votes1
answer46
viewsA: PK error during Asynchronous process of process
UPDLOCK Specifies that update locks will be used and maintained until the transaction is completed. UPDLOCK uses update locks only in line or page level reading operations. If UPDLOCK is combined…
-
0
votes3
answers73
viewsA: How do I select the latest records for specific entries?
tried to use MAX in the Purchase field, grouping the customer ? see the example below if it serves you. NOTE: I am considering the PURCHASE field as a intso it’s working. declare @Compras as Table…
sql-serveranswered Ricardo Souza 459 -
0
votes2
answers4225
viewsA: Convert Varchar to Time in SQL with data longer than 24 hours
Hello, Here’s an example I use here at the company and found in some forum on the net, but if you need to use seconds you will have to make a small modification. declare @tempo table (hora…
-
2
votes0
answers63
viewsQ: Update table with XML field and in different databases
I have two banks SQL SERVER 2012 installed on server A and server B, I need to insert and then make a Update in the server table B via linkedServer by the A server, but these tables have fields XML,…
-
2
votes2
answers857
viewsA: Group By By Data
Here is an example, used in SQL Sever Declare @tabela as table(DataHoraOcorrencia datetime, Id int) insert into @tabela (DataHoraOcorrencia, Id) Values ('10-07-2017 12:48:37',1), ('09-07-2017…
-
2
votes2
answers569
viewsQ: How to delete duplicated lines sequentially
I need help deleting the line that is duplicated sequentially, but not the duplicate amount of the same record in the table, example lines 6 and 7, are status repeated sequentially, so I would have…
sqlasked Ricardo Souza 459 -
1
votes2
answers710
viewsA: Help with Join in two different selects
See if the format below helps you. I didn’t make much modification in your query, I just used the two informed querys as a subselect, using a "union all" in them. SELECT X.UNIDADE, X.PROFISSIONAL,…
-
2
votes1
answer1088
viewsA: Connection between two DBMS Sql Server
You have to create a linkedServer between servers. Through Linked Server you create a communication link between one server and another so you can have free access to the data stored on the other…
-
0
votes1
answer63
viewsA: Delete Constraint after taking its name
Hello, Follow an alternative to your case. select 'ALTER TABLE ' + CC.TABLE_NAME + ' DROP [CONSTRAINT] '+ TC.Constraint_Name+' ; GO' from information_schema.table_constraints TC inner join…
-
0
votes0
answers41
viewsQ: Unique or something similar to 3 or more different tables
Sirs, In the company where I work uses an ERP that I do not know for what reason, is carried out the typing of sales notes, return of sales and returns of purchases in three different forms, and the…
-
2
votes1
answer33
viewsQ: Login transaction log MSSQL 2012
I need a help, I have to provide a login on MSSQL ERP company and my superior need to monitor transactions of type INSERT, DELETE and UPDATE that are made by them, in case the scripts that were…
-
2
votes2
answers234
viewsA: How to make a query using SUM correctly SQL
Buddy, you just put in the amount ( ItQtde ) in group by. SELECT YEAR(NFDtEmis) AS 'Ano', MONTH(NFDtEmis) AS 'Mes', ItProduto AS 'Produto', SUM(ItQtde) AS 'Quantidade total' FROM NotasItens INNER…
sqlanswered Ricardo Souza 459 -
3
votes1
answer48
viewsQ: Encrypting Function Script and Procedures in MSSQL
A doubt, I have some functions and procedures customized and I do not want my client (who has access to the bank) to be messing with their script. Does anyone know any native MSSQL way of encrypting…
-
0
votes3
answers1525
viewsA: How to create an incremental Update sql command
In MSSQL, I would use the row_number command, as the example below. Try this way or some similar command in your SQL version create table #Temp1 (Produto varchar(1), N_Serie int, Etiqueta int)…
-
6
votes3
answers1988
viewsA: How many days to a date
If you are using SQL Server, use DATEDIFF Examples: day difference : SELECT DATEDIFF ( DAY , '21/07/2017' , '30/07/2017' ) difference of the month: SELECT DATEDIFF ( MONTH , '21/07/2017' ,…
-
0
votes1
answer384
viewsA: Function regex blank spaces SQL Server 2008
Try the form below and you can use as a function. Declare @Texto1 varchar(100), @TextoTemp varchar(100), @Tamanho Integer, @Posicao Integer Set @Texto1= 'pro paro xiton a' Set @Tamanho=Len(@Texto1)…
-
2
votes3
answers873
viewsA: Do not return a field value when it is zero
Try to use the CASE for the field in question, if using SQLSERVER. CREATE TABLE #TABLE_TEMP (ID INT , VALOR NUMERIC(15,2)) INSERT INTO #TABLE_TEMP (ID, VALOR)…