On 2018/08/26 8:44 PM, Csányi Pál wrote:
On Sun, Aug 26, 2018 at 07:17:00PM +0200, R Smith wrote:
You have misunderstand the purpose of the MyLengthOfService table.
The MyLengthOfService table contains WorkPlaces not for many persons,
but for one person only.
So my goal is to get summed years, months and days for that one
person. The person has been worked on many places.
Great, that's even easier:
WITH worktime(grp, totDays) AS (
SELECT WorkPlaceName, SUM(CAST(strftime('%J',date(EndDate)) AS
INT)-CAST(strftime('%J',date(StartDate)) AS INT))
FROM MyLengthOfService
GROUP BY WorkPlaceName
)
SELECT grp AS WorkPlace, (totDays / 365) AS Years, ((totDays % 365) /
30) AS Months, ((totDays % 365) % 30) AS Days, totDays
FROM worktime
;
And, can be made into a VIEW if you like.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
