this query give me the correct result except off days and absent.i am trying but its a complex thing for me

this is my query

SELECT

EID,

LATE,

[HALF DAY],

ABSENT,

[OFF DAY],

[EXCESS / SHORT],

ExcessShort,

CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS [SPEND TIME]

FROM

(

SELECT

t.EID,

sum(case when t.Remarks = 'LATE' then 1 else 0...

sir your first question that

Q. do all employees have Sunday off or do different employees

have different days off ?

A.yes sir different days off. for employess.

Q.If employees have different days...

ok sir i give u full information

i am making a attendance project in this project when employee enter in factory he time in and when he go out from a...

sir here it is just copy and paste it into your sql server

create TABLE atend_log

(

eid int,

date datetime,

timein datetime,

timeout datetime,

spendtime datetime,

excessshort datetime,

remarks varchar(50)

)

insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])

values(1,'2013-01-01 00:00:00.000','2013-06-19 08:39:00.000','2013-06-19 18:04:00.000','1900-01-01 09:25:00.000','1900-01-01 00:25:00.000','Late')

insert...

SIR I AM REALLY SORRY I DONT HAVE AN OPTION OF SCRIPT DATA I JUST SCRIPT MY TABLE

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id...

sir this is my whole data

eid-------date---------------timein------------------timeout--------------spendtime-----------------excessshort--------remarks

17074--2013-01-01-------08:39:00.000-------18:04:00.000---------------09:25:00----------------00:25:00-------Late

17074--2013-01-02-------08:42:00.000-------09:56:00.000---------------08:50:00---------------10:00.000--------null

17074--2013-01-02-------10:09:00.000-------12:23:00.000---------------null---------------------null--------------null

17074--2013-01-02-------12:32:00.000--------3:14:00.000---------------null---------------------null--------------null

17074--2013-01-02-------13:30:00.000-------18:10:00.000---------------null---------------------null--------------null

17074--2013-01-03-------08:40:00.000-------17:55:00.000---------------09:15:00.000----------00:15:00.000------null

17074--2013-01-04-------08:32:00.000-------10:53:00.000---------------08:45:00.000----------00:15:00.000------null

17074--2013-01-04-------11:09:00.000-------12:57:00.000---------------null----------------------null-------------null

17074--2013-01-04-------13:06:00.000-------13:18:00.000---------------null----------------------null-------------null

17074--2013-01-04-------13:45:00.000-------18:09:00.000---------------null----------------------null-------------null

17074--2013-01-05-------08:47:00.000-------18:01:00.000------------ ---09:14:00.000------------00:14:00.000---halfday

17074--2013-01-08-------08:36:00.000-------10:25:00.000------------ ---09:55:00.000------------00:55:00.000-----null

17074--2013-01-08-------10:49:00.000-------13:02:00.000---------------null-----------------------null-------------null

17074--2013-01-08-------13:35:00.000-------15:24:00.000---------------null-----------------------null-------------null

17074--2013-01-08-------15:42:00.000-------19:46:00.000---------------null-----------------------null-----------------null

this is your query

;WITH AllDateCTE

AS

(

SELECT Eid,Date,SpendTime,Remarks FROM ATTEND_LOG WHERE EID=17074

UNION ALL

SELECT Eid,DATEADD(DD,-1,Date) AS Date,NULL AS SpendTime,

CASE

WHEN DATENAME(weekday,DATEADD(DD,-1,Date)) ='SUNDAY'

THEN ...

I AM SORRY SIR BUT THIS QUERY GIVE ME THAT RESULT

EID-------LATE----HALFDAY---ABSENT--OFFDAY--SPENDTIME

17074-----1---------1----------0--------0----------204:35

SIR I DID THAT BEFORE

WHEN DATENAME(weekday,DATEADD(DD,-1,Date)) ='SUNDAY'

BUT OFF DAY NOT SHOWING AND ABSENT ALSO NOT SHOWING

sir its not giving me off day

we have only one off day and that day is sunday

SIR ITS GIVING ME THE SAME RESULT I DID SOME CHANGES

;WITH AllDateCTE

AS

(

SELECT Eid,Date,SpendTime,Remarks FROM ATTEND_LOG WHERE EID=17074

UNION ALL

SELECT Eid,DATEADD(DD,-1,Date) AS Date,NULL AS SpendTime,CONVERT(VARCHAR(50),'Absent' )as Remarks

FROM AllDateCTE

WHERE...

sir that problem is solve by my self

the big problem is that this is my query

SELECT

EID,

NAME,

Late,

Halfday,

--Absent,

--OFFDAY,

CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS SPENDTIME

FROM

(

SELECT...

no absent record added in the attend_log.u have to calculate it and show absent into your data.that work is done u can see in the procedure .problem is when data...

no record added in the attend_log u have to calculate it and show absent into your data .that work is done .

problem is when data inserted on leave infomation table...

Thanks 🙂