Forum Replies Created

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

  • Reply To: Conversion of currency stored as varchar to decimal

    SELECT CONVERT (decimal(9, 2) , CAST('$15,000.50' AS money))

    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 20, 2022 at 3:10 pm

    #4089866

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

    No need to specify the last day / number of days in the month, that can easily be calculated:

    (CASE WHEN MONTH = @month AND DAY = @day...

    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 20, 2022 at 2:26 pm

    #4089847

  • Reply To: a good book on SQL

    I agree.  In fact, any book on T-SQL by Ben-Gan will be worth studying.

    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 15, 2022 at 2:52 pm

    #4088133

  • Reply To: How to get Key based on type and sub type

    aaron.reese wrote:

    You might find it easier to reason about with EXCEPT and INTERCECT.

    SELECT DISTINCT KEY FROM myTable WHERE type = 'COLL' and subtype = 'MOD'

    EXCEPT

    SELECT DISTINCT KEY...

    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 9:26 pm

    #4087824

  • Reply To: How to get Key based on type and sub type

    Yes.  If you only want a key that has both conditions, change the MAX:

    MAX(CASE WHEN type = 'QUAT' AND subtype = 'BALANCE' THEN 1 ELSE 0 END) = 1

    Note that...

    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 8:46 pm

    #4087815

  • Reply To: How to get Key based on type and sub type 

    SELECT key
    FROM dbo.your_table_name_here
    GROUP BY key
    HAVING MAX(CASE WHEN type = 'COLL' AND subtype = 'MOD' THEN 1 ELSE 0 END) = 1 AND
        MAX(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 14, 2022 at 7:46 pm

    #4087803

  • 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

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