>>>>>>>>>>>I am trying to build a view that totals an amount filed, I wish to have one column total be a YTD total and another column for just a particular day. I have tried using a join but that gives two rows for the result, I need one row with both totals.
>>>>>>>>>>>
>>>>>>>>>>>TIA
>>>>>>>>>>>
>>>>>>>>>>>Ian
>>>>>>>>>>Make that a union not a join
>>>>>>>>>
>>>>>>>>>Union gives you new row(s). Do you really want to have columns? Could you post a sample of wishful query results.
>>>>>>>>
>>>>>>>>The view is to show the amount of a commodity delivered to a warehouse both YTD and today
>>>>>>>>
>>>>>>>>Commodity YTD delivered Today delivered
>>>>>>>>Wheat 99999 11111
>>>>>>>>
>>>>>>>>Ian
>>>>>>
>>>>>>Firstly, excuse me for empty reply. Secondly, what do you have in base table? Do you have multiple commodities? It seems to me that cross-tab query will be part of solution.
>>>>>
>>>>>Yes there are multiple commoddities. What I think I am wondering is, is it possible to perform the query as follows:
>>>>>
>>>>>sum(amount) for dtdate < date() in one column and sum(amount) for dtdate = date() in a separate column.
>>>>
>>>>Don't rush, please! If you have multiple commodities then, I assume, you want to get many records (one for each commodity) in resulting cursor. Is this right?
>>>
>>>Sorry about that!
>>>
>>>Yes there are muliple commodities. Yes I do want one for each commodity
>>
>>Ok. I am leaving now, so I will give you some ideas how to fulfil your task:
>>1. Normalize your data around YTD, i.e. Select-SQL with adding one new field 'ytd' equal "a" if table.datefield>date() and "b" if table.fielddate<=date().
>>2. Group this cursor by two fields (commodity_name+ytd) to get SUM(amount).
>>3. Run cross-tab query now, using commodity_name as rowfield and ytd as columnfield.
>>I hope someone else will help you, while you are moving here.
>
>Try this:
>It uses two selects but in the end gives one row per commodity
>
>SELECT COMMODITY, SUM(AMOUNT) AS YTD, 00000000.00 AS DAY;
> FROM MYTABLE;
> WHERE YEAR(DATE) = YOURYEAR;
> GROUP BY 1;
>UNION ALL;
>SELECT COMMODITY, 000000.00 AS YTD, SUM(AMOUNT) AS DAY;
> FROM MYTABLE;
> WHERE DATE =TODAY;
> GROUP BY 1;
> INTO CURSOR FIRST
>
>SELECT COMMODITY, SUM(YTD) AS YTD, SUM(DAY) AS DAY;
> FROM FIRST;
> GROUP BY 1;
> INTO CURSOR FINAL
>
>Regards,
>
>Abdul Ahad

Yes, I think this right!

juan