Forum Replies Created

Viewing 15 posts - 2,791 through 2,805 (of 7,619 total)

  • Reply To: Are these good and appropriate data types?

    As regards the original "1. Key column ...", it should at least be demoted to a secondary key (to make the clustering key unique).

    This table almost certainly should be clustered...

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

    June 19, 2019 at 9:09 pm

    #3655496

  • Reply To: Calling same stored procedure

    Try this:

    BEGIN TRY

    BEGIN TRANSACTION [Tran1]

    select top (1) @caid=ca.id from Cases ca WITH  (UPDLOCK)

    where ca.applicationstatusentityID in (1,2,12,15)

    Insert into CaseAssigned table the caseId selected above

    Delete from the Cases table once a 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".

    June 19, 2019 at 7:14 pm

    #3655442

  • Reply To: Consolidate 10 second interval data to 30 minute interval data

    I ignored the actual calc before, but Drew is quite right, of course, that needs corrected too:

    SELECT TOP (100) PERCENT 
        DATEADD(SECOND, DATEDIFF(SECOND, base_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".

    June 19, 2019 at 3:45 pm

    #3655326

  • Reply To: Determine if DB name has numerics

    I'd use RIGHT rather than PATINDEX, just because I think's it mildly clearer:

    WHERE RIGHT(name, 2) LIKE '[0-9][0-9]'

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

    June 19, 2019 at 3:38 pm

    #3655325

  • Reply To: Are these good and appropriate data types?

    You can also use a computed column for as_of_month, there's no need to physically store it again.

    as_of_date date NOT NULL,

    as_of_month AS CONVERT(varchar(6), as_of_date, 112),

    That column is fully usable by all...

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

    June 18, 2019 at 8:57 pm

    #3654963

  • Reply To: Best practices for comparing ToDo tasks to completed tasks when the ToDo tasks c

    I'm guessing the ParentTaskCd is used to link the tasks together.  Naturally adjust the code as needed to get the specific results you need, but this is a general way...

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

    June 18, 2019 at 8:54 pm

    #3654962

  • Reply To: Consolidate 10 second interval data to 30 minute interval data

    Assuming you don't have dates before 1980, then:

    SELECT TOP (100) PERCENT DATEADD(MINUTE, ROUND(DATEDIFF(SECOND, base_date, DateTime) / 10.0, 0) * 30, base_date) AS Date_Time, SUM(Burner1) AS Burn1
    FROM dbo.tblOilBurner
    CROSS...

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

    June 18, 2019 at 8:32 pm

    #3654954

  • Reply To: Add working days but avoid holidays

    Here's a sample function using a physical tally table (it's not worth the trouble to me to try to use an inline tally table within a scalar function).  I've put...

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

    June 14, 2019 at 10:24 pm

    #3654008

  • Reply To: Are these good and appropriate data types?

    Don't know if it's officially deprecated, but it has lots of issues, so, yeah, probably better to stick to decimal.

    As to as_of_month, you'd be better off converting that to go...

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

    June 14, 2019 at 8:26 pm

    #3653960

  • Reply To: Are these good and appropriate data types?

    1. bigint would be better.
    2. date would be much better.
    3. don't need to store.  Use code on the display side to show only year and month from the as_of_date column.

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

    June 14, 2019 at 7:29 pm

    #3653943

  • Reply To: get value from parameter

    select * from table where sportyear=DATEADD(YEAR, -1, @year)

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

    June 14, 2019 at 2:11 pm

    #3653822

  • Reply To: Select from view where not exists in table

    Please be more specific on "Does not work" for Q.ArrangementType.  In general you should have no problem referencing columns in the view in a NOT EXISTS, so some other error...

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

    June 13, 2019 at 2:01 pm

    #3653257

  • Reply To: Parameters hard coded Views

    If you're truly on SQL 2016+, as you said, you can use SESSION_CONTEXT, as below.

    It's easier if the tables have the exact same structure, but we could "fudge" around 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".

    June 12, 2019 at 6:22 pm

    #3652872

  • Reply To: If possible need a more elegant solution to a current brute force view

    Oops, yep, sorry.  A copy/paste where I accidentally left the ", 0" at the end.

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

    June 12, 2019 at 5:33 pm

    #3652859

  • Reply To: If possible need a more elegant solution to a current brute force view

    Something along these lines:

    Declare @jan01 date
    Set @jan01 = Dateadd(Year, Datediff(Year, 0, GETDATE()), 0)

    select
    Case Left(PeriodID, 3)

    When 'Jan' THEN Dateadd(Day, -1, Dateadd(Month, 1, @jan01), 0)

    When 'Feb' THEN Dateadd(Day, -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".

    June 12, 2019 at 4:08 pm

    #3652823

Viewing 15 posts - 2,791 through 2,805 (of 7,619 total)