Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recent content by Lamprey13

  1. Lamprey13

    Query for Best Leading Match

    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'...
  2. Lamprey13

    SQL Teaser - Zodiac Signs

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

    SQL Teaser - Zodiac Signs

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

    SQL Teaser - Zodiac Signs

    For fun:
  5. Lamprey13

    SQL Server Ordered Append Syntax

    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?
  6. Lamprey13

    "IN" statement failing when multiple items passed in

    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 + ''')'
  7. Lamprey13

    SQL Teaser: uniqueidentifier

  8. Lamprey13

    SQL Problem with Top 3

    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
  9. Lamprey13

    Need an alternative to identity field as primary key

    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.
  10. Lamprey13

    SQL Problem with Top 3

    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...
  11. Lamprey13

    Need an alternative to identity field as primary key

    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...
  12. Lamprey13

    Subquery WHERE IN or EXISTS

    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...
  13. Lamprey13

    SQL loop to calculate fields???

    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.
  14. Lamprey13

    lock table

    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.
  15. Lamprey13

    sequence sorting

    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...
See more

Part and Inventory Search

Back
Top