Leonid
Wed Jun 25 22:43:19 CDT 2008
Yes, but
CREATE CURSOR Q1 (id Int , date Date, contrib Num(5,2))
ZAP IN q1
FOR i = 1 TO 10
INSERT INTO q1 values (1,DATE()+i,9)
ENDFOR
FOR i = 1 TO 11
INSERT INTO q1 values (1,DATE()+9+i,10)
ENDFOR
lnGroupSum = 50
SELECT id, date, contrib,;
(SELECT SUM(contrib) FROM Q1 AS Q2 ;
WHERE Q2.id=q1.id AND Q2.date<=q1.date) AS total, ;
(SELECT CEILING(SUM(contrib)/M.lnGroupSum) FROM Q1 AS Q2;
WHERE Q2.id=Q1.id AND Q2.date<=q1.date) AS Grouping;
FROM Q1 AS Q1 ;
INTO CURSOR Q2
BROWSE LAST NOWAIT
SELECT id, MIN(date),MAX(date),COUNT(*),SUM(contrib),MAX(total) ;
FROM Q2 GROUP BY id,Grouping ;
INTO CURSOR Q3
BROWSE LAST NOWAIT
Result:
Id Min_date Max_date Cnt Sum_contrib
Max_total
12008-06-26 2008-06-30 5 45.00 45.00
12008-07-01 2008-07-05 6 55.00 100.00
12008-07-06 2008-07-10 5 50.00 150.00
12008-07-11 2008-07-15 5 50.00 200.00
Looks wrong to me.
"Anders Altberg" <anders.altberg> wrote in message
news:%23Y1ajbw1IHA.5560@TK2MSFTNGP02.phx.gbl...
> CREATE CURSOR Q1 (id Int , date Date, contrib Num(5,2))
> ZAP IN q1
> FOR i = 1 TO 9
> INSERT INTO q1 values (1,DATE()+i,9)
> ENDFOR
> FOR i = 1 TO 11
> INSERT INTO q1 values (1,DATE()+9+i,10)
> ENDFOR
>
> lnGroupSum = 50
> SELECT id, date, contrib,;
> (SELECT SUM(contrib) FROM Q1 AS Q2 ;
> WHERE Q2.id=q1.id AND Q2.date<=q1.date) AS total, ;
> (SELECT CEILING(SUM(contrib)/M.lnGroupSum) FROM Q1 AS Q2;
> WHERE Q2.id=Q1.id AND Q2.date<=q1.date) AS Grouping;
> FROM Q1 AS Q1 ;
> INTO CURSOR Q2
> BROWSE LAST NOWAIT
>
> SELECT id, MIN(date),MAX(date),COUNT(*),SUM(contrib),MAX(total) ;
> FROM Q2 GROUP BY id,Grouping ;
> INTO CURSOR Q3
> BROWSE LAST NOWAIT
>
> Result:
> Id Min_date Max_date Cnt Sum_contrib
> Max_total
> 12008-06-26 2008-06-30 5 45.00 45.00
> 12008-07-01 2008-07-05 5 46.00 91.00
> 12008-07-06 2008-07-10 5 50.00 141.00
> 12008-07-11 2008-07-15 5 50.00 191.00
>
> Looks right to me.
>
> "Leonid" <ctr34 at inbox dot lv> wrote in message
> news:%23adWB2p1IHA.4704@TK2MSFTNGP05.phx.gbl...
>> No, CEILING will not help. The problem is that SUM(contrib) is not good
>> enough to distinguish between different Grouping. Let's say we have two
>> sequences
>>
>> 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 10
>>
>> and
>>
>> 10, 10, 10, 10, 10, 10, 10, 10, 10, 10
>>
>> The last members of them have equal SUM(contrib)=100, but they must lie
>> in different groups: in third for the first and in second for the second.
>> I doubt that modifying your code this may be accomplished. Actually I
>> doubt that this may be done with standard ANSI SQL. May be with ANSI
>> 2003, using windows functions?
>>
>> Leonid
>>
>> "Anders Altberg" <anders.altberg> wrote in message
>> news:ukSUG2f1IHA.528@TK2MSFTNGP02.phx.gbl...
>>> Could be because INT() rounds contrib/M.lnGroupSum down? See if using
>>> CEILING instead of INT solves it.
>>>
>>> -Anders
>>>
>>> "Leonid" <ctr34 at inbox dot lv> wrote in message
>>> news:etohlEQ1IHA.1240@TK2MSFTNGP02.phx.gbl...
>>>> Hi Anders,
>>>>
>>>> Seems that your code doesn't solve the problem. The author wrote "each
>>>> of which
>>>> must be under 500". Add RAND(1) as a first row to your code and you
>>>> will see that the second Sum_contrib will be 56 i.e. more than 50.
>>>>
>>>> Leonid
>>>>
>>>>
>>>>
>>>>
>>>> "Anders Altberg" <anders.altberg> wrote in message
>>>> news:eLoI$zD1IHA.4220@TK2MSFTNGP02.phx.gbl...
>>>>> Hi Stephen,
>>>>> Well it is possible:
>>>>>
>>>>> CREATE CURSOR Q1 (id Int , date Date, contrib Num(5,2))
>>>>> FOR i = 1 TO 2
>>>>> FOR k= 1 TO 30
>>>>> INSERT INTO Q1 VALUES (i, DATE()+k, INT(1+RAND()*10 ))
>>>>> NEXT
>>>>> NEXT
>>>>> GO TOP IN Q1
>>>>> BROWSE LAST NOWAIT
>>>>>
>>>>> lnGroupSum = 51
>>>>> Note: in order to get the groups sum >=50, the integer division must
>>>>> be set to 51.
>>>>> SELECT id, date, contrib,;
>>>>> (SELECT SUM(contrib) FROM Q1 AS Q2 ;
>>>>> WHERE Q2.id=q1.id AND Q2.date<=q1.date) AS total, ;
>>>>> INT((SELECT SUM(contrib)/M.lnGroupSum FROM Q1 AS Q2;
>>>>> WHERE Q2.id=Q1.id AND Q2.date<=q1.date)) AS Grouping;
>>>>> FROM Q1 AS Q1 ;
>>>>> INTO CURSOR Q2
>>>>> BROWSE LAST NOWAIT
>>>>>
>>>>> SELECT id, MIN(date),MAX(date),SUM(contrib),MAX(total) ;
>>>>> FROM Q2 GROUP BY id,Grouping ;
>>>>> INTO CURSOR Q3
>>>>> BROWSE LAST NOWAIT
>>>>>
>>>>> This can be rolled into a single query, as of VFP 9.
>>>>> The part that could be slow with big datasets is the correlated
>>>>> subquery that returns the accumulated sum.
>>>>> As it's repeated twice the query could be rewritten to only calculate
>>>>> that sum once.
>>>>> I've used a date column to aggregate the sum, but a serial number or
>>>>> even recno() could do as well.
>>>>> -Anders
>>>>>
>>>>>
>>>>> "Stephen Ibbs" <stephen@datadevelopments.co.uk> wrote in message
>>>>> news:eHtGZ3h0IHA.1768@TK2MSFTNGP03.phx.gbl...
>>>>>> Thanks Rush - I went and had a lie down and started again - and
>>>>>> realised that it was quite simple. The code I have used is:
>>>>>>
>>>>>> GO TOP
>>>>>>
>>>>>> DO WHILE ! EOF()
>>>>>>
>>>>>> dlnAggTotal = 0
>>>>>>
>>>>>> DO WHILE dlnAggTotal <=500
>>>>>>
>>>>>> IF dlnAggTotal + dlnAmount <=500
>>>>>>
>>>>>> dlnAggTotal = dlnAggTotal + dlnAmount
>>>>>>
>>>>>> ELSE
>>>>>>
>>>>>> EXIT
>>>>>>
>>>>>> ENDIF
>>>>>>
>>>>>> SKIP
>>>>>>
>>>>>> ENDDO
>>>>>>
>>>>>> INSERT INTO AggTemp VALUES(etc etc)
>>>>>>
>>>>>> ENDDO
>>>>>>
>>>>>> I had avoided SCAN/ENDSCAN in this case becuase of its implicit SKIP
>>>>>> and I wanted to be sure of where I was.
>>>>>>
>>>>>> I would still be curious to know whether this was possible - and
>>>>>> easier - with a SQL statement or two.
>>>>>>
>>>>>> Sincerely
>>>>>>
>>>>>> Stephen
>>>>>>
>>>>>> "Rush Strong" <rpstrong@gmail.com> wrote in message
>>>>>> news:clu6k.92180$bs3.63597@trnddc07...
>>>>>>> Stephen Ibbs wrote:
>>>>>>>> I have a table of donations, all random amounts but each one is
>>>>>>>> under 10.00
>>>>>>>>
>>>>>>>> I need to create a cursor that aggregates these into totals, each
>>>>>>>> of which must be under £500. In manual terms, go through the list,
>>>>>>>> adding up each donation with a calculator until the next donation
>>>>>>>> would take it over £500, then put the entry into the cursor then
>>>>>>>> continue through the list. The resulting cursor might have 8
>>>>>>>> records in it, each with a total of between 490 and 500. As a
>>>>>>>> cherry on the cake it would be nice if the cursor also showed how
>>>>>>>> many donations had made up each total.
>>>>>>>>
>>>>>>>> Is there any way these can be done via SQL - at the moment I am
>>>>>>>> disappearing down a black hole of do while, skips and exits
>>>>>>>>
>>>>>>>> Sincerely
>>>>>>>>
>>>>>>>> Stephen
>>>>>>>>
>>>>>>>
>>>>>>> Doesn't sound very tough without SQL. This isn't tested, but it
>>>>>>> looks good . . .
>>>>>>>
>>>>>>> *---------------------------------------------------------
>>>>>>>
>>>>>>> USE Donations
>>>>>>> USE LogFile IN 0
>>>>>>>
>>>>>>> lnTarget = 500
>>>>>>>
>>>>>>> SCAN
>>>>>>>
>>>>>>> lnTotal = 0
>>>>>>> lnDonationsCount = 0
>>>>>>>
>>>>>>> SCAN WHILE lnTotal + Donations.DonationAmount <= lnTarget
>>>>>>> lnTotal = lnTotal + Donations.DonationAmount
>>>>>>> lnDonationsCount = lnDonationsCount + 1
>>>>>>> ENDSCAN
>>>>>>>
>>>>>>> *-- We are now either on the first record of the next batch, or
>>>>>>> at
>>>>>>> EOF(). * In the first case, we back off one to allow for the
>>>>>>> SCAN skip.
>>>>>>> * In the latter case, the EOF() will properly toss us out of
>>>>>>> the
>>>>>>> first SCAN.
>>>>>>> IF EOF()
>>>>>>> SKIP -1
>>>>>>> ENDIF
>>>>>>>
>>>>>>> *-- Only add a log record if we picked up any donations
>>>>>>> IF lnDonationsCount > 0
>>>>>>> INSERT INTO LogFile;
>>>>>>> (DonCount, DonTotal);
>>>>>>> VALUES (lnTotal, lnDonationsCount)
>>>>>>> ENDIF
>>>>>>>
>>>>>>> ENDSCAN
>>>>>>>
>>>>>>> *-------------------------------------------
>>>>>>>
>>>>>>> - Rush
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>