I get “invalid column name daysdiff”. Maxlogtm is a datetime field. It’s the little stuff that drives me crazy. Answer Normally you can’t refer to field aliases in the WHERE clause. (Think of it as the entire SELECT including aliases, is applied after the WHERE clause.) But, as mention…
Tag: tsql
Know relationships between all the tables of database in SQL Server
I wish to all know how the tables in my database are related to each other (i.e PK/FK/UK) and hence i created a database diagram of all my tables in SQL Server. The diagram that was created was not easily readable and had to scroll (horizontally and sometimes vertically) to see the table on the other end. In …
How to get second-highest salary employees in a table
It’s a question I got this afternoon: There a table contains ID, Name, and Salary of Employees, get names of the second-highest salary employees, in SQL Server Here’s my answer, I just wrote it in …
Average of multiple columns
I have a table called Request and the data looks like: Req_ID R1 R2 R3 R4 R5 R12673 2 5 3 7 10 R34721 3 5 2 1 8 R27835 1 3 8 5 6 Now I want …
How to zero out all negative numbers in a group-by T-SQL statement
I have a T-SQL query where I want all negative quantities to be zeroed out. SELECT p.productnumber, v.[Description], SUM(i.Quantity) as quantity FROM … LEFT JOIN … LEFT JOIN … LEFT JOIN … …
Creating and Selecting table variables dynamically in SQL Server stored procedure?
Please guide me how to create table variables dynamically. I know it can be like this: How I will create if dont know columns and data types. Scenario is I have to create table variable as per a physical tables and selct data into them, use them in SP and then return data in table variables to C# program. For
Return sql rows where field contains ONLY non-alphanumeric characters
I need to find out how many rows in a particular field in my sql server table, contain ONLY non-alphanumeric characters. I’m thinking it’s a regular expression that I need along the lines of [^a-zA-Z0-9] but Im not sure of the exact syntax I need to return the rows if there are no valid alphanumer…
SQL Server Stored Procedure to Send Email
This is my first attempt at writing a stored procedure that emails someone. When trying to execute I get these errors: The code that I am using which is causing this is: Answer You’re missing a comma after the @body line, which is throwing off your declarations. Add it here:
SQL speed up performance of insert?
I am performing some test on sql server and I want to get the best insert speed possible. The statement I use is something like this: INSERT INTO db_Test_databse..tbl_test with(rowlock) ( …
Group by every N records in T-SQL
I have some performance test results on the database, and what I want to do is to group every 1000 records (previously sorted in ascending order by date) and then aggregate results with AVG. I’m actually looking for a standard SQL solution, however any T-SQL specific results are also appreciated. The qu…