February 1, 2012 at 2:13 am
Hi,
I have 3 tables: Staging.ClaCases, StagingViews.ClaQuestionView, AF_Fraud_Jan_24.
AF_Fraud_Jan_24 is the table I am working in. The main column in this table is ClaCaseID. This table is comprised of other tables and I am now attempting to add an additional column called Count_Reported_SOS where I will perform a count of a condition I will describe below. The columns from this table that are relevant are, ClaCaseID, NameID and IncidentDate.
The second table is Staging.ClaCases. This table contains every ClaCaseID and NameID that sits in the database. ALL ClaCaseID's and NameID's found in my table AF_Fraud_Jan_24 will be present in this table.
NB: Every ClaCaseID is unique in the Staging.ClaCases and hence is also unique in my AF_Fraud_Jan_24. However a NameID can have multiple ClaCaseID's.
The final table is StagingViews.ClaQuestionView. This table consists of ClaCaseID's that are unique to a certain condition. The condition is when a client called in for 'SOS' assistance. Each time this occurred a unique ClaCaseID was assigned. Now all the ClaCaseID's found in this table will be in the Staging.ClaCases. However NONE of these ClaCaseID's from StagingViews.ClaQuestionView will be in AF_Fraud_Jan_24 as the ClaCaseID's in AF_Fraud_Jan_24 are all motor related.
In StagingViews.ClaQuestionView there are different versions and the only way I can see to ensure that the correct version is used when joining to the other tables is described below in the join. The join does seem overcomplicated but I assure you it works. The columns of interest from this table is an answer column to a question column which contains the number 202 ie 'Is this call for SOS assistance'. There are several other questions but I am only concerned with this one. Another Key column is the QuestionDate column.
My aim is to perform a count of SOS incidents before a particular motor claim IncidentDate and these are linked via NameID. So say a person had a motor claim. He will then have a ClaCaseID, NameID and IncidentDate on AF_Fraud_Jan_24. Now I want to perform a count of how many times this same NameID called in and made an SOS claim prior to the Motor IncidentDate ie where QuestionDate < IncidentDate but NameID is the same.
I have written a query that links all three of these tables and have inserted it into #temp. I will provide some sample date for #temp and then provide the final result I want for AF_Fraud_Jan_24.
Hope this explanation is clear.
Thanks a lot!!!
Select zz.ccClaCaseID, zz.CQVClaCaseID, zz.ccNameID,zz.Reported_SOS, zz.QuestionDate, af.ClaCaseID, af.NameID, af.IncidentDate
into #temp
from
(
Select cc.ClaCaseID ccClaCaseID, x.ClaCaseID CQVClaCaseID, cc.NameID ccNameID, x.Reported_SOS, x.QuestionDate
from Staging.ClaCases cc
LEFT JOIN
(
select cqv.ClaCaseID, LEFT(MAX(ISNULL(CASE WHEN cqv.QuestionID = 202 THEN cqv.Answer ELSE NULL END,0)),1) Reported_SOS, cqv.QuestionDate
from StagingViews.ClaQuestionView cqv
INNER JOIN
(
Select ClaCaseID, max(ClaQuestionHistoryID) ClaQuestionHistoryID
from StagingViews.ClaQuestionView
where QuestionID = 202
group by ClaCaseID
) z
ON cqv.ClaCaseID = z.ClaCaseID
and cqv.ClaQuestionHistoryID = z.ClaQuestionHistoryID
and cqv.ClaQuestionHistoryID = (
select MAX(cqv2.ClaQuestionHistoryID)
from StagingViews.ClaQuestionView cqv2
where cqv.ClaCaseID = cqv2.ClaCaseID
and cqv.QuestionClassID = cqv2.QuestionClassID
)
group by cqv.ClaCaseID, cqv.QuestionDate
)x
ON cc.ClaCaseID = x.ClaCaseID
) zz
LEFT JOIN AF_Fraud_Jan_24 af
ON zz.ccClaCaseID = af.ClaCaseID
Create Table #temp (ccClaCaseID INT,CQVClaCaseID int,ccNameID int,Reported_SOS varchar(10), QuestionDate Date, ClaCaseID int, NameID int, IncidentDate date )
INSERT INTO #temp VALUES (8476,8476,146773,'Y','2008-12-5',NULL,NULL,NULL)
INSERT INTO #temp VALUES (21378,21378,146773,'Y','2009-05-25',NULL,NULL,NULL)
INSERT INTO #temp VALUES (25622,NULL,146773,NULL,NULL,25622,146773,'2009-06-27')
INSERT INTO #temp VALUES (36134,NULL,146773,NULL,NULL,NULL,NULL,NULL)
INSERT INTO #temp VALUES (42582,NULL,146773,NULL,NULL,42582,146773,'2010-01-23')
INSERT INTO #temp VALUES (42587,NULL,146773,NULL,NULL,42587,146773,'2009-10-23')
INSERT INTO #temp VALUES (54354,54354,146773,'Y','2010-04-19',NULL,NULL,NULL)
INSERT INTO #temp VALUES (75721,75721,146773,'Y','2010-06-28',NULL,NULL,NULL)
INSERT INTO #temp VALUES (103840,103840,146773,'Y','2010-09-14',NULL,NULL,NULL)
INSERT INTO #temp VALUES (166879,166879,146773,'Y','2010-12-28',NULL,NULL,NULL)
INSERT INTO #temp VALUES (3105,NULL,147559,NULL,NULL,NULL,NULL,NULL)
INSERT INTO #temp VALUES (3918,NULL,147559,NULL,NULL,3918,147559,'2008-08-15')
INSERT INTO #temp VALUES (7888,7888,147559,'Y','2008-11-24',NULL,NULL,NULL)
INSERT INTO #temp VALUES (11282,NULL,147559,NULL,NULL,NULL,NULL,NULL)
INSERT INTO #temp VALUES (17991,NULL,147559,NULL,NULL,NULL,NULL,NULL)
INSERT INTO #temp VALUES (52479,52479,147559,'Y','2010-04-12',NULL,NULL,NULL)
INSERT INTO #temp VALUES (12898,12898,1456075,'Y','2009-02-27',NULL,NULL,NULL)
INSERT INTO #temp VALUES (30902,NULL,1456075,NULL,NULL,30902,1456075,'2009-08-25')
INSERT INTO #temp VALUES (69740,69740,1456075,'Y','2010-06-22',NULL,NULL,NULL)
INSERT INTO #temp VALUES (277781,277781,1456075,'Y','2011-06-14',NULL,NULL,NULL)
INSERT INTO #temp VALUES (298861,298861,1456075,'Y','2011-07-1',NULL,NULL,NULL)
INSERT INTO #temp VALUES (312601,312601,1456075,'Y','2011-07-13',NULL,NULL,NULL)
INSERT INTO #temp VALUES (356194,356194,1456075,'Y','2011-12-6',NULL,NULL,NULL)
INSERT INTO #temp VALUES (39863,39863,2479621,'Y','2009-12-17',NULL,NULL,NULL)
INSERT INTO #temp VALUES (43434,43434,2479621,'Y','2010-02-5',NULL,NULL,NULL)
INSERT INTO #temp VALUES (45666,45666,2479621,'Y','2010-02-24',NULL,NULL,NULL)
INSERT INTO #temp VALUES (145864,NULL,2479621,NULL,NULL,145864,2479621,'2010-11-11')
Select * from #temp
--drop table #temp
Create table #AF_Fraud_Jan_24 (ClaCaseID int, NameID int, IncidentDate date, Count_Reported_SOS int)
INSERT INTO #AF_Fraud_Jan_24 VALUES(25622, 146773, '2009-06-27', 2)
INSERT INTO #AF_Fraud_Jan_24 VALUES(42582, 146773, '2010-01-23', 2)
INSERT INTO #AF_Fraud_Jan_24 VALUES(42587, 146773, '2009-10-23', 2)
INSERT INTO #AF_Fraud_Jan_24 VALUES(3918, 147559, '2008-08-15', 0)
INSERT INTO #AF_Fraud_Jan_24 VALUES(30902, 1456075,'2009-08-25', 1)
INSERT INTO #AF_Fraud_Jan_24 VALUES(145864,2479621,'2010-11-11', 3)
select * from #AF_Fraud_Jan_24
--drop table #AF_Fraud_Jan_24
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply