Forum Replies Created

Viewing 15 posts - 2,356 through 2,370 (of 7,613 total)

  • Reply To: Update and Joins

    I don't see a more efficient way.  The speed problem may be that it ise UPDATEing every row of the Brs table, since there's no join to it in 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".

    August 3, 2020 at 3:14 pm

    #3776894

  • Reply To: update full path for record

    I thought LEAD / LAG were added in SQL 2012?!, although I'm not sure how useful they'd be here anyway.

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

    July 31, 2020 at 10:31 pm

    #3776449

  • Reply To: Help, so close with this script yet... needs to be tweaked (using Replace)

     

    DROP TABLE IF EXISTS #testdata;

    CREATE TABLE #testdata ( description nvarchar(max) NULL, new_description nvarchar(max) NULL );
    INSERT INTO #testdata ( description) VALUES 
        ('Hyperlink[https://OldURL/Site/location.doc?andnowuneededstuff]'),
      ...

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

    July 31, 2020 at 10:06 pm

    #3776447

  • Reply To: Help, so close with this script yet... needs to be tweaked (using Replace)

    Not 100% sure what you are ultimately trying to do, but maybe something like this:

    ...

    SET DESCRIPTION = REPLACE(CAST(DESCRIPTION AS nvarchar(max)), N'.doc?', N'?web=1')

    ...

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

    July 31, 2020 at 6:04 pm

    #3776377

  • Reply To: Backup DBs by non SQL Admin

    It's vital to insure that these backups are securely encrypted.  Highly sensitive data going to a removal drive would be a very bad practice otherwise.

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

    July 30, 2020 at 9:26 pm

    #3776137

  • Reply To: Retrieve a row with non-unique cluster keys

    Well good luck.  I would hate to have to look at actual physical data pages to do data comparisons -- and I'm a long-time DBA!

    It wasn't too bad having to...

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

    July 29, 2020 at 8:22 pm

    #3775817

  • Reply To: Retrieve a row with non-unique cluster keys

    > I need to generate some events when an insert or delete occurs and this system needs to be pluggable to any SQL database.  <<

    Presumably you would want to do...

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

    July 29, 2020 at 8:01 pm

    #3775799

  • Reply To: File import

    If the entire file is less than 2GB, I suggest BULK IMPORTing the file into a single CLOB.  Then use DelimitedSplit8K to separate it into rows (assuming each row 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".

    July 29, 2020 at 7:09 pm

    #3775789

  • Reply To: Retrieve a row with non-unique cluster keys

    No, you cannot access nor specify the "uniquifier" value added by SQL Server.

    Often it's best just to use identity or some other value to insure uniqueness yourself, and you would...

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

    July 29, 2020 at 5:00 pm

    #3775730

  • Reply To: How to add to a date column excluding weekends and holidays for SLA calculation

    Be careful, there could be a lot of matching rows for that query.

    I think you need to specify that you want the first work date only.  I also have not...

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

    July 28, 2020 at 4:22 am

    #3775265

  • Reply To: Calculate aging between two date fields excluding weekends and holidays in SQL

    Jeffrey Williams wrote:

    Jeff Moden wrote:

    ScottPletcher wrote:

    Jeff Moden wrote:

    Anand929 wrote:

    Hi,

    Please refer below code snippet -

    ;WITH DateTable AS (
    SELECT * FROM (VALUES
    ('2020-07-01',0,0),
    ('2020-07-02',0,0),
    ('2020-07-03',0,1),
    ('2020-07-04',1,0),
    ('2020-07-05',1,0),
    ('2020-07-06',0,0),
    ('2020-07-07',0,0),
    ('2020-07-08',0,0)
    ) AS t(DateValue,isWeekEnd,isHoliday)
    ),
    TicketTable AS (
    SELECT * FROM (VALUES
    ('Ticket1','2020-07-01','2020-07-06'),
    ('Ticket2','2020-07-07','2020-07-08'),
    ('Ticket3','2020-07-07',NULL)
    ) AS t(Ticket,CreateDate,ResolvedDate)
    )
    SELECT t.Ticket,t.CreateDate,t.ResolvedDate,
    DATEDIFF(DAY,t.CreateDate,t.ResolvedDate)...

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

    July 28, 2020 at 4:12 am

    #3775264

  • Reply To: Need Help to write a query

    >> from master..spt_values <<

    Referencing master db like that is a horrible idea, just stop doing it.  It's very easy instead to create your own inline table.

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

    July 24, 2020 at 4:24 pm

    #3774505

  • Reply To: Calculate aging between two date fields excluding weekends and holidays in SQL

    I have separate work_day and nonwork_day tables, for assorted reasons, including that I think it is simpler and very efficient (esp. when I need to see only nonwork days).  Code...

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

    July 23, 2020 at 4:12 am

    #3774039

  • Reply To: Calculate aging between two date fields excluding weekends and holidays in SQL

    Jeff Moden wrote:

    Anand929 wrote:

    Hi,

    Please refer below code snippet -

    ;WITH DateTable AS (
    SELECT * FROM (VALUES
    ('2020-07-01',0,0),
    ('2020-07-02',0,0),
    ('2020-07-03',0,1),
    ('2020-07-04',1,0),
    ('2020-07-05',1,0),
    ('2020-07-06',0,0),
    ('2020-07-07',0,0),
    ('2020-07-08',0,0)
    ) AS t(DateValue,isWeekEnd,isHoliday)
    ),
    TicketTable AS (
    SELECT * FROM (VALUES
    ('Ticket1','2020-07-01','2020-07-06'),
    ('Ticket2','2020-07-07','2020-07-08'),
    ('Ticket3','2020-07-07',NULL)
    ) AS t(Ticket,CreateDate,ResolvedDate)
    )
    SELECT t.Ticket,t.CreateDate,t.ResolvedDate,
    DATEDIFF(DAY,t.CreateDate,t.ResolvedDate) - SUM(d.isWeekEnd)-SUM(d.isHoliday) 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".

    July 22, 2020 at 3:39 am

    #3772165

  • Reply To: Select Statement Where Column Does Not Exist

    Be sure to specify NULL rather than letting nullability default, because the default might be NOT NULL, which would cause an error.

    ALTER TABLE #Test2

    ADD D int NULL;

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

    July 22, 2020 at 3:37 am

    #3772164

Viewing 15 posts - 2,356 through 2,370 (of 7,613 total)