Forum Replies Created

Viewing 15 posts - 1,336 through 1,350 (of 7,613 total)

  • Reply To: YTD Date calculation

    Phil Parkin wrote:

    CLR functions for performing Regular Expression searches aren't SQL either, but they're useful. Where do you draw the line?

    IIF is part of T-SQL and is more succinct than CASE,...

    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 18, 2021 at 3:07 pm

    #3921412

  • Reply To: YTD Date calculation

    Jeff Moden wrote:

    ScottPletcher wrote:

    I don't see the need for all that, this is much simpler:

    SET @ytdfrDate = DATEFROMPARTS(YEAR(@frdate) - 
        CASE WHEN MONTH(@frdate) < 4...

    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 18, 2021 at 12:54 pm

    #3921252

  • Reply To: Create a delta from downloaded info

    Jeff Moden wrote:

    Invoice line items can have a ship date

    Only with a bad data model.  Parts from the same invoice line could be shipped on different dates.  You don't split...

    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 18, 2021 at 5:03 am

    #3921134

  • Reply To: YTD Date calculation

    I don't see the need for all that, this is much simpler:

    SET @ytdfrDate = DATEFROMPARTS(YEAR(@frdate) - 
        CASE WHEN MONTH(@frdate) < 4 THEN 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".

    August 18, 2021 at 4:59 am

    #3921132

  • Reply To: Create a delta from downloaded info

    Jeff Moden wrote:

    Bruin wrote:

    With the same Line,subline?

    Look at the PK you've chosen.  IMHO, only the first 3 columns of InvoiceNbr, Line, and SubLine should make up the PK.

    I'm more confused than...

    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 18, 2021 at 4:49 am

    #3921129

  • Reply To: Data type convert help

    CONVERT works on a single value at a time.  Thus, the query should be more like:

    and a.myDate BETWEEN CONVERT(date, '20201108') AND CONVERT(date, '20201116')

    The strings will have to be converted 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".

    August 18, 2021 at 4:41 am

    #3921126

  • Reply To: Is that a reason DBA should Install SQL Server

    Either one can do the install.  The DBA will need to provide some settings for the Windows team, but that team should be able to do an install as well.

    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 17, 2021 at 3:50 pm

    #3920829

  • Reply To: SP causing blocking

    No.

    If it's doing a lookup only (not modifying data, just reading it), you could also use WITH (NOLOCK) on the table to reduce overhead of the lookups.  I would argue...

    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 17, 2021 at 1:53 am

    #3920566

  • Reply To: Returning Database Properties related to "Change Tracking"

    View sys.change_tracking_databases returns the db names of all dbs that have change tracking enabled.

    Thus, the db name will not be in the view if change tracking is off.

    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 17, 2021 at 1:51 am

    #3920565

  • Reply To: Cursor replacement suggestions

    You could do some major clean up on your existing code:

    1. Don't use nvarchar unless you absolutely must have it, use varchar instead.

    2. Don't use (MAX) unless you absolutely must...

    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 16, 2021 at 3:47 pm

    #3920418

  • Reply To: Deadlock where victim has two shared locks and blocker two exclusive

    There are only two processes involved in the deadlock.

    Make sure you have an index on

    dvd ( bed_id, wns_id, dvb_id )

    Actually, all these tables should almost certainly be clustered first on bed_id,...

    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 16, 2021 at 2:40 pm

    #3920395

  • Reply To: Problem with query that has a date conversion to 'yyyy-mm-dd' format

    ...

    N'<th>Count of Records Successfully Uploaded Today: </th></tr>' +

    -- Below is the query of which the results are passed into the rectangle

    CAST((SELECT COUNT(*) FROM .[dbo].[Stage_SMSLog]

    with (NOLOCK) WHERE datestamp = CONVERT(nvarchar(10), GetDate(),120))...

    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 12, 2021 at 6:03 pm

    #3919358

  • Reply To: Problem with query that has a date conversion to 'yyyy-mm-dd' format

    You need format 120 rather than 20: 20 will leave off the century, so you'd get 21 for yy instead of 2021, therefore it won't match.

    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 12, 2021 at 4:58 pm

    #3919329

  • Reply To: Update a table with inserted value of another table

    A trigger would be more consistent than using a proc, unless you only want to do this for certain, specific INSERTs:

    USE tempdb;

    IF OBJECT_ID('dbo.FirstTable') IS NOT 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".

    August 11, 2021 at 7:41 pm

    #3918914

  • Reply To: convert time for daylight saving ..UTC to EST

    AT TIME ZONE clause.

    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 11, 2021 at 5:19 pm

    #3918878

    • Viewing 15 posts - 1,336 through 1,350 (of 7,613 total)