"R.Smith" wrote...
>
> 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;
How much I owe you? :-) The holidays was the next question. ;P Thanks.
This will help me understand CTE much deeper/better. Thanks.
