CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
Hearty
Hearty

Reputation: 85

how to write query to fetch last week records

I need to fetch records from sqlserver2008r2, my query for fetching records for current week is:

select EmpCode,EventDate1 as EventDate,InTime,
case when OutTime is null then 'N/A' else Outtime end as OutTime from    
TMS_HOURCALC WHERE intime1
  BETWEEN dateadd(dd,(datediff(DD,-53684,getdate())/7)*7,-53684) 
    AND dateadd(dd,((datediff(dd,-53684,getdate())/7)*7)+7,-53684) 
    and empcode = @empcode
GROUP BY EmpCode, InTime,OutTime, EventDate1,intime1     
order by intime1;

Anyone please help me to fetch records for last week only from last sunday to last saturday.

Upvotes: 3

Views: 249

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44316

Making a calculation on a column and refering to it in the where clause, will give a terrible performance. This is a much better approach:

select EmpCode,EventDate1 as EventDate,InTime,
case when OutTime is null then 'N/A' else Outtime end as OutTime from    
TMS_HOURCALC 
WHERE intime1 >= dateadd(dd,(datediff(DD,-1,getdate())/7)*7-7,-1) 
and intime1 < dateadd(dd,((datediff(dd,-1,getdate())/7)*7),-1) 
and empcode = @empcode
GROUP BY EmpCode, InTime,OutTime, EventDate1,intime1     
order by intime1;

Upvotes: 2

valex
valex

Reputation: 24134

Make sure in your DB @@DateFirst is set to 7 (Sunday default) and use DATEPART()

Try to use:

where 
DATEPART(ww, intime1) = DATEPART(ww, GetDate())
and 
YEAR(intime1) = YEAR(GetDate())

Upvotes: 1

Related Questions