Forum Replies Created

Viewing 15 posts - 571 through 585 (of 7,613 total)

  • Reply To: Return 80 % match 

    SELECT VendorName
    FROM VenInfo
    GROUP BY VendorName
    HAVING SUM(CASE WHEN VenSN = 'Y' THEN 1 ELSE 0 END) * 100.0 / SUM(1) >= 80

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

    January 9, 2023 at 3:25 pm

    #4135523

  • Reply To: Generation of Auto Series based on logic

    Nor trying to be difficult, but that just lists the values, it doesn't assign them as the table ID in the existing table, as stated in the OP.

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

    January 6, 2023 at 10:40 pm

    #4134715

  • Reply To: Create a Job Schedule that runs on the 1st of month only if it falls on a Sunday

    You could schedule a job to run on the first of the month, but immediately exit the job if that day is a Sunday.  Add a second sched to 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".

    January 1, 2023 at 2:02 am

    #4132327

  • Reply To: Best primarykey and index

    frederico_fonseca wrote:

    ScottPletcher wrote:

    Jeff Moden wrote:

    ScottPletcher wrote:

    To be absolutely clear, you want a:

    2) unique clustered [index] on (tenant,year,id)

    And if you partition the data (which typically wouldn't be necessary here), partition it by...

    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 30, 2022 at 10:11 pm

    #4132068

  • Reply To: Trouble with date values

    Jeffrey Williams wrote:

    ScottPletcher wrote:

    You can, and definitely should, get rid of the other ISNULLs in your WHERE:

    AND ISNULL(d.value_2,'') NOT IN ('Clay','Volusia') --replace with -->  AND d.value_2 NOT IN ('Clay','Volusia')

    AND ISNULL(rc.referral_category_ud,'') <>...

    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 30, 2022 at 9:43 pm

    #4132066

  • Reply To: Trouble with date values

    You can, and definitely should, get rid of the other ISNULLs in your WHERE:

    AND ISNULL(d.value_2,'') NOT IN ('Clay','Volusia') --replace with

    -->  AND d.value_2 NOT IN ('Clay','Volusia')

    AND ISNULL(rc.referral_category_ud,'') <> 'FFS_NO_TAKE_BAC' --replace with

    -->...

    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 30, 2022 at 5:29 pm

    #4131977

  • Reply To: Best primarykey and index

    Jeff Moden wrote:

    ScottPletcher wrote:

    To be absolutely clear, you want a:

    2) unique clustered [index] on (tenant,year,id)

    And if you partition the data (which typically wouldn't be necessary here), partition it by (tenant, 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".

    December 30, 2022 at 3:55 pm

    #4131945

  • Reply To: Split One Column into Multiple that uses / as the delimiter

    It is.  Google for function "dbo.DelimitedSplit8K".  It's a function you install once and can use from then on for this.

    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 30, 2022 at 3:54 pm

    #4131944

  • Reply To: Best primarykey and index

    To be absolutely clear, you want a:

    2) unique clustered [index] on (tenant,year,id)

    And if you partition the data (which typically wouldn't be necessary here), partition it by (tenant, year) not just...

    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 30, 2022 at 2:58 pm

    #4131906

  • Reply To: Subtract calculated columns

    Seems like you might be looking for a total?!:

    SELECT yt.account_number, SUM(ca1.Debit) AS Total_Debits, SUM(ca1.Credit) AS Total_Credits, 
        SUM(ca1.Debit) - SUM(ca1.Credit) AS Net_Amount
    FROM dbo.your_table yt
    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".

    December 29, 2022 at 5:56 pm

    #4131590

  • Reply To: Proper CASE statement

    Use COALESCE instead:

    COALESCE(fa.S1SDAT, ar2.S1ADAT, ar.S1SDATE)

    ...

    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 28, 2022 at 9:12 pm

    #4131249

  • Reply To: Best table structure for a tall table that needs to be pivoted

    Definitely use char(2) for the values, as suggested (varchar requires two bytes of extra overhead (to store length) per column).

    If they're not already encoded, encode the names.  That is, "Code1"...

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

    #4130417

  • Reply To: A question about stored procedures and Transactions

    Yes, those procs will both run under the same transaction (assuming no COMMIT(s) in the procs).

    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 22, 2022 at 9:20 pm

    #4129075

  • Reply To: Query help

    ? Don't see anything attached ?

    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 21, 2022 at 8:50 pm

    #4128620

  • Reply To: get a substring value from string

    carsten.saastamoinen wrote:

    No, a solution like this is hopefully only on few rows - as mentioned earlier, else change the way to solve the problem. And if my solution runs in...

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

    #4127653

Viewing 15 posts - 571 through 585 (of 7,613 total)