On 2015-07-30 10:59 PM, jose isaias cabrera wrote:
>
> Ok, I have one more option for you, and since you think it's fun, I
> need to exclude Saturdays and Sundays. I have a hack in the
> programming side of things, but I would like to do it right from
> SQLite. Thougths?
WITH DTBase(HrsToAdd, DayStartsAt, HrsPerDay, HrsNow) AS (SELECT
5.5,
8.5,
8.5,
(CAST(strftime('%H','now','localtime') AS
REAL)+(CAST(strftime('%M','now','localtime') AS REAL)/60)-8.5)
), DTFill(HrsToFillToday,HrsRemain) AS (SELECT
MAX(HrsPerDay-HrsNow,0),
MAX(HrsToAdd-MAX(HrsPerDay-HrsNow,0),0)
FROM DTBase
), DTDays(DaysToAdd,DayHrsToAdd) AS (SELECT
CAST((HrsRemain/HrsPerDay) AS INT),
(HrsRemain-(CAST((HrsRemain/HrsPerDay) AS INT)*HrsPerDay))
FROM DTFill,DTBase
), DTTime(DaysToAdd,DayHrsToAdd) AS (SELECT DaysToAdd,
(DayStartsAt+(CASE WHEN HrsRemain>0 THEN DayHrsToAdd+24 ELSE
HrsNow+HrsToFillToday END))
FROM DTBase,DTFill,DTDays
), NewDT(NewDateTime) AS (SELECT
datetime( 'now', 'localtime', 'start of day', '+'||DaysToAdd||'
days', '+'||DayHrsToAdd||' hours') AS NewTime
FROM DTTime
), WkEnd(WEDays) AS (
SELECT ((CAST(DaysToAdd AS INT)/7)*2) FROM DTTime
UNION ALL
SELECT (WEDays+1) FROM WkEnd,NewDT WHERE
(strftime('%w',datetime(NewDateTime,'+'||WEDays||' day')) IN ('0','6'))
)
SELECT datetime( 'now', 'localtime', 'start of day',
'+'||(MAX(DaysToAdd)+MAX(WEDays))||' days', '+'||MAX(DayHrsToAdd)||'
hours') AS NewTime
FROM DTTime, WkEnd;
Wait... Let me guess... next you want Holidays added too, right?
Well, just in case you do... here is the SQL:
(Just add your own country's Holidays in the HDays CTE)
WITH DTBase(HrsToAdd, DayStartsAt, HrsPerDay, HrsNow) AS (SELECT
5.5,
8.5,
8.5,
(CAST(strftime('%H','now','localtime') AS
REAL)+(CAST(strftime('%M','now','localtime') AS REAL)/60)-8.5)
), HDays(HoliDay) AS (
SELECT '01-01' UNION ALL -- New Year's
SELECT '05-01' UNION ALL -- Worker's
SELECT '07-04' UNION ALL -- Independence
SELECT '12-25' -- Xmas
), DTFill(HrsToFillToday,HrsRemain) AS (SELECT
MAX(HrsPerDay-HrsNow,0),
MAX(HrsToAdd-MAX(HrsPerDay-HrsNow,0),0)
FROM DTBase
), DTDays(DaysToAdd,DayHrsToAdd) AS (SELECT
CAST((HrsRemain/HrsPerDay) AS INT),
(HrsRemain-(CAST((HrsRemain/HrsPerDay) AS INT)*HrsPerDay))
FROM DTFill,DTBase
), DTTime(DaysToAdd,DayHrsToAdd) AS (SELECT DaysToAdd,
(DayStartsAt+(CASE WHEN HrsRemain>0 THEN DayHrsToAdd+24 ELSE
HrsNow+HrsToFillToday END))
FROM DTBase,DTFill,DTDays
), NewDT(NewDateTime) AS (SELECT
datetime( 'now', 'localtime', 'start of day', '+'||DaysToAdd||'
days', '+'||DayHrsToAdd||' hours') AS NewTime
FROM DTTime
), WkEnd(WEDays) AS (
SELECT ((CAST(DaysToAdd AS INT)/7)*2) FROM DTTime
UNION ALL
SELECT (WEDays+1) FROM WkEnd,NewDT
WHERE (strftime('%w',datetime(NewDateTime,'+'||WEDays||'
day')) IN ('0','6')) OR
(strftime('%m-%d',datetime(NewDateTime,'+'||WEDays||' day')) IN
(SELECT Holiday FROM HDays))
)
SELECT datetime( 'now', 'localtime', 'start of day',
'+'||(MAX(DaysToAdd)+MAX(WEDays))||' days', '+'||MAX(DayHrsToAdd)||'
hours') AS NewTime
FROM DTTime, WkEnd;
