I have a situation where I need to take a “quantity consumed” from one table, and apply it against a second table that has 1 or more rows that are “pooled lots” of quantities. I’m not sure how to describe it better, here’s what I mean from a table perspective: I need a resulting rowset from a SQL query that
Tag: sql-server-2005
insert into values with where clause
I am trying to programmatically enter values into my table. I cannot use a straight Select @variables. I have to use the keyword Values. How can I create a where clause when using Values in the insert into. I am trying to avoid duplicates Answer
Generate sequence in SQL Server, poor performance with cross apply
I obtained the following code from the web many years ago and it has served my very well. It is simply a function that generates a sequence of numbers from 1 to whatever you pass in. Basically it’s a way of doing a for loop in a SQL statement. This generally works very well and is fast but I have
Dynamically name indexes in SQL Server 2005?
As most people who work with Sql Server know, you can declare a temporary table like so: …this will create a primary key and a default constraint on this temporary table; the names for these objects will be unique, dynamically created by Sql Server. Is it possible to create a dynamically named index for the table after it’s been created?
Referring to a Column Alias in a WHERE Clause
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 mentioned in other answers, you can force SQL to treat SELECT to
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 …
Finding a Primary Key Constraint on the fly in SQL Server 2005
I have the following SQL: Since I have multiple environments, that PK_PS_userVariables constraint name is different on my different databases. How do I write a script that gets that name then adds it into my script? Answer While the typical best practice is to always explicitly name your constraints, you can get them dynamically from the catalog views:
Format() function doesn’t work?
I am trying to execute following built-in function in sql but it gives me error that this function doesn’t exist my query: Error i am getting: What may be the problem, or what am i doing wrong? Thanks! Answer Use Convert function instead. Example:
How to delete all duplicate records from SQL Table?
Hello I have table name FriendsData that contains duplicate records as shown below I want to remove duplicate combinations rows using MS SQL? Remove latest duplicate records from MS SQL FriendsData table. here I attached image which highlights duplicate column combinations. How I can removed all duplicate combinations from SQL table? Answer Try this See here Or here is more
Simple DateTime sql query
How do I query DateTime database field within a certain range? I am using SQL SERVER 2005 Error code below Note that I need to get rows within a certain time range. Example, 10 mins time range. Currently SQL return with Incorrect syntax near ’12’.” Answer You missed single quote sign: Also, it is recommended to use ISO8601 format YYYY-MM-DDThh:mm:ss.nnn[