I have 3 tables (simplified): tblOrder(OrderId INT) tblVariety(VarietyId INT,Stock INT) tblOrderItem(OrderId,VarietyId,Quantity INT) If I place an order, I drop the stock level using this: UPDATE tblVariety SET tblVariety.Stock = tblVariety.Stock - tblOrderItem.Quantity FROM tblVariety INNER JOIN tblOrderItem ON tblVar...
I have a dynamic set of data X of the form: ---------------------------------- x.id | x.allocated | x.unallocated ---------------------------------- foo | 2 | 0 bar | 1 | 2 ---------------------------------- And I need to get to a result of Y (order is unimportant): ---------------------------------- y.id | y.st...
How would I go about updating existing DateTime columns in an update statement? I'd like to subtract one day but I get an error message saying that I cannot use DateAdd in an update (something about an overflow). I came up with this query but of course it will not execute. begin tran upd update answer set SentForApprovalAt = Date...
Hi all, I've come across a very interesting problem.. quite lost actually. Suppose I have a table with range of values, like: id.........From........To 1..........0...........5 2..........6...........10 3..........11...........15 4..........16...........29 So now I have an integer say @MyInt = 8, I want to select that single row in w...
Hello, I have a table like the following: (date1, date2, date3, date4, date5) and I want to check if ANY of these dates is different than any other. The trivial solution is: WHERE date1 <> date2 OR date1 <> date3 OR date1 <> date4 OR date1 <> date5 OR date2 <> date3 OR date2 <> date4 OR date2 <> date5 OR date3 <>...
I have a table: Series ======== ID SeriesName ParentSeriesID A series can be a "root" series, (ParentSeriesID is 0 or null) or it can have a Parent. A series can also be several levels down, i.e. its Parent has a Parent, which has a Parent, etc. How can I query the table to get a Series by it's ID and ALL descendant Series' ? So...
Why does the following expression in SQL Server return -9223372036854775808 and not 123? I am calling this from a stored proc where I can't pass null parameters declare @t bigint; set @t = -9223372036854775808; --min value for bigint / long select ISNULL(@t, 123) ...
Over the years I have read a lot of people's opinions on how to get better performance out of their SQL (Microsoft SQL Server, just so we are all on the same page...) queries. However, they all seem to be tightly tied to either a high-performance OLTP setup or a data warehouse OLAP setup (cubes-galore...). However, my situation today is ...
How can I get a list of tables in a database without a timestamp column? Any suggestions? ...
Need help understanding what I am doing with the syntax here, please help! I not only need to convert from float to decimal but I also need to override the original data being migrated to the same format and data (in this case) where necessary to match the newer data. ,CASE WHEN fInvReqMargin IS NOT NULL THEN (CONVERT(DECIM...
I have a table that maps a user's permissions to a given object. So, it is essentially a join table to 3 different tables. (Object, User, and Permission) The values of each row will always be unique for all 3 columns, but not any 2. I need to create a non-clustered index. I want to put the index on the foreign keys to the object and u...
I have my database with the columns as follows: keyword, part1_d1, part1_d2 ........ part1_d25, part2_d26, ......part2_d34 FYI: d1 through d34 are documents.. How can I give a query to obtain columns with column_name like '%part1%'; as below keyword, part1_d1, part1_d2, ........ part1_d25 I tried the query: select (Select COLUMN...
Please consider this sql statements Create table abc (A int, B int ) insert into abc values (1,2) Both of the below statements do the same thing. Why? update abc set A = B, B =0 where A=1 and update abc set B =0, A = B where A=1 I was thinking that in the later statement B columns value is set first and then A columns' value...
Hi, I wondering about how in T-SQL can I retrieve - let's say - 1/2 of total row count form a table (basing on some criteria) or even the first 50 rows (basing on some criteria too) ? ...
Ok, this is something I'm sure I know how to do under normal circumstances, but my brain apparently doesn't want to co-operate with me today. (Speaking of which, if you can think of a better title, that would also be useful...) We have a table which is a bit like this: Year Episode Code 2000 1 A001 2000 1 A001 2000 ...
I have query like that: (as You see I'd like to retrieve 50% of total rows or first 100 rows etc) //@AllRowsSelectType is INT SELECT TOP ( case @AllRowsSelectType when 1 then 100 PERCENT when 2 then 50 PERCENT when 3 then 25 PERCENT when 4 then 33 PERCENT when 5 then 50 when 6 then...
Lets say I have 2 tables: 1 with users and another one which keeps record of which users used what codes. Users ----- Id, Name 1, 'John' 2, 'Doe' Codes ------ Id, UserId, code 1, 1, 145 2, 1, 187 3, 2, 251 Now I want to pull a query that results he following Name, UsedCodes 'John', '145,187' 'Doe', '251' How can this be done wi...
In my application I have a form that has some filter fields. None are required, if the user does enter a value I need to provide those as parameters in a where clause. I don't know how to handle this in my stored procedure though. for example the following stored procedure: --parameters @customername varchar(50), SELECT * from ORDERS...
I have three tables 'Employees', 'Departments' and 'EmployeesInDepartments' The 'Employees' tables references the 'Departments' table (Each employee must have a DepartmentId). However, an employee can exist in multiple departments by adding entries (EmployeeId and DepartmentId) to 'EmployeeInDepartments' table. I currently have the foll...
I have a table, with the following columns: PK1 PK2 ID DATE Value flag I do a calculation that involves taking the max value per ID. select id, max(value) from table group by id I want to mark the flag on the rows that I am using. If id and the max(value) correspond to multiple rows flag the one with the...