We help IT Professionals succeed at work.

SQL View / Qtry

RichardAtk
RichardAtk asked
on
104 Views
Last Modified: 2017-03-22
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

CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Perfect many thanks
Vitor Montalv√£oIT Engineer
CERTIFIED EXPERT
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