Forum Replies Created

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

  • 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

  • Reply To: Group by month/year

    Use just one date for each month; in keeping with "standard" practice, I use the first of the month.

    Also, you must remove H.Quantity from the GROUP BY.

    select...

    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 2:11 pm

    #3652757

  • Reply To: How to Retrieve a Substring From a Larger String of Varying Length

    Again, MS is following the relational model, where a set does not have a defined order.  That may be fine theoretically, but in the real world we often do need...

    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 11, 2019 at 7:42 pm

    #3652476

  • Reply To: How to Retrieve a Substring From a Larger String of Varying Length

    Here's another alternative to splitting, to show how CROSS/OUTER APPLY can assign alias names to computed values that can be used in subsequent APPLYs and in the SELECT itself, without...

    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 11, 2019 at 2:23 pm

    #3652329

  • Reply To: How to Retrieve a Substring From a Larger String of Varying Length

    Presumably MS is following relational theory, where the order of a set doesn't matter.

    But, in the real world, the order often does matter a lot, especially when the set has...

    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 10, 2019 at 9:01 pm

    #3651962

  • Reply To: How to update a table that causes temporary duplicate index

    Note that you don't have to drop the index and re-create it.

    Instead you can DISABLE it, then REBUILD it.

    ALTER INDEX no_dups_index ON dbo.your_table DISABLE;

    UPDATE your_table

    SET ... = ... + 5

    /*WHERE...

    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 10, 2019 at 4:38 pm

    #3651903

  • Reply To: Count of entries & grouped by month - extracted from a date 

    SELECT MonthCount AS [Total Entries],
        DATENAME(MONTH, RecvdMonth) + ' ' + CAST(YEAR(RecvdMonth) AS varchar(4)) AS MONTH

    FROM (

    SELECT

    DATEADD(MONTH, DATEDIFF(MONTH, 0, RecvdDate), 0) AS RecvdMonth,
    COUNT(ID) 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".

    June 6, 2019 at 3:03 pm

    #3650802

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