calculate number of days

Question

calculate number of days

Barcelona10

SSCertifiable

Points: 5065
More actions
October 8, 2015 at 11:29 am

#312189

I have similar task to previous post
i have client table which has client_id Eff_from and Eff_to columns.Eff_from and Eff_to are the dates that client is eligible for service.I need to know the average number of days from the day that he became not eligible and new eligibility date .
CLIENT_IDEFF_FREFF_TO
1001 12/24/200712/8/2010
100112/13/20123/26/2013
1001 5/27/20138/2/2013
10019/24/201310/30/2016
for expl days between
12/8/2010 and 12/13/2012
3/26/2013 and 5/27/2013
8/2/2013 and 9/24/2013
then AVG them
Thank you advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183686 More actions · Answer 1

I guess you could at least post your sample data in a consumable format (CREATE TABLE & INSERT statements) if you want some coded help.

If not, I can tell you that you can use a self JOIN with the help of a ROW_NUMBER. With that, you can use the AVG and DATEDIFF functions.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

pietlinden SSC Guru Points: 63198 More actions · Answer 2

If you are lucky and have 2012, then you can use LAG, something like this:

SELECT Client_ID

, EFF_FR

, EFF_To

, LAG(EFF_To,1) OVER (PARTITION BY Client_ID ORDER BY Eff_Fr) AS Prev

FROM (

SELECT 1001 AS CLIENT_ID, '12/24/2007' AS EFF_FR,'12/8/2010' AS EFF_TO

UNION ALL

SELECT 1001,'12/13/2012','3/26/2013'

UNION ALL

SELECT 1001, '5/27/2013','8/2/2013'

UNION ALL

SELECT 1001,'9/24/2013','10/30/2016') x;

Otherwise, you'd have to simulate LAG and go from there... Here's an article[/url] on how to do it.

Helps a LOT if you post your data in consumable format... something like this:

SELECT 1001 AS CLIENT_ID, '12/24/2007' AS EFF_FR,'12/8/2010' AS EFF_TO

UNION ALL

SELECT 1001,'12/13/2012','3/26/2013'

UNION ALL

SELECT 1001, '5/27/2013','8/2/2013'

UNION ALL

SELECT 1001,'9/24/2013','10/30/2016';

Barcelona10 SSCertifiable Points: 5065 More actions · Answer 3

Sorry for inconvenience.next time i'll make sure to post sample data.And thank you for your replies