anyone can help me simplify my code? 2

[R17]

icount=1

sele 1
use lcard
dele all
pack

sele 2
use employee
go top
do while not eof()
	cempno=empno
	do while icount<=31
		sele lcard
		append blank
		replace empno with cempno, dday with icount
		icount=icount+1
	enddo
	icount=1
	sele employee
	skip
enddo
		
sele lcard

select empno, swipedate, upcode, t_min, u_min ;
	from dtr ;
	order by empno, swipedate ;
	into cursor crstemp1

sele crstemp1
go top
do while not eof()
	ckey=empno+alltrim(str(val(substr(dtoc(swipedate),4,2))))
	d=swipedate
	a=upcode
	t=t_min
	u=u_min
	sele lcard
	locate for empno+alltrim(str(dday))=ckey
	if found()
		do case
			case substr(dtoc(d),1,2)=&quot;07&quot;
				replace f1 with a, f2 with t, f3 with u
			case substr(dtoc(d),1,2)=&quot;08&quot;
				replace f4 with a, f5 with t, f6 with u
			case substr(dtoc(d),1,2)=&quot;09&quot;
				replace f7 with a, f8 with t, f9 with u
			case substr(dtoc(d),1,2)=&quot;10&quot;
				replace f10 with a, f11 with t, f12 with u
			case substr(dtoc(d),1,2)=&quot;11&quot;
				replace f13 with a, f14 with t, f15 with u
			case substr(dtoc(d),1,2)=&quot;12&quot;
				replace f16 with a, f17 with t, f18 with u
		endcase
	endif
	sele crstemp1
	skip
enddo

========================================================


in my previous thread i posted this,

i have this sample data,

empno date        A     T     U   
00001 11/01/2003  S      
00001 11/02/2003        10       
00001 11/03/2003  F
00001 11/04/2003
00001 11/05/2003
.
.
.
00001 11/30/2003              34
00001 12/01/2003              45
00001 12/02/2003
00001 12/03/2003  S
00001 12/04/2003  X
00001 12/05/2003
.
.
.
00001 12/31/2003

00002 11/01/2003        
00002 11/02/2003        10       
00002 11/03/2003  
00002 11/04/2003        40
00002 11/05/2003
.
.
.
00002 11/30/2003              
00002 12/01/2003  S            
00002 12/02/2003
00002 12/03/2003  
00002 12/04/2003  
00002 12/05/2003
.
.
.
00002 12/31/2003

how can i select all these records so that i can have this,

            nov       dec    
empno day A  T  U   A  T  U
00001  1  S              45  
00001  2    10
00001  3  F
00001  4
00001  5
.
.
.

etc..

 

[ChrisRChamberlain]

R17

At a quick first glance and not meant to be a comprehensive answer, replace

DELE ALL
PACK

with

ZAP

Replace

DO WHILE !EOF()
ENDD

with

SCAN
ENDS

FAQ184-2483 - the answer to getting answered.​

Chris

http://www.pdfcommander.com

http://www.pdfcommander.co.uk

 

[clarkrj]

Hi
replace the "append blank & replace" command with "insert"

 

[StewartUK]

R17,

In addition to Chris & clarkrj's suggestions:

I think the line
ckey = empno + substr(dtoc(swipedate),4,2)
gives the same result as your line:
ckey=empno+alltrim(str(val(substr(dtoc(swipedate),4,2))))

Use
for icount = 1 to 31
.
.
endfor
rather than
do while icount<=31
.
.
enddo
because a for...endfor loop increments icount automatically.

As clarkrj says, inside that loop you can use the one line
insert into lcard (empno, dday) values (cempno, icount)
rather than 3 lines you have.

When you run an SQL-select as you have, it leaves you in the resulting cursor, so you don't need to have the sele crstemp1 after it.

Hope that helps as well.

Stewart

 

[Stella740pl]

R17,

I don't know if others will agree with me, but I personally would replace DO CASE section with the following:

n=VAL(SUBSTR(DTOC(d),1,2)
IF BETWEEN(n,1,6)
n1=ALLTRIM(STR(n*3-2))
n2=ALLTRIM(STR(n*3-1))
n3=ALLTRIM(STR(n*3))
REPLACE f&n1. WITH a, f&n2. WITH t, f&n3. WITH u
ENDIF

and also would probably use DTOS instead of DTOC.

Stella

 

[foxwizard]

R17,

I think the ouput you wish to have is very difficult to provide with a select statement alone, perhaps you can consider and try these codes.

Assuming you have your sample data store in the table mydata.dbf with the following attributes:

Attributes of mydata.dbf:

empno C(10)
date D(8)
a C(1)
t N(10)
u N(10)

then create a blank table and name it myoutput.dbf with the following attributes:

empno C(10)
empday N(2)
a_nov C(1)
t_nov N(10)
u_nov N(10)
a_dec C(1)
t_dec N(10)
u_dec N(10)


you can create additional attributes from january to september if you wish but for now let's assume it's only november and december.

local lcempno,lc_a,lc_t,lc_u,lcday

select 1
use mydata
select 2
use myoutput

select distinct empno from mydata into cursor tmpcursor

select tmpcursor
go top
do whil !eof()
m.empno = alltr(empno)
select myoutput
for i = 1 to 31
m.empday = i
insert into (alias()) from memvar
endfor
select tmpcursor
skip
enddo

select mydata
go top
do whil !eof()
lcempno = alltr(empno)
lcday = day(date)
lcmonth = month(date)
lc_a = a
lc_t = t
lc_u = u

select myoutput
locate for empno = lcempno and empday = lcday
if !eof()
do case && you can insert here from jan to sep.
case lcmonth = 11
replace a_nov with lc_a, t_nov with lc_t, ;
u_nov with lc_u
case lcmonth = 12
replace a_dec with lc_a, t_dec with lc_t, ;
u_dec with lc_u
endcase
endif

select mydata
skip
enddo
close all
return


Hope this may help you.


Bren
VFP - Philippines

 

[R17]

hi bren, thanks your reply. where from in pi?

 

[foxwizard]

R17,

Somewhere here in manila but working in mandaluyong.


Bren
VFP - Philippines