Forum Replies Created

Viewing 15 posts - 2,836 through 2,850 (of 7,613 total)

  • Reply To: Scalar-valued Function Alternative

    Off the top, just streamline it as much as you can:

    BEGIN
        RETURN (
            SELECT product = 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".

    April 25, 2019 at 3:24 pm

    #3535967

  • Reply To: Using Cursor in DELETE statement

    What row was just FETCHed from, that is the row that SQL will delete, since that is the current position of the cursor.

    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 24, 2019 at 6:58 pm

    #3532057

  • Reply To: Using Cursor in DELETE statement

    Don't force SQL to fully reprocess the conditions before DELETEing each row, instead use WHERE CURRENT OF in the DELETE:

    DECLARE @user VARCHAR(50)
    DECLARE @role VARCHAR(50)

    DECLARE db_cursor CURSOR LOCAL...

    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 24, 2019 at 6:41 pm

    #3532032

  • Reply To: Generate list of working day according to rule defined. 

    ;WITH
    cteTally10 AS (
        SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
    ),
    cteTally100 AS (
        SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
      ...

    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 19, 2019 at 8:20 pm

    #3471029

  • Reply To: How to sum a number of rows with minute data where column format is datetime

    FYI, just in case you care, I was able to get my code fully corrected.

    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 18, 2019 at 8:33 pm

    #3456775

  • Reply To: How to sum a number of rows with minute data where column format is datetime

    I personally wouldn't use "+2", as it's nebulous unless you already know what it's doing; instead, I'd use the actual base date.  And, if you shift from Excel, the base...

    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 18, 2019 at 6:51 pm

    #3456026

  • Reply To: Hash Social Security Number

    Under no circumstances should you use the actual SSN to link accounts.  You might have to store the SSN in one place, but you sure don't have to store it...

    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 18, 2019 at 4:40 pm

    #3454093

  • Reply To: How to sum a number of rows with minute data where column format is datetime

    Sorry, I thought you only wanted it down to the minute based on your initial description

    duration in terms of minutes

    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 17, 2019 at 5:52 pm

    #3429660

  • Reply To: How to sum a number of rows with minute data where column format is datetime

    SELECT SUM(DATEDIFF(MINUTE, '1899-12-30', duration)) AS duration_in_mins

    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 17, 2019 at 4:39 pm

    #3427945

  • Reply To: One-to-Many entity in a single table 

    SELECT emailaddress, COUNT(DISTINCT emailID) AS emailID_Count
    FROM ##myTable
    GROUP BY emailaddress
    HAVING COUNT(DISTINCT emailID) > 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".

    April 16, 2019 at 4:45 pm

    #3413378

  • Reply To: Create Key with distinct values

    Table "C" only has each unique Name and EIN combination only 1 time, no matter how many fiscal years or amounts it has.  That't the whole point of the "C"...

    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 15, 2019 at 9:14 pm

    #3388753

  • Reply To: Create Key with distinct values

    You can use either VALUES or SELECT, you can't combine them:

    Insert into Cntrl.tbl_Entity_master

    select distinct [Entity Name], [Entity EIN] from B

    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 15, 2019 at 6:53 pm

    #3385040

  • Reply To: Create Key with distinct values

    Yep, just create another table.

    CREATE TABLE dbo.Entity_master (

    [Entity Name] nvarchar(60) NOT NULL, [Entity EIN] char(10) /*or whatever*/ NOT NULL,

    CONSTRAINT Entity_master__PK PRIMARY KEY ( [Entity Name], [Entity EIN] )

    )

     

     

    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 15, 2019 at 6:41 pm

    #3384830

  • Reply To: Good sources for SQL training?

    Itzik Ben-Gan books and/or videos.

    Books will typically you a much more in-depth knowledge.  If you'd rather just have an overview level of knowledge, then a video(s) would work.

    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 15, 2019 at 6:13 pm

    #3384058

  • Reply To: Estimated Number of Rows 4,056,100,000,000,000,000,000,000 !

    We really need to see the execution plan.  Estimated is probably good enough, although actual is better.

    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 15, 2019 at 6:12 pm

    #3383937

Viewing 15 posts - 2,836 through 2,850 (of 7,613 total)