Link to home
Start Free TrialLog in
Avatar of RichardAtk
RichardAtk

asked on

SQL View / Qtry

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.
ASKER CERTIFIED SOLUTION
Avatar of YZlat
YZlat
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RichardAtk
RichardAtk

ASKER

Perfect many thanks
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