I am looking to create some buckets of dates in Sage intelligence. I am very new to Sage Intelligence, so please help me understand.
I started by creating a view in SQL (I was planning on creating a view for each bucket and then combining the views into a final view) . So the buckets are stock date was less than 3 months, 3-6 months, 6-12 months, 12-24 months and over 24 months.
I had tried these:
SELECT ITEMNUM, LOCATION, QTYAVAIL, STOCKDATE, ASSETCOST
FROM dbo.ICXLOT
WHERE (QTYAVAIL > 0) AND (STOCKDATE <= DATEPART(MONTH, DATEADD(MONTH, - 3, GETDATE())))
and then for the next bucket:
SELECT ITEMNUM, LOCATION, QTYAVAIL, STOCKDATE, ASSETCOST
FROM dbo.ICXLOT
WHERE (QTYAVAIL > 0) AND (STOCKDATE > DATEPART(MONTH, DATEADD(MONTH, - 3, GETDATE()))) AND (STOCKDATE > DATEPART(MONTH, DATEADD(MONTH, - 6, GETDATE())))
but that did not seem to change anything.
The dates are in the Sage YYYMMDD format.