Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DATEADD Expand / Collapse
Author
Message
Posted Wednesday, May 28, 2014 4:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 9:19 AM
Points: 198, Visits: 397
Good Morning.

I have the following script that pulls me back a number of "jobs".

SELECT	DISTINCT		 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

WHERE STAGE.[stage-code] = '93'

) AS STAGE1
ON JOB.[JOB-NO] = STAGE1.[STAGE.Job_Number]


INNER JOIN
[dbo].[IH_IH-LOCATION] AS LOC
ON LOC.[place-ref] = JOB.[PLACE-REF]
INNER JOIN [dbo].[IH_RE-TNCY-PLACE] AS TPLAC
ON TPLAC.[place-ref] = LOC.[place-ref]
INNER JOIN [dbo].[IH_RE-TENANCY] AS TEN
ON TPLAC.[tncy-sys-ref] = TEN.[tncy-sys-ref]








WHERE LOC.[scheme] <> 'LEASED'
AND TEN.[tncy-status] <> 'FOR'




ORDER BY STAGE1.[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
Post #1575119
Posted Wednesday, May 28, 2014 4:51 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
Yes - add it to the WHERE bit:

WHERE [existing conditions]
AND STAGE1.[STAGE.Completion_Date] > dateadd(d,-7,getdate())



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1575123
Posted Wednesday, May 28, 2014 4:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 9:19 AM
Points: 198, Visits: 397
Legend. Thanks Phil
Post #1575125
Posted Wednesday, May 28, 2014 4:55 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1575126
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse