如何在SQL中插入update atteandamce [英] How to insert update atteandamce in SQL

问题描述

我有一张名为Attendance Register的表，它的专栏是

EntryId，EmpId，AttMonth，D1，D2，D3，D4 ........ D31



在此表中每天检查是否存在Recoard然后更新其他插入。



我的Problam是如何每天更新的打孔



我尝试过：

i have a table named Attendance Register and Its Column are
EntryId , EmpId, AttMonth, D1, D2 ,D3,D4........D31

In This Table every day check Is Recoard exists then Update else Insert.

My Problam is how to update Day to Day After Punch

What I have tried:

INSERT INTO dbo.EmpPunch
		(
			EmpId, PunchId, PunchInDate,[Status]
		)
		VALUES
		(
			@EmpId, @PunchId, GETDATE(), 'P'
		)
	
	IF NOT EXISTS(SELECT * from dbo.AttendanceRegister WHERE EmpId = @EmpId AND MONTH(AttMonth) = MONTH(GETDATE()) AND YEAR(AttMonth) = YEAR(GETDATE()))
		BEGIN

			INSERT INTO dbo.AttendanceRegister 
			(
				EmpId,AttMonth 
			) 
			VALUES
			(
				@EmpId, GETDATE()
			)
		END

		UPDATE dbo.AttendanceRegister SET
			D16 = 'P'
		WHERE EmpId = @EmpId AND MONTH(AttMonth) = MONTH(GETDATE()) AND YEAR(AttMonth) = YEAR(GETDATE())

推荐答案

因为SQL本身就是多用户，更好的解决方案是进行更新，如果不起作用，则执行INSERT：

Because SQL is inherently multiuser, a better solution is to do the UPDATE, and if that doesn't work, then do an INSERT:

BEGIN TRANSACTION;
UPDATE AttendanceRegister SET ... WHERE EmpId = @EmpId AND MONTH(AttMonth) = MONTH(GETDATE()) AND YEAR(AttMonth) = YEAR(GETDATE()));
IF @@ROWCOUNT = 0
BEGIN
  INSERT INTO EmpPunch (EmpId, PunchId, PunchInDate,[Status]) VALUES(@EmpId, @PunchId, GETDATE(), 'P');
END
COMMIT TRANSACTION;

我有一张名为Attendance Register的表格及其专栏是

EntryId ，EmpId，AttMonth，D1，D2，D3，D4 ........ D31



我的Problam是如何动态更新日柱...



Ex。

八月 - 2011

如果日期是8月1日那么更新D1

如果日期8月2日然后更新D2

如果日期8月3日然后更新D3

如果日期8月4日然后更新D4

。

。

。

。

。

如果日期是8月31日那么更新D31



如果可能在没有案例的情况下动态更新动态使用



和Inser在附件表中重新加入更新后更新所有星期日列值周休息.......

i have a table named Attendance Register and Its Column are
EntryId , EmpId, AttMonth, D1, D2 ,D3,D4........D31

And My Problam is How To Update Day Column Dynamically...

Ex.
August - 2011
If Date 1 August Then Update D1
If Date 2 August Then Update D2
If Date 3 August Then Update D3
If Date 4 August Then Update D4
.
.
.
.
.
If Date 31 August Then Update D31

If Possible Update Without Case Using an Dynamically

ANd After Inser a New Recoard in Attandance Table To Update All Sunday Column Value Week Off.......

以下将仅在没有记录的情况下创建记录。通过< empid的值>我的意思是代码中的参数@empid：

The following will create a record only if no record already exists. By <value of empid> I mean the parameter @empid in your code:

insert into attendanceregister(empid, attmonth)
select a.*
from
(
select <value of empid>, getdate()
) as a,
(
select <value of empid>, month(getdate())
except
select empid, month(attmonth) from attendanceregister
) as b

这篇关于如何在SQL中插入update atteandamce的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！