Hello, I have such a simple question, I feel stupid for asking it. I can't believe I'm hitting my head on this. I have a table called "Orders". "Orders" has a smalldatetime field called "DateOrdered". I need to get all of the "Orders" on a specific date. I have tried the following without any success: SELECT * FROM Orders WHERE [DateO...
Hey all, i was wondering if there was a way to do this in MS SQL Server 2005: DECLARE @theDate varchar(60) SET @theDate = '''2010-01-01'' AND ''2010-08-31 23:59:59''' SELECT AdministratorCode, SUM(Total) as theTotal, SUM(WOD.Quantity) as theQty, AVG(Total) as avgTotal, (SELEC...
Is is possible in SQL Server 2008 to create such a constraint that would restrict two columns to have NULL values at the same time? So that Column1 Column2 NULL NULL -- not allowed 1 NULL -- allowed NULL 2 -- allowed 2 3 -- allowed ...
So we have some developers who went a little view happy. So now we have views that reference views that reference views, ad nauseum. So what I want, in order to assist me in Tuning, is to expand these views. I want a function that takes a string and returns a string. The input string is the query, the output string is the same query w...
This table is rather backwards from a normal schema, and I'm not sure how to get the data I need from it. Here is some sample data, Value (column) Info (column) --------------------------------------------- Supplier_1 'Some supplier' Supplier_1_email '[email protected]' Supplier_1_rating '5' Supplier_1_st...
Suppose I have a table Orders in sql with 4 columns (OrderId int, ProductID int, Price money, Size int) with OrderID and ProductID as a joint primary key. If I want a query to return the most recent order (along with price and size) for every product (i.e., maximal OrderId) is there a way to do this without a join? I was thinking of ...
I have the following database schema (names changed to protect the innocent) Code Table | code_id | code_desc | |---------|-----------| | 1 | good | |---------|-----------| | 2 | bad | |---------|-----------| | 3 | ugly | |---------|-----------| Foo Table AssignedFoo Tabl...
When allowing a user to select a date range, let's say: Show me entries from [August 1] to [September 1] As a user, I would generally expect this to include the results for September 1. Especially when you consider that when I select the same date for both ends, I obviously mean "from start of day to end of day": Show me entri...
Hello, I have a query that checks a tables and groups all entries from a user and counts those entries SELECT username, count(userid) FROM orders GROUP BY userid This returns a list of username's and how many orders they have submitted username count(userid) ------------------------ test 1 test2 1 tes...
Terrible title, I know... This works and gives me what I'm looking for, but I know there must be a better way to do it: var query = (from a in DB where a.Date.HasValue select a).AsEnumerable(); double avg = (from a in query select (a.Date.Value.Subtract(a.OtherDate).Days).Average(); Basically,...
Because I use rails I've become rusty on sql since I rarely use it in rails. I have two related tables: comments 1:m comment_views I want to find all comments where comment_views.viewed is false. Problem is, for some comments there is not a relating record in comment_views yet. So far I have select comments.id from comments ...
I have a table for settings, which can be shown as: object_id (int) key (varchar) value (text) I am trying to grab the object_id that has key's equal to 2 items. SELECT `object_id` FROM `settings` WHERE `key` = 'A' AND `key` = 'B' I know that won't work, the only way I can think of doing this is joining it on itself: SELECT a.`objec...
UPDATE: Adding more Employee and ReplacementEmployee Table Employee - EmployeeId SerialNo ----------------------------- 1 11111 34 23233 23 13234 Table ReplacementEmployee- ReplacementId SerialNo ----------------------------- 11 11111 23 23233 13...
I have a simple thing which I want to do, yet is so frustrating in attempting and completing. I just want to take some value such as this: 'a value with line breaks' And insert it into a CLOB field and retain the line breaks in DB2. Something like this statement insert into some_table (the_clob_field) VALUES('a value with line breaks...
INSERT into error_log (id_user, id_error, severity, date) VALUES ('93, '1', '6', '1285886665') Throwing You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1', '6', '1285886794')' at line 4 Table CREATE TABLE `error_log` ( `id` int(25) N...
Hi, I have a requirement. I have two tables say TableA and TableB. Both having a column called "rec_id". My requirement is to get the maximum of the values contained in these two columns using a single query. eg: In TableA, I have "rec_id" values as {1,5,6} and in TableB, I have "rec_id" values as {1,4,2}. So after executing the query...
I have 4 SQL server(2008 version) tables 1) USER- to store user information (Fields : UserId,UserName) 2) FILES - to store files uploaded by user (FileId,FileName,UserId) 3) PHOTOS -to store files uploaded by user (PhotoId,PhotoName,UserId) 4) GROUPS= to store groups created by user ( GroupId,GroupName,UserId) Now I want to get a USE...
Can SQL be used to find all the brands that has the most common categories? For example, the brand "Dove" can have category of Soap, Skin Care, Shampoo It is to find all the brands that has the most matching categories, in other words, the most similar brands. It can be done programmatically using Ruby or PHP: just take a brand, and lo...
how can I use loop 'for' through a table in SQL to check if a column is 0? I need to do like this in a stored procedure: for each record in tablex if table.column1=0 then insert into table1; Else insert into table2; End for; ...
Can I use a single SQL statement to return three separate results I can display through PHP? I'm looking to formulate three different "total" counts on a single table with three different conditions. Eg something like.... $resultGetTotals = SELECT COUNT(Total1) FROM table_xyz WHERE Status = X AS Total1 SELECT COUNT(Total2) FROM table_x...