Hello everyone: I have a select statement in which I just want the records that are from the last 7 days from todays date, can someone offer some assistance? Records are added everyday. What can I do to this statement to get my results? The column I need to get the last 7 days from is dbo.APP_REQUEST.OPEN_DATE

SELECT dbo.APP_REQUEST.APP_NBR, dbo.APP_REQUEST.PROJ_NBR, dbo.APP_REQUEST.APP_CODE, dbo.APP_REQUEST.STATUS,
                     dbo.APP_REQUEST.STAGE, dbo.APP_REQUEST.OPEN_DATE, dbo.APP_REQUEST.ACPT_DATE, dbo.APP_REQUEST.APPL_NAME,
                     dbo.APP_REQUEST.APPL_PHONE, dbo.APP_REQUEST.OWN_NAME, dbo.APP_REQUEST.OWN_PHONE, dbo.APP_REQUEST.PLANNER,
                     dbo.APP_REQUEST.SCHED_EXP_DATE, dbo.PROJECT.PROJ_NAME, dbo.PROJECT.FOLIO_NBR, dbo.TBL_APP_REQUEST.DESCRIPTION
FROM dbo.APP_REQUEST INNER JOIN
                     dbo.PROJECT ON dbo.APP_REQUEST.PROJ_NBR = dbo.PROJECT.PROJ_NBR INNER JOIN
                     dbo.TBL_APP_REQUEST ON dbo.APP_REQUEST.APP_CODE = dbo.TBL_APP_REQUEST.APP_CODE
WHERE (dbo.APP_REQUEST.APP_CODE IN ('SDP', 'SDPA', ' SDPI'))

__________________
Jaime E. Maccou

I Found a simple solution

I added and app_request.open_date > getdate()-7 my my where clause

SELECT [dbo].[APP_REQUEST].[APP_NBR], [dbo].[APP_REQUEST].[PROJ_NBR], [dbo].[APP_REQUEST].[APP_CODE], [dbo].[APP_REQUEST].[STATUS], [dbo].[APP_REQUEST].[STAGE], [dbo].[APP_REQUEST].[OPEN_DATE], [dbo].[APP_REQUEST].[ACPT_DATE], [dbo].[APP_REQUEST].[APPL_NAME], [dbo].[APP_REQUEST].[APPL_PHONE], [dbo].[APP_REQUEST].[OWN_NAME], [dbo].[APP_REQUEST].[OWN_PHONE], [dbo].[APP_REQUEST].[PLANNER], [dbo].[APP_REQUEST].[SCHED_EXP_DATE], [dbo].[PROJECT].[PROJ_NAME], [dbo].[PROJECT].[FOLIO_NBR], [dbo].[TBL_APP_REQUEST].[DESCRIPTION]
FROM [dbo].[APP_REQUEST], [dbo].[PROJECT], [dbo].[TBL_APP_REQUEST]
where app_request.proj_nbr = project.proj_nbr
and app_request.app_code = tbl_app_request.app_code
and app_request.app_code in ('SDP', 'SDPA',' SDPI')
and app_request.open_date > getdate()-7

Hi,

Alternatively you can also take the help of DATEADD function in SQL Server.

SELECT dbo.APP_REQUEST.APP_NBR, dbo.APP_REQUEST.PROJ_NBR, dbo.APP_REQUEST.APP_CODE, dbo.APP_REQUEST.STATUS,
                      dbo.APP_REQUEST.STAGE, dbo.APP_REQUEST.OPEN_DATE, dbo.APP_REQUEST.ACPT_DATE, dbo.APP_REQUEST.APPL_NAME,
                      dbo.APP_REQUEST.APPL_PHONE, dbo.APP_REQUEST.OWN_NAME, dbo.APP_REQUEST.OWN_PHONE, dbo.APP_REQUEST.PLANNER,
                      dbo.APP_REQUEST.SCHED_EXP_DATE, dbo.PROJECT.PROJ_NAME, dbo.PROJECT.FOLIO_NBR, dbo.TBL_APP_REQUEST.DESCRIPTION
FROM dbo.APP_REQUEST INNER JOIN
                      dbo.PROJECT ON dbo.APP_REQUEST.PROJ_NBR = dbo.PROJECT.PROJ_NBR INNER JOIN
                      dbo.TBL_APP_REQUEST ON dbo.APP_REQUEST.APP_CODE = dbo.TBL_APP_REQUEST.APP_CODE
WHERE (dbo.APP_REQUEST.APP_CODE IN ('SDP', 'SDPA', ' SDPI'))
WHERE CONVERT(VARCHAR(10), dbo.APP_REQUEST.OPEN_DATE, 101) > DATEADD(DAY, -7, CONVERT(VARCHAR(10), GETDATE(), 101))

Thanks,

Lalit
Life Means More...;)

HI,

U can use DateDifff function in ur WHERE clause.

Select .......From.....
Where Datediff(Day,Date_Open,Getdate())< = 7

Hope this helps.



B. Anant