Merging tables

[youwannawhat]

I've got two tables storing information with thousands of customers and tens of thousands of orders. Table A stores information about 'A' type orders and table B stores information about 'B' type orders. I've been asked for a report merging the two tables into a report in the following format.

CUSTID ORDER A DATES ORDER B DATE
______ _____________ ____________

XXXXXXX 01/01/02 01/05/03
03/04/02


YYYYYYY 05/05/01
07/07/01



ZZZZZZZ 04/04/02



As you can see, some subjects have no 'A' orders, or no 'B' orders, and some have both 'A' and 'B' orders. My report is showing repeated dates. I've tried grouping fields in the report, but it's still not working.

Any help would be greatly appreciated.


Thanks,

Paul

 

[csr]

Here is a grind-it-out, inching along, method.
Not sophisticated, but it will work.
The LOCATE method should probably be replaced with some kind of looping action since the data is in CUST order but this could get you going.

I am sure there are better ways but 1 way is better than no-way, so here is 1-way.



select A-Orders (Sorted by CUST+DATE)
Select B-Orders (Sorted by CUST+DATE)
Create reportfile (CUST,A-DATE,B-DATE)

select A-Orders
go top
do while not eof()
cCust = CUST
do while CUST = cCust
select reportfile
append blank
replace CUST with cCust
replace A-DATE with A-Orders.date
select A-Orders
skip
enddo
enddo

select B-Orders
go top
do while not eof()
cCust = CUST
do while CUST = cCust
select reportfile
locate for CUST = cCust and empty(B-DATE)
if not found()
append blank
replace CUST with cCust
endif
replace B-DATE with B-Orders.date
select B-Orders
skip
enddo
enddo


select reportfile
go top
(run your report)

Don

 

[youwannawhat]

I thought it might be this tedious.

Thanks

 

[baltman]

Or you could do something like this. Half the code makes what you indicate is your sample data, the other half makes the report.

Brian

&&Make Sample Data
CREATE TABLE ordersa (custid c(10), orderdate d)
APPEND BLANK
REPLACE custid WITH "XXXXXXX"
REPLACE orderdate WITH {01/01/2002}
APPEND BLANK
REPLACE custid WITH "XXXXXXX"
REPLACE orderdate WITH {03/04/2002}
APPEND BLANK
REPLACE custid WITH "YYYYYYY"
REPLACE orderdate WITH {05/05/2001}
APPEND BLANK
REPLACE custid WITH "YYYYYYY"
REPLACE orderdate WITH {07/07/2001}

CREATE TABLE ordersb (custid c(10), orderdate d)
APPEND BLANK
REPLACE custid WITH "XXXXXXX"
REPLACE orderdate WITH {01/05/2006}
APPEND BLANK
REPLACE custid WITH "ZZZZZZZ"
REPLACE orderdate WITH { 04/04/2002}


&&Make Report
SELECT dist custid+DTOS(orderdate),;
MAX(custid) as custid,MAX(orderdate) as orderdate,;
sum(1) as Ncnt,"A" as ordertype,0000000 as indexnum ;
GROUP BY 1 FROM ordersA INTO TABLE temp1

DO pop_index

SELECT dist custid+DTOS(orderdate),;
MAX(custid) as custid,MAX(orderdate) as orderdate,;
sum(1) as Ncnt,"B" as ordertype,0000000 as indexnum ;
GROUP BY 1 FROM ordersB INTO TABLE temp2

DO pop_index

SELECT temp1
APPEND FROM temp2

SELECT custid+ALLTRIM(str(indexnum)) as gby,;
MAX(custid) as custid,;
sum(IIF(ordertype="A",Ncnt,0)) as CntOrderA,;
MAX(IIF(ordertype="A",orderdate,{})) as orderdtA,;
sum(IIF(ordertype="B",Ncnt,0)) as CntOrderB,;
MAX(IIF(ordertype="B",orderdate,{})) as orderdtB ;
GROUP BY 1 FROM temp1 ;
INTO TABLE order_report

ALTER TABLE order_report drop column gby
BROWSE nowait
DROP TABLE temp1
DROP TABLE temp2
DROP TABLE ordersa
DROP TABLE ordersb
RETURN

PROCEDURE pop_index
lcCustid="***"

SCAN
IF custid#lcCustid
lcCustid=custid
lnIndex=1
REPLACE indexnum WITH lnIndex
ELSE
lnIndex=lnIndex+1
REPLACE indexnum WITH lnIndex
ENDIF
ENDSCAN
ENDPROC

 

[ramani]

Hi Paul,

1. Step 1 : First collect all data...
SELECT custId, a.OrderDate AS OrderDateA, {} AS OrderDateB ;
FROM TableOrderA a ;
UNION ALL SELECT ;
custId, {} OrderDateA, b.Orderdate AS OrderDateB ;
FROM TableOrderB b ;
INTO CURSOR myCursor1

2. Step 2: Organize your data as you want..
SELECT custId, MAX(OrderDateA) AS OrderDateA, ;
MAX(OrderDateB) AS OrderDateB ;
FROM DBF("myCursor1&quot INTO CURSOR myCursor ;
GROUP BY custId

Note that only one last order date is picked up from each of the table. If there are multiple orders from the same customer, then the last of such order date will be reported in above.




____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/