Forum Replies Created

Viewing 15 posts - 721 through 735 (of 1,228 total)

  • RE: Group By Date loses records but Date required for join

    mic.con87 (12/22/2011)

    Well Basically I look at Staging.ClaCases and select the NameID for the records your query returned. This will return all the claims that this NameID has.

    I focus on the...

    [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 23, 2011 at 6:07 am

    #1425124

  • RE: Group By Date loses records but Date required for join

    mic.con87 (12/22/2011)

    ...

    As for the more efficient inner join GROUP BY query, results are lost when compared to the one previously posted.

    and how do the counts compare to the query "apply...

    [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 22, 2011 at 7:50 am

    #1424801

  • RE: Group By Date loses records but Date required for join

    mic.con87 (12/22/2011)

    The Query runs. The count is still incorrect but I'm assuming there is still another stage;-)

    ....

    How are you measuring this?

    [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 22, 2011 at 7:47 am

    #1424798

  • RE: Group By Date loses records but Date required for join

    mic.con87 (12/22/2011)

    So far so good, cqv.QuestionDate < it.discoverdate holds for all cases 🙂

    You might get away with the following as a more efficient alternative to the inner select - give...

    [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 22, 2011 at 6:24 am

    #1424763

  • RE: Group By Date loses records but Date required for join

    Good - then apply the next level:

    SELECT

    d.NameID,

    NoOfSOSCalls = COUNT(*)--,

    --Reported_SOS = MAX(ISNULL(CASE WHEN d.QuestionID = 202 THEN d.Answer ELSE NULL END,0))

    FROM (

    SELECT

    cc.NameID,

    --cqv.QuestionDate,

    --it.discoverdate,

    cqv.ClaQuestionHistoryID,

    MAX_ClaQuestionHistoryID = MAX(cqv.ClaQuestionHistoryID) OVER(PARTITION BY cqv.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 22, 2011 at 6:19 am

    #1424758

  • RE: Group By Date loses records but Date required for join

    mic.con87 (12/22/2011)

    The query returns results however they are incorrect. I had to change the alias cc.QuestionDate to cqv.QuestionDate...The count doesn't seem to take into account cqv.DiscoverDate < it.discoverdate...

    They will...

    [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 22, 2011 at 5:57 am

    #1424743

  • RE: Group By Date loses records but Date required for join

    mic.con87 (12/22/2011)

    I had a look at your code and I found something that ensures no results will be returned ie AND it.QuestionClassID = cqv.QuestionClassID

    it.QuestionClassID will never be the same as...

    [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 22, 2011 at 5:30 am

    #1424733

  • RE: Group By Date loses records but Date required for join

    mic.con87 (12/22/2011)

    No results are returned even if I do comment out the where clause...

    Okaaaay...comment out the join to the temp table, it's the only change left. Also, please post the...

    [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 22, 2011 at 4:34 am

    #1424713

  • RE: Group By Date loses records but Date required for join

    Sure. Focus on the inner SELECT:

    SELECT

    cc.NameID,

    cqv.QuestionID,

    cqv.Answer,

    cqv.ClaQuestionHistoryID,

    MAX_ClaQuestionHistoryID = MAX(cqv.ClaQuestionHistoryID) OVER(PARTITION BY cqv.ClaCaseID, cqv.QuestionID, cqv.QuestionClassID)

    FROM Staging.ClaCases cc

    INNER JOIN #InitialTable_24 it ON it.NameID = cc.NameID

    INNER JOIN StagingViews.ClaQuestionView cqv

    ON...

    [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 22, 2011 at 4:24 am

    #1424707

  • RE: First name And last Name in SQL

    Dev (12/22/2011)

    ChrisM@home (12/22/2011)

    Dev (12/21/2011)

    "Doctor & Mrs. Johnathan van Huron Esq. - DO, MD, MVP"

    Title FName LName Degree

    Agree on Names but Titles & Degrees shouldn't be in...

    [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 22, 2011 at 4:03 am

    #1424695

  • RE: Group By Date loses records but Date required for join

    mic.con87 (12/22/2011)

    ...when using your code i receive the error: 'The column 'ClaCaseID' was specified multiple times for 'd'.'

    I'm not sure what you mean by "Also, can you change the column...

    [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 22, 2011 at 3:29 am

    #1424674

  • RE: Group By Date loses records but Date required for join

    mic.con87 (12/22/2011)

    ...

    My problem is I need to add condition: where CTE.QuestionDate < it.discoverdate

    Now obtaining CTE.QuestionDate is a problem. When I use this in the Group By clause in the '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 22, 2011 at 3:00 am

    #1424651

  • RE: Group By Date loses records but Date required for join

    Hello

    The first query in your post shows a LEFT JOIN, but there are references to columns from it in the WHERE clause, resulting in an INNER join.

    Written slightly differently, it...

    [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 22, 2011 at 2:34 am

    #1424632

  • RE: First name And last Name in SQL

    Dev (12/21/2011)

    "Doctor & Mrs. Johnathan van Huron Esq. - DO, MD, MVP"

    Title FName LName Degree

    Agree on Names but Titles & Degrees shouldn't be in source data.

    Try telling 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 22, 2011 at 1:53 am

    #1424624

  • RE: First name And last Name in SQL

    Jeff Moden (12/21/2011)

    ...Even though that works, what are you going to do with first names that begin with a salutation, last names that end with other titles and suffixes, and...

    [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 22, 2011 at 1:45 am

    #1424622

Viewing 15 posts - 721 through 735 (of 1,228 total)