February 1, 2012 at 3:32 am
Hi Michael
I've got a hunch that this problem could be resolved with something simple like so:
SELECT af.*, d.*
FROM AF_Fraud_Jan_24 af
INNER JOIN (
SELECT cc.NameID, cqv.ClaCaseID, cqv.Answer, cqv.QuestionDate
FROM StagingViews.ClaQuestionView cqv
INNER JOIN Staging.ClaCases cc
ON cc.ClaCaseID = cqv.ClaCaseID
WHERE cqv.QuestionID = 202
) d ON d.NameID = af.NameID AND d.QuestionDate < af.IncidentDate
I can't really figure out what the posted query is doing without sample data, and I also suspect that it's this query which is confusing the issue - it doesn't sit well with the description. I think you should focus on the table ClaQuestionView in isolation from the other tables (except perhaps ClaCases provided that ClaCaseID is unique within it) and figure out how to return relevant rows from it before joining other tables.
Can you post a sample set from ClaQuestionView?
Cheers
ChrisM
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]
February 1, 2012 at 5:44 am
Hi Chris,
I've thought over the problem and I've come up with a much simpler solution. I really was overcomplicating the issue. I need to ignore ClaQuestionView altogether.
If we focus on Staging.ClaCases and AF_Fraud_Jan_24 it should be possible to yield a solution. The relationship between these two tables is the same as I described previously.
There are two new columns in Staging.ClaCases that I want to make use of namely NotificationDate and QuestionClassID.
A QuestionClassID = 120 indicates an SOS call and NotificationDate is when this call was made.
Once again I would like to add a column to AF_Fraud_Jan_24 called CountOfSOSCalls whereby there will be a count of the number of times that an SOS call was made ie Staging.ClaCases.QuestionClassID = 120 prior to the AF_Fraud_Jan_24.IncidentDate for AF_Fraud_Jan_24.ClaCaseID's.
I have provided some sample data that should clarify the above explanation.
Create Table #StagingClaCases (ClaCaseID int, NameID int, QuestionClassID int, NotificationDate date)
INSERT INTO #StagingClaCases VALUES (3099,147109,120,'2008-07-10')
INSERT INTO #StagingClaCases VALUES (6484,147109,130,'2008-10-30')
INSERT INTO #StagingClaCases VALUES (17358,147109,200,'2009-04-14')
INSERT INTO #StagingClaCases VALUES (17365,147109,170,'2009-04-14')
INSERT INTO #StagingClaCases VALUES (27783,147109,120,'2009-07-25')
INSERT INTO #StagingClaCases VALUES (42166,147109,30,'2010-01-21')
INSERT INTO #StagingClaCases VALUES (84995,147109,30,'2010-08-10')
INSERT INTO #StagingClaCases VALUES (173117,147109,20,'2011-01-21')
INSERT INTO #StagingClaCases VALUES (310062,147109,210,'2011-07-11')
INSERT INTO #StagingClaCases VALUES (2244,216674,120,'2008-05-29')
INSERT INTO #StagingClaCases VALUES (2245,216674,120,'2008-05-29')
INSERT INTO #StagingClaCases VALUES (6997,216674,120,'2008-11-10')
INSERT INTO #StagingClaCases VALUES (15388,216674,120,'2009-03-20')
INSERT INTO #StagingClaCases VALUES (19138,216674,20,'2009-05-5')
INSERT INTO #StagingClaCases VALUES (28129,216674,30,'2009-07-30')
INSERT INTO #StagingClaCases VALUES (28143,216674,30,'2009-07-30')
INSERT INTO #StagingClaCases VALUES (55014,216674,25,'2010-04-19')
INSERT INTO #StagingClaCases VALUES (76260,216674,25,'2010-07-2')
INSERT INTO #StagingClaCases VALUES (107980,216674,20,'2010-09-22')
INSERT INTO #StagingClaCases VALUES (13412,875902,120,'2009-03-6')
INSERT INTO #StagingClaCases VALUES (24530,875902,120,'2009-06-15')
INSERT INTO #StagingClaCases VALUES (28385,875902,20,'2009-08-3')
INSERT INTO #StagingClaCases VALUES (41907,875902,120,'2010-01-19')
INSERT INTO #StagingClaCases VALUES (41927,875902,120,'2010-01-19')
INSERT INTO #StagingClaCases VALUES (42005,875902,120,'2010-01-19')
INSERT INTO #StagingClaCases VALUES (42793,875902,120,'2010-01-28')
INSERT INTO #StagingClaCases VALUES (45012,875902,120,'2010-02-18')
INSERT INTO #StagingClaCases VALUES (339565, 17292722, 25, '2011-08-29')
INSERT INTO #StagingClaCases VALUES (4345900,17292722, 200,'2011-10-03')
INSERT INTO #StagingClaCases VALUES (349626, 17292722, 20, '2011-10-26')
select * from #StagingClaCases
Create Table #AF_Fraud_Jan_24 (ClaCaseID int, NameID int, NotificationDate date)
INSERT INTO #AF_Fraud_Jan_24 Values (173117,147109,'2011-01-21')
INSERT INTO #AF_Fraud_Jan_24 Values(107980,216674,'2010-09-15')
INSERT INTO #AF_Fraud_Jan_24 Values(76260,216674,'2010-07-2')
INSERT INTO #AF_Fraud_Jan_24 Values(55014,216674,'2010-04-19')
INSERT INTO #AF_Fraud_Jan_24 Values(19138,216674,'2009-05-5')
INSERT INTO #AF_Fraud_Jan_24 Values(28385,875902,'2009-08-1')
INSERT INTO #AF_Fraud_Jan_24 Values(349626,17292722,'2011-10-25')
INSERT INTO #AF_Fraud_Jan_24 Values(339565,17292722,'2011-08-27')
select * from #AF_Fraud_Jan_24
Create Table #New_AF_Fraud_Jan_24 (ClaCaseID int, NameID int, NotificationDate date, CountOfSOSCalls int)
INSERT INTO #New_AF_Fraud_Jan_24 Values (173117,147109,'2011-01-21',2)
INSERT INTO #New_AF_Fraud_Jan_24 Values(107980,216674,'2010-09-15',4)
INSERT INTO #New_AF_Fraud_Jan_24 Values(76260,216674,'2010-07-2',4)
INSERT INTO #New_AF_Fraud_Jan_24 Values(55014,216674,'2010-04-19',4)
INSERT INTO #New_AF_Fraud_Jan_24 Values(19138,216674,'2009-05-5',4)
INSERT INTO #New_AF_Fraud_Jan_24 Values(28385,875902,'2009-08-1',2)
INSERT INTO #New_AF_Fraud_Jan_24 Values(349626,17292722,'2011-10-25',0)
INSERT INTO #New_AF_Fraud_Jan_24 Values(339565,17292722,'2011-08-27',0)
select * from #New_AF_Fraud_Jan_24
Thanks again for the help!!!
February 1, 2012 at 6:08 am
SELECT *
FROM #AF_Fraud_Jan_24 af
CROSS APPLY (SELECT COUNT(*) AS CountOfSOSCalls
FROM #StagingClaCases scc
WHERE scc.QuestionClassID = 120 AND af.NameID = scc.NameID
AND af.NotificationDate > scc.NotificationDate) countSOS
February 1, 2012 at 6:16 am
Thanks very much!!! That was a lot simpler than I thought. Appreciate the help!!!
February 1, 2012 at 6:27 am
mic.con87 (2/1/2012)
Thanks very much!!! That was a lot simpler than I thought. Appreciate the help!!!
No problem.
If you need any help understanding it, try these great articles by Paul White: -
Understanding and Using APPLY Part 1[/url]
Understanding and Using APPLY Part 2[/url]
If they don't cover it, then post back with any questions.
February 1, 2012 at 7:07 am
:w00t:
Hi micheal ,
I cann't understand any thing ,try :hehe: simplify yourQuestion please
Thanks
February 1, 2012 at 8:21 am
Hi Michael
Something is still bugging me about this project. Of these three tables...
StagingViews.ClaQuestionView
Staging.ClaCases
AF_Fraud_Jan_24
...which if any are created or altered by the exercise "find individuals (NameID) where a motor claim was preceeded by an SOS incident"?
Or to put it another way, do you perform some setting-up work on any tables or views prior to running the script provided by Cadavre, or is the script all you need to obtain the results?
Cheers
ChrisM
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]
February 1, 2012 at 11:31 pm
Hi Chris,
I'm not exactly sure if I understand your question.
AF_Fraud_Jan_24 is the table that will be updated with the additional column. This is a table I created. The other two sit in the database and are queried often by multiple users.
The script Cadavre provided was altered slightly in my query:
SELECT it.*, CountOfSOSCalls
INTO #AF_Fraud_Feb_01
FROM #AF_Fraud_Jan_24 it
CROSS APPLY (SELECT COUNT(*) AS CountOfSOSCalls
FROM Staging.ClaCases scc
WHERE scc.QuestionClassID = 120 AND it.NameID = scc.NameID
AND it.IncidentDate > scc.NotificationDate) countSOS
ClaQuestionView is a view works as follows. There are a series of questions lets say 3 that are asked for a motor claim ie 3 questions will be mapped to one unique ClaCaseID. However this is versioned by a column called ClaQuestionHistoryID the larger number representing the newest version.
In this table there is also a question 'Is this for SOS'. Now this question is a stand alone question and will correspond to a unique ClaCaseID. So an SOS question will never map to a motor ClaCaseID. This is also versioned as described above.
CREATE TABLE #ClaQuestionView (ClaCaseID INT,Question varchar(max), Answer varchar(max),QuestionID INT,QuestionClassID INT,ClaQuestionHistoryID int )
INSERT INTO #ClaQuestionView VALUES (1234,'Did did the incident occur at night','Y',1,20,801)
INSERT INTO #ClaQuestionView VALUES (1234,'Were there any witnesses',null,2,20,801)
INSERT INTO #ClaQuestionView VALUES (1234,'Who is at fault','OP',3,20,801)
INSERT INTO #ClaQuestionView VALUES (1234,'Did did the incident occur at night','Y',1,20,901)
INSERT INTO #ClaQuestionView VALUES (1234,'Were there any witnesses','N',2,20,901)
INSERT INTO #ClaQuestionView VALUES (1234,'Who is at fault','OP',3,20,901)
INSERT INTO #ClaQuestionView VALUES (5674,'Did did the incident occur at night','Y',1,20,500)
INSERT INTO #ClaQuestionView VALUES (5674,'Were there any witnesses','null',2,20,500)
INSERT INTO #ClaQuestionView VALUES (5674,'Who is at fault','C',3,20,500)
INSERT INTO #ClaQuestionView VALUES (5674,'Did did the incident occur at night','Y',1,20,722)
INSERT INTO #ClaQuestionView VALUES (5674,'Were there any witnesses','Y',2,20,722)
INSERT INTO #ClaQuestionView VALUES (5674,'Who is at fault','C',3,20,722)
INSERT INTO #ClaQuestionView VALUES (4533,'Is this for SOS',null,202,120,776)
INSERT INTO #ClaQuestionView VALUES (4533,'Is this for SOS','Y',202,120,976)
INSERT INTO #ClaQuestionView VALUES (12567,'Is this for SOS',null,202,120,6678)
INSERT INTO #ClaQuestionView VALUES (12567,'Is this for SOS','N',202,120,7254)
select * from #ClaQuestionView
I hope this clarifies the matter.
Cadavre's solution returns what I require but thanks again for your help. I should have thought more about the problem before deciding to use ClaQuestionView and over complicate the problem.
Viewing 8 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply