Okay - Good Morning.
I am trying to calculate Item Usage for specific date ranges and fumbling with using GET DATE function for 'in between' Dates...
Here's a straight forward script to calculate Item MO Usage for past 90 days - it works great.
(SELECT [Item Number], sum([TRX QTY] * - 1) AS MOUsageQty90
FROM InventoryTransactions
WHERE ([Document Type] = 'Adjustment') AND ([Source Indicator] in ('Issue', 'Reverse Issue') AND ([Document Date] >= GETDATE() - 90))
GROUP BY [Item Number])
When I try to calculate usage between 90 and 180 days - I modified as such and I get nothing in return:
(SELECT [Item Number], sum([TRX QTY] * - 1) AS MOUsageQty180
FROM InventoryTransactions
WHERE ([Document Type] = 'Adjustment') AND ([Source Indicator] = 'Issue') AND ([Document Date] BETWEEN GETDATE() -90 AND GETDATE() - 180) OR
([Document Type] = 'Adjustment') AND ([Source Indicator] = 'Reverse Issue') AND ([Document Date] BETWEEN GETDATE() - 90 AND GETDATE() - 180)
GROUP BY [Item Number])
I think my issue might be related to [Document Date] being a date time field and Get Date is just a Date format? Or am I missing something obvious?