March 19, 2015 at 12:51 am
Hi,
I have a job that was setup that reports on everything that was added this week and sends out the report.
I'm wanting to change this to report on everything the day before instead.
Can you please advise how I need to change the below script so that it shows everything from the day before instead of this week? I need the script to automatically pull all records from yesterday and to automatically update it every day to yesterday.
WHERE (dbo.ocsTimeHeader.tshStatus = 'ACTIVE' OR
dbo.ocsTimeHeader.tshStatus = 'IDLE') AND (dbo.ocsPeriod.tpdStartDate >= DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 0)) AND
(dbo.ocsPeriod.tpdStartDate <= DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 6)) AND (dbo.v_Dept.Dept_Name <> ' ')
March 19, 2015 at 2:36 am
Use DATEADD(DAY,-1,GETDATE())?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 19, 2015 at 6:38 am
Here you go
where (
dbo.ocsTimeHeader.tshStatus = 'ACTIVE'
or dbo.ocsTimeHeader.tshStatus = 'IDLE'
)
and (dbo.ocsPeriod.tpdStartDate >= dateadd(day, datediff(day, 0, getdate()), -1)
and (dbo.ocsPeriod.tpdStartDate < dateadd(day, datediff(day, 0, getdate()), 0)
and (dbo.v_Dept.Dept_Name <> ' ')
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply