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!

  • Congratulations strongm 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: *

  1. markros

    Advise on SQL Pivot

    I believe it's quite simple and for multi column pivot it's the best solution. I know SQL Server MVP who prefers this case based syntax and never uses PIVOT syntax. PluralSight Learning Library
  2. markros

    Advise on SQL Pivot

    Use case based pivot instead of the regular Pivot. Check this blog post that explains it http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/understanding-sql-server-2000-pivot PluralSight Learning Library
  3. markros

    select record based on max date

    Or check your MSDN thread where you got the same solution. PluralSight Learning Library
  4. markros

    simplify complex query

    I think your code is OK, but you can also do: UPDATE LOAD SET ERR_F = 'W' OUTPUT INSERTED.*, 'G1', GETDATE(), COALESCE((SELECT top (1) EXC_X FROM ExceptionCode WHERE EXC_C = 'G1') + ' : ','') + ACCT, 1 INTO EXCEPTION WHERE CRIS_Code in ( SELECT...
  5. markros

    Return most-recent cost

    ;with cte as (select *, row_number() over (partition by item order by [Date] DESC) as rn from PricesInfo) select * from cte where Rn = 1 PluralSight Learning Library
  6. markros

    Update maximum value of each group

    You don't need to join back to CTE, you can UPDATE cte directly, e.g. UPDATE MaxCounter SET NewILandId = ILandId WHERE RowID = 1 PluralSight Learning Library
  7. markros

    String or binary data would be truncated on insert problem

    Exactly! The same suggestion has also been made in your MSDN forum's thread. PluralSight Learning Library
  8. markros

    iterate through a table

    You will need to use a cursor to 'loop' through each row in a table. Take a look at this link with the similar concept http://www.sqlusa.com/bestpractices2005/importimage/ See also this link as how to use cursors the best way (the above sample does not use the proper keywords): The Truth about...
  9. markros

    Fetch Data on current date

    Take a look at this blog post Bad habits to kick : mis-handling date / range queries PluralSight Learning Library
  10. markros

    Insert record having most recent trans date

    Are you sure your where clause is correct? Do you keep (ItemNumber) Description as ItemNumber in the Balance table? Is the ItemNumber unique in the Inventory? Anyway, try: ;with cte as (Select '(' + RTrim(i.ItemNumber) + ') ' + RTrim(s.ItemDescription) as FullItemName...
  11. markros

    concatenation strings within a select statement

    You will have to execute it dynamically by building the whole string and then executing it execute(@SQL). You need to double every single quote. PluralSight Learning Library
  12. markros

    Table Update Based on Comparison Results

    Your WHERE clause makes no sense. Do you only want to update one row with the Max date? If so, try: ;with cte as (select top (1) FileLoadDate, FileLoadStatus, CASE WHEN exists ( SELECT 1 FROM TestSubmission t WHERE t.Region <> @region OR t.SUB_TYPE <> @subType OR t.VENDOR_ID <> @vendorID...
  13. markros

    Table Update Based on Comparison Results

    Yes, most likely this is what the OP wanted. PluralSight Learning Library
  14. markros

    Table Update Based on Comparison Results

    May be: update SubmissionManagement SET FileLoadStatus = case when exists (SELECT 1 FROM TestSubmission t WHERE t.Region <> @region OR t.SUB_TYPE <> @subType OR t.VENDOR_ID <> @vendorID OR t.DELIVERY_TYPE <> @deliveryType OR t.DELIVERY_TYPE <> @deliveryMethod ) then 4 else 1 end...
  15. markros

    Constraint question

    Dmitry Korotkevich has a blog post on this exact topic http://aboutsqlserver.com/2010/09/12/sunday-t-sql-tip-uniqueness-of-nullable-field/ PluralSight Learning Library
  16. markros

    SSMS 2008 R2 Invalid prefix or suffix characters

    SSMS is not really a GUI for editing tables. Perhaps you can simply write an UPDATE command PluralSight Learning Library
  17. markros

    SQL Server 2005 Standard - support index on a view

    Yes, it does support it assuming that all necessary conditions are qualified. However, the behavior of the SELECT information from the view depends on the edition of SQL Server. There is an interesting quiz by Pinal Dave that explains the problem...
  18. markros

    Delete duplicates

    ;with cte as (select *, row_number() over (partition by myWords order by ID) as Rn) delete from cte where Rn > 1 PluralSight Learning Library
  19. markros

    xp_cmdshell - How can I run multiple CMD commands with one script?

    May be you can create a bat file? Also, why do you need to do this from SQL Server? PluralSight Learning Library
  20. markros

    How to save Excel 2010 with Memo fields

    I'll check without the spaces, but we already defined the format for our spreadsheet. I'll play with just one sheet and with the names without spaces just for the test. PluralSight Learning Library

Part and Inventory Search

Back
Top