How to insert update atteandamce in SQL

Question

0.00/5 (No votes)

See more:

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())

Posted 25-Aug-17 22:14pm

Kailashkumar Raghvani

Updated 26-Aug-17 0:44am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kailashkumar Raghvani · Answer 1 · 2017-09-16T00:25:00

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.......

RAMASWAMY EKAMBARAM · Answer 2 · 2017-08-26T00:44:00

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

SQL

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

OriginalGriff · Answer 3 · 2017-08-25T22:29:00

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;

How to insert update atteandamce in SQL

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0