Submit My Question - Recent Questions - Recent Answer

Msdn Tech >> Visual FoxPro >> Visual FoxPro General

can i just use sql to get this?

date transfer
20050101 200.00

20050103 -100.00

20050530 95.30

...

SQL to get：

date transferbalance

20050101 200.00200.00

20050103 -100.00100.00

20050530 95.30195.30

[303 byte] By [jetsu] at [2008-2-15]

«« I need help with Printing
»» FrameworkInitialize method in .NET FW beta2.

# 1

VFP9 :

SELECT table.date, table.transfert, ;

(SELECT Sum(prev.transfert) as balance ;

FROM table prev ;

WHERE table.date >= prev.date ) ;

FROM table

Thierryp at 2007-9-8 >

top of Msdn Tech,Visual FoxPro,Visual FoxPro General...

# 2

i am using vfp8.0 now
;-(
it's told me "invalid use of subquery"

jetsu at 2007-9-8 >

# 3

SELECT x1.date, x1.transfer, SUM(x2.transfer) as RUN_BAL ;
FROM testdata x1 ;
JOIN testdata x2 ON x2.date <= x1.date ;
GROUP BY 1,2

PS: Version independant. For Fox2x:

SELECT x1.date, x1.transfer, SUM(x2.transfer) as RUN_BAL ;
FROM testdata x1, testdata x2 where x2.date <= x1.date ;
GROUP BY 1,2

CetinBasoz at 2007-9-8 >

# 4

CetinBasoz
thank you very much

jetsu at 2007-9-8 >

# 5

hi CetinBasoz
but i found a problem that if i have same date then the result will not be right,for example

20050103 -100.00

20050103 10.00

20050530 95.30

20050530 100.30

then i can't get the right answer

SELECT *,DTOC(date)+STR(recn(),10) as rec FROM detail INTO CURSOR temp
SELECT x1.date, x1.transfer, SUM(x2.transfer) as RUN_BAL ;
FROM temp x1 ;
JOIN temp x2 ON x2.rec <= x1.rec ;
GROUP BY 1,2
in vfp9.0 i can use in one sentece.
can i just use one sentence in vfp 8.0?

jetsu at 2007-9-8 >

# 6

Then maybe instead of just date you should use datetime.
Why there is a dtoc() conversion in your code? If field was already date datatype you didn't need any conversion in original SQL (and if you'd use dtoc() then consider using dtoc(,1) ).
PS: Do not use recno() in SQL unless it's a single table.

[code]
select *,DTOC(date,1)+Sys(2015) as uid from detail into cursor temp nofilter
SELECT x1.date, x1.transfer, SUM(x2.transfer) as RUN_BAL, x1.uid ;
FROM temp x1 ;
JOIN temp x2 ON x2.uid <= x1.uid ;
GROUP BY 1,2,4 ;
order by 1,4
[/code]
Using a single SQL might be possible but I don't recommend.

CetinBasoz at 2007-9-8 >

# 7

hi CetinBasoz
i got
i can make it in one sentence in sqlserver and vfp9.0,but how i can make it in vfp8.i am just interesting in it .;-)

jetsu at 2007-9-8 >

# 8

Use datetime.

CetinBasoz at 2007-9-8 >

# 9

hi CetinBasoz
i also can use recno() right?
Now i just want to know whether i can use one sql sentence to get the same result in vfp8.0.(i can make it in vfp9 and sqlserver)

for instance in vfp9
it can be like this
SELECT x1.date, x1.transfer, SUM(x2.transfer) as RUN_BAL, x1.uid ;
FROM (select *,recno() as uid from detail) x1 ;
JOIN (select *,recno() as uid from detail) x2 ON x2.uid <= x1.uid ;
GROUP BY 1,2,4 ;
order by 1,4

jetsu at 2007-9-8 >

# 10

Wrong. See my previous reply. It might work but open to errors (already yours is).

Do you consider this as a single SQL? Syntactically it's but it's not, you now have 3 SQLs. I don't understand why are you forcing yourself to create slower code. Having less lines doesn't make it faster. Not using datetime in the first place is another problem. Use datetime and you wouldn't need complex,slower SQL.
With VFP even it might be much faster if you do it without SQL.

PS: Don't compare VFP's SQL with MSSQL server SQL. With MSSQL you could create much more complex SQL yet much faster.

You're saying MSSQL, how in MSSQL you're keeping such data, no primary key? And in MSSQL there is no date type.

CetinBasoz at 2007-9-8 >

# 11

this question i got from my friends,i don; know why he wanted to use one sql either.;-)
thank you verymuch

jetsu at 2007-9-8 >

Visual FoxPro General

Visual FoxPro Hot Topic

Visual FoxPro

Visual FoxPro General

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech