sql

Best way to achieve exclusion matrix via query.

What is the best way to achieve this exclusion matrix via query. There are fixed number of products in a table and idea is if a product is sold to a customer (represented by row), the other products (columns) may or may not be sold based on the rule matrix below. The aim is to get products code which are allowed to sold for any given s...

How do I use a comma separated list of values as a filter in T-SQL?

I have a basic SQL query, starting with: SELECT top 20 application_id, [name], location_id FROM apps Now, I would like to finish it so that it does this (written in Pseudocode) if @lid > 0 then WHERE location_id IN (@lid) else WHERE location_id is all values in location_id column As requested, here is an example applicat...

sql 2005 case statement - Invalid Column Name

Can someone tell me why I get error: Msg 207, Level 16, State 1, Procedure ExtractPDP4FromPDP, Line 21 Invalid column name 'ContainsEX'. when executing the following stored procedure. CREATE PROCEDURE ExtractPDP4FromPDP -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result se...

MySQL match brand of item that you don't know brand of?

Say I have a table that has id and brand columns. Say I have an item I know the ID is 300, but that's all I know. Is there a way to make a query pull all the items with the same brand as the item, or do I have to break it into 2 queries and first select the brand of item 300? Thanks. ...

Remove Invalid data from VarChar column

I have a database with a column of VarChar type, within which are integers (which I want to keep) and miscellaneous non-numeric values (which I want to remove). If I alter the table and add a new integer column, how can I copy only the integers into the new integer column? ...

Getting number of fields in a database with an SQL Statement?

How would i get the number of fields/entries in a database using an SQL Statement? Thanks, Ash ...

How can I write this summing query?

I didn't design this table, and I would redesign it if I could, but that's not an option for me. I have this table: Transactions Index --PK, auto increment Tenant --this is a fk to another table AmountCharged AmountPaid Balance Other Data The software that is used calculates the balance each time from the pr...

SQL Server 2008 express performance in production environment?

Hi there, I am about to move servers and i was talking to somebody and they suggested using sql server express 2008 installed on the servers. I have full access to the server. Does this express engine work at the same speed (performance) as a true sql server 2008? I know about the limitations i..e max 4 GB per DB ... and max 1 GB of r...

MS Access SQL Duplicate Rows?

Query: SELECT DISTINCT ([Equipment List].ID) AS Expr1, [Job Assignments].Job FROM [Equipment List] LEFT JOIN [Job Assignments] ON [Equipment List].ID = [Job Assignments].EquipmentID; In this query, the equipment ID is distinct if and only if I do not add the [Job Assignments].Job in the select statement. As soon as I do, I get ma...

How can I select only one row for each ID in MySQL?

I have a MySQL table that holds many entries with repeated IDs (for various reasons) So you might have something like ID TIME DATA 1 xx xx 2 xx xx 3 xx xx 1 xx xx 3 xx xx What query can I run through PHP to select each ID only once? So I would like my result set to look like ID TIME DATA 1 xx xx 2 xx xx 3 xx x...

How can I make this complex update query work?

SELECT (SELECT IIF(IsNull(sum(b.AmountCharged) - sum(b.AmountPaid)), a.Balance, (sum(b.AmountCharged) - sum(b.AmountPaid))) FROM tblCurrentTransaction AS b WHERE b.TenantTransactionID <= a.TenantTransactionID AND b.Tenant = a.Tenant GROUP BY b.Tenant ) AS True...

What is the difference between SELECT and SET in T-SQL

I'm working on a stored proc that executes some dynamic sql. Here's the example I found on 4GuysFromRolla.com CREATE PROCEDURE MyProc (@TableName varchar(255), @FirstName varchar(50), @LastName varchar(50)) AS -- Create a variable @SQLStatement DECLARE @SQLStatement varchar(255) -- Enter the dynamic SQL statement i...

SQL: How to produce next date given month and day.

In my table I have a Month(tinyint) and a Day(tinyint) field. I would like to have a function that takes this month and day and produces a datetime for the next date(including year) given this month and day. So if I had Month = 9, Day = 7 it would produce 9/7/2009. If I had Month 1, Day 1 it would produce 1/1/2010. ...

Is an overuse of nullable columns in a database a "code smell"?

I'm just stepping into a project and it has a fairly large database backend. I've started digging through this database and 95% of the fields are nullable. Is this normal practice in the database world? I'm just a lowly programmer, not a DBA but I would think you would want to keep nullable fields to a minimum, only where they make se...

Generating Invoices

I need to generate invoices in large batch which will be transformed to EDI and transported to our HQ. There is an order table with all the orders coming in. At some point in the day I have to grab a set (about 1k) and generate the invoices. Where would be the best place to generate the invoice numbers? On SQL Server backend? Or grab ...

SQL Syntax -- Group By ... Custom Aggregate Functionality?

I've got a situation like this: Table: FunTable ItemKey.....ItemName.....ItemPriceEffectiveDate....ItemPrice 11.....ItemA.....6/6/2009.....$500 12.....ItemA.....6/15/2009.....$550 13.....ItemA.....9/9/2009.....$450 14.....ItemB.....3/9/2009.....$150 15.....ItemB.....9/9/2009.....$350 I need to do the following: Select   ItemName as It...

Can a many-to-many join table have more than two columns?

I have some tables that benefit from many-to-many tables. For example the team table. Team member can hold more than one 'position' in the team, all the positions are listed in the position db table. The previous positions held are also stored for this I have a separate table, so I have member table (containing team details) positions...

SQL Server - how to set (nolock) hint as a default?

is there some way to tell sql server to use the (nolock) hint or every select in a stored procedure? is pretty tiresome to add it to each an every select.... ...

Return value from a stored proc on error

Hi there, I have an sp in SQL Server that when errors returns -4 what does -4 mean? Is there a table somewhere explaning what the possible return values are? ...

Flexible LIKE operator

I'm new to SubSonic. I've been looking for a way to use LIKE operator that let me build SQL query like this: select * from person where lastname like '%bob%', but I could not find a generic way to do this beside inserting string into the quey like q.OR(Person.Columns.FirstName, SubSonic.Comparison.Like, "%bob%"); that is not a prefered...