我需要计算工作总时数,工作总天数和总叶数 [英] I need to calculate the total hours worked, total days worked and total leaves taken
问题描述
USE [ctsdev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo]。[tbl_Leaves](
[Emp_id] [int] NOT NULL,
[CompanyName] [varchar](100)NULL,
[ConsultantName] [varchar](50)NULL,
[01] [int] NULL,
[02] [int] NULL,
[03] [int] NULL,
[04] [int] NULL,
[05] [int] NULL,
[06] [int] NULL,
[07] [int] NULL,
[08] [int] NULL,
[09] [int] NULL,
[10] [int] NULL,
[ 11] [int] NULL,
[12] [int] NULL,
[13] [int] NULL,
[14] [int] NULL,
[15] [int] NULL,
[16] [int] NULL,
[17] [int ] NULL,
[18] [int] NULL,
[19] [int] NULL,
[20] [int] NULL ,
[21] [int] NULL ,
[22] [int] NULL,
[23] [int] NULL,
[24] [int] NULL,
[25] [int] NULL,
[26] [varchar](20)NULL,
[27] [int] NULL,
[28] [int] NULL,
[29] [int] NULL,
[30] [int] NULL,
[31] [int] NULL,
[Total_Hours_Worked] [varchar](250)NULL,
[Total_Days_Worked] [varchar](10) NULL,
[Total_Leave_Taken] [varchar](10)NULL,
[月] [varchar](20)NULL,
[年] [varchar](20)NULL
)ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
这是我的请假表。
**** ************************** *
USE [ctsdev]
GO >
/ ******对象:StoredProcedure [dbo]。[usp_insertEmpleaves41]脚本日期:06/06/2016 10:03:23 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE程序[dbo]。[usp_insertEmpleaves41]
@Emp_id int,
@CompanyName varchar(100),
@ConsultantName varchar(50),
@ 01 varchar(20),
@ 02 varchar(20),
@ 03 varchar(20),
@ 04 varchar(20),
@ 05 varchar(20),
@ 06 varchar (20),
@ 07 varchar(20),
@ 08 varchar(20),
@ 09 varchar(20),
@ 10 varchar(20),
@ 11 varchar(20),
@ 12 varchar(20),
@ 13 varchar(20),
@ 14 varchar(20),
@ 15 varchar(20),
@ 16 varchar (20),
@ 17 varchar(20),
@ 18 varchar(20),
@ 19 varchar(20),
@ 20 varchar(20),
@ 21 varchar(20),
@ 22 varchar(20),
@ 23 varchar(20),
@ 24 varchar(20),
@ 25 varchar(20),
@ 26 varchar (20),
@ 27 varchar(20),
@ 28 varchar(20),
@ 29 varc har(20),
@ 30 varchar(20),
@ 31 varchar(20),
@Month varchar(20),
@Yarar varchar(20),
@Total_Hours_Worked varchar(20)
- @ Total_Days_Worked varchar(10)
- @ Total_Leave_Taken varchar(10)
- @ idate int
AS
BEGIN
BEGIN
插入[tbl_Leaves]
(
Emp_id,
CompanyName ,
顾问名称,
01,
02,
03,>
04,
05,
06,
07,
08,
09,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19,
20 ,
21,
22,
23,
24,
25,
26,
27,
28,>
29,
30,
31,>
月,
年,
Total_Hours_Worked
--Total_Days_Worked
--Total_Leave_Taken
)
值
(
(从[tbl_empinfo]中选择Emp_id,其中FirstName = @ ConsultantName),>
@CompanyName,
@ConsultantName,
@ 01,
@ 02,
@ 03,
@ 04,
@ 05,
@ 06,
@ 07,
@ 08,
@ 09,
@ 10,
@ 11,
@ 12 ,
@ 13,
@ 14,
@ 15,
@ 16,
@ 17,
@ 18,
@ 19,
@ 20,
@ 21,
@ 22,
@ 23,
@ 24,
@ 25,
@ 26,
@ 27,
@ 28,
@ 29,
@ 30,
@ 31,
(从Mstr_CTS_MonthName中选择Month_ID,其中Month_Name = @ Month),
@年,
(选择总和(01+02+03+04+05+06+07+08+09+10+11+12 + 13 + 14 + 15 + 16 + 17+18+19
+20+21+22+23+24+25+26+27 +28+29+30+31)
来自dbo.tbl_Leaves的
其中Emp_id = @Emp_id)
)
更新tbl_Leaves
SET
Total_Hours_Worked =(选择总和( 01 + 02 + 03 + 04 + 05 + 06 + 07 + 08 + 09 + 10 + 11 + 12 + 13+14+15+16+17+18+19
+20+21+22+23 +24+25+26+27+28+29+30+31)
来自dbo.tbl_Leaves的
,其中Emp_id = 13)
结束
结束
GO
这是ma插入程序
我尝试过:
插入每天工作的小时数后,我试图计算工作总时数,它的工作正常工作太多了,总工作时间和工作总天数我也做不到,请问我建议我一些解决方案
USE [ctsdev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Leaves](
[Emp_id] [int] NOT NULL,
[CompanyName] [varchar](100) NULL,
[ConsultantName] [varchar](50) NULL,
[01] [int] NULL,
[02] [int] NULL,
[03] [int] NULL,
[04] [int] NULL,
[05] [int] NULL,
[06] [int] NULL,
[07] [int] NULL,
[08] [int] NULL,
[09] [int] NULL,
[10] [int] NULL,
[11] [int] NULL,
[12] [int] NULL,
[13] [int] NULL,
[14] [int] NULL,
[15] [int] NULL,
[16] [int] NULL,
[17] [int] NULL,
[18] [int] NULL,
[19] [int] NULL,
[20] [int] NULL,
[21] [int] NULL,
[22] [int] NULL,
[23] [int] NULL,
[24] [int] NULL,
[25] [int] NULL,
[26] [varchar](20) NULL,
[27] [int] NULL,
[28] [int] NULL,
[29] [int] NULL,
[30] [int] NULL,
[31] [int] NULL,
[Total_Hours_Worked] [varchar](250) NULL,
[Total_Days_Worked] [varchar](10) NULL,
[Total_Leave_Taken] [varchar](10) NULL,
[Month] [varchar](20) NULL,
[Year] [varchar](20) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
This is my leave table.
******************************
USE [ctsdev]
GO
/****** Object: StoredProcedure [dbo].[usp_insertEmpleaves41] Script Date: 06/06/2016 10:03:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_insertEmpleaves41]
@Emp_id int,
@CompanyName varchar(100),
@ConsultantName varchar(50),
@01 varchar(20) ,
@02 varchar(20) ,
@03 varchar(20) ,
@04 varchar(20) ,
@05 varchar(20) ,
@06 varchar(20) ,
@07 varchar(20) ,
@08 varchar(20) ,
@09 varchar(20) ,
@10 varchar(20) ,
@11 varchar(20) ,
@12 varchar(20) ,
@13 varchar(20) ,
@14 varchar(20) ,
@15 varchar(20) ,
@16 varchar(20) ,
@17 varchar(20) ,
@18 varchar(20) ,
@19 varchar(20) ,
@20 varchar(20) ,
@21 varchar(20) ,
@22 varchar(20) ,
@23 varchar(20) ,
@24 varchar(20) ,
@25 varchar(20) ,
@26 varchar(20) ,
@27 varchar(20) ,
@28 varchar(20) ,
@29 varchar(20) ,
@30 varchar(20) ,
@31 varchar(20) ,
@Month varchar(20),
@Year varchar(20),
@Total_Hours_Worked varchar(20)
--@Total_Days_Worked varchar(10)
--@Total_Leave_Taken varchar(10)
--@idate int
AS
BEGIN
BEGIN
Insert into [tbl_Leaves]
(
Emp_id,
CompanyName ,
ConsultantName,
"01",
"02",
"03",
"04",
"05",
"06",
"07",
"08",
"09",
"10",
"11",
"12",
"13",
"14",
"15",
"16",
"17",
"18",
"19",
"20",
"21",
"22",
"23",
"24",
"25",
"26",
"27",
"28",
"29",
"30",
"31",
Month ,
Year,
Total_Hours_Worked
--Total_Days_Worked
--Total_Leave_Taken
)
Values
(
(Select Emp_id from [tbl_empinfo ] where FirstName=@ConsultantName),
@CompanyName,
@ConsultantName,
@01,
@02,
@03,
@04,
@05,
@06,
@07,
@08,
@09,
@10,
@11,
@12,
@13,
@14,
@15,
@16,
@17,
@18,
@19,
@20,
@21,
@22,
@23,
@24,
@25,
@26,
@27,
@28,
@29,
@30,
@31,
(Select Month_ID from Mstr_CTS_MonthName where Month_Name=@Month),
@Year,
(select sum ("01"+"02"+"03"+"04"+"05"+"06"+"07"+"08"+"09"+"10"+"11"+"12"+"13"+"14"+"15"+"16"+"17"+"18"+"19"
+"20"+"21"+"22"+"23"+"24"+"25"+"26"+"27"+"28"+"29"+"30"+"31")
from dbo.tbl_Leaves
where Emp_id=@Emp_id)
)
Update tbl_Leaves
SET
Total_Hours_Worked =(select sum ("01"+"02"+"03"+"04"+"05"+"06"+"07"+"08"+"09"+"10"+"11"+"12"+"13"+"14"+"15"+"16"+"17"+"18"+"19"
+"20"+"21"+"22"+"23"+"24"+"25"+"26"+"27"+"28"+"29"+"30"+"31")
from dbo.tbl_Leaves
where Emp_id=13)
END
END
GO
This is ma insert procedure
What I have tried:
after inserting the hours worked for each day i tried to calculate the total hours worked,it is working properly too bt the total leaves taken and total days worked i couldnt able to make,could u pls suggest me some solutions
推荐答案
到目前为止我能看到最大的问题em是数据结构的设计。这会导致计算等问题。
目前,您将每天的叶子定义为列。这会导致您需要对来自不同列的数据求和的情况。在我看来,正确的方法是将叶子存储为行,并将日期作为单个列。
考虑以下结构
tblLeaves
- Emp_id
- 公司名
- 顾问名号
- LeaveDate
现在每条记录代表一个假期,然后你可以根据行而不是列来计算。
尝试将设计转移到这个方向,我相信你现在面临的问题就会消失。
As far a I can see the biggest problem is the design of the data structures. This causes problems in calculations etc.
Currently you define leaves per day as columns. This causes a situation where you need to sum data from different columns. In my opinion the correct way would be to store the leaves as rows and just have the date as a single column.
Consider the following structure
tblLeaves
- Emp_id
- CompanyName
- ConsultantName
- LeaveDate
Now each record would represent a single leave and you could then calculate based on rows instead of columns.
Try shifting the design to this direction and I believe that the problem you're now facing disappears.
这篇关于我需要计算工作总时数,工作总天数和总叶数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!