使用SQLite的SQL语言计算两个日期间的工作日

这段程序只是计算两个日期之间的工作日,没有把节假日去掉,只是把星期六和星期日去掉了.

原理:建立一个名称为tt的表,存储临时计算结果.先上程序

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
drop TABLE  IF EXISTS `tt`;
CREATE TABLE tt (dt,days,weeknum,week,modday,workday);
INSERT INTO "tt" VALUES('2019-10-31',0,0,0,0,0);
INSERT INTO "tt" VALUES('2019-10-30',0,0,0,0,0);
INSERT INTO "tt" VALUES('2019-10-29',0,0,0,0,0);
INSERT INTO "tt" VALUES('2019-10-28',0,0,0,0,0);
INSERT INTO "tt" VALUES('2019-10-27',0,0,0,0,0);
INSERT INTO "tt" VALUES('2019-10-26',0,0,0,0,0);
INSERT INTO "tt" VALUES('2019-10-25',0,0,0,0,0);
INSERT INTO "tt" VALUES('2019-10-24',0,0,0,0,0);
INSERT INTO "tt" VALUES('2019-10-23',0,0,0,0,0);
INSERT INTO "tt" VALUES('2019-10-22',0,0,0,0,0);
INSERT INTO "tt" VALUES('2019-10-21',0,0,0,0,0);
INSERT INTO "tt" VALUES('2019-10-20',0,0,0,0,0);
INSERT INTO "tt" VALUES('2019-10-19',0,0,0,0,0);
INSERT INTO "tt" VALUES('2019-10-18',0,0,0,0,0);
INSERT INTO "tt" VALUES('2019-10-17',0,0,0,0,0);
INSERT INTO "tt" VALUES('2019-10-16',0,0,0,0,0);

--计算两个日期之间的差值+1,这样能包括两端的日期.
update tt set days=(julianday('2019-12-31')-julianday(date(dt)))+1;
-- 计算剩余天数,周数,现在是星期几
update tt set modday=days%7,weeknum=(days-days%7)/7,week=strftime('%w',('2019-12-31'))+0;
-- 工作天数=周数*5+剩余天数
update tt set workday=weeknum*5+modday;
-- 计算剩余天数全部都是工作日的情况(天数为n,星期n到五之间返回n天)
-- update tt set workday=weeknum*5+modday where modday<6 and modday>0 and week>=modday and week<6 ;
-- 当前日期为星期六或modday-1,则工作日天数减一
update tt set workday=weeknum*5+modday-1 where modday>0 and (week=6 or week=modday-1);
-- 剩余天数大于2且在星期日到剩余天数-2之间,则工作日天数减二
update tt set workday=weeknum*5+modday-2 where  week>=0 and week<=modday-2 and modday>1;
COMMIT;
.d

| 剩余天数   | 星期  | 工作日  | 星期  | 工作日  | 星期  |  工作日 |
| 1        | 1-5  | 1      | 0,6  | 0      |      |        |
| 2        | 2-5  | 2      | 1,6  | 1      | 0    | 0      |
| 3        | 3-5  | 3      | 2,6  | 2      | 0-1  | 1      |
| 4        | 4-5  | 4      | 3,6  | 3      | 0-2  | 2      |
| 5        | 5    | 5      | 4,6  | 4      | 0-3  | 3      |
| 6        |      |        | 5,6  | 5      | 0-5  | 4      |

1 days 存储两个日期间的天数

2 week 结束日期是星期几(0星期日,1-6表示星期一到星期六)

3 weeknum 从开始日期的整周数

4 modday 最后一周剩余的天数

5 最后一天是星期六或星期(剩余天数-1),最后一周有一个休息日

6 剩余天数>1,最后一天是星期日到星期(剩余天数-2),最后一周有二个休息日

使用Ｅｘｃｅｌ中的networkdays公式进行验证,结果一样!

程序运行后的结果如下

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE tt (dt,days,weeknum,week,modday,workday);
INSERT INTO "tt" VALUES('2019-10-31',62.0,8.0,2,6.0,44.0);
INSERT INTO "tt" VALUES('2019-10-30',63.0,9.0,2,0.0,45.0);
INSERT INTO "tt" VALUES('2019-10-29',64.0,9.0,2,1.0,46.0);
INSERT INTO "tt" VALUES('2019-10-28',65.0,9.0,2,2.0,47.0);
INSERT INTO "tt" VALUES('2019-10-27',66.0,9.0,2,3.0,47.0);
INSERT INTO "tt" VALUES('2019-10-26',67.0,9.0,2,4.0,47.0);
INSERT INTO "tt" VALUES('2019-10-25',68.0,9.0,2,5.0,48.0);
INSERT INTO "tt" VALUES('2019-10-24',69.0,9.0,2,6.0,49.0);
INSERT INTO "tt" VALUES('2019-10-23',70.0,10.0,2,0.0,50.0);
INSERT INTO "tt" VALUES('2019-10-22',71.0,10.0,2,1.0,51.0);
INSERT INTO "tt" VALUES('2019-10-21',72.0,10.0,2,2.0,52.0);
INSERT INTO "tt" VALUES('2019-10-20',73.0,10.0,2,3.0,52.0);
INSERT INTO "tt" VALUES('2019-10-19',74.0,10.0,2,4.0,52.0);
INSERT INTO "tt" VALUES('2019-10-18',75.0,10.0,2,5.0,53.0);
INSERT INTO "tt" VALUES('2019-10-17',76.0,10.0,2,6.0,54.0);
INSERT INTO "tt" VALUES('2019-10-16',77.0,11.0,2,0.0,55.0);
COMMIT;