February 13, 2016 at 6:28 am
I have a table that has over 1mil records. hers is an example of the table
insurer claim_number location start_date end_date
john 5555 east 1/5/10 1/17/10
john 5555 west 1/5/10 1/17/10
john 5555 south 1/5/10 1/17/10
jane 3333 west 4/6/12 4/16/12
jane 3333 north 4/6/12 4/16/12
jane 3333 east 4/6/12 4/16/12
basically what i want to do is to get the datediff for each person
select insurer, claim_number, location, datediff(day start_date,end_date) / count of their claims (so for john he has 3 claims because it is done in 3 different locations even though he has the same claim number). so John date diff would be 12/3 .
February 13, 2016 at 7:26 am
what happens if john has more claims with andifferent claim number?
as an aside, it is better to post CREATE TABLE and INSERT statements when asking for help so folks wanting to provide a working query can easily setup your environment on their side. You'll get better responses, likely with tested code, if you provide.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 13, 2016 at 7:34 am
If he has different claims with different claim number
That would be a different line item.
so 555 would have lets say 12days date diff divide by 3 claims
if he has a new claim like 5643 (a date diff of 4 days)
it would be the date diff / the amout of claims (let say he has 2 claims under that number)
5555 = datediff (12days ) / 3 claims
5643 -= datediff (4days) 2
CLAIM NUMBER IS UNIQUE
February 13, 2016 at 7:39 am
can there be different dates on different rows with the same claim number?
how about some schema and sample data code?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 13, 2016 at 7:40 am
are the start_date end_date ALWAYS the same same for each unique claim
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 13, 2016 at 7:46 am
here is some sample script to set up your data....you should always provide when asking questions such as this.
it helps us help you.
CREATE TABLE #yourtable(
insurer VARCHAR(4) NOT NULL
,claim_number INTEGER NOT NULL
,location VARCHAR(5) NOT NULL
,start_date DATETIME NOT NULL
,end_date DATETIME NOT NULL
);
INSERT INTO #yourtable(insurer,claim_number,location,start_date,end_date) VALUES ('john',5555,'east','1/5/10','1/17/10');
INSERT INTO #yourtable(insurer,claim_number,location,start_date,end_date) VALUES ('john',5555,'west','1/5/10','1/17/10');
INSERT INTO #yourtable(insurer,claim_number,location,start_date,end_date) VALUES ('john',5555,'south','1/5/10','1/17/10');
INSERT INTO #yourtable(insurer,claim_number,location,start_date,end_date) VALUES ('jane',3333,'west','4/6/12','4/16/12');
INSERT INTO #yourtable(insurer,claim_number,location,start_date,end_date) VALUES ('jane',3333,'north','4/6/12','4/16/12');
INSERT INTO #yourtable(insurer,claim_number,location,start_date,end_date) VALUES ('jane',3333,'east','4/6/12','4/16/12');
select * from #yourtable
please read
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
ps...this looks like homework...can you post what you have tried so far.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 13, 2016 at 11:41 am
PS its not home works
February 13, 2016 at 11:47 am
djreggaestyles (2/13/2016)
PS its not home works
so what have tried so far......what isnt working?
here are some pointers....
GROUP BY
DATEDIFF
COUNT
....I am far happier helping someone who has tried, rather than someone who just wants a cut and paste solution.....hope this doesnt offend, as its not meant to.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 13, 2016 at 12:00 pm
PS THIS IS NOT HOMEWORK
The code i wrote to accomplish this is
Select Insurer,location, Claim_number, datediff(day, start_day, end_day) / count(claim_number). count (claim_number) may noy work because, i want to divide the count of individual claims, not the count of all the claims in the table
The datediff portion is correct. i simply am trying to basically divide the results from the datediff
by the count of the total distinct claim, maybe, a claim can be processed in 1 location or 4, in the example i gave for John his claim has been processed in 3 locations. therefore if his datediff calculation = 12 days then it should be 12/ 3 That's IT.
CLAIM number is distinct however that claim number can be assigned to different locations
February 13, 2016 at 12:13 pm
ok...a starter to build upon
maybe this is what you require or maybe not.
please note......it would be helpful to post set up scripts and expected answers when you start a new question.
Also....you may well find that you over simplified your example....questions were asked before about start/end dates remaining constant per claim....i dont see this has been answered
USE tempdb
GO
CREATE TABLE yourtable(
insurer VARCHAR(4) NOT NULL
,claim_number INTEGER NOT NULL
,location VARCHAR(5) NOT NULL
,start_date DATETIME NOT NULL
,end_date DATETIME NOT NULL
);
INSERT INTO yourtable(insurer,claim_number,location,start_date,end_date) VALUES ('john',5555,'east','1/5/10','1/17/10');
INSERT INTO yourtable(insurer,claim_number,location,start_date,end_date) VALUES ('john',5555,'west','1/5/10','1/17/10');
INSERT INTO yourtable(insurer,claim_number,location,start_date,end_date) VALUES ('john',5555,'south','1/5/10','1/17/10');
INSERT INTO yourtable(insurer,claim_number,location,start_date,end_date) VALUES ('jane',3333,'west','4/6/12','4/16/12');
INSERT INTO yourtable(insurer,claim_number,location,start_date,end_date) VALUES ('jane',3333,'north','4/6/12','4/16/12');
INSERT INTO yourtable(insurer,claim_number,location,start_date,end_date) VALUES ('jane',3333,'east','4/6/12','4/16/12');
SELECT insurer
, claim_number
, cast(DATEDIFF(day, start_date, end_date) * 1.0 / COUNT(*) as decimal(9,2)) AS result
FROM yourtable
GROUP BY insurer
, claim_number
, start_date
, end_date
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 13, 2016 at 1:00 pm
djreggaestyles (2/13/2016)
count (claim_number) may noy work because, i want to divide the count of individual claims
Just responding to the above without digging any deeper into the problem (so perhaps I am misunderstanding you) ... have you tried COUNT(DISTINCT claim_number) ?
February 13, 2016 at 1:32 pm
cool thx for your help, i am going to try something else but your suggestion did help
February 13, 2016 at 1:36 pm
djreggaestyles (2/13/2016)
cool thx for your help, i am going to try something else but your suggestion did help
fair enough...would appreciate seeing what else you try and how you get on with solving this.
please post back.
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 16, 2016 at 2:09 am
You could always use the window function to count the number of times the Claim Number appears within the set something like
SELECT
Insurer
,Location
,Claim_Number
,(DATEDIFF(day,Start_Date, End_Date)) dateDifference
,Count(Claim_Number) OVER (PARTITION BY Insurer,Claim_Number order by Start_Date) Claimcount
,(DATEDIFF(day,Start_Date, End_Date))/convert(Decimal(18,2), Count(Claim_Number) OVER (PARTITION BY Insurer,Claim_Number order by Start_Date))
FROM #yourtable
Does that fullfill your requirements.
The script uses the Table and data that J Livingston produced (many thanks)
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply