Forum Replies Created

Viewing 15 posts - 2,491 through 2,505 (of 7,619 total)

  • Reply To: Convert Varchar to Int and SUM

    SELECT SUM(TRY_CAST(LEFT(RE80_VALORE, CHARINDEX('.', RE80_VALORE + '.')) AS int))

    FROM dbo.RE80_MOVSCHPUNTI

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

    February 21, 2020 at 3:55 pm

    #3727732

  • Reply To: Changing column datatype performance question

    I think it's a bit more complex than that, at least if you want to keep existing code.  Personally I would not try rewrite all existing INSERT(s) / UPDATE(s) on...

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

    February 19, 2020 at 9:17 pm

    #3727202

  • Reply To: Changing column datatype performance question

    I'd create completely new tables.

    One table to hold all the varchar values and their new int value, as you're already done, I'm sure.  Load it with all the distinct values...

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

    February 19, 2020 at 6:29 pm

    #3727159

  • Reply To: Question on restoring a backup

    <Is it true that for those reasons he/ I could not do this?>

    No, SQL Server would never allow one file to be used by two different dbs at the same...

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

    February 18, 2020 at 10:54 pm

    #3726914

  • Reply To: Extract part of string

    ChrisM@Work wrote:

    Less than four functions, anyone?

    SELECT string, numbers
    FROM ( VALUES
        ('Total # of bytes : 128270200832 (119.46GB)'),
        ('Total # of bytes...

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

    February 18, 2020 at 5:13 pm

    #3726834

  • Reply To: Extract part of string

    To allow for some variations in the format:

    SELECT string, numbers
    FROM ( VALUES
        ('Total # of bytes : 128270200832 (119.46GB)'),
        ('Total #...

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

    February 18, 2020 at 3:29 pm

    #3726774

  • Reply To: Add two rows to a view

     

    Select p.PREmp, p.PRDept, 0 As [Admin] 
    From Payroll p
    UNION ALL
    Select h.PREmp, p.PRDept, 1 As [Admin] 
    From HR h 
    cross join (
        select distinct PRDept
    ...

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

    February 17, 2020 at 10:57 pm

    #3726609

  • Reply To: Handling Aggregations on a Poorly Designed Database

    How's about this instead?!:

    SELECT
        us.Name
        ,SUM(wl.HoursWorked) / COUNT(DISTINCT us.TeamID) AS HoursWorked
    FROM Users us
    INNER JOIN WorkLog wl ON wl.UserKey = us.UserKey
    GROUP 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".

    February 17, 2020 at 8:08 pm

    #3726565

  • Reply To: How to remove decimal trailing zeroes of varchar(50) in SQL Server

    WHEN 'SCC' THEN CONCAT(' (', CAST(ROUND([Column], 0, 1) AS int), ' ', nestedTempTableAlerts.[Threshold_Value], ')')

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

    February 17, 2020 at 7:22 pm

    #3726549

  • Reply To: How to remove decimal trailing zeroes of varchar(50) in SQL Server

    SELECT value AS original_value,

    CAST(ROUND(value, 0, 1) AS int) AS value0,

    CAST(ROUND(value, 2, 1) AS decimal(9, 2)) AS value2,

    CAST(ROUND(value, 1, 1) AS decimal(9, 1)) AS value1

    FROM ( VALUES(100.199) ) AS test_data(value)

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

    February 17, 2020 at 5:58 pm

    #3726531

  • Reply To: Insert Data and read all Foreign Keys/Constraints first

    That code is for one row, so whatever values you are INSERTing for the current row contains the key(s).

    For multiple rows / a batch of rows, yes, you would initially...

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

    February 13, 2020 at 6:52 pm

    #3725720

  • Reply To: Insert Data and read all Foreign Keys/Constraints first

    You'll want to add ERROR_MESSAGE() at least to your CATCH code.  That will give you the specific error msg SQL issued.  You should probably go ahead and add ERROR_LINE(), although...

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

    February 13, 2020 at 6:07 pm

    #3725704

  • Reply To: math issue

    See if this works for you:

    ;WITH cte_raw_counts AS (
        SELECT COUNT(*) AS total_company_count,
            SUM(CASE WHEN rr.[company_name] =...

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

    February 12, 2020 at 8:17 pm

    #3725358

  • Reply To: Insert Data and read all Foreign Keys/Constraints first

    I think it would be more accurate and far less work to rely on the existing constraints.

    You should be able to use a BEGIN TRANSACTION and a TRY/CATCH block 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".

    February 12, 2020 at 3:42 pm

    #3725155

  • Reply To: Blocking during bulk insert between table swaps

    I'd do an explicit sp_recompile on all the tables affected by the renames, just to be safe.  Renaming is great, but it's a short-cut method that's not fully "recognized" 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".

    February 10, 2020 at 10:29 pm

    #3724536

Viewing 15 posts - 2,491 through 2,505 (of 7,619 total)