Forum Replies Created

Viewing 15 posts - 16 through 30 (of 7,618 total)

  • Reply To: Pivot but preserve all rows on Aggregate column

    You can also do it with a single result set.  Which performs better would depend, so you'd have to test that out.

    This method would also be much easier if you...

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

    December 10, 2025 at 9:10 pm

    #4698568

  • Reply To: Pivot but preserve all rows on Aggregate column

    Great, glad it helped.  Many thanks for the feedback.

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

    December 10, 2025 at 9:04 pm

    #4698567

  • Reply To: Pivot but preserve all rows on Aggregate column

    I'm not sure PIVOT can give you the results you want.

    Maybe try this instead?:

    SELECT 
        COALESCE(BBBB.STOCK_CODE, CCCC.STOCK_CODE) AS STOCK_CODE,
        COALESCE(BBBB.MNEMONIC, CCCC.MNEMONIC)...

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

    December 10, 2025 at 7:58 pm

    #4698543

  • Reply To: This doesn't make sense to me - making me crazy - er

    D'OH, obviously there is an mbi column, since it's directly referenced in the query.

    The point here is that, in a subquery, if you reference a column that exists in a...

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

    December 9, 2025 at 4:15 pm

    #4697845

  • Reply To: This doesn't make sense to me - making me crazy - er

    Is there an MBI column in table "edi.dbo.PEC_RGT_EDI_834_Inbound_Exception"?

    If so, the second query works because it is comparing xx.MBI to itself, which will always be equal (unless MBI is NULL, oc).

    x.MBI...

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

    December 9, 2025 at 2:44 pm

    #4697787

  • Reply To: Best analysis approach to reduce data type length in multiple tables

    My thoughts on this:

    (1) Decide whether you want to include nvarchar or not.

    (2) Gen a list of the tables to be changed: then, if you need to, you can custom...

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

    December 1, 2025 at 8:57 pm

    #4692990

  • Reply To: Looking for help with SQL statement

    I identified the changes I made to the original code using /*--<<--*/ on/around them.  This code should also perform better.

    SELECT
    sa.partid AS ALLOC_Part,
    sa.quantity,
    sa.ordertype,
    sa.worksorderid,
    sa.reference,
    sa.stockvalue,
    apm.partdesc,
    soi.orderid,
    soi.itemnumber,
    soi.partid AS SOI_Part,
    so.traderid,
    t.name,
    u.name As SalesRep,
    sub.Max_Batchvalue AS...

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

    November 24, 2025 at 4:55 pm

    #4689910

  • Reply To: Select @ for different fields

    You could also use Brian Gale's approach and use a proc to generate all the code for you.  Personally I would stick with dynamic SQL, but the other is an...

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

    November 17, 2025 at 9:38 pm

    #4686727

  • Reply To: Columnstore index question

    Columnstore won't gain you much (other than perhaps some disk space) since you are reading all the columns.  Personally, I would just use page compression.

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

    November 12, 2025 at 9:58 pm

    #4684433

  • Reply To: Columnstore index question

    Absolutely agree with analyzing page compression for the table.  It could be a huge help.  You can use:

    EXEC sys.sp_estimate_data_compression_savings 'dbo', 'table_name', NULL, NULL, 'PAGE'

    As  you noted, the nonclus index is...

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

    November 12, 2025 at 6:31 pm

    #4684326

  • Reply To: Varchar(max) to lines

    I thought it was more complicated than that because a CHAR(13) should also count as a "space" as far as splitting words?!  Perhaps not, I guess it depends on exactly...

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

    November 6, 2025 at 7:38 pm

    #4681809

  • Reply To: Determining the Updated Columns 

    IF UPDATE(CreditLimit)

    That approach seems preferable to me, unless you really have to use COLUMNS_UPDATED()

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

    August 13, 2025 at 2:07 pm

    #4634941

  • Reply To: Replace calculation in cursor with set based logic

    If the quirky UPDATE works, can you modify the original table to include the new calculated column?

    Let me ask: once a value has been calc'd, would it ever change?  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".

    May 12, 2025 at 6:12 pm

    #4590321

  • Reply To: continuous membership

    Please provide data in usable format: CREATE TABLE and INSERT statment(s).  That is way more useful to us than a "picture" of data.

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

    May 9, 2025 at 6:35 pm

    #4589524

  • Reply To: SQL Query Performance with Left Join

    Would need to see row counts to better understand the query.

    How is the Departments table clustered?  In general, again without knowing any details at this point, DepartmentID first followed by...

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

    April 30, 2025 at 6:09 pm

    #4586169

Viewing 15 posts - 16 through 30 (of 7,618 total)