'What to include in WHERE clause to show previous week of data
I'm writing a stored procedure for an SSRS report and one of the criteria is to show the data from the previous week. I've tried multiple different things in my WHERE clause to show the previous week and I can't find one that returns any records.
I've tried Datepart (w,-1,st.startdate), DATEDIFF, DATEPART(WEEK, GETDATE() - 7) and others, below is what I'm currently trying which also does not return any records.
DECLARE @EndDate DATETIME
SET @StartDate = DATEADD(DAY, DATEDIFF(DAY, 7, GETDATE()), 0)
SET @EndDate = GETDATE()
SELECT sfd.SalesForecastID,
sf.StartDate,
sf.EndDate,
sf.[Description],
CASE
WHEN sf.StatusID = 1 THEN 'Open'
WHEN sf.StatusID = 2 THEN 'Closed'
END AS 'Status',
pm.Generic,
pm.SubGeneric,
pm.SKU AS 'PartNumber',
sfd.ProposedVolume,
sfd.EstimatedValue,
sfd.MonthlyRunRate,
sfd.ProductNotes
FROM [ForestDBDev].[dbo].[SalesForecastDetail] sfd
INNER JOIN [ForestDBDev].[dbo].[SalesForecast] sf ON sf.SalesForecastID = sfd.SalesForecastID
INNER JOIN dbo.OrdPart op ON op.OrdPart_ID = sfd.PartID COLLATE DATABASE_DEFAULT
INNER JOIN [ForestDBDev].[ProductDB].[PartMaster] pm ON op.PartNum = pm.SKU COLLATE DATABASE_DEFAULT
WHERE sf.CreatedDate BETWEEN @StartDate AND @EndDate
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
