GROUPING QUERY 1

[sqlpro]

hI FRIENDS

I HAVE DATA LIKE FOLLOWING

Master Type

1111111111 Avail
1111111111 Meet
1111111111 Oper
1111111111 TD
1210 Avail
1210 Avail
1269 Avail
4532 GC
4532 JA
4532 Avail
4532 Oper


I WANT FINAL OUTPUT LIKE BELOW

Master Type

1111111111 Avail
1111111111 Oper
4532 Avail
4532 Oper


I.E WHERE MASTER HAVING TYPE "Avail" AND "Oper"

I TRIED LIKE FOLLOWING

SELECT master,type FROM BOOKING WHERE INLIST(UPPER(TRIM(TYPE)),'AVAIL','OPER') GROUP BY MASTER,TYPE HAVING COUNT(MASTER)>1 ORDER BY 1

BUT IT RETURNS MASTER "1210" WHICH I DONT WANT.
ANY IDEAS PLZ
tHANKS


cheers

 

[baltman]

I'm sure there's a slicker way, but this works:

create table BOOKING (Master c(20), Type c(20))

insert into BOOKING values("1111111111", "Avail")
insert into BOOKING values("1111111111", "Meet ")
insert into BOOKING values("1111111111", "Oper ")
insert into BOOKING values("1111111111", "TD ")
insert into BOOKING values("1210", "Avail")
insert into BOOKING values("1210", "Avail")
insert into BOOKING values("1269", "Avail")
insert into BOOKING values("4532", "GC ")
insert into BOOKING values("4532", "JA ")
insert into BOOKING values("4532", "Avail")
insert into BOOKING values("4532", "Oper ")


SELECT master,MAX(iif(upper(TYPE) ='AVAIL',"AVAIL"," ")) as AVAIL,;
MAX(iif(upper(TYPE) ='OPER',"OPER"," ")) as OPER ;
FROM BOOKING GROUP BY MASTER ;
having !empty(AVAIL) and !empty(OPER) into table curTemp

sele master,AVAIL as Type from curTemp into table curTemp2
sele master,OPER as Type from curTemp into table curTemp3
append From curTemp2

sele master,Type from curTemp3 order by master,type into table tblResult

brow nowait

 

[baltman]

This is slicker:

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 AvailN=OperN into cursor curTemp nowait

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

 

[sqlpro]

Baltman u r great
It works like a charm

cheers

 

[baltman]

I appreciate your feedback

FYI, I believe that this type of query will be executable in one line in the next version of VFP.

Brian

 

[sqlpro]

Thanks Brian
by the way can u give me little explanation on ur query plz
so that i'll have better idea when handling similar situations.
Thanks anyway

cheers

 

[sqlpro]

Hi Brian
may b we dont need the condition

and AvailN=OperN

bcoz there may b record like followng

Master      Type 
1111111111 Avail
1111111111 Avail
1111111111 Oper

the abv record wont be returned from our query in prev.post
isn't it ?


cheers

 

[sqlpro]

i changed it like following.It works fine

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 nowait

sele master,type from booking where (upper(type)="AVAIL" or upper(type)="OPER") ;
 and master in (sele distinct master from curTemp) and !EMPTY(master) group by master,type ORDER BY 1 nowait

cheers

 

[baltman]

Right, that's just an assumption that you're looking for a 1 to 1 match.

If you want to allow for asymmetry, go for it, you could also allow the AvailN to be no more than 1 higher than the OperN if that suits your purpose.

e.g. having AvailN>0 and between(AvailN-OperN,0,1)

Brian

 

[sqlpro]

Thanx Brian

cheers

 

[sqlpro]

Hi Brian
i've added few condition 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


cheers

 

[baltman]

Obviously only one of the 2 conditions is true. Without a fuller data set and the code for curTemp, it isn't easy to suggest anything.

You may have a date issue, do you mean to be a month apart?

Also *warning* I'm on the East Coast of the US and it's getting near my bed time

 

[sqlpro]

sorry may b u could reply tomorrow
anyway the code for curtemp is

sele master,sum(iif(upper(type)="IC",1,0)) as IC,;
sum(iif(upper(type)="CS",1,0)) as CS ;
where !EMPTY(TRIM(master)) and (upper(type)="IC");
or (upper(type)="CS");
group by 1 from booking ;
having IC>0 and CS>0 into cursor curTemp nowait

actually i'm looking for masters where type is "CS" and "IC"
and if type IC then it should between those dates i mentioned dates mentioned in prev and so is type CS.
Thank u very much.


cheers

 

[baltman]

Actually, a representitive dataset would be very helpful.

If you could post in code as I did earlier e.g insert into BOOKING values("4532", "Oper "), I'm sure it'd allow someone to help you sooner than I.

Brian

 

[sqlpro]

ok here is the data

insert into booking values ('AWrPsfQDm7',CS','18/02/2003 11:00:00 AM')
insert into booking values('AWrPsfQDm7','PLas','01/04/2003 11:00:00 AM')
insert into booking values('AWrPsfQDm7','IC','20/03/2003 12:00:00 PM')
insert into booking values('AWrPsfQDm7','LASIK','20/03/2003 06:00:00 PM')
insert into booking values('AWrPsfQDm7','PO','21/03/2003 08:45:00 AM')
insert into booking values('AWrPsfQDm7','PLas','24/06/2003 01:45:00 PM')
insert into booking values('AWrPsfQDm7','LASIK','26/06/2003 04:45:00 PM')
insert into booking values('AWrPsfQDm7','PO'.'27/06/2003 07:30:00 AM')
insert into booking values('AWrPsfQDm7','CanC'.'11/03/2004 06:45:00 PM')

Thanks


cheers

 

[baltman]

You have 2 issues. 1) the parentheses weren't right for an OR and 2) it is correct that a record cannot be both a CS and IC.

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

 

[sqlpro]

sorry for the late reply Brian.
Thanks for ur post

cheers