Recalulating work free days

[Bern Ertl]

Untested, but I believe the following will work:

'Set DAG_LOPNR for each inserted row
CREATE TRIGGER tr_ins_rec AFTER INSERT ON CAL FOR EACH ROW WHERE NEW.ARBFRI <> 'x'
  BEGIN
    UPDATE CAL SET DAG_LOPNR = (SELECT MAX( DAG_LOPNR) FROM CAL WHERE DATE < NEW.DATE)) + 1 WHERE DATE = NEW.DATE;
  END;

'Update DAG_LOPNR for remaining row(s) when deleting a row
CREATE TRIGGER tr_del_rec AFTER DELETE ON CAL FOR EACH ROW WHERE OLD.DAG_LOPNR > 0
  BEGIN
    UPDATE CAL SET DAG_LOPNR = OLD.DAG_LOPNR WHERE DAG_LOPNR = OLD.DAG_LOPNR + 1;
  END;

'Update DAG_LOPNR for row with a later date when DAG_LOPNR is updated
CREATE TRIGGER tr_update_rec_decr_DAG_LOPNR AFTER UPDATE OF DAG_LOPNR ON CAL FOR EACH ROW WHERE NEW.DAG_LOPNR < OLD.DAG_LOPNR
  BEGIN
    UPDATE CAL SET DAG_LOPNR = OLD.DAG_LOPNR WHERE DAG_LOPNR = OLD.DAG_LOPNR + 1;
  END;
CREATE TRIGGER tr_update_rec_incr_DAG_LOPNR AFTER UPDATE OF DAG_LOPNR ON CAL FOR EACH ROW WHERE NEW.DAG_LOPNR > OLD.DAG_LOPNR
  BEGIN
    UPDATE CAL SET DAG_LOPNR = NEW.DAG_LOPNR + 1 WHERE DAG_LOPNR = NEW.DAG_LOPNR;

'Update DAG_LOPNR for row when ARBFRI is updated
CREATE TRIGGER tr_update_rec_ARBFRI_set AFTER UPDATE OF ARBFRI ON CAL FOR EACH ROW WHERE NEW.ARBFRI <> OLD.ARBFRI AND NEW.ARBFRI = 'x'
  BEGIN
    UPDATE CAL SET DAG_LOPNR = (SELECT MAX( DAG_LOPNR) FROM CAL WHERE DATE < NEW.DATE)) + 1 WHERE DATE = NEW.DATE;
  END;
CREATE TRIGGER tr_update_rec_ARBFRI_reset AFTER UPDATE OF ARBFRI ON CAL FOR EACH ROW WHERE NEW.ARBFRI <> OLD.ARBFRI AND NEW.ARBFRI <> 'x'
  BEGIN
    UPDATE CAL SET DAG_LOPNR = 0 WHERE DATE = NEW.DATE;
  END;

[Fim]

Cj and Bern,
Thanks for help and new ideas.
Because I have a background in COBOL, I thought of another way.
I will have a table with a record for each year, with year as a key.
In each record there are 366 groups, one for each day.
Each group includes date, week, weekday, work-free, bank-free ach daglopnr.
/Fim W.

[cj]

> Year as a single record, but how many columns?

The week/weekday can be returned using built-in function https://www.sqlite.org/lang_datefunc.html

Note:
SQL might make it easy, but calculating the start/end byte of every day would be instantaneous
and the record number 1 to 99999 could be any julian day - the starting day number offset.
It all depends on how much SQL is needed.  SQLite could be eliminated using option 4 with PowerBASIC.
I wouldn't rewrite records to obtain the order number unless it is needed since it can be quickly obtained
by sorting on a column and the element numbers are the order.  It might be required in the table?


Option 1 Year tables

Insert into Table2017(day1,column, column, ...)
Insert into Table2018(day2,column, column, ...)


Option 2  Blob

Insert into Table2017("DAY1  DAY2  DAY3"


Option 3 Attach as needed

slOpen "Year2017.db3","C"
Insert Create Table MyTable(day1,column,column, ...)
ATTACH "Name"


Option 4  slGetFile/slPutFile

TYPE  MyRecordType
  column 1
  column 2
END TYPE
Rec = MyRecordType

slGetFile ByteStart,Length

[cj]

#INCLUDE "sqlitening.inc"
%FirstDay = 150101
%LastDay =  %FirstDay + 19

SUB UpdateAll
LOCAL counter AS LONG
LOCAL sDay()  AS STRING
slexe "begin"
slselAry "select DATE from CAL where ARBFRI=' '",sDay(),"Q9c"
FOR counter = 1 TO UBOUND(sDay)
   'this could be optimized into a single call
   slexe USING$("update CAL set DAGCount=# where DATE=&",counter,sDay(counter))
NEXT
slexe "end"
END SUB

FUNCTION PBMAIN () AS LONG 'freeday.bas

LOCAL day AS LONG
slOpen "Fim.db3","C"
slexe "drop table if exists CAL"
IF slsel("select DATE from CAL LIMIT 1",0,"E0") THEN 'no table
  slexe "begin"
  slexe "drop table if exists CAL"
  slexe "create table if not exists CAL(DATE integer primary key,ARBFRI,DAGCOUNT INTEGER not null)"
  FOR day = %FirstDay TO %LastDay
   slexe USING$("insert into CAL values(#_,'X'_,#)",day,0)
  NEXT
  slexe "end"
END IF

UpdateWorkDay %FirstDay
UpdateWorkDay %LastDay

UpdateAll '<--- UPDATE ORDER COLUMN

DIM sArray() AS STRING
slSelAry"select * from CAL",sArray(),"Q9" 'get worked days
  ? JOIN$(sArray(),$CR),,USING$("&   (# records)",DATE$,UBOUND(sArray))

END FUNCTION

SUB UpdateWorkDay(Day AS LONG)
slexe USING$("update CAL set ARBFRI=' ' where DATE=#",day)
END SUB

SUB UpdateFreeDay(Day AS LONG)
slexe USING$("update CAL set ARBFRI='X' where DATE=#",day)
END SUB