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 Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  • Users: Lamprey13
  • Order by date
  1. Lamprey13

    Query for Best Leading Match

    ...INSERT @Dest SELECT '07', 'Personal numbers, messaging and mobiles' UNION ALL SELECT '077', 'Mobile phone' UNION ALL SELECT '07702', 'O2' SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY P.PhoneNumber ORDER BY LEN(D.Data) DESC) AS RowNum FROM @Phone P INNER JOIN @Dest...
  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

    ...@Golfers VALUES (2,33) SELECT Golfer, AVG(Score) FROM ( SELECT A.Golfer, A.Score FROM @Golfers A INNER JOIN @Golfers B ON A.Golfer = B.Golfer WHERE A.Score >= B.Score GROUP BY A.Golfer, A.Score HAVING COUNT(*) BETWEEN 1 AND 3 ) AS T GROUP BY Golfer
  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

    ...SELECT 'x', 'b', 'n' ; -- Declare CTE WITH Temp AS ( SELECT c1, c2, c3, 0 AS Level FROM @T as T WHERE c3 = '0' UNION ALL SELECT T1.c1, T1.c2, T1.c3, Level + 1 FROM @T AS T1 INNER JOIN Temp AS T2 ON T1.c3 = T2.c2 ) -- Get results SELECT * FROM Temp
  16. Lamprey13

    Split Data

    If you have 2005 you can use CROSS APPLY to acheive the result you are looking for. For example, I have a function that splits a list of integers: DECLARE @T TABLE (List VARCHAR(50)) INSERT @T SELECT '123,456,789' UNION ALL SELECT '987,654,321' SELECT Temp.Val FROM @T AS T CROSS APPLY...
  17. Lamprey13

    Correct use of Like

    LIKE '[a-z]%' LIKE '[0-9]%'
  18. Lamprey13

    IF Statement in SELECT Query

    Inconceivable! Obligatory :)
  19. Lamprey13

    Adding three statements together, need help

    Since you did not supply you desired output, I'm not sure what you want, but here is one option using UNION ALL as JBenson suggested:SELECT item_number, SUM(cases_scanned), SUM(cases_sold), SUM(units_sold), SUM(units_scanned) FROM ( SELECT uvb.item_number, COUNT(uvb.item_number)...
  20. Lamprey13

    Not returning max date

    You need to alias your derived table:SELECT AdditionalStatus.LoanID, Max(AdditionalStatus.DateAdded) as DateAdded, AdditionalStatus.StatusValue FROM AdditionalStatus INNER JOIN ( SELECT Distinct LoanID FROM R_IFS_AdditionalStatus ) AS R_IFS_AdditionalStatus ON...

Part and Inventory Search

Back
Top