problem with sql script 1

[castleWolf]

Hi everyone,
Can anyone help me to improve the query
first i wanna to explain that what customer wanted for the first time,and after it he changed his mind and wanted another report which is diffcult to do for me.
First he wanted that to display 1 year payment by month
exactly:
Select customerid,
Min(Iif(Month(date_of_payment)=1,"+","-")) m1,;
MIN(Iif(Month(date_of_payment)=2,"+","-")) m2,;
MIN(Iif(Month(date_of_payment)=3,"+","-")) m3,;
MIN(Iif(Month(date_of_payment)=4,"+","-")) m4,;
MIN(Iif(Month(date_of_payment)=5,"+","-")) m5,;
MIN(Iif(Month(date_of_payment)=6,"+","-")) m6,;
MIN(Iif(Month(date_of_payment)=7,"+","-")) m7,;
MIN(Iif(Month(date_of_payment)=8,"+","-")) m8,;
MIN(Iif(Month(date_of_payment)=9,"+","-")) m9,;
MIN(Iif(Month(date_of_payment)=10,"+","-")) m10,;
MIN(Iif(Month(date_of_payment)=11,"+","-")) m11,;
MIN(Iif(Month(date_of_payment)=12,"+","-")) m12;
from Payment ;
WHERE date_of_payment>=first_date;
AND date_of_payment<=last_date;
AND Not Deleted();
group By customerid Into Cursor who_payed_in_year

but all this cases range between date is 12 month.
now he wants that range must be any range but between range is always 12 month.
exacly :
in first task i wrote
first_date='01.01.2004'
last_date='31.12.2004'
but now he wants that the range must be like that
first_date='01.06.2004'
last_date='31.05.2005'
or
first_date='01.02.2004'
last_date=' 31.01.2005'(in all cases between date is 12 month)
and i see that
Min(Iif(Month(date_of_payment)=1,"+","-")) this construction doesn't solve my problem.
How can i do it?
somebody give me an idea.

 

[StewartUK]

castleWolf,

I can't tell what output you are trying to get.

Does the customer make many payments in each 12 month range and you want 12 totals for each month?

Stewart

 

[castleWolf]

No I want to get: if there has payment in this range of month then display '+',else '-'
exactly
(data of payment table)
select * from payment

customerid date_of_payment charge
1 01.01.2004 15000
1 01.02.2004 15000
1 01.05.2004 15000
1 01.06.2004 20000
1 01.07.2004 15000
if i use above mentioned query
i get result like this.
customerid m1 m2 m3 m4 m5 m6 m7 m8 m9 m10 m11 m12
1 + + - - + + + - - - - -

But it displays current fixed year result(2004).
ANd i want that my date range starts 2004 ends 2005
exactly
date_of_payment between '01.06.2004' and '31.05.2005'
in such range i can't group charge('+') by month.
I think now you undestand what i mean

 

[TheRambler]

Hi,

Use the crosstab generator, more or less like this:

Select customerid, ;
left(dtos(date_of_payment),6) payment_month, ;
count(date_of_payment);
from Payment ;
WHERE date_of_payment>=first_date;
AND date_of_payment<=last_date;
AND Not Deleted();
group By customerid, payment_month ;
order By customerid, payment_month ;
Into Cursor SYS(2015)

DO (_GENXTAB) WITH 'who_payed_in_year',.t.,.t.,.t.,,,,.t.,0,.t.
BROWSE NOMODIFY

You will get the number of payments per month for every customer id.

Alternatively you could use Ramani's cross tab generator if you need to improve the speed.

 

[Olaf Doschke]

Hi castleWolf,

although Min(Iif(Month(date_of_payment)=1,"+","-")) is quite an unusual expression it works, and it will work for any Range of a year that has full months in it, so also from 1st July 2004 to 31st June 2005. Only the order of the fields will not be in the order of time. m1 to m5 will belong to 2005, m6 to m12 belong to 2004, so you only should display it in the order m6,...m12, m1,...m5 and everything's fine.

That doesn't make the select and its result wrong. You only would get a wrong result if you would select from 15th July 2004 to 14th July 2005, as then you would mix up the month July from 2004 and 2005. And the range cannot be higher as 1 Year in total.

And as TheRambler suggests, this is a typical task for a cross table generator.

Bye, Olaf.

 

[castleWolf]

i have analyzed the GENXTAB and throught that the best solution of this query is to open a cursor,get data from first table(who_payed_in_year) and insert hoizonallt to the second cursor.
But i don't think how to write the code of this idea.
Anyhow
Thanx for help

 

[castleWolf]

I made this query but it works very slowly.
Please somebody help me to improve spped of this report

