Forum Replies Created

Viewing 15 posts - 1,981 through 1,995 (of 2,462 total)

  • RE: SP Help

    Below is a T-SQL script I created last month for this kind of thing. Note my comments, you will have to make a couple changes for this to work.

    1)...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    February 3, 2014 at 6:11 pm

    #1685708

  • RE: tuning query using max() over(partition by) clause

    This can still be optimized (I'm out of time here). But this should be an enormous improvement.

    WITH CTE AS

    (

    SELECT Usr,

    CASE WHEN val1=1 THEN 1 END AS val1,

    CASE WHEN...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    January 31, 2014 at 4:25 pm

    #1685253

  • RE: tuning query using max() over(partition by) clause

    I am looking at this. Is it possible for you to post the actual execution plan that is created when you run this query?

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    January 31, 2014 at 3:16 pm

    #1685230

  • RE: query help

    Sean Lange (1/31/2014)

    Alan.B (1/30/2014)

    KtmGuy (1/29/2014)

    Try executing this query ;

    UPDATE a

    SET

    a.Status = CASE WHEN b.action IN(1,3,4,5,6) THEN 'Y' ELSE 'N' END

    FROM

    TableA a

    JOIN

    TableB b

    ON

    a.SID = b.SID

    Well done!...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    January 31, 2014 at 1:38 pm

    #1685195

  • RE: query help

    KtmGuy (1/29/2014)

    Try executing this query ;

    UPDATE a

    SET

    a.Status = CASE WHEN b.action IN(1,3,4,5,6) THEN 'Y' ELSE 'N' END

    FROM

    TableA a

    JOIN

    TableB b

    ON

    a.SID = b.SID

    Well done! Just a quick...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    January 30, 2014 at 4:33 pm

    #1684884

  • RE: Need To display data in Blocks (Column name: value )

    There were some problems with your sample data which I cleaned up.

    USE tempdb

    GO

    IF OBJECT_ID('tempdb.dbo.Test_AA') IS NOT NULL DROP TABLE dbo.Test_AA

    IF OBJECT_ID('tempdb..#Test_AA_stg') IS NOT NULL DROP TABLE #Test_AA_stg

    GO

    CREATE TABLE [dbo].[Test_AA](

    ID int...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    January 29, 2014 at 11:41 am

    #1684447

  • RE: How to use scalar function without WHILE

    ChrisM@home (1/27/2014)

    vip.blade (1/27/2014)

    Hi,

    thanks for your fast response! It's a function to calculate the Levenshtein-Distance from this article: http://www.sqlservercentral.com/articles/Fuzzy+Match/92822/

    As far as I know there is no way to transform it...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    January 27, 2014 at 2:34 pm

    #1683720

  • RE: How to use scalar function without WHILE

    Hi,

    thanks for your fast response! It's a function to calculate the Levenshtein-Distance from this article: http://www.sqlservercentral.com/articles/Fuzzy+Match/92822/ [/url]

    As far as I know there is no way to transform it into an...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    January 27, 2014 at 2:28 pm

    #1683717

  • RE: SELECT INTO for running total query

    Before you go any farther, if you've got lots of rows this is going to be pretty slow because of the triangular join you're doing. You probably should take...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    January 24, 2014 at 3:18 pm

    #1683285

  • RE: SSRS report and multiple datasets

    pietlinden (1/24/2014)

    Just wondering, but can you force the queries to run in a single transaction, so they all execute and then the report is rendered?

    If there are parameters (especially cascading...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    January 24, 2014 at 3:04 pm

    #1683282

  • RE: SSRS report and multiple datasets

    jignesh209 (1/24/2014)

    SSRS is not blocking,but is it a good practice to create 6 different Datasets and tying it up with 6 different stored procedures.

    There is nothing wrong with that at...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    January 24, 2014 at 2:55 pm

    #1683278

  • RE: Running Total

    patrickmcginnis59 10839 (1/21/2014)

    Awesome link here discussing various methods:

    http://stackoverflow.com/questions/11310877/calculate-running-total-running-balance

    This article provides different ways to do a running total but has some might not be the best advice based on my experience....

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    January 24, 2014 at 12:19 pm

    #1683240

  • RE: Find the second or third record with 30 days

    This should do the trick:

    WITH visitInfo AS

    (

    SELECT *,

    RANK()

    OVER (PARTITION BY UnitNumber, YEAR(AdmitDateTime), MONTH(AdmitDateTime)

    ORDER BY AdmitDateTime) VisitSeqThisMonth

    FROM dbo.TEST

    )

    SELECTUnitNumber,

    AccountNumber,

    Diagnosis,

    AdmitDateTime,

    DischargeDateTime,

    ReadmitDate,

    DaysToReadmit,

    ReadmitAccountNumber

    FROM visitInfo

    WHERE VisitSeqThisMonth=1

    --ORDER BY UnitNumber, AdmitDateTime;

    Edit: Code cleanup,...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    January 24, 2014 at 10:16 am

    #1683205

  • RE: Order of Tables in JOIN ??

    Luis Cazares (1/23/2014)

    That's because when SQL Server is "reading" the code, it won't find a reference to Table_B alias until it gets to Table_B.

    Beat me by 3 seconds!

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    January 23, 2014 at 2:47 pm

    #1682949

  • RE: Order of Tables in JOIN ??

    homebrew01 (1/23/2014)

    I thought it didn't matter what order the JOIN statements are in a query, but if I join TABLE_B before Table_C, I get an error. If I join Table_B...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    January 23, 2014 at 2:46 pm

    #1682948

Viewing 15 posts - 1,981 through 1,995 (of 2,462 total)