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 Rhinorhino 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. DaveInIowa

    Return value between two pipe symbols

    This is pretty ugly but might provide the results you're after... SELECT SUBSTRING(TextColumn, PATINDEX('%[A-Z][A-Z]-[0-9]%|%', TextColumn), CHARINDEX('|', TextColumn, PATINDEX('%[A-Z][A-Z]-[0-9]%|%', TextColumn)) - PATINDEX('%[A-Z][A-Z]-[0-9]%|%', TextColumn)) YourValue FROM YourTable...
  2. DaveInIowa

    Using the same scripts over and over again

    If you're only interested in the results of that exact query without making any tweaks to it, consider assigning it to a Query Shortcut. Tools > Options > Environment > Keyboard > Query Shortcuts Copy the query to one of the shortcuts, then just press the Ctrl combination to display the results...
  3. DaveInIowa

    Does not give details if a field is NULL

    Glad you were able to solve the problem. This isn't related to the original post but may I make a suggestion? In my opinion it would make the code more readable. Declare a 2-char variable to replace all of your CHAR(13) + CHAR(10) references... DECLARE @CrLf CHAR(2) = CHAR(13) + CHAR(10) ...
  4. DaveInIowa

    Reference a worksheet name that can change

    Going back to your original post It's been a while since I've worked in VBA but I would think you would want to work with the CodeName which is the name of the worksheet that is not in parenthesis in the Project - VBAProject panel. I would rename Sheet1 to something like InvoiceSheet and then...
  5. DaveInIowa

    Nested IF Statement

    I'd also move the check for @MaxDateDuplicateId > @MaxDateMergeToId to the outer IF statement. May as well do the simple variable comparison first before any statements that access tables.
  6. DaveInIowa

    SQL How to create patterned number sequence?

    Once you have a row number (from either a CTE or Temp table), you can calculate the third column value with a CASE statement like... CASE WHEN RowNo = 1 THEN '0' WHEN RowNo % 4 = 1 THEN CAST((RowNo / 4) + 0 AS VARCHAR) + '.4' WHEN RowNo % 4 = 0 THEN CAST((RowNo / 4) + 0 AS VARCHAR) +...
  7. DaveInIowa

    Two Laps

  8. DaveInIowa

    SUM rows where value = DT

    Could you process all unprocessed quotes like this? DECLARE @UnprocessedQuotes TABLE (quote_no VARCHAR(16)) INSERT INTO @UnprocessedQuotes SELECT DISTINCT quote_no FROM @Table WHERE quote_print = 'DT' AND Sub_Total IS NULL DECLARE csr CURSOR FOR SELECT quote_print, Total_After_Discount...
  9. DaveInIowa

    SUM rows where value = DT

    ...IS NOT NULL) DROP TABLE #TableWithRowAndGroup SELECT ROW_NUMBER() OVER (ORDER BY Sub_Quote_Code, Sort_Order) RowNo, CAST(NULL AS INT) GroupNo, * INTO #TableWithRowAndGroup FROM @Table UPDATE twrg SET GroupNo = g.GroupNo FROM #TableWithRowAndGroup twrg CROSS APPLY (SELECT...
  10. DaveInIowa

    TOP 1 for Several Record Pairs

    Would it be as simple as this? ;WITH t AS ( SELECT Manufacturer, OldCityMfgKey, NewCityMfgKey, ROW_NUMBER() OVER (PARTITION BY Manufacturer, OldCityMfgKey ORDER BY NewCityMfgKey) RowNo FROM YourTable ) SELECT * FROM t WHERE RowNo = 1
  11. DaveInIowa

    select distinct most frequent value within date range

    ...RANK function to accommodate ties. ;WITH t AS ( SELECT ClientID, Date, Location, RANK() OVER (PARTITION BY ClientID, Date ORDER BY COUNT(*) DESC) FreqRank FROM @Table GROUP BY ClientID, Date, Location ) SELECT ClientID, Date, Location FROM t WHERE FreqRank = 1 ORDER BY...
  12. DaveInIowa

    RE: Easy SQL help...but its Friday - Brain freeze

    Since there is no inherent order to rows in SQL you'll need to assign an order before matching up rows. I'm going on the assumption that the lowest Start_Time_1 should match to the lowest Start_Time_2 and so on, but what happens if the are more rows of one type than the other? ;WITH t1 AS (...
  13. DaveInIowa

    Adding Control to Form Changes Visible Property

    I'm wondering if anyone can explain this behavior. The example uses 2 controls on a form, comboBox1 & textBox1. Both of their Visible properties are true in the designer. When I step through the code I can see where the Visible property of each control changes to false after they are added to...
  14. DaveInIowa

    Change Cross Apply to Left Outer Join

    Use OUTER APPLY instead; it's the equivalent of a LEFT JOIN.
  15. DaveInIowa

    Query Shortcut for Displaying All DateTime Conversion Styles

    There have been times when I’ve needed a specific format for a DateTime value but don’t have the different style values memorized. Instead of doing a search on the CONVERT function each time, I created this query shortcut and thought I’d share. Go to (SSMS 2014) ... Tools > Options… >...
  16. DaveInIowa

    Multiple If then statements in VBA excel

    Here are a couple other options... Dim NamesToSkip As String NamesToSkip = "*TOC*Cover*End*Disclaimer*Letter of presentation*LoP*Glossary*" For Each aSheet In Worksheets Dim Name As String Name = Trim(aSheet.Name) Dim FormatTab As Boolean...
  17. DaveInIowa

    Constraint to modify value if not unique

    I guess I'm not clear on when the unique suffix is to be generated; during bulk import or at a later date? Why not just use an identity value to make each row unique?
  18. DaveInIowa

    Constraint to modify value if not unique

    ...12345678), ('AcmeTools', 20000, '1/15/2018', 'Credit', 12345678), ('AcmeTools', 20000, '1/15/2018', 'Credit', 12345679) ;WITH t AS ( SELECT *, COUNT(*) OVER (PARTITION BY Name, Number, SomeDate, Type, BiggerNumber) DupKeyValues, ROW_NUMBER() OVER (PARTITION BY Name, Number, SomeDate...
  19. DaveInIowa

    How to set a variable - perform an action - and then set the variable to + 1

    A couple things to note; you'll never do more than 1 pass because you are updating all mytable rows with a non-null folder value and there are a couple shortcuts you could use DECLARE @foldernumber int = 1 -- [highlight #FCE94F]Declare and initialize on 1 statement[/highlight] WHILE (EXISTS...
  20. DaveInIowa

    Why the Aliases

    Typically when you see ugly code like that, it means it was created by a code generator such as Query by Example.

Part and Inventory Search

Back
Top