sum of datediff to many dates
-
i have this periods of experience i want to calculate the sum or total period of these periods 2004-01-06 2004-07-05 2004-07-07 2004-11-30 2010-07-01 2010-07-29 2011-06-12 2011-07-28 2012-07-22 2012-08-20 2013-10-17 2015-05-17 Lost & Forgotten
-
i have this periods of experience i want to calculate the sum or total period of these periods 2004-01-06 2004-07-05 2004-07-07 2004-11-30 2010-07-01 2010-07-29 2011-06-12 2011-07-28 2012-07-22 2012-08-20 2013-10-17 2015-05-17 Lost & Forgotten
If you provide the query you are trying to get working, you are more likely to get help. What is the underlying database? SQLServer, Oracle, Postgres, etc?
-
If you provide the query you are trying to get working, you are more likely to get help. What is the underlying database? SQLServer, Oracle, Postgres, etc?
sql i made a query calculate the datediff in days and sumed all days now i want to convert this days (integer) to years months and days thanks Lost & Forgotten
-
i have this periods of experience i want to calculate the sum or total period of these periods 2004-01-06 2004-07-05 2004-07-07 2004-11-30 2010-07-01 2010-07-29 2011-06-12 2011-07-28 2012-07-22 2012-08-20 2013-10-17 2015-05-17 Lost & Forgotten
If the answer for your question isn't satisfying, than refine your question - do not post it again... http://www.codeproject.com/Forums/1725/Database.aspx?select=4775204&tid=4775204[^]
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
-
If the answer for your question isn't satisfying, than refine your question - do not post it again... http://www.codeproject.com/Forums/1725/Database.aspx?select=4775204&tid=4775204[^]
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
i am sorry Lost & Forgotten
-
i am sorry Lost & Forgotten
-
sql i made a query calculate the datediff in days and sumed all days now i want to convert this days (integer) to years months and days thanks Lost & Forgotten
ahmad_yossef wrote:
i want to convert this days (integer) to years months and days
Well, break it down.. if you have the number of days, how many days in a year? Let's assume 365; so number of years in int(number of days / 365). Months... let's assume 30 days on average... Months = int((number of days - years * 365) / 30) And days are then number of days - years * 365 - months * 30 If that doesn't work, better define what you're after. Given the sample dates you provided, what are the values you calculated for years, months and days?
-
ahmad_yossef wrote:
i want to convert this days (integer) to years months and days
Well, break it down.. if you have the number of days, how many days in a year? Let's assume 365; so number of years in int(number of days / 365). Months... let's assume 30 days on average... Months = int((number of days - years * 365) / 30) And days are then number of days - years * 365 - months * 30 If that doesn't work, better define what you're after. Given the sample dates you provided, what are the values you calculated for years, months and days?
thanks for your replay but i face a problem when i have a number like 1456 the query will be like that SELECT dayss, dayss / 365 AS years, (dayss - dayss / 365 * 365) / 30 AS Months, (dayss - dayss / 365 * 365) - (dayss - dayss / 365 * 365) / 30 * 30 AS days FROM dbo.lv_npay_tmp3 which will return days1 years months days 1456 3 12 1 and it should be days1 years months days 1456 4 0 1 i hope you got me thanks alot Lost & Forgotten
-
thanks for your replay but i face a problem when i have a number like 1456 the query will be like that SELECT dayss, dayss / 365 AS years, (dayss - dayss / 365 * 365) / 30 AS Months, (dayss - dayss / 365 * 365) - (dayss - dayss / 365 * 365) / 30 * 30 AS days FROM dbo.lv_npay_tmp3 which will return days1 years months days 1456 3 12 1 and it should be days1 years months days 1456 4 0 1 i hope you got me thanks alot Lost & Forgotten
1456 == (3 × 365) + 361. That's clearly not 4 years and 1 day. The problem is that you're assuming 365 days per year, but only 30 days per month.(30 × 12) = 360, so you've lost five days. You need to assume(365 / 12) = 30.4166666...days per month, which will give you an answer of 3 years, 11 months and 27 days.SELECT
dayss,
dayss / 365 As years,
Cast(Floor((dayss % 365) * 12 / 365.0) As int) As Months,
(dayss % 365) - Cast(Floor((dayss % 365) * 12 / 365.0) * 365.0 / 12 As int) As days
FROM
dbo.lv_npay_tmp3
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
i have this periods of experience i want to calculate the sum or total period of these periods 2004-01-06 2004-07-05 2004-07-07 2004-11-30 2010-07-01 2010-07-29 2011-06-12 2011-07-28 2012-07-22 2012-08-20 2013-10-17 2015-05-17 Lost & Forgotten
I will just say that this sort of exercise seldom ends well without business requirements. I am not going to analyze all possible interpretations but I will provide and example from your own data. 2013-10-17 2015-05-17 A business person asks you for how many years in that. All of the following are possible answers. - One. 2013-10 to 2014-10 is one year, but 2014-10 to 2015-05 isn't. - Zero. Because 2013-10 to 2014-10 is one year but it isn't 2014-10 yet - Three. 2013, 2014, 2015 = 3. - Two. 2013-10 to 2014-10 is one year, but 2014-10 to 2015-05 is partial, so round up to get 2. - Five. The dates are supposed to be based on the contract period not the annual service dates (which the above dates are.) - 17. Because the person that actually wants this wants a count of months covered.
