Forum Replies Created

Viewing 15 posts - 2,806 through 2,820 (of 7,619 total)

  • 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

  • Reply To: How to define a table valued function to accept tables and columns as parameters

    You could do this using a stored procedure.  The structure can be returned by the query itself, you don't have to pre-define it.  In theory there are potential SQL injection...

    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 5, 2019 at 8:53 pm

    #3650585

  • Reply To: First Monday of the Month

    I can see that, so another try with the code again.  If I've done dup posts, admins please delete the extraneous post(s).

    I've been extremely busy and so haven't had time...

    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 3, 2019 at 5:56 pm

    #3649876

  • Reply To: First Monday of the Month

    I can't see my own last post(s), so I don't know if it(they) showed up or not.

    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 3, 2019 at 5:55 pm

    #3649874

  • Reply To: How to delete records from multiple tables?

    I'd urge you not to use a temp table for that.  If SQL goes down during the processing, you'll never be able to determine what the original key values were.

    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 3, 2019 at 5:18 pm

    #3649849

  • Reply To: First Monday of the Month

    The function code is in the code box.  To invoke it, do this:

    SELECT * FROM dbo.GetMonthStartAndEndDates(GETDATE())

    Or, if using with another table:

    SELECT ...

    FROM dbo.data_table dt

    CROSS APPLY dbo.GetMonthStartAndEndDates(dt.some_date_column) gm

    SET...

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

    May 30, 2019 at 8:31 pm

    #3648729

  • Reply To: SQL Date Functions: How do I convert day name to date having the date range 

    DECLARE @day varchar(9)
    DECLARE @end_date date
    DECLARE @start_date date

    SET @start_date = '20190525'
    SET @end_date = '20190531'
    SET @day = 'Wednesday'

    SELECT DATEADD(DAY, -DATEDIFF(DAY, day_number, @end_date) % 7, @end_date) AS date_you_want
    FROM (
     ...

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

    May 29, 2019 at 6:22 pm

    #3648345

  • Reply To: Insert query help 

    SELECT ca1.*
    FROM #SomeTable st
    CROSS APPLY (
        SELECT Col1, Col2, Col3, Col4
        WHERE Col4 <> 'ALL'
        UNION 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".

    May 23, 2019 at 9:26 pm

    #3646682

  • Reply To: Help Needed in sql row to column 

    ;with data as (
    select 1 as ID,'Apple' as ProductName,'1' as serving, 'g' as unit union all
    select 1 as ID,'Orange' as ProductName,'2' as serving, 'mg' as unit union...

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

    May 23, 2019 at 5:35 pm

    #3646618

  • Reply To: Indexing VARCHAR. Does Length Matter ?

    Yes, there is a penalty, because SQL bases its memory allocation on the column lengths.  If you think about it, that actually makes perfect sense, as presumably longer columns would...

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

    May 20, 2019 at 7:20 pm

    #3645521

Viewing 15 posts - 2,806 through 2,820 (of 7,619 total)