create table #testdata ( ID int, Total money ) insert into #testdata ( ID, Total ) select 1, 100 union all select 2, 105 union all select 3, 110 union all …
Tag: tsql
How to substring non constant strings?
I have the following query: With this query I try to associate the value of substring to my variable NUMERO_ORDINE when I find the “NR.” string in the Text column. Sometimes I don’t have the “NR.” string, so this query doesn’t work and I get wrong values from substring function. How can I create the same substring when I don’t
How to define self-defined log in SQL Server
I am now dealing with a huge table that contains XML each row. My job is to write sp to parse the XML and insert its data to the other corresponding tables row by row. But there is a problem that when the sp interrupts, partial data is inserted into the new table while the others are not. So, I
Searching with a lateral join extremely slow
I’m currently implementing a search function in one of our grids and when doing so, my query goes from running in 1 second or less to running in about 16 seconds (I’ve added suggested indices, etc). The data returned from this query will be the parent’s id, parent’s name, most recent child’s first name and most recent child’s last name.
First load table in CTE and then query the CTE?
A colleague of mine writes views like this: He says that his professor taught him this because ‘things will be loaded in RAM and it is a lot faster’. Unfortunately the professor is no longer able to explain this. I think the following works at least as good and avoids unnecessary complexity in the code. Am I missing something? I
Creating a PIVOT with dynamic rows and columns – SQL Server 2016
First, let’s define the tables: The following query gets all the answers with questions for a specific reviewee: And the output looks more or less like this: What I want is output that looks like this: The number of questions and reviews/review_dates is not known until runtime. I am ultimately writing this query for a VB/ASP front end, so if
MS SQL Server Date Month Output
SQL will output a month part as a number but I’d like to out put the 3 char version (Jan, Feb, Mar etc) My query thus far is Answer For each DBMS the way is different Here are few 4 DBMS’s MS SQL Server MySQL Postgresql Oracle So in your case use the appropriate date field instead of current date
case statement is returning a 0 instead of empty string
I have the following case statement; I would like it to return But the output I am getting is; I dont want the zeros, I would like the empty string. Answer You want to return a string, so you need to cast the else:
How to select records in the order they were inserted, and then group them by a type
I have a query I can not seem to get to work. I have a table like this: ThingID, FK_ThingTypeID, Etc… And I want to select records in the order they were inserted, but group them by type. So if the data looks in the table is in the following order: I want to get select the records like this:
Concatenate and add a character to integer columns in SQL
I have a 10 Character length values in a column in SQL Server. I need to split that column at fixed length and remove the leading zeros and add a – after each of the values. I am able to split the …