我有两个表:
1.Tbl_project
fld_id fld_allocated_days
1 10
2 3
3 1
4 99
2.Tbl_project_timesheet
fld_id fld_allocated_time fld_project_id
1 8.00 1
2 8.00 1
3 8.00 2
4 8.00 3
5 8.00 2
6 8.00 2
7 8.00 1
8 8.00 4
9 8.00 1
--fld_project_id从tbl_project引用(fld_id)
我想做一个函数,将表tbl_project中的fld_allocated_days列与tbl_project_timesheet中的SUM(fld_allocated_time)列进行比较。
如果第一列(fld_allocated_times)低于第二列(fld_allocated_days),请在表tbl_project_timesheet上插入。
函数参数之一是p_project_id,它将选择为哪个项目插入分配的时间。
我有一个功能,可以插入分配的时间,但不检查是否超过了分配的天数。
CREATE OR REPLACE FUNCTION function_add_timesheet_record( p_project_id integer, p_allocated_time numeric)
RETURNS void AS
BEGIN
INSERT INTO tbl_project_timesheet(fld_project_id,fld_allocated_time)
VALUES (p_project_id, p_allocated_time);
END
基于上面的评论,我们假设1 day
from from tbl_project
equals 8 hours
from table tbl_project_timesheet
。这样,采用您提供的数据,项目1的丢失天数为6天,项目4的丢失天数为98天。我们将其转换为秒,然后比较两个表中的值。
然后,我们将整天数量的八分之一填充到数组中,然后将数组的其余部分追加到数组中(如果存在不完整的天数)。
然后我们对数组进行嵌套,以便每行(天)有8个小时。最后,我们插入数据。
WITH
d AS (
SELECT
p.fld_id,
p.fld_allocated_days * 8 * 3600 - COALESCE(
SUM(
EXTRACT(epoch FROM t.fld_allocated_time)::int
),
0) AS diff
FROM
tbl_project p LEFT JOIN tbl_project_timesheet t
ON
p.fld_id=t.fld_project_id
GROUP BY
p.fld_id
),
agg_d AS (
SELECT
fld_id,
UNNEST(
ARRAY_APPEND(
ARRAY_FILL(
'8:00'::time,
ARRAY[(diff / (3600 * 8))::int]
),
((diff % (3600 * 8)) * interval '1 second')::time
)
) AS hours FROM d
WHERE
diff > 0
)
INSERT INTO tbl_project_timesheet
(fld_project_id, fld_allocated_time)
SELECT
fld_id, hours
FROM
agg_d
WHERE
hours > '0:0'::time;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句