Forum Replies Created

Viewing 15 posts - 1,276 through 1,290 (of 7,613 total)

  • Reply To: Need some help with tsql code

     

    SELECT
        COALESCE(P.DatabaseName, D.DatabaseName, T.DatabaseName, S.DatabaseName) AS DatabaseName,
        CASE WHEN P.DatabaseName IS NULL THEN '' ELSE 'Yes' END AS Prod,
      ...

    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".

    September 17, 2021 at 4:23 pm

    #3930627

  • Reply To: Issue with the SUM function

    Best is to move the SUM() into a derived table, like so:

    SELECT F.LOC AS 'Chain', F.DMDUNIT AS 'Item', D.DESCR AS 'Item Attributes. Description', D.U_PRODUCT_CATEGORY AS 'Item Attributes....

    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".

    September 17, 2021 at 1:11 am

    #3930355

  • Reply To: Formatting issue with percent

    You're welcome!

    Btw, you got 0 in the original calc because 2/100 is 0.02.  But, since the values were integer, SQL makes the result integer and thus 0 only (the .02...

    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".

    September 16, 2021 at 10:20 pm

    #3930326

  • Reply To: Formatting issue with percent

    ...

    CAST(p.QTY * 100.00 / s.OH AS decimal(5, 2)) AS 'Instock %'

    ...

    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".

    September 16, 2021 at 10:03 pm

    #3930323

  • Reply To: Retrieving a group from BOTH tables, if at least one record meets the condition

     

    WITH cde_a AS
    (SELECT 'Berries' AS fruit_type,
            'strawberries' AS fruit,
            1 AS red
    UNION ALL
    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".

    September 15, 2021 at 6:46 pm

    #3929918

  • Reply To: What is the best strategy to check for changes to a record in DWH ETL processes

    #1 is (way) too much overhead.

    #2 is better.  You want static code that is generated dynamically.

    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".

    September 15, 2021 at 2:41 pm

    #3929852

  • Reply To: read committed snapshot

    msimone wrote:

    In a DB I executed

    alter database xxx set read_committed_snapshot ON

    If I execute

    set transaction isolation level read committed

    Is the session with isolation level read committed or read committed snapshot?

    Read  Committed...

    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".

    September 15, 2021 at 2:39 pm

    #3929850

  • Reply To: Exclude rows where value used in another row

    Jeff Moden wrote:

    AND, looking a bit closer, my code doesn't work correctly using the following data but the mod to pietlinden's code does, so use his code with the mod.

    Here's 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".

    September 15, 2021 at 3:36 am

    #3929661

  • Reply To: Backup has stopped working

    Of course lots of changes could be made to a db that would not be reflected in sys.dm_db_index_usage_stats.  For example, file(s) added; user(s) added/removed; permissions added/removed; etc..

    The most likely causes:...

    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".

    September 10, 2021 at 7:08 pm

    #3928678

  • Reply To: Invalid length parameter passed to the LEFT or SUBSTRING function.

    Add an asterisk to the end of the string; it won't hurt if it's not needed but is vital if it is needed.

    SELECT

    SUBSTRING(UCL.PROCEDURE_COMMENT,

    CHARINDEX('*', UCL.PROCEDURE_COMMENT) + LEN('*'),

    CHARINDEX('*', UCL.PROCEDURE_COMMENT + '*', --<<--

    CHARINDEX('*',...

    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".

    September 9, 2021 at 6:00 pm

    #3928320

  • Reply To: I killed the process. And it is till running.

    Although it seems odd, that's actually quite normal.  The KILLed process must rollback any data it needs to.  Issue a:

    KILL XXX WITH STATUSONLY

    command.  If it reports 0 and 0%, then...

    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".

    September 8, 2021 at 10:57 pm

    #3928036

  • Reply To: get lastname,firstname, middlename as first 3 pipe delimiter last data no need

    To me, 50/50 on using a splitter for just the first 3.  Here's code using CHARINDEX & SUBSTRING:

    DECLARE @string varchar(200)

    SET @string = 'NAZARE|DEV|T||Current~NAZARE|DEV|T||Previous'

    SELECT string, Lastname, Firstname, Middlename
    FROM...

    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".

    September 8, 2021 at 3:17 pm

    #3927929

  • Reply To: Inserting records in bulk, but check total quantity of all products first

    Looks like you're trying to do something like below.  Note that if multiple, different ProductIds are INSERTed at the same time, if ANY of them fail, ALL will be rolled...

    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".

    September 7, 2021 at 8:36 pm

    #3927628

  • Reply To: How to Copy SPROC and Rename?

    pietlinden wrote:

    This looks like it's supposed to be looking for the @current_proc_name value, not the literal string 'SOURCE', but then WTH do I know?

    AND CHARINDEX('ALTER', @proc_source) <...

    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".

    September 7, 2021 at 5:20 pm

    #3927577

  • Reply To: Speeding up Query

    Jeff Moden wrote:

    Ah... be careful.  Changing the clustered index on the quick fly could cost you everywhere else.

    How is that, specifically, when the current clus index is an identity column?  You don't...

    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".

    September 7, 2021 at 5:19 pm

    #3927576

Viewing 15 posts - 1,276 through 1,290 (of 7,613 total)