Forum Replies Created

Viewing 15 posts - 361 through 375 (of 7,613 total)

  • Reply To: table with varcahar(max)

    sqlguy80 wrote:

    3409177 rows, of course only way we query is using logid ,

    If, as you say, you are querying by a single, unique PK value, the lookup should be quick...

    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 24, 2023 at 2:51 am

    #4292829

  • Reply To: Next visit date after discharge

    I'll take a look at it a bit later today.

    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 19, 2023 at 3:03 pm

    #4289696

  • Reply To: Next visit date after discharge

    You're welcome.  Yeah, OUTER APPLY is pretty useful, like here when you kinda want a join but only want 1 row, not all of them.

    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 18, 2023 at 7:56 pm

    #4289072

  • Reply To: Next visit date after discharge 

    ;WITH CTE_ADMITS AS (
        SELECT A.*, ISNULL(LEAD(A.DISCHARGE_DATE, 1) OVER(PARTITION BY ID_NUM ORDER BY DISCHARGE_DATE), '20790601') AS NEXT_DISCHARGE
        FROM ADMITS A
    )
    SELECT A.*,...

    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 18, 2023 at 6:38 pm

    #4289031

  • Reply To: Converting file name MMDDYYYY.txt to a Date Field

    Jeff Moden wrote:

    IF the MMDDYYYY formatted string date is guaranteed to always be 8 characters, the following works (most of the posted code doesn't consider the inclusion of the ".txt") according...

    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, 2023 at 3:15 pm

    #4287214

  • Reply To: Both values between Start and End date

    I wasn't 100% sure if 101 and 102 could appear more than once in the risk table, so I coded it to allow that, just in case.

    If each can appear...

    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, 2023 at 8:57 pm

    #4286643

  • Reply To: How to get total sum across the table and sum grouped by date ranges

    What result do you want?  You never explicitly stated the results you want to see.

    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, 2023 at 6:21 pm

    #4286550

  • Reply To: How to get total sum across the table and sum grouped by date ranges

    Maybe try this:

    select k.charges,
    sum(case when k.range>='101' then charges else 0 end) as '101charges',
    sum(case when k.range>='201' then charges else 0 end) as '201charge'
    from
    (select charges,case when substring(daterange,1,3) like '0-%'...

    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, 2023 at 6:19 pm

    #4286547

  • Reply To: Converting file name MMDDYYYY.txt to a Date Field 

    SELECT CAST(SUBSTRING((getFileName, 5, 4) + 
        LEFT(getFileName, 4) AS date) AS FileNameDate 
    FROM #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".

    September 14, 2023 at 2:59 pm

    #4286465

  • Reply To: Script for deleting data from a table and corresponding FKTables

    If you can afford to have CASCADE in effect, drop and recreate the index with ON DELETE CASCADE specified.

    Hmm, yeah, if not, we'll have some complex code to write.  We...

    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, 2023 at 6:50 pm

    #4285166

  • Reply To: Database Query Optimization: How to Improve Query Performance in SQL

    Jonathan AC Roberts wrote:

    ScottPletcher wrote:

    If you are going to do that -- and then keep modifying that index endlessly as the base query add columns and otherwise changes -- you might 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".

    September 12, 2023 at 5:35 pm

    #4285148

  • Reply To: Database Query Optimization: How to Improve Query Performance in SQL

    If you are going to do that -- and then keep modifying that index endlessly as the base query add columns and otherwise changes -- you might as well do...

    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, 2023 at 5:17 pm

    #4285122

  • Reply To: Database Query Optimization: How to Improve Query Performance in SQL

    It depends, but, yes, if this query is vital for you, then change the clustering key to ( registration_date, UserID /*assuming a unique UserID*/ ) (the PK can still be...

    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, 2023 at 4:57 pm

    #4285110

  • Reply To: Script for deleting data from a table and corresponding FKTables

    SQL itself will delete from the FK-related tables if you "tell" it to: that is, you set the ON DELETE CASCADE option on for that FK.

    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, 2023 at 4:54 pm

    #4285108

  • Reply To: Backup procedure not reading last modified date

    Great, glad that resolved it.  Autoclose on or taking the db offline were the only things I could think of that would cause that to happen.

    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, 2023 at 4:46 pm

    #4281762

Viewing 15 posts - 361 through 375 (of 7,613 total)