Forum Replies Created

Viewing 15 posts - 2,371 through 2,385 (of 7,619 total)

  • 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

  • Reply To: ORACLE TO SQL CONVERSION

    Something like below.  It looks the original trigger is an INSTEAD OF INSERT and AFTER UPDATE at the same time.  SQL Server doesn't allow you to define them together.  If...

    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:26 am

    #3772138

  • Reply To: Rebuild or Reorganize index

    The pages might "bubble up" one at a time, but that has nothing to do with a "bubble sort", which requires vastly more RAM / storage.

    Also, it seems to that...

    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 20, 2020 at 6:07 pm

    #3771456

  • Reply To: Rebuild or Reorganize index

    Erland Sommarskog wrote:

    But, yes, depending in which way your index is disorganised, REORGANIZE can mean a lot of operations, as it performs a bubble sort of the file.

    I don't think so. ...

    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 20, 2020 at 2:40 pm

    #3771361

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

    Something like this:

    SELECT A.TicketID, A.Status, A.CreatedDate, A.ResolvedDate,
        (SELECT COUNT(*) FROM dbo.Date_Table DT 
         WHERE DT.Date >= A.CreatedDate AND DT.Date >=...

    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 20, 2020 at 2:35 pm

    #3771359

  • Reply To: Query 

    ;WITH cte_date_calcs AS (
        SELECT 
            CASE WHEN todays_day >= 16 
         ...

    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 17, 2020 at 7:36 pm

    #3770963

  • Reply To: Rebuild or Reorganize index

    REORGANIZE is not meant to reduce total / overall I/O, rather it's meant to do it in chunks instead on in bulk, as REBUILD must do it.  You're expecting REORG...

    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 17, 2020 at 7:19 pm

    #3770959

  • Reply To: Rebuild or Reorganize index

    I've found REORGANIZE is often useful when a lot of rows have been deleted / purged from a table.  In that situation, I don't want the other pages messed with...

    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 17, 2020 at 4:15 pm

    #3770890

  • Reply To: Rebuild or Reorganize index

    You shouldn't routinely rebuild or reorganize indexes just because you can.

    Far more important is to insure that you have the best clustered index on every existing table.  Naturally that requires...

    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 16, 2020 at 4:45 pm

    #3770588

  • Reply To: Isnull in a Where Clause

    For a single conditional column comparison, there's no reason to use multiple queries or procs, as it's unnecessarily overly complex.

    As to using ISNULL(), that should simply never be used 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".

    July 14, 2020 at 7:48 pm

    #3769863

Viewing 15 posts - 2,371 through 2,385 (of 7,619 total)