another select - sql question

[R17]

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..

any help pls?

 

[Shardlow]

Hi R17,

Do you only need the results for Nov and Dec or are there an unlimited number of months to collect data for?

 

[R17]

Shardlow,

Hi, actually the month range will be based on the input of the user. He can go and select all records from jan - dec, or just select a single month.

can i do this using simple select?

 

[baltman]

Try This.

Brian

&&prep data
CREATE TABLE MyData (empno c(6),date c(10), A c(6), T n(5), U n(5))
APPEND FROM testsql.txt TYPE SDF &&copied and pasted from post

REPLACE ALL a WITH ALLTRIM(a)
ALTER TABLE MyData alter COLUMN date d

DELETE FOR VAL(empno)=0
pack
&&end data prep

CALCULATE MIN(date) TO mindate &&to be user defined
CALCULATE MAX(date) TO maxdate &&to be user defined

lnMonthsToCreate=(12*YEAR(maxdate)+MONTH(maxdate))-(12*YEAR(mindate)+MONTH(mindate))

lcSQLCMD=""
FOR lnDateCounter= 0 TO MIN(80,lnMonthsToCreate) &&80 is to catch a request for too many columns
ldVarDate=GOMONTH(CTOD(TRANSFORM(MONTH(mindate))+"/01/"+TRANSFORM(YEAR(mindate))),lnDateCounter)

*Build MAX(A) Query
lcSQLCMD=lcSQLCMD+",MAX(IIF(MONTH(date)="+TRANSFORM(MONTH(ldVarDate))+" AND "+;
"YEAR(date)="+TRANSFORM(YEAR(ldVarDate))+",A,' ')) "+;
"as A_"+left(cmonth(ldVarDate),3)+transform(year(ldVarDate))

*Build SUM(T) Query
lcSQLCMD=lcSQLCMD+",SUM(IIF(MONTH(date)="+TRANSFORM(MONTH(ldVarDate))+" AND "+;
"YEAR(date)="+TRANSFORM(YEAR(ldVarDate))+",T,00000)) "+;
"as T_"+left(cmonth(ldVarDate),3)+transform(year(ldVarDate))

*Build SUM(U) Query
lcSQLCMD=lcSQLCMD+",SUM(IIF(MONTH(date)="+TRANSFORM(MONTH(ldVarDate))+" AND "+;
"YEAR(date)="+TRANSFORM(YEAR(ldVarDate))+",U,00000)) "+;
"as U_"+left(cmonth(ldVarDate),3)+transform(year(ldVarDate))

ENDFOR
lcSQLCMD=RIGHT(lcSQLCMD,LEN(lcSQLCMD)-1)

SELECT dist empno+DTOC(date),empno,date,000 as days,&lcSQLCMD ;
GROUP BY 1 FROM MyData INTO TABLE results

lnEmpno="XXX"
SCAN
IF empno#lnEmpno
lndays=1
lnEmpno=empno
ENDIF
REPLACE days WITH lndays
lndays=lndays+1
ENDSCAN

GO TOP
BROWSE NOWAIT

 

[baltman]

Actually, use this as the SQL command so that the dates sort correctly when multiple years are selected:

SELECT dist empno,date,000 as days,&lcSQLCMD ;
GROUP BY empno,date FROM MyData INTO TABLE results

Birian

 

[R17]

thanks baltman!

but i'm having an error at line "lnMonthsToCreate=(12*YEAR(maxdate)+MONTH(maxdate))-(12*YEAR(mindate)+MONTH(mindate))"

 

[baltman]

Are you running on my test data, or on your data? Is the date field a date? What's the exact error?