How to get absent days where the days not in log table ?

Question

0.00/5 (No votes)

See more:

I need to get absent days from #dates table if the days not in log table
any help will be appreciated

What I have tried:

SQL

Declatr @DateT Table(D Date);
select @d1 = '10/1/2013',@d2= '10/31/2013';
;with dates ( date )
as
(
select @d1
union all
select dateadd(d,1,date) 
from dates
where date < @d2
)
insert into @DateT
select date as CountedDays  from dates ;
 
   Select distinct ID,convert(time,Log_D),Late,EarlyOut,AbsentOne,Day(Log_D) as day1 From(SELECT  m.ID,L.Log_D,L.C,T.EnterAllow_FD,W.FirstStartTime,T.Late_FD,T.EarlyOut_FD,T.OutAllow_FD,W.FirstEndTime,a.StartDate,a.EndDate,W.id WID, 
   
   (CASE WHEN convert(time,L.Log_D)>Dateadd(mi,T.Late_FD,W.FirstStartTime) and convert(time,L.Log_D)<Dateadd(mi,T.EnterAllow_FD+T.Late_FD,W.FirstStartTime) and COUNT(*) OVER (PARTITION BY L.EmpId,day(L.Log_D)) = 2 and L.C =1  
   then 'ت'end )as Late,
   (CASE WHEN convert(time,L.Log_D)<Dateadd(mi,-T.EarlyOut_FD,W.FirstEndTime) and convert(time,L.Log_D)>Dateadd(mi,-(T.EarlyOut_FD+T.OutAllow_FD),W.FirstEndTime)and COUNT(*) OVER (PARTITION BY L.EmpId,day(L.Log_D)) = 2   and L.C =2  
   then 'خ,م'end )as EarlyOut,
  
  (CASE WHEN (COUNT(*) OVER (PARTITION BY L.EmpId,day(L.Log_D)) < 2) 
  or (convert(time,L.Log_D)>Dateadd(mi,T.EnterAllow_FD+T.Late_FD,W.FirstStartTime )
   and convert(time,L.Log_D)<Dateadd(mi,-(T.EarlyOut_FD+T.OutAllow_FD),W.FirstEndTime))  THEN 'غ'  END) as [AbsentOne]

   from WorkPeriod W,LogT L full join MinimumInfoT m on m.ID =L.EmpId   ,TimeRulesT T,WorkPeriod a2 join WorkPeriod a on  a2.id=a.PeridNo where  month(L.Log_D) =10
   and year(L.Log_D) =2013 
  
   and L.C in(1,2) 
   and L.Log_D  not Between a.StartDate and a.EndDate
   and M.ID =L.EmpId 
   and W.id =m.W_Period 
   and W.id=T.WorkPeriodID
   and W.id=a.PeridNo)  as tt,@DateT T2 order by ID,day1

Posted 5-Apr-17 22:30pm

Member 11323395

Updated 6-Apr-17 2:23am

v2

Add a Solution

2 solutions

Solution 1

use this code

SQL

SELECT  DATENAME(dw, GETDATE()) union all
SELECT  DATENAME(dw, DATEADD(dd, 1, GETDATE())) union all
SELECT  DATENAME(dw, DATEADD(dd, 2, GETDATE())) union all
SELECT  DATENAME(dw, DATEADD(dd, 3, GETDATE())) union all
SELECT  DATENAME(dw, DATEADD(dd, 4, GETDATE())) union all
SELECT  DATENAME(dw, DATEADD(dd, 5, GETDATE())) union all
SELECT  DATENAME(dw, DATEADD(dd, 6, GETDATE()))

Posted 5-Apr-17 23:14pm

S.Dwaik

Comments

Member 11323395 6-Apr-17 5:58am

could you explain how to use this code with my existing code ?
because this code bring all absent days but does not bring the days that is not in the log table
thanks in advance

CHill60 6-Apr-17 7:50am

Listing the days of the week goes no where near answering the OP's question

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)

CHill60 · Accepted Answer · 2017-04-06T02:23:00

The easiest way to do this is to use a JOIN to filter the data.

I wasn't going to wade through your code to try and work out your table schemas so I created a dummy set of tables to demonstrate the point :

SQL

create table Employee (EmpId int identity(1,1), Name nvarchar(125))
insert into Employee values ('Joe Bloggs')

Create Table attendanceLog
(
	id int identity(1,1),
	EmpId int,
	DateLogged Date
)
insert into attendanceLog values
(1, '2013-10-02'),
(1, '2013-10-03'),
(1, '2013-10-04'),
(1, '2013-10-05'),
(1, '2013-10-06'),
(1, '2013-10-07'),
(1, '2013-10-08')

The following query will then list the dates from (your) @DateT table that do not appear in (my) AttendanceLog table:

SQL

select D 
from @DateT D
left outer JOIN attendanceLog AL ON AL.DateLogged=D.D
WHERE EmpId IS NULL

Note I have used a Date column type. You appear to be using a lot of CONVERT functions in your code so you may need to change the ON clause to

SQL

ON CAST(AL.DateLogged AS Date)=D.D