Forum Replies Created

Viewing 15 posts - 586 through 600 (of 7,613 total)

  • Reply To: calculate 12 months of data from a given date from a user

    If you want only 12 months of data, then you'll need to restrict the ending date too.  Assuming you want to include the month that @Date is in, then:

    WHERE

    tblPropsFreeRent.dtMonth >=...

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

    December 19, 2022 at 7:04 pm

    #4127633

  • Reply To: get a substring value from string 

    select *, 
        case when last_bar = 0 or next_to_last_bar = 0 then ''
             else SUBSTRING(serialnumber,...

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

    December 14, 2022 at 8:07 pm

    #4125682

  • Reply To: SQL Syntax for BETWEEN any dates and specific time

    I'd be surprised if an index on a computed column ultimately helped the query plan at all.  Unless you have a fully covering index, which would likely duplicate most of...

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

    December 14, 2022 at 3:06 pm

    #4125545

  • Reply To: SQL Syntax for BETWEEN any dates and specific time

    Yes, it would.  I took:

    i want to select from 00:00:00 - 06:00:00

    to mean a contiguous range from 00 to 06, but I likely should have used 00 and 05, if...

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

    December 13, 2022 at 7:24 pm

    #4125136

  • Reply To: record loading

    To keep the query on the remote server "sargable", you shouldn't use any function on its columns in the WHERE, so something more like this (I may not have the...

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

    December 13, 2022 at 7:19 pm

    #4125135

  • Reply To: Grouping Same ID's with different Names

    Glad it helped!  Here's the 2nd version I mentioned: if you don't have more than 2 total dups, this might be easier to use.

    --(2)
    SELECT ID, 
     ...

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

    December 12, 2022 at 7:06 pm

    #4124678

  • Reply To: SQL Syntax for BETWEEN any dates and specific time

    WHERE DATEPART(HOUR, datetime_column) BETWEEN 0 AND 6

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

    December 12, 2022 at 7:03 pm

    #4124677

  • Reply To: Grouping Same ID's with different Names

    If you want the details too, you can either:

    (1) join back to the main table to list all rows <or>

    (2) list the min, max and count

    --(1)
    SELECT t1.*
    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".

    December 12, 2022 at 4:06 pm

    #4124644

  • Reply To: Grouping Same ID's with different Names 

    SELECT ID
    FROM #tst
    GROUP BY ID
    HAVING 
        MIN(FNAME) <> MAX(FNAME) OR
        MIN(LNAME) <> MAX(LNAME) OR
        MIN(DOB) <> MAX(DOB)

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

    December 12, 2022 at 4:04 pm

    #4124643

  • Reply To: I have an odd situation that I just can't wrap my head around

    Each location can't be completely separate if email addresses must be unique.  You need to replicate the email addresses across all tables, or have a "master" email table that contains...

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

    December 12, 2022 at 3:02 pm

    #4124635

  • Reply To: Problem Importing NULLs in varchar field

    You could also explicitly define ("format") the column as "Text" data type in Excel.

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

    December 8, 2022 at 3:51 pm

    #4123449

  • Reply To: Either/ Or Query

    Here's a quick way:

    DECLARE @JobDomain int
    SET @JobDomain = 1

    ;WITH cte_Type_B AS (
        SELECT *
        FROM dbo.TestTable
        WHERE JobDomain...

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

    December 8, 2022 at 1:09 am

    #4123169

  • Reply To: Optimization for Single Table with Thousands of Concurrent Updates

    Is the clustered index on the table on BaseID?

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

    December 6, 2022 at 4:01 pm

    #4122589

  • Reply To: SQL bug

    You can also use:

    '20211130 00:00:00.000'

    The only special chars you need are in the time, no - no T.

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

    October 25, 2022 at 10:27 pm

    #4104501

  • Reply To: STAT command conindex equivalent

    I've never heard of anything like that in SQL Server.

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

    October 25, 2022 at 10:23 pm

    #4104500

Viewing 15 posts - 586 through 600 (of 7,613 total)