How to group by date range input ? 1

[tdong]

Thanks in advance
Sql Table has
id date1 date2
1 1/1/2005 1/6/2005
2 1/5/2005 1/7/2005
3 1/7/2005 1/9/2005
4 1/1/2005 1/9/2005

user select range 1/1/2005 to 1/9/2005 for each date in date range it will return either date1 or date2 that equal to date range
1/1/2005 this display record 1,4 since if contain in date1
1
4
1/2/2005
1/3/2005
1/4/2005
1/5/2005
2
1/6/2005
1
1/7/2005
2 -contain in date2
3 -contain in date1
1/8/2005
1/9/2005
3
4

 

[synapsevampire]

Keep in mind that the date ranges for those not in the data are not records, they are within the parameter range, so you will have to manage that using a loop or an array.

In either case some versions of Crystal have limitations, so not stating your edition/version limits the help you'll receive.

If you didn't care about displaying dates that did not have a match it would be much easier, and just in case you don't care, and the display was overkill, just use the record selection formula and place something like:

{table.date1} = minimum({?mydaterangeparm})
or
{table.date1} = maximum({?mydaterangeparm})
or
{table.date2} = minimum({?mydaterangeparm})
or
{table.date2} = maximum({?mydaterangeparm})

This should prove simple and very fast.

Otherwise what you probably want to do is fill an array with the dates of the range, and then as you iterate through every row store values to a corresponding array (Keep in mind that Crystal's array handling is a bit weak):

Report header formula:

datevar array MyDates[ubound({?mydaterangeparm})];
numbervar x;
datevar startdate:=minimum({?mydaterangeparm});
for x := 1 to ubound(MyDates) do(
MyDates[x]:=Startdate;
Startdate:=Startdate+1;
);

This will fill the MyDates array with your dates. This isn't really required as we could simply loop through the dates and populate the proper TheIDs stringvar array but I thought it might prove more understandable this way.

Details section formula:
datevar array MyDates;
stringvar array TheIds[ubound(MyDates)];
numbervar x;
for x := 1 to ubound[MyDates] do(
if {table.date1}=MyDates[x] or
{table.date2}=MyDates[x] then
TheIDs[x]:=TheIDs[x]&chr(13)&{table.id};
);

Now display them in the report footer using a formula:

datevar array MyDates;
stringvar array TheIds[ubound(MyDates)];
numbervar x;
for x := 1 to ubound[MyDates] do(
MyDates[x]& TheIDs[x]
);

This should get you very close, I don't have Crystal here to test, but the theory is sound.

-k

 

[synapsevampire]

Sorry, this part should have read:

If you didn't care about displaying dates that did not have a match it would be much easier, and just in case you don't care, and the display was overkill, just use the record selection formula and place something like:

{table.date1} in {?mydaterangeparm})
or
{table.date2} in {?mydaterangeparm}

then just display your rows, you'll only get those with corresponding dates, and you can group on the date field.

-k

 

[tdong]

Thank you very much I don't need to display the date that has no record . Let me try thanks

 

[tdong]

Just test your suggestion. it is the same as
{table.date1} in({?mydaterangeparm}) or
{table.date2} in({?mydaterangeparm})

what I need is a GroupBy the input date
1/1/2005
1,4
1/5/2005
2
1/6/2005
1
1/7/2005
2,3
1/9/2005
3,4

 

[synapsevampire]

Group by the date field, you'll get the same grouping, you just won't get all of the days.

-k

 

[tdong]

which date field do i use group by ? I can't use group by date1 or date2 I need to use groupby from the user input. this select is using OR statement which mean I can't use the GroupBy of any table value.

example user enter daterange 1/1/2005 to 1/9/2005 for each date from user input I need select all the records with date1 = userDate or date2 = userDate and somehow merge them together in the same date. For userDate = 1/1/2005 select record where date1 = 1/1/2005 or date2 = 1/1/2005
and so on for 1/2/2005

1/1/2005
1 1/1/2005 1/6/2005
4 1/1/2005 1/9/2005
1/5/2005
2 1/5/2005 1/7/2005
1/6/2005
1 1/1/2005 1/6/2005
1/7/2005
2 1/5/2005 1/7/2005
3 1/7/2005 1/9/2005
1/9/2005
3 1/7/2005 1/9/2005
4 1/1/2005 1/9/2005

 

[synapsevampire]

Ahhh. right.

You can't use a grouping at all, remember that a parameter is NOT data. You can use an array as in my example, or consider this cheat:

Create a Union Query to create a single date field.

This can be done in a View, or within Crystal, the latter is based upon your version of Crystal, which is still a secret

select 'date1' type, id, date1 from table
union all
select 'date2' type, id, date2 from table

Now you have the data in a single column which makes this much simpler.

In cr 8.5 you can edit the Database->Show SQL Qery to do this, in CR 9 or above, use the add command to create the SQL, or as previously stated, create a View on the database.

-k

 

[synapsevampire]

Ahhh, right, good point.

You could create an array as previously demonstrated, or you might cheat the whole thing by using a union query to create a single column date field, as in:

select 'date1' type, id, date1 from table
union all
select 'date2' type, id, date2 from table

You can create a view on the database, or use a query as the datasource for your report.

How you use a query as a datasource is crystal version dependent.

-k