Forum Replies Created

Viewing 15 posts - 646 through 660 (of 7,613 total)

  • Reply To: query one table three for three different where variables

    I don't think so.  PIVOT works on only one column at a time, IIRC.  A cross-tab is almost certainly the best method here.

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

    September 14, 2022 at 7:12 pm

    #4087794

  • Reply To: Find Members who have lost eligibility for more than 3 years or more.........

    Great point; sorry, I accidentally left out that "current year" requirement.

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

    September 14, 2022 at 2:22 pm

    #4087626

  • Reply To: Find Members who have lost eligibility for more than 3 years or more.........

    If you just want the MemberID, then as below.  Of course you can adjust the query against the cte to pull more columns if you want them.  Either way, I...

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

    September 13, 2022 at 11:12 pm

    #4087326

  • Reply To: Large table, clustered columnstore index and NO perf. improvements.

    frederico_fonseca wrote:

    ScottPletcher wrote:

    You want the unique clustered index on ( date_, ID_vard ), assuming that you do primarily query the table by date_, and not on just ID_vard.

    Can you clarify...

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

    September 13, 2022 at 4:08 pm

    #4087185

  • Reply To: Large table, clustered columnstore index and NO perf. improvements.

    You want the unique clustered index on ( date_, ID_vard ), assuming that you do primarily query the table by date_, and not on just ID_vard.

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

    September 13, 2022 at 2:48 pm

    #4087142

  • Reply To: How do I query to exclude the "first position of a 3 digit number"

    You likely should encode the room name for internal use -- I'd probably call it "room_code" -- but you'll need at least a smallint, and I'd probably just "byte" 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".

    September 12, 2022 at 8:04 pm

    #4086799

  • Reply To: Stored procedure getting blocked and deadlocked

    I am going to add this statement at the top of the SP, it will prevent or minimize deadlocks:

    SET DEADLOCK_PRIORITY HIGH

    No, it won't.  Instead, it will force SQL to kill...

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

    September 12, 2022 at 7:51 pm

    #4086791

  • Reply To: query one table three for three different where variables

    Change the WHERE conditions to match:

    ...
    WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and --<<-- "day = " removed
    ( (month = @month and year = @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".

    September 8, 2022 at 11:38 pm

    #4085612

  • Reply To: Partition Table by Varchar column

    I think it would be as below.  You must put the partition values in alpha order.

    CREATE PARTITION FUNCTION PF_Postcode(varchar(100))

    AS RANGE LEFT FOR VALUES

    (

    '7420_FMIL',

    'C7_4JM',

    'ND8_1ZE'

    )

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

    September 8, 2022 at 11:35 pm

    #4085611

  • Reply To: query one table three for three different where variables

    brian.cook wrote:

    Hi Scott, getting an error on Column not found in this piece;

    MAX(CASE WHEN month = @month AND year = @year THEN ControlPointName END)

    That one is part of 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".

    September 8, 2022 at 4:33 pm

    #4085501

  • Reply To: Database Inconsistent Errors

    Run CHECKDB to determine errors.  Backup the existing db to make sure you'll never be worse off than you are now.

    If an error is in a non-clustered index, simply drop...

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

    September 8, 2022 at 4:30 pm

    #4085500

  • Reply To: query one table three for three different where variables

    I think this will do it, but I have not tested it.


    SELECT
        MAX(CASE WHEN month = @month AND year = @year THEN ControlPointName 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".

    September 8, 2022 at 4:13 pm

    #4085496

  • Reply To: How do I query to exclude the "first position of a 3 digit number"

    Jeff Moden wrote:

    ScottPletcher wrote:

    I'd strongly urge you to avoid a tinyint.  Mainly because the room number is "904", not just 4 (and not 94).  If you think about it, "room 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".

    September 7, 2022 at 4:25 pm

    #4085022

  • Reply To: 2 new Column from string 

    ;WITH cte_test_data AS (
        SELECT CAST('163 Bathroom {BATHROOM CLEANING}{2.5%}' AS varchar(100)) AS string
    )
    SELECT
        string AS original_string,
        CASE WHEN...

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

    September 7, 2022 at 4:18 pm

    #4085018

  • Reply To: How do I query to exclude the "first position of a 3 digit number"

    I'd strongly urge you to avoid a tinyint.  Mainly because the room number is "904", not just 4 (and not 94).  If you think about it, "room 4" is meaningless,...

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

    September 6, 2022 at 3:47 pm

    #4084534

Viewing 15 posts - 646 through 660 (of 7,613 total)