Please DO NOT CROSS POST.

You already have a thread here http://www.sqlservercentral.com/Forums/Topic1703612-3412-1.aspx. Creating a new post only serves to fragment the discussion.

Drew

You're using not equals when you should be using NOT EXISTS. So if the @StaffID = 101, the @SegmentID = 101, and the @SubjectID = 555 then using not...

As twin.devil already pointed out, if you use MERGE instead of a simple UPDATE, it will automatically raise an error if you try to update the same record twice. ...

A common practice is to use ROW_NUMBER() in a CTE to set the priority and then select the record in the main query where the row number = 1. ...

It sounds like what you are looking for is the following.

SELECT name, cost, yr, mth, CAST(cost/AVG(cost) OVER(PARTITION BY name) AS DECIMAL(10,4))

FROM (

VALUES

('shory', 34346.00, 2014, 1)

,('qualig', 4637, 2015, 1)

,('qualig', 6044, 2014,...

This is essentially an islands and gaps problem with the added wrinkle that the '@@@@@' records are counted in both the islands and the gaps. I used a cross...

As a concrete example,

DECLARE @group_members TABLE (

user_id INT NOT NULL,

group_id INT NOT NULL,

role_id INT NULL,

from_date DATE DEFAULT SYSDATETIME(),

to_date DATE DEFAULT '9999-12-30'

)

INSERT @group_members(

user_id,

group_id

)

VALUES(1,...

Also, a 1-1 relationship means that not only can the person only have one group, but the group can only have one person. Groups, by definition, allow for multiple...

The standard way to handle a many-to-many relationship in an OLTP database is a bridge table, because that is the most efficient. Create a table for group memberships that holds...

This also works, but it doesn't perform as well as Kevin's even though it doesn't use a CTE.

SELECT DISTINCT widgetID

, FIRST_VALUE(funkCode) OVER(PARTITION BY widgetID ORDER BY...

Also, when you...

ISNULL() requires an opening paren and the subquery also requires an opening paren. You are trying to conflate them. You also have the second argument of the ISNULL() in the...

sgmunson (7/10/2015)

---

Just curious as to whether you might be allowed to add the ROW_NUMBER() column to the table as a computed, persisted column... Might simplify things quite a...

You're casting your strings as CHAR(), so it's padding your strings with spaces as necessary at the end. You want to cast it to VARCHAR().

Drew

sgmunson (7/8/2015)

---

DECLARE @ProdCodes AS TABLE (

Prefix varchar(8),

Code varchar(5)

);

INSERT INTO @ProdCodes (Prefix, Code)

VALUES

('b', 'CH0'),

('b', 'CH1'),

('b', 'CH2'),

('b', 'CHX'),

('b', 'CM0'),

('b', 'CM1'),

('b', 'CM2'),

('b', 'CMX'),

('b', 'CN0'),

('b', 'CN1'),

('b', 'CN2'),

('b', 'CMX');

WITH CTE AS (

SELECT Prefix, Code,

CASE SUBSTRING...