December 22, 2011 at 6:24 am
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
For better assistance in answering your questions, please read this[/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:32 am
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.
December 22, 2011 at 7:47 am
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?
For better assistance in answering your questions, please read this[/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
mic.con87 (12/22/2011)
...As for the more efficient
inner joinGROUP 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"?
For better assistance in answering your questions, please read this[/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 8:52 am
Very nice discussion on SQL query. Thank you for this information
S. Shradha
SQL DBA
December 22, 2011 at 11:30 pm
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.
December 23, 2011 at 6:07 am
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.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 3, 2012 at 4:39 am
@ 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
January 3, 2012 at 8:20 am
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.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 4, 2012 at 12:21 am
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!!
January 18, 2012 at 2:08 am
Hi Chris, any feedback on the problem? Thanks
January 18, 2012 at 2:17 am
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.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 18, 2012 at 4:23 am
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.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 18, 2012 at 4:38 am
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