Select customerid, ;
left(Dtos(date_of_payment),6) payment_month, ;
count(date_of_payment) count_cust;
from Payment ;
where
date_of_payment>=first_date;
AND date_of_payment<=last_date;
AND Not Deleted();
group By customerid, payment_month ;
Into Cursor who_payed_in_year
Public date_range
Dimension date_range[1] &&,pay_cust[1]
Select Distinct payment_month From who_payed_in_year Into Array date_range
Select Distinct customerid From who_payed_in_year Into Cursor pay_cust

Create Cursor temp;
(customerid N(20),m1 Chr(1) Default "-" ;
,m2 Chr(1) Default "-" ;
,m3 Chr(1) Default "-" ;
,m4 Chr(1) Default "-" ;
,m5 Chr(1) Default "-" ;
,m6 Chr(1) Default "-" ;
,m7 Chr(1) Default "-" ;
,m8 Chr(1) Default "-" ;
,m9 Chr(1) Default "-" ;
,m10 Chr(1) Default "-" ;
,m11 Chr(1) Default "-" ;
,m12 Chr(1) Default "-")

* structure of temp
* customerid,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12
Select pay_cust
Scan
Insert Into temp (customerid);
values(pay_cust.customerid)
Select * From who_payed_in_year Where customerid=pay_cust.customerid Into Cursor customer_temp
Select customer_temp
Scan
For i=1 To 12
Do Case
Case date_range[1]=customer_temp.payment_month
Update temp Set m1="+" Where customerid=customer_temp.customerid
Case date_range[2]=customer_temp.payment_month
Update temp Set m2="+" Where customerid=customer_temp.customerid
Case date_range[3]=customer_temp.payment_month
Update temp Set m3="+" Where customerid=customer_temp.customerid
Case date_range[4]=customer_temp.payment_month
Update temp Set m4="+" Where customerid=customer_temp.customerid
Case date_range[5]=customer_temp.payment_month
Update temp Set m5="+" Where customerid=customer_temp.customerid
Case date_range[6]=customer_temp.payment_month
Update temp Set m6="+" Where customerid=customer_temp.customerid
Case date_range[7]=customer_temp.payment_month
Update temp Set m7="+" Where customerid=customer_temp.customerid
Case date_range[8]=customer_temp.payment_month
Update temp Set m8="+" Where customerid=customer_temp.customerid
Case date_range[9]=customer_temp.payment_month
Update temp Set m9="+" Where customerid=customer_temp.customerid
Case date_range[10]=customer_temp.payment_month
Update temp Set m10="+" Where customerid=customer_temp.customerid
Case date_range[11]=customer_temp.payment_month
Update temp Set m11="+" Where customerid=customer_temp.customerid
Case date_range[12]=customer_temp.payment_month
Update temp Set m12="+" Where customerid=customer_temp.customerid
Endcase
Next
Endscan
Endscan

 

[TheRambler]

I tested your code and I have the following observations:
[tt]
Select Distinct payment_month ;
From who_payed_in_year ;
Into Array date_range[/tt]

fails when there aren't any payments for certain months, between first_date and last_date. My test data was a small table so I spotted that bug easily. Fill your data_range array like this instead:
[tt]
lnMonth = 1
DO WHILE first_date < last_date
Dimension date_range[lnMonth]
date_range[lnMonth] = LEFT(DTOS(first_date),6)
lnMonth = lnMonth + 1
first_date = GOMONTH(first_date,1)
ENDDO[/tt]

Another observation is that you don't need the For ... Next loop, if you remove it you will get the same results. Try it.

Inmediately after you create the cursor who_payed_last_year, create an index on customerid so that this query
[tt]
Select * ;
From who_payed_in_year ;
Where customerid=pay_cust.customerid ;
Into Cursor customer_temp[/tt]

won't take too long for every customer.

If it is still slow, I am sure we can improve the Do Case ... EndCase structure, but I just don't have time now, maybe somebody else does.

 

[TheRambler]

I'm back, this is what I did:

Use a two dimensional array to store the payment month and the field associated to it.
[tt]
lnMonth = 1
DO WHILE first_date < last_date
Dimension date_range[lnMonth,2]
date_range[lnMonth,1] = LEFT(DTOS(first_date),6)
date_range[lnMonth,2] = "m"+TRANSFORM(lnMonth)
lnMonth = lnMonth + 1
first_date = GOMONTH(first_date,1)
ENDDO
[/tt]

That way you can replace the Do Case ... Endcase structure with the following:
[tt]
Select customer_temp
SCAN
lcField = date_range[ASUBSCRIPT(date_range, ;
ASCAN(date_range,payment_month),1),2]
Update temp Set &lcField="+" ;
Where customerid=customer_temp.customerid
Endscan
[/tt]
Hope it helps.

 

[TheRambler]

