Forum Replies Created

Viewing 15 posts - 1,981 through 1,995 (of 7,619 total)

  • Reply To: Dynamic query based of business rules

    I suggest that the results of the dynamic SQL should be to create a view.  Your manager can then SELECT from the view.

    Likely the view name would be the same...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 8, 2021 at 6:44 pm

    #3830109

  • Reply To: Dynamic query based of business rules

    I would like your opinion on this solution

    First, you really need to go back and do logical modeling, aka normalization, before you create tables (you have a table but it...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 8, 2021 at 6:35 pm

    #3830105

  • Reply To: how to update a column to replace the end of a pattern (SOLVED) 

    SELECT 
        path AS original_path,
        LEFT(path, LEN(path) - CHARINDEX('/', REVERSE(path)) + 1) + 'this.bat'
    FROM
    (
        VALUES ('temp/a/g.txt')
      ...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 8, 2021 at 5:12 pm

    #3830060

  • Reply To: Query to retried list of names with value and if a value does not exist!

    It's better to put all conditions related to the LEFT table directly in the LEFT JOIN, i.e.:

    SELECT
    Names.productName
    ,NameProperty.Value
    ,NameProperty.Property
    FROM Names

    LEFT OUTER JOIN NameProperty
    ON Names.productName = NameProperty.productName 
    AND NameProperty.Property =...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 8, 2021 at 5:07 pm

    #3830059

  • Reply To: Isn't it the case that an empty string is different from a null string?

    Oracle dbms treats an empty string as NULL.  Don't ask me why, it's totally bogus to me, but it does.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 7, 2021 at 11:23 pm

    #3828771

  • Reply To: Load Monitor

    Yes, definitely.  That's another reason I wrote the trigger that way, to allow sample value(s) to be pulled for use in the email, if needed.

    For example:

    DECLARE @min_number_of_entries_expected...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 7, 2021 at 10:08 pm

    #3828744

  • Reply To: How to convert YYYY_WW in order to compare with a datetime column

    Or take the month difference and divide by 12.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 7, 2021 at 6:47 pm

    #3828692

  • Reply To: Load Monitor

    Create a job to check on the data.  Even though it will check the last hour, you could schedule it to run as often as you wanted to check:

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers".  Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 7, 2021 at 5:14 pm

    #3828658

  • Reply To: How to convert YYYY_WW in order to compare with a datetime column

    My first thought would be to simply take the difference of the year.  Do you want to adjust based on the month as well as the year?


    SELECT...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 7, 2021 at 4:15 pm

    #3828603

  • Reply To: Query is hanging

    I don't see any view defs.  Instead of attaching them, you can post them using the "insert/edit code sample" button when you go to post a Reply.

    If I'm overlooked them...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 7, 2021 at 3:38 pm

    #3828593

  • Reply To: Rebuilding and Reorganizing Indexes

    Jeff Moden wrote:

    ScottPletcher wrote:

    Jeff Moden wrote:

    If you have some indexes that have woefully low (less than 80% average percent of page fullness [page density]), then rebuild (NOT reorganize) them at 82%...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 6, 2021 at 11:03 pm

    #3828321

  • Reply To: Rebuilding and Reorganizing Indexes

    Jeff Moden wrote:

    If you have some indexes that have woefully low (less than 80% average percent of page fullness [page density]), then rebuild (NOT reorganize) them at 82% to recover the...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 6, 2021 at 10:24 pm

    #3828297

  • Reply To: Trying to Extract customer Transacted in two unique merchants

     

    SELECT a.cardnumber, Count(*), Sum(AMT) 
    from transactions a
    where Merchant like '%Google%' or Merchant like '%Microsoft%'
    group by a.cardnumber
    having max(case when Merchant like '%Google%' then 1 else 0 end) =...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 6, 2021 at 10:01 pm

    #3828290

  • Reply To: query filter

    here the fieldName 'Status' is 'On' AND filedName 'Subs' isn't 'No' ... only mach 1 would be returned

    But for mach 1, the value for Subs is 'No' in your sample...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 6, 2021 at 9:56 pm

    #3828289

  • Reply To: Using AVG but need to ROUND UP

    If you want round up the avg to whole seconds, then you can use CEILING.  If you want to round to a certain number of decimal places, use ROUND.  In...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 6, 2021 at 9:43 pm

    #3828288

    • Viewing 15 posts - 1,981 through 1,995 (of 7,619 total)