Recent content by Lamprey13

I was not sure if you wanted something that started with 07702 to fall into all of the categories or not, if you did not, you might be about to use the following if you have 2005:DECLARE @Phone TABLE (PhoneNumber VARCHAR(11)) INSERT @Phone SELECT '07702987zzz' UNION ALL SELECT '07704567yyy'...

Dang hit the wrong button, this version should work correctly:

Dang, forgot to test it before I sent it.. Scratch my entry as it doesn't work correctly. :)

For fun:

The ORDER BY will be applied, then the TOP ## will be applied. So, if I follow you, that is what you want and should work just fine. Did you try this already and not get the results you want?

I'd suggest getting a Split/Parse fuction and losing the Dynamic Sql, but I think this will work: sSQL = sSQL + 'WHERE tAGProjects.AGProjectKey IN (' + @AGProjectKey + ''')'

And for 2005 you can do: SELECT Golfer, AVG(Score) FROM ( SELECT A.Golfer, A.Score, ROW_NUMBER() OVER (PARTITION BY Golfer ORDER BY Score ASC) AS RowNum FROM @Golfers A ) AS Temp WHERE RowNum < 4 GROUP BY Golfer

Dang, nevermind.. I missed the part about two simultaneous inserts. I don't know if it'll help in your case, but this is a common problem with data warehousing. You might do some searches for how we do it there. The gist is that you have a "master" table that other systems get their IDs from.

Here is another way (thanks to bborissov for the set up code): DECLARE @Golfers TABLE (Golfer int, Score int) INSERT INTO @Golfers VALUES (1,85) INSERT INTO @Golfers VALUES (1,100) INSERT INTO @Golfers VALUES (1,77) INSERT INTO @Golfers VALUES (1,99) INSERT INTO @Golfers VALUES (2,110) INSERT...

jmille34, I'm not sure I fully understand your problem. Are you saying you have one server that you do an insert on and you can get the identity just fine, but when you replicate that table over to another server there are issues because that other server has the same table also with an...

Here is one way that should work:SELECT t1.unit, t1.dept, t1.code, t1.month, t1.year FROM tbl1 t1 INNER JOIN ( SELECT DISTINCT t2.unit, t2.dept, t2.code FROM tbl2 t2 WHERE t2.unit>'500' AND t2.dept>'600' ) AS Temp ON t1.unit = Temp.Unit AND t1.dept = Temp.dept AND...

Are you using SQL 2005? Also, can you give a sample of what your expected results should be from your sample data set? I'm confused if on which date difference you want.

You also may be able to use the SET TRANSACTION ISOLATION LEVEL command with the SERIALIZABLE option. Check out BOL (Books online) for moer information about isolation levels.

You can make use of Recursive CTEs (if you have 2005):--Setup DECLARE @T TABLE (c1 CHAR(1), c2 CHAR(1), c3 CHAR(1)) INSERT @T SELECT 'x', 'y', '0' UNION ALL SELECT 'x', 'c', 'y' UNION ALL SELECT 'x', 'n', 'c' UNION ALL SELECT 'x', 'a', 'b' UNION ALL SELECT 'x', 'b', 'n' ; -- Declare...