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 it a try:

    SELECT

    cc.NameID,

    MAX_ClaQuestionHistoryID = MAX(cqv.ClaQuestionHistoryID)

    FROM Staging.ClaCases cc

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

    INNER JOIN StagingViews.ClaQuestionView cqv

    ON cqv.ClaCaseID = cc.ClaCaseID

    AND cqv.QuestionClassID = cc.QuestionClassID

    AND cqv.Question = 'is this for sos?'

    WHERE cqv.QuestionDate < it.discoverdate

    GROUP BY cc.NameID, cqv.ClaCaseID, cqv.QuestionID, cqv.QuestionClassID


    [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]

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

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

  • 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]

  • 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 the next level"?


    [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]

  • Very nice discussion on SQL query. Thank you for this information

    S. Shradha

    SQL DBA

    http://www.dbawork.com

  • 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 columns in Staging.ClaCases : ClaCaseID, NameID, QuestionClassID and ClaClassID.

    For 'is this for sos?' QuestionClassID should = 120 and ClaClassID = 120.

    FYI QuestionClassID is also on StagingViews.ClaQuestionView if this helps at all as a possible join or check.

    I then perform a manual count of the number of times that QuestionClassID = 120 and match this with your result.

    What I'm seeing is that in some cases the results returned from your query are perfect in that the count of NoOfSOSCalls is correct for that NameID but in other cases your results return too many records which may have something to do with the fact that selecting MAX(ClaQuestionHistoryID) is not working properly and returning potential duplicates or there could be a bad join.

    Also condition QuestionDate < it.discoverdate doesn't seem to have taken effect at all. Even in the cases where your count is correct it is incorrect in terms of this condition.

  • 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 columns in Staging.ClaCases : ClaCaseID, NameID, QuestionClassID and ClaClassID.

    For 'is this for sos?' QuestionClassID should = 120 and ClaClassID = 120.

    FYI QuestionClassID is also on StagingViews.ClaQuestionView if this helps at all as a possible join or check.

    I then perform a manual count of the number of times that QuestionClassID = 120 and match this with your result.

    What I'm seeing is that in some cases the results returned from your query are perfect in that the count of NoOfSOSCalls is correct for that NameID but in other cases your results return too many records which may have something to do with the fact that selecting MAX(ClaQuestionHistoryID) is not working properly and returning potential duplicates or there could be a bad join.

    Also condition QuestionDate < it.discoverdate doesn't seem to have taken effect at all. Even in the cases where your count is correct it is incorrect in terms of this condition.

    Can you knock up some sample data which displays this? Both 'too many records' and the date filter not working? The exact same data if you can would be marvellous. You don't need too many rows in each table, just enough to show both 'anomalies' with some data which appears to conform.


    [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]

  • @ ChrisM@Home: I'm extremely sorry for the late reply! I actually completely forgot we were busy with this, used your previous code and moved on to the other problems you have been assisting with.

    Here is some sample data that will hopefully clarify the matter.

    #Results is what your query returns and if you compare this with #ClaCases you can see that the count is not accurate.

    Create Table #ClaCases (ClaCaseID INT,NameID int, DiscoverDate Date, QuestionClassID int )

    INSERT INTO #ClaCases VALUES (8476,146773,'2008-10-25',120)

    INSERT INTO #ClaCases VALUES (21378,146773,'2009-05-23',120)

    INSERT INTO #ClaCases VALUES (25622,146773,'2009-06-27',25)

    INSERT INTO #ClaCases VALUES (36134,146773,'2009-10-28',30)

    INSERT INTO #ClaCases VALUES (42582,146773,'2010-01-23',20)

    INSERT INTO #ClaCases VALUES (42587,146773,'2009-10-23',20)

    INSERT INTO #ClaCases VALUES (54354,146773,'2010-04-16',120)

    INSERT INTO #ClaCases VALUES (75721,146773,'2009-09-25',120)

    INSERT INTO #ClaCases VALUES (103840,146773,'2010-09-13',120)

    INSERT INTO #ClaCases VALUES (166879,146773,'2010-12-24',120)

    INSERT INTO #ClaCases VALUES (19039,146927,'2009-04-29',120)

    INSERT INTO #ClaCases VALUES (99403,146927,'2010-09-8',30)

    INSERT INTO #ClaCases VALUES (350439,146927,'2011-09-1',20)

    INSERT INTO #ClaCases VALUES (3099,147109,'2008-06-17',120)

    INSERT INTO #ClaCases VALUES (6484,147109,'2008-10-29',130)

    INSERT INTO #ClaCases VALUES (17358,147109,'2009-04-8',200)

    INSERT INTO #ClaCases VALUES (17365,147109,'2009-04-8',170)

    INSERT INTO #ClaCases VALUES (27783,147109,'2009-06-15',120)

    INSERT INTO #ClaCases VALUES (42166,147109,'2010-01-20',30)

    INSERT INTO #ClaCases VALUES (84995,147109,'2010-08-9',30)

    INSERT INTO #ClaCases VALUES (173117,147109,'2011-01-21',20)

    INSERT INTO #ClaCases VALUES (310062,147109,'2011-07-8',210)

    INSERT INTO #ClaCases VALUES (21645,148873,'2009-05-22',20)

    INSERT INTO #ClaCases VALUES (34213,148873,'2009-09-14',30)

    INSERT INTO #ClaCases VALUES (85410,148873,'2010-08-12',110)

    INSERT INTO #ClaCases VALUES (93336,148873,'2010-08-31',120)

    INSERT INTO #ClaCases VALUES (95939,148873,'2010-08-27',30)

    INSERT INTO #ClaCases VALUES (143856,148873,'2010-11-4',120)

    INSERT INTO #ClaCases VALUES (181616,148873,'2011-03-1',30)

    INSERT INTO #ClaCases VALUES (348993,148873,'2011-10-22',210)

    INSERT INTO #ClaCases VALUES (349043,148873,'2011-10-22',210)

    INSERT INTO #ClaCases VALUES (349093,148873,'2011-10-22',20)

    INSERT INTO #ClaCases VALUES (13692,149357,'2009-03-10',20)

    INSERT INTO #ClaCases VALUES (27004,149357,'2009-07-13',30)

    INSERT INTO #ClaCases VALUES (36773,149357,'2009-09-7',120)

    INSERT INTO #ClaCases VALUES (163084,149357,'2010-12-15',20)

    INSERT INTO #ClaCases VALUES (241721,149357,'2011-05-3',20)

    INSERT INTO #ClaCases VALUES (2522,151327,'2008-05-8',120)

    INSERT INTO #ClaCases VALUES (13921,151327,'2008-12-2',120)

    INSERT INTO #ClaCases VALUES (39671,151327,'2009-12-12',20)

    INSERT INTO #ClaCases VALUES (76041,151327,'2010-07-1',20)

    INSERT INTO #ClaCases VALUES (175337,151327,'2011-02-3',20)

    INSERT INTO #ClaCases VALUES (207801,151327,'2011-04-2',200)

    INSERT INTO #ClaCases VALUES (273063,151327,'2011-06-9',25)

    INSERT INTO #ClaCases VALUES (2238,151572,'2008-04-7',120)

    INSERT INTO #ClaCases VALUES (20882,151572,'2009-05-19',120)

    INSERT INTO #ClaCases VALUES (21064,151572,'2009-05-22',20)

    INSERT INTO #ClaCases VALUES (35011,151572,'2009-10-12',30)

    INSERT INTO #ClaCases VALUES (59164,151572,'2010-05-20',20)

    INSERT INTO #ClaCases VALUES (219303,151572,'2011-04-15',20)

    INSERT INTO #ClaCases VALUES (287601,151572,'2011-06-23',20)

    INSERT INTO #ClaCases VALUES (14297,152285,'2009-03-17',120)

    INSERT INTO #ClaCases VALUES (19571,152285,'2009-05-10',120)

    INSERT INTO #ClaCases VALUES (40980,152285,'2009-12-14',120)

    INSERT INTO #ClaCases VALUES (55485,152285,'2010-04-23',20)

    INSERT INTO #ClaCases VALUES (243048,152285,'2011-05-2',120)

    INSERT INTO #ClaCases VALUES (3906,152986,'2008-08-17',20)

    INSERT INTO #ClaCases VALUES (7088,152986,'2008-07-11',120)

    INSERT INTO #ClaCases VALUES (343963,152986,'2011-08-28',20)

    INSERT INTO #ClaCases VALUES (95880,153125,'2010-09-5',30)

    INSERT INTO #ClaCases VALUES (101320,153125,'2010-09-11',120)

    INSERT INTO #ClaCases VALUES (352247,153125,'2011-11-12',25)

    INSERT INTO #ClaCases VALUES (1890,153346,'2008-04-3',30)

    INSERT INTO #ClaCases VALUES (2248,153346,'2008-04-5',120)

    INSERT INTO #ClaCases VALUES (3204,153346,'2008-07-14',20)

    INSERT INTO #ClaCases VALUES (40167,154962,'2009-12-20',25)

    INSERT INTO #ClaCases VALUES (41346,154962,'2010-01-7',200)

    INSERT INTO #ClaCases VALUES (60308,154962,'2010-05-31',120)

    INSERT INTO #ClaCases VALUES (163543,154962,'2010-12-15',120)

    INSERT INTO #ClaCases VALUES (309281,154962,'2011-07-10',25)

    INSERT INTO #ClaCases VALUES (2925,155144,'2008-07-1',25)

    INSERT INTO #ClaCases VALUES (42669,155144,'2010-01-26',120)

    INSERT INTO #ClaCases VALUES (83101,155144,'2010-07-19',30)

    INSERT INTO #ClaCases VALUES (298821,155144,'2011-06-30',120)

    INSERT INTO #ClaCases VALUES (350313,155144,'2011-10-31',20)

    INSERT INTO #ClaCases VALUES (358330,155144,'2011-12-17',40)

    INSERT INTO #ClaCases VALUES (7073,155691,'2008-08-30',120)

    INSERT INTO #ClaCases VALUES (17992,155691,'2009-04-16',240)

    INSERT INTO #ClaCases VALUES (32149,155691,'2009-08-21',20)

    INSERT INTO #ClaCases VALUES (57895,155691,'2009-12-19',120)

    INSERT INTO #ClaCases VALUES (61642,155691,'2010-06-9',25)

    Select * from #ClaCases

    Create Table #InitialTable_27 (ClaCaseID int, NameID int , DiscoverDate date)

    INSERT INTO #InitialTable_27 VALUES (2925,155144,'2008-07-1')

    INSERT INTO #InitialTable_27 VALUES (3204,153346,'2008-07-14')

    INSERT INTO #InitialTable_27 VALUES (3906,152986,'2008-08-17')

    INSERT INTO #InitialTable_27 VALUES (13692,149357,'2009-03-10')

    INSERT INTO #InitialTable_27 VALUES (21064,151572,'2009-05-22')

    INSERT INTO #InitialTable_27 VALUES (21645,148873,'2009-05-22')

    INSERT INTO #InitialTable_27 VALUES (25622,146773,'2009-06-27')

    INSERT INTO #InitialTable_27 VALUES (32149,155691,'2009-08-21')

    INSERT INTO #InitialTable_27 VALUES (39671,151327,'2009-12-12')

    INSERT INTO #InitialTable_27 VALUES (40167,154962,'2009-12-20')

    INSERT INTO #InitialTable_27 VALUES (42582,146773,'2010-01-23')

    INSERT INTO #InitialTable_27 VALUES (42587,146773,'2009-10-23')

    INSERT INTO #InitialTable_27 VALUES (55485,152285,'2010-04-23')

    INSERT INTO #InitialTable_27 VALUES (59164,151572,'2010-05-20')

    INSERT INTO #InitialTable_27 VALUES (61642,155691,'2010-06-9')

    INSERT INTO #InitialTable_27 VALUES (76041,151327,'2010-07-1')

    INSERT INTO #InitialTable_27 VALUES (163084,149357,'2010-12-15')

    INSERT INTO #InitialTable_27 VALUES (173117,147109,'2011-01-21')

    INSERT INTO #InitialTable_27 VALUES (175337,151327,'2011-02-3')

    INSERT INTO #InitialTable_27 VALUES (219303,151572,'2011-04-15')

    INSERT INTO #InitialTable_27 VALUES (241721,149357,'2011-05-3')

    INSERT INTO #InitialTable_27 VALUES (273063,151327,'2011-06-9')

    INSERT INTO #InitialTable_27 VALUES (287601,151572,'2011-06-23')

    INSERT INTO #InitialTable_27 VALUES (309281,154962,'2011-07-10')

    INSERT INTO #InitialTable_27 VALUES (343963,152986,'2011-08-28')

    INSERT INTO #InitialTable_27 VALUES (349093,148873,'2011-10-22')

    INSERT INTO #InitialTable_27 VALUES (350313,155144,'2011-10-31')

    INSERT INTO #InitialTable_27 VALUES (350439,146927,'2011-09-1')

    INSERT INTO #InitialTable_27 VALUES (352247,153125,'2011-11-12')

    Select * from #InitialTable_27

    Create Table #ClaQuestionView (ClaCaseID Int, QuestionID int, QuestionClassID int, QuestionDate date, ClaQuestionHistoryID int)

    INSERT INTO #ClaQuestionView VALUES (2238,202,120,'2008-05-29',1408)

    INSERT INTO #ClaQuestionView VALUES (2248,202,120,'2008-05-29',1418)

    INSERT INTO #ClaQuestionView VALUES (2522,202,120,'2008-06-17',1720)

    INSERT INTO #ClaQuestionView VALUES (3099,202,120,'2008-07-10',2254)

    INSERT INTO #ClaQuestionView VALUES (7073,202,120,'2008-11-11',7313)

    INSERT INTO #ClaQuestionView VALUES (7088,202,120,'2008-11-11',7334)

    INSERT INTO #ClaQuestionView VALUES (8476,202,120,'2008-12-5',9019)

    INSERT INTO #ClaQuestionView VALUES (13921,202,120,'2009-03-16',17174)

    INSERT INTO #ClaQuestionView VALUES (14297,202,120,'2009-03-18',17759)

    INSERT INTO #ClaQuestionView VALUES (19039,202,120,'2009-05-4',24322)

    INSERT INTO #ClaQuestionView VALUES (19571,202,120,'2009-05-11',25250)

    INSERT INTO #ClaQuestionView VALUES (20882,202,120,'2009-05-21',26926)

    INSERT INTO #ClaQuestionView VALUES (21378,202,120,'2009-05-25',27583)

    INSERT INTO #ClaQuestionView VALUES (36773,202,120,'2009-11-5',55521)

    INSERT INTO #ClaQuestionView VALUES (40980,202,120,'2010-01-5',63646)

    INSERT INTO #ClaQuestionView VALUES (42669,202,120,'2010-01-27',66827)

    INSERT INTO #ClaQuestionView VALUES (54354,202,120,'2010-04-19',85700)

    INSERT INTO #ClaQuestionView VALUES (57895,202,120,'2010-05-6',91170)

    INSERT INTO #ClaQuestionView VALUES (60308,202,120,'2010-06-1',95681)

    INSERT INTO #ClaQuestionView VALUES (75721,202,120,'2010-06-28',115602)

    INSERT INTO #ClaQuestionView VALUES (93336,202,120,'2010-09-1',141109)

    INSERT INTO #ClaQuestionView VALUES (101320,202,120,'2010-09-13',154486)

    INSERT INTO #ClaQuestionView VALUES (103840,202,120,'2010-09-14',157446)

    INSERT INTO #ClaQuestionView VALUES (143856,202,120,'2010-11-5',204253)

    INSERT INTO #ClaQuestionView VALUES (163543,202,120,'2010-12-17',229343)

    INSERT INTO #ClaQuestionView VALUES (166879,202,120,'2010-12-28',233474)

    INSERT INTO #ClaQuestionView VALUES (243048,202,120,'2011-05-4',331293)

    INSERT INTO #ClaQuestionView VALUES (298821,202,120,'2011-07-1',425617)

    Select * from #ClaQuestionView

    Create Table #Results (NameID INT, NoOfSOSCalls INT)

    INSERT INTO #Results VALUES (146773,6)

    INSERT INTO #Results VALUES (146927,1)

    INSERT INTO #Results VALUES (147109,1)

    INSERT INTO #Results VALUES (148873,2)

    INSERT INTO #Results VALUES (149357,2)

    INSERT INTO #Results VALUES (151327,8)

    INSERT INTO #Results VALUES (151572,8)

    INSERT INTO #Results VALUES (152285,3)

    INSERT INTO #Results VALUES (152986,1)

    INSERT INTO #Results VALUES (153125,1)

    INSERT INTO #Results VALUES (153346,1)

    INSERT INTO #Results VALUES (154962,2)

    INSERT INTO #Results VALUES (155144,2)

    INSERT INTO #Results VALUES (155691,3)

    Select * from #Results

  • mic.con87 (1/3/2012)


    @ ChrisM@Home: I'm extremely sorry for the late reply! I actually completely forgot we were busy with this, used your previous code and moved on to the other problems you have been assisting with.

    Here is some sample data that will hopefully clarify the matter.

    #Results is what your query returns and if you compare this with #ClaCases you can see that the count is not accurate.

    ...[/code]

    No problem.

    Can you set up a table of the results you are expecting to see? Cheers.


    [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]

  • I have joined the #ClaCases and the #ClaQuestionView table via this script:

    Select cc.ClaCaseID,cc.NameID,cc.DiscoverDate,cc.QuestionClassID, zz.ClaCaseID,zz.Question,zz.QuestionDate

    from #ClaCases cc

    LEFT JOIN

    (select max(claquestionhistoryid)claquestionhistoryid , ClaCaseID, QuestionClassID, Question, QuestionDate from #ClaQuestionView

    where QuestionID = 202

    group by claquestionhistoryid,ClaCaseID, QuestionClassID, Question,QuestionDate) zz

    ON cc.ClaCaseID = zz.ClaCaseID

    I selected a sample of NameID's and the results are as follows:

    Drop Table #JOIN

    Create Table #JOIN (ClaCaseID int, NameID int, DiscoverDate date, QuestionClassID int,CQVClaCaseID int null, Question varchar(25) null, QuestionDate date null)

    INSERT INTO #JOIN VALUES (8476,146773,'2008-10-25',120,8476,'is this for sos?','2008-12-5')

    INSERT INTO #JOIN VALUES (21378,146773,'2009-05-23',120,21378,'is this for sos?','2009-05-25')

    INSERT INTO #JOIN VALUES (25622,146773,'2009-06-27',25,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (36134,146773,'2009-10-28',30,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (42582,146773,'2010-01-23',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (42587,146773,'2009-10-23',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (54354,146773,'2010-04-16',120,54354,'is this for sos?','2010-04-19')

    INSERT INTO #JOIN VALUES (75721,146773,'2009-09-25',120,75721,'is this for sos?','2010-06-28')

    INSERT INTO #JOIN VALUES (103840,146773,'2010-09-13',120,103840,'is this for sos?','2010-09-14')

    INSERT INTO #JOIN VALUES (166879,146773,'2010-12-24',120,166879,'is this for sos?','2010-12-28')

    INSERT INTO #JOIN VALUES (19039,146927,'2009-04-29',120,19039,'is this for sos?','2009-05-4')

    INSERT INTO #JOIN VALUES (99403,146927,'2010-09-8',30,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (350439,146927,'2011-09-1',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (3099,147109,'2008-06-17',120,3099,'is this for sos?','2008-07-10')

    INSERT INTO #JOIN VALUES (6484,147109,'2008-10-29',130,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (17358,147109,'2009-04-8',200,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (17365,147109,'2009-04-8',170,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (27783,147109,'2009-06-15',120,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (42166,147109,'2010-01-20',30,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (84995,147109,'2010-08-9',30,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (173117,147109,'2011-01-21',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (310062,147109,'2011-07-8',210,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (21645,148873,'2009-05-22',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (34213,148873,'2009-09-14',30,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (85410,148873,'2010-08-12',110,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (93336,148873,'2010-08-31',120,93336,'is this for sos?','2010-09-1')

    INSERT INTO #JOIN VALUES (95939,148873,'2010-08-27',30,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (143856,148873,'2010-11-4',120,143856,'is this for sos?','2010-11-5')

    INSERT INTO #JOIN VALUES (181616,148873,'2011-03-1',30,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (348993,148873,'2011-10-22',210,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (349043,148873,'2011-10-22',210,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (349093,148873,'2011-10-22',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (13692,149357,'2009-03-10',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (27004,149357,'2009-07-13',30,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (36773,149357,'2009-09-7',120,36773,'is this for sos?','2009-11-5')

    INSERT INTO #JOIN VALUES (163084,149357,'2010-12-15',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (241721,149357,'2011-05-3',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (2522,151327,'2008-05-8',120,2522,'is this for sos?','2008-06-17')

    INSERT INTO #JOIN VALUES (13921,151327,'2008-12-2',120,13921,'is this for sos?','2009-03-16')

    INSERT INTO #JOIN VALUES (39671,151327,'2009-12-12',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (76041,151327,'2010-07-1',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (175337,151327,'2011-02-3',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (207801,151327,'2011-04-2',200,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (273063,151327,'2011-06-9',25,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (2238,151572,'2008-04-7',120,2238,'is this for sos?','2008-05-29')

    INSERT INTO #JOIN VALUES (20882,151572,'2009-05-19',120,20882,'is this for sos?','2009-05-21')

    INSERT INTO #JOIN VALUES (21064,151572,'2009-05-22',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (35011,151572,'2009-10-12',30,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (59164,151572,'2010-05-20',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (219303,151572,'2011-04-15',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (287601,151572,'2011-06-23',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (14297,152285,'2009-03-17',120,14297,'is this for sos?','2009-03-18')

    INSERT INTO #JOIN VALUES (19571,152285,'2009-05-10',120,19571,'is this for sos?','2009-05-11')

    INSERT INTO #JOIN VALUES (40980,152285,'2009-12-14',120,40980,'is this for sos?','2010-01-5')

    INSERT INTO #JOIN VALUES (55485,152285,'2010-04-23',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (243048,152285,'2011-05-2',120,243048,'is this for sos?','2011-05-4')

    INSERT INTO #JOIN VALUES (3906,152986,'2008-08-17',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (7088,152986,'2008-07-11',120,7088,'is this for sos?','2008-11-11')

    INSERT INTO #JOIN VALUES (343963,152986,'2011-08-28',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (95880,153125,'2010-09-5',30,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (101320,153125,'2010-09-11',120,101320,'is this for sos?','2010-09-13')

    INSERT INTO #JOIN VALUES (352247,153125,'2011-11-12',25,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (1890,153346,'2008-04-3',30,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (2248,153346,'2008-04-5',120,2248,'is this for sos?','2008-05-29')

    INSERT INTO #JOIN VALUES (3204,153346,'2008-07-14',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (40167,154962,'2009-12-20',25,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (41346,154962,'2010-01-7',200,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (60308,154962,'2010-05-31',120,60308,'is this for sos?','2010-06-1')

    INSERT INTO #JOIN VALUES (163543,154962,'2010-12-15',120,163543,'is this for sos?','2010-12-17')

    INSERT INTO #JOIN VALUES (309281,154962,'2011-07-10',25,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (2925,155144,'2008-07-1',25,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (42669,155144,'2010-01-26',120,42669,'is this for sos?','2010-01-27')

    INSERT INTO #JOIN VALUES (83101,155144,'2010-07-19',30,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (298821,155144,'2011-06-30',120,298821,'is this for sos?','2011-07-1')

    INSERT INTO #JOIN VALUES (350313,155144,'2011-10-31',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (358330,155144,'2011-12-17',40,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (7073,155691,'2008-08-30',120,7073,'is this for sos?','2008-11-11')

    INSERT INTO #JOIN VALUES (17992,155691,'2009-04-16',240,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (32149,155691,'2009-08-21',20,NULL,NULL,NULL)

    INSERT INTO #JOIN VALUES (57895,155691,'2009-12-19',120,57895,'is this for sos?','2010-05-6')

    INSERT INTO #JOIN VALUES (61642,155691,'2010-06-9',25,NULL,NULL,NULL)

    select * from #JOIN

    I perform the count for the specific ClaCaseID's I have in my table #InitialTable_27 ie I look at the ClaCaseID's in #InitialTable_27 which exist in #ClaCases (since they are taken from here). I then look at the ClaCaseID's PRIOR to the one I have in #InitialTable_27 and perform the count of SOS calls.

    My Desired Results are as followed:

    Drop Table #DesiredResults

    Create Table #DesiredResults (ClaCaseID int, NameID int, DiscoverDate date, NoOfSOSCalls int)

    INSERT INTO #DesiredResults VALUES (2925,155144,'2008-07-1','0')

    INSERT INTO #DesiredResults VALUES (3204,153346,'2008-07-14','1')

    INSERT INTO #DesiredResults VALUES (3906,152986,'2008-08-17','0')

    INSERT INTO #DesiredResults VALUES (13692,149357,'2009-03-10','0')

    INSERT INTO #DesiredResults VALUES (21064,151572,'2009-05-22','2')

    INSERT INTO #DesiredResults VALUES (21645,148873,'2009-05-22','0')

    INSERT INTO #DesiredResults VALUES (25622,146773,'2009-06-27','2')

    INSERT INTO #DesiredResults VALUES (32149,155691,'2009-08-21','1')

    INSERT INTO #DesiredResults VALUES (39671,151327,'2009-12-12','2')

    INSERT INTO #DesiredResults VALUES (40167,154962,'2009-12-20','0')

    INSERT INTO #DesiredResults VALUES (42582,146773,'2010-01-23','2')

    INSERT INTO #DesiredResults VALUES (42587,146773,'2009-10-23','2')

    INSERT INTO #DesiredResults VALUES (55485,152285,'2010-04-23','3')

    INSERT INTO #DesiredResults VALUES (59164,151572,'2010-05-20','2')

    INSERT INTO #DesiredResults VALUES (61642,155691,'2010-06-9','2')

    INSERT INTO #DesiredResults VALUES (76041,151327,'2010-07-1','2')

    INSERT INTO #DesiredResults VALUES (163084,149357,'2010-12-15','1')

    INSERT INTO #DesiredResults VALUES (173117,147109,'2011-01-21','1')

    INSERT INTO #DesiredResults VALUES (175337,151327,'2011-02-3','2')

    INSERT INTO #DesiredResults VALUES (219303,151572,'2011-04-15','2')

    INSERT INTO #DesiredResults VALUES (241721,149357,'2011-05-3','1')

    INSERT INTO #DesiredResults VALUES (273063,151327,'2011-06-9','2')

    INSERT INTO #DesiredResults VALUES (287601,151572,'2011-06-23','2')

    INSERT INTO #DesiredResults VALUES (309281,154962,'2011-07-10','2')

    INSERT INTO #DesiredResults VALUES (343963,152986,'2011-08-28','1')

    INSERT INTO #DesiredResults VALUES (349093,148873,'2011-10-22','2')

    INSERT INTO #DesiredResults VALUES (350313,155144,'2011-10-31','2')

    INSERT INTO #DesiredResults VALUES (350439,146927,'2011-09-1','1')

    INSERT INTO #DesiredResults VALUES (352247,153125,'2011-11-12','1')

    Select * from #DesiredResults

    Hope this helps, thanks!!

  • Hi Chris, any feedback on the problem? Thanks

  • mic.con87 (1/18/2012)


    Hi Chris, any feedback on the problem? Thanks

    Gosh, I'm sorry, this one fell by the wayside. On it now.


    [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]

  • mic.con87 (1/18/2012)


    Hi Chris, any feedback on the problem? Thanks

    Yes - your results don't match your sample data.

    The results table #DesiredResults contains ClaCaseID 25622, 42582, 42587 - possibly more - which don't exist in table #ClaQuestionView.


    [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]

  • Hi Chris,

    Sorry that must have been an error on my part but the data does exist in the tables. Any ClaCaseID in the #ClaCases exists in the #ClaQuestionView.

    I'll run the query again and let you know if there is still an incorrect count. Maybe try focus on #JOIN and assume this data is correct.

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply