Baq to return each date within a two date parameters

[bordway (Bruce Ordway)]

Wonder if anyone has successfully built a BAQ to generate a list of dates between two date parameters? If you can share outline?

Thanks ahead of time.
I’m just starting on this one.
I saw some SQL examples listed on the web but…
TBD if anything will translate to a BAQ.

[Aaron_Moreng]

Two date params, one is StartDate, one is EndDate. Put the StartDate param on the date field of your table and do >=, put the EndDate param on the same field (with AND) and do <=.

[Banderson (Brandon Anderson)]

There is a CTE that someone made, but the CTE fails after 100.

Maximum recursion 100 has been exhausted before statement completion

I’ve created a CTE to get all dates from the first day of the year to the last day of the year. When I limit the date range to less than 100 days (April 10) then it runs fine. When I set the limit to 365 days (December 31) it reports "maximum recursion 100 has been exhausted before statement completion" and stops running. I’ve read that I have to set "option (maxrecursion 0)" but I can’t find anywhere to set that option. I’ve tried reading through the application help and looking through the exe…

[bordway (Bruce Ordway)]

Still… I liked the example
I’m thinking I can get away with a months worth of days.
but… I see farther down in that thread there is a workaround if I need it.
Thanks…

[ckrusen (Calvin Krusen)]

I needed a cheat that required having a list of numbers from 1 … N - where N was driven by the user.

So I just had a UD table with 1000 rows, with Key1 = 1, 2, 3, 4, …999, 1000

Then I join that table (with a table criteria of Key1 <= N) and it gives me that many rows of what I join it to.

FWIW - this was to create N qtys of labels, with N specified during Order Entry.

OrderDtl.LblQty_c held an int for the num of labels to print
OrderDtl.LblText_c held the text to print

Joining OrderDtl to UD05, with UD05.Key1 <= OrderDtl.LblQty_c, would giver me multiple records for each OrderLine. Then the BAQ Report would print a label for each record. This allowed us to print different qty’s of labels for each order line, and didn’t require the user to enter a qty during printing.

[Banderson (Brandon Anderson)]

FWIW, I usually just find a well populated table at least midly related to what I am doing and just return the date and group by it. I functionally get a list of every date that means something to what I’m doing. So far it’s worked out for me well. If I’m looking in the past, I can used parttran or laborDtl, as they usually have some record on a date, and if there wasn’t anything on that date, it’s probably not needed for what I’m doing anyways.

It hasn’t bit me yet doing it that way.

[bordway (Bruce Ordway)]

@Banderson

, I usually just find a well populated table

Yes, I am still thinking about getting dates from the parttran table. It’s about as related & well populated as I will get.

Even though I have the basic CTE generating a the date list…
I would still need to pull in A LOT of other data, where hairy linking is already involved.
Regardless of what I end up doing eventually… I like that CTE

Thanks