欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-05-18 08:01:23
...

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

原理:建立一个名称为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),最后一周有二个休息日

使用Excel中的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;

 

相关标签: sqlite