Hello,


another little sql problem:

I have got an select statement:

select mytable.*
from mytable
order by mytable.datefield, mytable.orderno, mytable.posno
selectdo
|do something with the records found
selectempty
| nothing found
endselect

Now all is straight forward and simple, but -
the first order field is a tcdate field -
when the order by is executed it will obviously sort by the date and then by the orderno and posno fields. But i need baan to sort by using the corresponding calender week of the date and not the date as is stored.

Example data:

Date Orderno Posno
01.02.2002 123456 20
02.02.2002 123456 10
10.10.2002 111111 10

i need to sort by orderno,posno within the same calender week, therefore giving:

02.02.2002 123456 10
01.02.2002 123456 20
10.10.2002 111111 10

does anybody know how i can do this?

regards and thanks to anyone who has read the problem
Nick LeWe

Is this for a report? If yes then look at the sprintf$ function and it's date options. If one of those options gives you what you mean by calendar week - to me this means week 1 to 52 and it does do this - then you can pass this week variable to the report and let it do the sorting for you. Not sure if there is a option for which week of the month.

If this is not for a report then I will have to think about it some more. Not sure how you would do the sort then. Maybe more details on which table and such would help someone come up with a answer.

Mark

Yes the calender week is 1-52 (week of year) and the results are going to a report. If however, if I let the report do the sorting it seems to accumulate the records before they are sent and this disturbs control variable which I'm using to control various layout output.


Maybe I should try something on lines of:
In the select :
order by field1, field2

and then
save the date field to a variable as a calender week and year and try the report sort on that field.

Nick

If you have this select in your program script you can calculate the week between the selectdo/endselect. You should add this week field in your report input fields and sort on it.

Or do I miss something?

Han

Yes correct.

That's what I'm doing. The report sort however, disturbs the sequence of record output to the report and my control variables which I set to control the output of various layouts (they are used in the layout output expression) does not seem to work correctly.

All is solved!

Thanks for all the help.

Here's the way it worked:

1) select the data without order by
2) save week/year to temp. variable
3) use report sorting
4) remove output expressions
5) enter output expressions in report script using lattr.print accordingly
6) fire up and away you go


thanks everybody

Nick LeWe