May 28, 2014 at 4:40 am
Good Morning.
I have the following script that pulls me back a number of "jobs".
SELECTDISTINCT JOB.[PLACE-REF] AS 'UPRN'
,TEN.[tenancy-ref] AS 'Tenant_Number'
,JOB.[JOB-NO] AS 'PK_Job_Number'
,STAGE1.[STAGE.Completion_Date] AS 'Completion_Date'
,TEN.[corr-name1] AS 'Tenant_Name'
FROM[dbo].[IH_RM-JOB] AS JOB
INNER JOIN
(
SELECT STAGE.[job-no] AS 'STAGE.Job_Number'
,STAGE.[stage-date] + STAGE.[stage-time] AS 'STAGE.Completion_Date'
FROM[dbo].[IH_RM-STAGE-HISTORY] AS STAGE
WHERESTAGE.[stage-code] = '93'
) AS STAGE1
ONJOB.[JOB-NO] = STAGE1.[STAGE.Job_Number]
INNER JOIN
[dbo].[IH_IH-LOCATION] AS LOC
ONLOC.[place-ref] = JOB.[PLACE-REF]
INNER JOIN[dbo].[IH_RE-TNCY-PLACE] AS TPLAC
ONTPLAC.[place-ref] = LOC.[place-ref]
INNER JOIN[dbo].[IH_RE-TENANCY] AS TEN
ONTPLAC.[tncy-sys-ref] = TEN.[tncy-sys-ref]
WHERELOC.[scheme] <> 'LEASED'
ANDTEN.[tncy-status] <> 'FOR'
ORDER BYSTAGE1.[STAGE.Completion_Date]
What I want to do is schedule this script to run every week. But when it runs it only pulls through the jobs of the last 7 days.
Is it the Where clause that I would place this ? Could you please advise how I could just pull back a weeks worth of jobs from the last 7 days?
Using the STAGE1.[STAGE.Completion_Date]
Thanks
May 28, 2014 at 4:51 am
Yes - add it to the WHERE bit:
WHERE [existing conditions]
AND STAGE1.[STAGE.Completion_Date] > dateadd(d,-7,getdate())
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
May 28, 2014 at 4:53 am
Legend. Thanks Phil
May 28, 2014 at 4:55 am
Ryan Keast (5/28/2014)
Legend. Thanks Phil
No problem. And if you want to ignore the time component, you might like to use
dateadd(d,-7,cast(getdate() as date))
instead.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply