I'm trying to retrieve a subset of data based on the sum of a column. The goal is a script to release backordered items. Say we have 100 BO products and get 50 in stock. I'd like to update the oldest orders where the sum of order qty < 50. So something like: Sample Schema: Orders Table: order_id, order_date order_products Table: ...
Simple and common tree like data structures Data Structure example Animated Cartoons have 4 extremities (arm, leg,limb..) Human have 4 ext. Insects have 6 ext. Arachnids have 6 ext. Animated Cartoons have 4 by extremity Human have 5 by ext. Insects have 1 by ext. Arachnids have 1 by ext. Some Kind of Implementation Level/Table0 ...
Good Morning All, I'm trying to refactor an SQL stored procedure. I'm no SQL expert, but something tells me there must be a better way to do this. IF @ipv_dtEndDate IS NOT NULL BEGIN SET @ipv_dtEndDate = DATEADD(hh,23,@ipv_dtEndDate) SET @ipv_dtEndDate = DATEADD(mi,59,@ipv_dtEndDate) SET @ipv_dtEndDate = DAT...
Hi, I am faced with a peculiar requirement which is as follows: A network-intensive operation is triggered to a server by multiple clients, through a web-interface. However, only one operation is allowed at a time, and hence an entry(tuple) is made in an SQL table to indicate that the operation is in progress. Once the operatio...
i have a table with two column: Name Values ---------------- Target 20 v1 10 v2 20 v3 10 Total 30 percent ? i want to calculate the percentage with the single column value to get the formula as --> Target/Total * 100. i.e for ex: Total = SUM (Values) in that way...... by using two rows in the colu...
I apologize if this is a duplication. What column standardization would you use for storing international country subdivision data? For example, if it was just US and Canada I believe all subdivisions have a 2-character abbreviation... which might lend to a Char(2) This cannot possibly be sustainable internationally lest we presume th...
In our ancient Classic ASP environment, we utilize OWASP to get the password from the request object and encrypt non-alphanumeric characters. This is a first line of defense to preventing sql injection. We use other methods for full sql injection prevention. The problem is, when we are collecting data to put together an HTTP post mess...
I'm trying to use the 'For Xml Path' T-SQL to generate a comma separated list of values from a column. This seems to work great, but the problem is I would like to get a count of the items in the comma separated list. Here is an example of the code I am using to generate the comma separated list: Create Table #List ([col] varchar) Inse...
Suppose I have this data: Employee Task IsCurrent -------- ---- --------- Jeff 1 Yes Jeff 2 No Jane 1 No Jane 2 Yes Jane 3 No Jane 4 No Jake 1 Yes Employees have some number of tasks, and one of them will be current. The task number indicates an ordering -- t...
Thank you for your help in advance. I am looking for a neat solution for the following update scenario: I have a table TableA with 5 columns (KeyCol, colA, ColB, ColC, ColD) I have another table Table2 with 2 columns (KeyCol, AvgCol) I want to do something like this: update AvgCol in table2, joining on KeyCol from this logic: if...
I have this table: CREATE TABLE `categories` ( `id` int(11) NOT NULL auto_increment, `category_id` int(11) default NULL, `root_id` int(11) default NULL, `name` varchar(100) collate utf8_unicode_ci NOT NULL, `lft` int(11) NOT NULL, `rht` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `category_id` (`category_id`), KEY `lft`...
Hi all! I have 2 mysql tables: The first table table1: | ID | NAME | | 1 | cat | | 2 | mouse | | 3 | mouse | | 4 | mouse | | 5 | mouse | | 6 | mouse | The secound table table2: | ID | NAME_NA | | 1 | cat | | 2 | mouse | | 3 | cd_rom | | 4 | dvd_rw | | 5 | house | And i want to output t...
First of all, thank you very much in advance for any attempt at helping me with this. My current query (below) works the way i want it to save the requirement mentioned in the title: I need to retrieve the SUM's of the money columns in my 'expenses' table AND each of these values must correspond to a period_ending value that is greater t...
Hi there. I want to know if I can create an Identity (auto increment on a Varchar column. and how can I make it a primary key and create foreign key references on other table. This is the code i have - CREATE TABLE Questions( QuestionID int IDENTITY PRIMARY KEY, QuestionNo as 'Q'+Cast(QuestionID as Varchar(10), Que...
I have a table called "scholarships" that has a field named "majors" which contains comma separated major names for the majors the scholarship is related too. Let's say the field could contain one (or more separated by commas) of the following: business, agribusiness, business administration, international business. If someone is searc...
I am using MS SQL server 2005 I have a table with 3 columns where I store user-message mapping like: msg_for msg_from msg_id bob bob 1 bob john 1 bob steve 1 bob bob 2 bob john 2 bob bob 3 bob john 3 bob steve 3 The PK is on 3 columns and msg_id is FK to message...
Why is SELECT * bad practice? Wouldn't it mean less code to change if you added a new column you wanted? I understand that SELECT COUNT(*) is a performance problem on some DBs, but what if you really wanted every column? ...
I have a query that groups all entries from a table and groups them by the datetime column. This is all working great: SELECT SUM( `value` ) AS `sum` , DATE(`datetime`) AS `dt`` FROM `entry` WHERE entryid = 85 AND DATETIME BETWEEN '2010-01-01' AND '2010-03-01' GROUP BY `dt` ORDER BY `datetime` The problem is, I need it to return...
How should i use while loop in mysql? while exists (select * from table1) Do //sql statements end while Error Code : 1064 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 while.... This what I want to do: Inserting 20K records into a table...
my date column type NVARCHAR(50) example date 02 September 2010 Friday i want to CONVERT DATETIME for can make ORDER BY .. i use ORDER BY CONVERT(DATETIME,M.DeletedDate,102) DESC and i tried 103,104,105...... error message = Conversion failed when converting date and/or time from character string. how can i convert that value to DATET...