Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2016
    Posts
    1

    Unanswered: Datediff divide by count assistance

    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 .

  2. #2
    Join Date
    Nov 2003
    Posts
    167
    Hi, I may be misunderstanding your requirements, but it sounds like you're looking for a roll-up, though hopefully you have something more specific to aggregate on than first name. Would the following produce the set you're looking for?:
    Code:
    select	x.insurer,datediff(dd,x.minStartDate,x.maxStartDate)/claims
    from	(
    		select	insurer
    				,min(start_date) minStartDate
    				,max(start_date) maxStartDate
    				,count(*) claims 
    		from [table] 
    		group by insurer
    		)x
    Kit Lemmonds

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •