Forum Replies Created

Viewing 15 posts - 706 through 720 (of 1,228 total)

  • RE: Loop through data to find consecutive results prior to a certain date within a 6 month period

    VIG (12/30/2011)

    ...It was my mistake...

    Not necessarily - it could still be interpretation. The same three missed payments are captured by two different event rows. The correct solution could be one...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    December 30, 2011 at 12:26 pm

    #1427101

  • RE: Loop through data to find consecutive results prior to a certain date within a 6 month period

    VIG (12/30/2011)

    ChrisM@home (12/30/2011)

    Hey VIG, this one's right up your street, been expecting you.

    Our results differ, I get two positive rows, you get one:

    Why ???

    ClaCaseIDNameIDIncidentDateMissed

    37430 ...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    December 30, 2011 at 11:42 am

    #1427090

  • RE: Loop through data to find consecutive results prior to a certain date within a 6 month period

    Hey VIG, this one's right up your street, been expecting you.

    Our results differ, I get two positive rows, you get one:

    ClaCaseIDNameIDIncidentDateMissed

    43703754542008-09-01N

    132459640852009-02-20N

    285845806272009-08-02Y

    374305806272009-11-11Y

    417279640852009-12-11N

    2063089640852011-03-31N

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    December 30, 2011 at 10:47 am

    #1427072

  • RE: I'm stuck on this problem - Msg 4104, Level 16, State 1, Line 25 The multi-part identifier "pp.PhoneNumber" could not be bound

    andy.julson (12/29/2011)

    ...

    FROM dbo_HomePhone hp, dbo_Subscription sub, dbo_Address_G1 ad, dbo_PhoneDelivery pd

    LEFT OUTER JOIN dbo_PersonPhone pp

    ON hp.PhoneID = pp.PhoneID

    WHERE hp.PhoneID = pd.PhoneID and

    pd.AddressID = ad.AddressID and

    hp.PhoneID...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    December 30, 2011 at 8:13 am

    #1426978

  • RE: Recursive loop

    Jeff Moden (12/30/2011)

    malleswarareddy_m (12/30/2011)

    Hi,

    with cte this code will work fine.

    with cte as

    (select 1 as i

    union all

    select i+1 from cte where i<5

    )

    select 'ABC'+CAST(i as varchar) from cte

    Please read the following article...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    December 30, 2011 at 6:46 am

    #1426928

  • RE: Loop through data to find consecutive results prior to a certain date within a 6 month period

    Alter the sample data slightly so that there are 3 non-consecutive rows with PaymentStatus = 9 for ReceiverID = 964085:

    INSERT INTO #AccPayments VALUES

    (4,375454,'2008-05-17'), (4,375454,'2008-05-19'), (4,375454,'2008-06-04'), (4,375454,'2008-06-30'), (4,375454,'2008-07-28'), (4,375454,'2008-08-28'),

    (4,375454,'2008-09-29'), (4,375454,'2008-10-13'),...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    December 30, 2011 at 6:36 am

    #1426922

  • RE: Loop through data to find consecutive results prior to a certain date within a 6 month period

    INSERT INTO #Result VALUES (4370,375454,375454,'2008-09-01','N')

    INSERT INTO #Result VALUES (13245,964085,964085,'2009-02-20','N')

    INSERT INTO #Result VALUES (28584,580627,580627,'2009-08-02','Y')

    INSERT INTO #Result VALUES (37430,580627,580627,'2009-11-11','N') -- incorrect

    INSERT INTO #Result VALUES (41727,964085,964085,'2009-12-11','N')

    INSERT INTO #Result VALUES (206308,964085,964085,'2011-03-31','N')

    Select * from #Result

    ;WITH...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    December 30, 2011 at 3:18 am

    #1426827

  • RE: How to create recursive query in SQL Server?

    Post some sample data and the expected result - for more information about how best to ask a question, please read the link in my sig.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    December 30, 2011 at 2:39 am

    #1426807

  • RE: How view will help in performance and how can i identify tempdb is full

    vaithi.saran846 (12/29/2011)

    Hi,

    what is the main advantage of view.? How view will help in performance. consider i have the some complex select query and i am running that...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    December 30, 2011 at 2:19 am

    #1426799

  • RE: Loop through data to find consecutive results prior to a certain date within a 6 month period

    mic.con87 (12/29/2011)

    Sorry another thing is that ClaCaseID is unique but a NameID can have multiple ClaCaseID's:w00t:

    Post an extended sample data set which yields the incorrect results you are getting from...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    December 30, 2011 at 2:10 am

    #1426797

  • RE: Loop through data to find consecutive results prior to a certain date within a 6 month period

    pdharmaraju (12/29/2011)

    specifically you asked like " you want this logic in looping concept" , thats why i have implemented that way, otherwise i would have tried in the efficient...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    December 30, 2011 at 2:02 am

    #1426796

  • RE: Performance Issue using CTE and user defined function

    Can you post the code for the function?

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    December 30, 2011 at 1:59 am

    #1426795

  • RE: Loop through data to find consecutive results prior to a certain date within a 6 month period

    ;WITH OrderedData AS (

    SELECT

    a.*, c.ClaCaseID,

    c.IncidentDate,

    rn = ROW_NUMBER() OVER(PARTITION BY a.ReceiverID ORDER BY a.DueDate)

    FROM #AccPayments a

    INNER JOIN #ClaCases c ON c.ReceiverID = a.ReceiverID

    WHERE a.DueDate >= DATEADD(month,-6,c.IncidentDate)

    ) SELECT

    o1.ClaCaseID,...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    December 29, 2011 at 11:15 am

    #1426594

  • RE: Group by in Stored procedure

    GROUP BY won't give you what you're looking for - it's the aggregate operator. ORDER BY gives you TOP. However, unless you have a suitably-constructed rollup table or view, you...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    December 26, 2011 at 4:07 am

    #1425481

  • RE: Need help joining two tables without common key

    -- check first using the corresponding SELECT:

    SELECT a.*, b.*, c.*

    from locn a

    join acs b on a.[lid]=b.[lid]

    join aco c on c.[uid]=b.[uid]

    where c.[type]='c'

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    December 26, 2011 at 3:54 am

    #1425479

Viewing 15 posts - 706 through 720 (of 1,228 total)