Another option is to use a table or cursor instead of the array, and move the SQL Update out of the SCAN ... ENDSCAN loop.
[tt]
SELECT customerid, ;
LEFT(DTOS(date_of_payment),6) payment_month, ;
COUNT(date_of_payment) count_cust;
FROM Payment ;
WHERE date_of_payment>=first_date;
AND date_of_payment<=last_date;
AND Not DELETED();
GROUP By customerid, payment_month ;
INTO CURSOR who_payed_in_year
INDEX ON customerid TAG customerid

CREATE CURSOR date_range ;
(payment_month c(6), payment_field c(3))
lnMonth = 1
DO WHILE first_date < last_date
INSERT INTO date_range ;
VALUES (LEFT(DTOS(first_date),6), "m"+TRANSFORM(lnMonth))
lnMonth = lnMonth + 1
first_date = GOMONTH(first_date,1)
ENDDO
SELECT date_range
INDEX ON payment_month TAG pay_month
SELECT DISTINCT customerid ;
FROM who_payed_in_year ;
INTO CURSOR pay_cust
CREATE CURSOR temp;
(customerid N(20),m1 Chr(1) Default "-" ;
,m2 Chr(1) Default "-" ;
,m3 Chr(1) Default "-" ;
,m4 Chr(1) Default "-" ;
,m5 Chr(1) Default "-" ;
,m6 Chr(1) Default "-" ;
,m7 Chr(1) Default "-" ;
,m8 Chr(1) Default "-" ;
,m9 Chr(1) Default "-" ;
,m10 Chr(1) Default "-" ;
,m11 Chr(1) Default "-" ;
,m12 Chr(1) Default "-")

SELECT pay_cust
SCAN
INSERT INTO temp (customerid);
VALUES (pay_cust.customerid)
SELECT * ;
FROM who_payed_in_year ;
WHERE customerid=pay_cust.customerid ;
INTO CURSOR customer_temp
SELECT customer_temp
SET RELATION TO payment_month INTO date_range
lcSet = ''
SCAN
lcField = date_range.payment_field
lcSet = lcSet + "&lcField='+',"
ENDSCAN
lcSet = LEFT(lcSet, LEN(lcSet)-1)
UPDATE temp SET &lcSet ;
WHERE customerid=pay_cust.customerid
ENDSCAN
[/tt]
This last version should be the fastest, I think.

 

[castleWolf]

Thanx TheRambler
The last query works much faster than before.

 

[TheRambler]

You are welcome anytime castleWolf, I am glad I could help.

 

[Olaf Doschke]

Hi castleWolf,

and how about USING genxtab, instead of imitating it's code?

Create cursor customer_payments (custid  I,paydate D,  charge I)

Insert into customer_payments values(1,Date(2004,1,1),15000)
Insert into customer_payments values(1,Date(2004,2,1),15000)
Insert into customer_payments values(1,Date(2003,5,1),15000)
Insert into customer_payments values(1,Date(2004,6,1),20000)
Insert into customer_payments values(1,Date(2004,7,1),15000)
Insert into customer_payments values(1,Date(2004,7,15),15000)
Insert into customer_payments values(2,Date(2004,4,1),15000)
Insert into customer_payments values(2,Date(2006,4,1),15000)

Local ldStartdate
ldStartdate = Date(2003,6,1)

SELECT CUSTOMER_PAYMENTS.Custid, Left(dtos(CUSTOMER_PAYMENTS.Paydate),6) as paymonth, (1) as Flag;
    FROM CUSTOMER_PAYMENTS;
    GROUP BY 1,2;
    ORDER BY 1,2;
    where paydate between ldStartdate and GoMonth(ldStartdate,12)-1;
    INTO CURSOR curTemp readwrite

Select distinct custid from curtemp into cursor curCustomers

* Insert records (flag=0) to get something for every month:
Local ldDate, lnCnt
ldDate = ldStartdate
For lnCnt=1 to 12
   Select curCustomers
   Scan
      Insert into curtemp values (curCustomers.custid,Left(dtos(ldDate),6),0)
   EndScan 
   ldDate = GoMonth(ldDate,1)
EndFor 
    
Select curTemp
DO (_GENXTAB) WITH 'curCross',,,,,,,,,
BROWSE NOMODIFY

Instead of showing "+" and "-" this will create a cursor curCross having 1="+" and 0="-" in the months columns.

Bye, Olaf.

 

[castleWolf]

OlafDoschke ,
i have analyzed your suggestion(part of module).
I looked size of _GENXTAB(vfpxtab.prg) it 42 kb.
But above mentioned code(TheRambler suqqested) works much faster and little by size.That is why i prefer it.
And another problem is that:if someting will happend in data or report(or script).it will be very complicated to analize the code.particularly i didn't wrote the_GENXTAB (i think).
But thanx for your suqqestion.
I appreciate your help