I have a SQL Server table that I'm using as a queue, and it's being processed by a multi-threaded (and soon to be multi-server) application. I'd like a way for a process to claim the next row from the queue, flagging it as "in-process", without the possibility that multiple threads (or multiple servers) will claim the same row at the sam...
Which of these queries is more efficient, and would a modern DBMS (like SQL Server) make the changes under the hood to make them equal? SELECT DISTINCT S# FROM shipments WHERE P# IN (SELECT P# FROM parts WHERE color = ‘Red’) vs. SELECT DISTINCT S# FROM shipments, parts WHERE shipments.P# = p...
Need to get a certain subgroup of data per day (Separated by weekday) For example Select weekday,bla,blabla,blablabla from dbo.blabla where bla = @StartDate and bla <=@endDate I need the output to be: Monday bla blabla blablabla Tuesday bla blabla blablabla If someone could help me that would be awesome. Thanks & Regards Jacques ...
As you know the PIVOT syntax is like below : FROM table_source PIVOT ( aggregate_function ( value_column ) FOR pivot_column IN ( <column_list>) ) table_alias I want to know is it possible that we pass a query as <column_list> to PIVOT? In action, I want to write FOR DepartmentName IN (SELECT Name From Department)) instead of ...
Hi. Is there a way to convert '@my_variable' string into a value of @my_variable? I have a table which stores names of variables. I need to get the value of this variable. Something like this: DECLARE @second_variable AS NVARCHAR(20); DECLARE @first_variable AS NVARCHAR(20); SET @first_variable = '20'; SET @second_variable = SELECT '@f...
We have a function that pulls the date from the first 8 characters of a string. Because of the way this system was written, the users are told they have to enter the date in an 8 character format in the field. For example, 12/06/10 must be entered. The way the original function was written, 12/6/10 or 12/06/2010 would generate a wrong...
I am trying to make the following work: declare @ActTable as varchar(1000) declare @cPK as VarChar(100) declare @SQL as nvarchar(2000) declare @FK as VarChar(100) declare @FKRef as VarChar(200) declare @TblRef as varchar (100) create table #temp ( M2MTable varchar(50), PK varchar (100), FK Varchar(100), FKRefTable Varchar(50)) insert...
Suppose I have a set in SQL like this: Product | Quantity A 1 B 2 I want (in a single SELECT statement) to transform that to: Product A B B Can anyone point me towards (T-SQL preferably), a trick on how to do this? ...
Is it possible to use IN in CASE..THEN ? WHERE record.field = CASE WHEN @flag = 1 THEN a WHEN @flag = 2 THEN IN (b, c) END Or how to rewrite such condition? ...
Query: SELECT TOP 1 ReportInvoked , EmailSent FROM tblReportInvoker WHERE WebUserId = 12345 This gives me two bit values. What I really want is a scalar result that is the logical AND of these two values. Is this possible? This seems like it would be easy but I'm not finding a syntax that will work. Edit: Of course the flaw in my...
I've got code similar to the following in a stored procedure that inserts a row into a table, I'd like to set the last column (FieldD) to @prmSomeValue unless it is null, otherwise just use the default value defined for that column. IF (@prmSomeValue IS NULL) INSERT INTO MyTable (fieldA,FieldB,FieldC) SELECT A,B,C FRO...
Possible Duplicate: SQL Server: Can I Comma Delimit Multiple Rows Into One Column? I have a query like this: SELECT name from users and it's result is a number of records: 1 user1 2 user2 3 user3 I want to get all this records in a single line separated by comma: user1, user2, user3 and an empty line if query result i...
So I've got 3 different columns (basket 1, 2, and 3). Sometimes these columns have all the information and sometimes one or two of them are null. I have another column that I'm going to average these values into and save. Is there a sleek/easy way to get the average of these three columns even if one of them is null? Or do I have to hav...
Lets say I have two tables - Person and Clothes and both of these tables have associated Key/Value tables which store attributes about a Person and an item of Clothing. A joined version of Person to Attributes might look like: PersonID | AttributeKey | AttributeValue 1 'Age' '20' 1 'Size' 'La...
Business problem - understand process fallout using analytics data. Here is what we have done so far: Build a dictionary table with every possible process step Find each process "start" Find the last step for each start Join dictionary table to last step to find path to final step In the final report output we end up with a list of...
I have a table structure with columns like this [ID] [Name] [ParentId] [ParentName] The parents are contained in the same table, and i would like to populate the parent name column using a statement like: UPDATE Table SET ParentName = (select Name from Table where Id = ParentId) ...
Hi, For my question lets consider the following sample table data: ProductID ProductName Price Category 1 Apple 5.00 Fruits 2 Apple 5.00 Food 3 Orange 3.00 Fruits 4 Banana ...
Hi all I have the following problem. I have a table with a few hundred thousand records, which has the following identifiers (for simplicity) MemberID SchemeName BenefitID BenefitAmount 10 ABC 1 10000 10 ABC 1 2000 10 ABC ...
Thanks for taking a look at this. The issue has to do with the following sql query. Basically it recursively finds all pages underneath a certain page(master page), and then applies the master page's categories(looked up in the xref_pages_categories) to all of those pages. Upon running the insert, querying the table for data that shoul...
I have a table, tblClient, which stored a client's date of birth in a field of type datetime, DOB. The goal here is that, when a client reaches 65 years old (need to be calculated thru DOB), I need to insert a new record into another table. But since the age of a client does not change due to a database transaction (INSERT,UPDATE,DELET...