SQL View / Qtry

RichardAtk
RichardAtk used Ask the Experts™
on
SELECT     DW_Account, [Forecast Date], [Item No.], SUM([Forecast Quantity]) AS Expr1, [Location Code]
FROM        dbo.[NAV_dbo_Production Forecast Entry_R]
GROUP BY DW_Account, [Forecast Date], [Item No.], [Location Code]
HAVING     (SUM([Forecast Quantity]) > 0)

Hi I have this view that I would like to limit by only bringing in data after the 15th of the current month  or setting forecast qty to 0 for anything before this.  So for March we are on the 22nd I want to bring back all the data after 15th March 2017 when we get into April qry stays the same but after April 15h the report then only brings back data after April 15th and so on with each change of month.  Guess it's
greater than 15th of current month/year after current day of the month passes 15.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
use

select cast(convert(varchar(10), dateadd(dd,datediff(dd,datepart(dd,getdate()),15),getdate()), 101) as datetime)

Open in new window



something like this:

SELECT     DW_Account, [Forecast Date], [Item No.], SUM([Forecast Quantity]) AS Expr1, [Location Code]
FROM        dbo.[NAV_dbo_Production Forecast Entry_R]
WHERE  [Forecast Date] > cast(convert(varchar(10), dateadd(dd,datediff(dd,datepart(dd,getdate()),15),getdate()), 101) as datetime)
GROUP BY DW_Account, [Forecast Date], [Item No.], [Location Code]
HAVING     (SUM([Forecast Quantity]) > 0)

Open in new window

Author

Commented:
Perfect many thanks
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Something like this?
SELECT DW_Account, [Forecast Date], [Item No.], SUM([Forecast Quantity]) AS Expr1, [Location Code]
FROM dbo.[NAV_dbo_Production Forecast Entry_R]
WHERE (DAY(GETDATE()) >= 15 AND [Forecast Date] >= DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),15))
	OR (DAY(GETDATE()) < 15 AND [Forecast Date] >= DATEFROMPARTS(YEAR(DATEADD(month,-1,GETDATE())),MONTH(DATEADD(month,-1,GETDATE())),15))
GROUP BY DW_Account, [Forecast Date], [Item No.], [Location Code]
HAVING (SUM([Forecast Quantity]) > 0)

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial