GROUPING QUERY

Question

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Glad to hear, I was just banging my head on FoxPro there.

sqlpro Hall of Fame Points: 3859 More actions · Answer 2

Hall of Fame

Points: 3859

March 25, 2004 at 6:54 pm

Thank u so much for ur posts

sqlpro Hall of Fame Points: 3859 More actions · Answer 3

small correction.

sele master,sum(iif(upper(type)="AVAIL",1,0)) as AvailN,;

sum(iif(upper(type)="OPER",1,0)) as OperN ;

group by 1 from booking ;

where upper(type)="AVAIL" or upper(type)="OPER" ;

having AvailN>0 and OperN>0 into cursor curTemp

sele master,type from booking where (upper(type)="AVAIL" or upper(type)="OPER") ;

and master in (sele distinct master from curTemp) and group by master,type ORDER BY 1

sqlpro Hall of Fame Points: 3859 More actions · Answer 4

Hi guys

i've added few conditions to abv query and have small doubt

i have some data like

Master Type Starttime

AWrPsfQDm7 CS 18/02/2003 11:00:00 AM

AWrPsfQDm7 IC 20/03/2003 12:00:00 PM

when i run following query it gets me abv data

sele master,type,starttime from booking ;

where (upper(type)="IC" and BETWEEN(TTOD(starttime),CTOD('17/03/2003'),CTOD('21/03/2003')));

or ;

(upper(type)="CS" and BETWEEN(TTOD(starttime),CTOD('16/02/2003'),CTOD('21/02/2003')) ) ;

and master in (sele master from curTemp) ;

group by master,type,starttime ORDER BY 1 nowait

but when i remove OR with AND like following(i'm refering OR in independent line)

sele master,type,starttime from booking ;

where (upper(type)="IC" and BETWEEN(TTOD(starttime),CTOD('17/03/2003'),CTOD('21/03/2003')));

and ;

(upper(type)="CS" and BETWEEN(TTOD(starttime),CTOD('16/02/2003'),CTOD('21/02/2003')) ) ;

and master in (sele master from curTemp) ;

group by master,type,starttime ORDER BY 1 nowait

it won't reurn any data at all.

am i missing anything here