Oct 31, 2019 at 04:22 PM

For Next in Command table Select Statement

46 Views

I have created a Command Table in Crystal using a SELECT statement and a WHERE statement that uses a datetime parameter. I have displayed a simplified version below.

SELECT

ci.inventory_id,

ci.activity_datetime

FROM COMPONENT_INVENTORY ci

WHERE ci.activity_datetime<{?INV_DateTime}

I presently UNION this with a duplicate SELECT statement having the WHERE statement be the datetime parameter minus 1

WHERE ci.activity_datetime<{?INV_DateTime} -1

I presently have this Command Table set up where i do 29 UNIONS to get information from 30 different days.

I would like to be able to set this up as some kind of FOR-NEXT query where i could just enter a parameter to tell it to look for this data for 30 different dates or 356 different dates etc.

I am presently using this to make a graph tracking inventory levels over a 30 day period. The first question my superior asked was to see 90 days of data.

Suggestions?