Multiple queries 3

[audiopro]

I am updating an existing app. by replacing hard code with SQL queries. Most of the change over has been completed but I have hit a problem with a weekly sales summary.
I have a form which displays a breakdown of the weekly sales figures into sections.
The columns are
Date, Products (type 1), Products(type 2), Products(type 3), Paid In, Paid Out, Refunds.
I assume the simplest way to achieve this would be to use a grid, each column being populated from the results of its own query ie.

*!* Find start of week
sunday=date()-(dow(date())-1)

*!* Get items with alpha start followed by a digit
SELECT upper(left(cdow(date),3)),DATE,SUM(NVL(each*QTY, 000000000)) AS SALES from z:\d_day;
WHERE ISALPHA(LEFT(CODE,1)) AND ! ISALPHA(SUBSTR(CODE,2,1)) and date>=sunday and date<=sunday+7;
GROUP BY DATE;
into cursor sales

This is just a test query to get some useful data.

The problem I have, is that there are days when no transactions take place, Sundays and Bank Holidays, therefore no record is created for that day.
I could fill in the missing days by hard coding but wondered if it was possible to force the query to create a blank record on the missing days, giving me 7 records per query.


Keith

 

[ramani]

*!* Find start of week
sunday=date()-(dow(date())-1)

*!* Get items with alpha start followed by a digit
SELECT upper(left(cdow(date),3)) AS cCdow ,DATE,SUM(NVL(each*QTY,NTOM(0))) AS SALES from z:\d_day;
WHERE ISALPHA(LEFT(CODE,1)) AND ! ISALPHA(SUBSTR(CODE,2,1)) and date>=sunday and date<=sunday+7;
GROUP BY DATE;
into cursor sales READWRITE

SCATTER MEMVAR BLANK
FOR i = 1 to 7
m.Date = sunday + i - 1
INSERT INTO sales FROM MEMVAR
ENDFOR

SELECT cCDOW, DATE, SALES from Sales INTO CURSOR Sales ;
GROUP BY DATE ORDER BY Date


____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[audiopro]

Thanks ramani

The script throws an error on the cursor's READWRITE clause, is this an addition to VFP later than my VFP6?
My docs don't mention it.

Keith

 

[ramani]

Hi Keith

I answered with whatever approach you already put in there.

However, my suggestion will be to decide the form report...
as..
Format1:
Product: SUN MON TUE WED .......
1
2
3

OR

Format2:
DOW Product1 Prd2 Prd3 .....
SUN
MON
TUE
..

The format-1 has the advantage of using the same without modification for n number of products (say even 50 or 100 items) since the columns get limited to 1+7 only.

The format-2 has the disadvantage that you may want to reconstruct after some limits.


Now in either case..

SELECT rowField, ColField, SUM(sales) .... group by field1, field2... INTO CURSOR myResults

and then running the Cross Tab function will provide the required results nicely.

For an extended usage of an xtab, you can get the code from

http://winnersoft.coolfreepages.com/vfp/gsxtab1.htm.

=gsXtab([Sales]) should give you a cursor named Sales in the columed fashion.

However, if u r happy with what u have already done, then that is ok.

Cheers

____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[ramani]

i think your question and my second reply crossed..

In VFP6, you cannot use READWRITE

instead you have to create a DBF instead of cursor

so change the CURSOR to DBF and leave out readwrite.

However, you have to do a cleanup of the DBF created after the reports are over.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[audiopro]

Thanks again for that
I started reading your reply to the second question and thought I was cracking up (lol).
Creating the DBF and zapping it after use is no problem.
The layout will be constant 7 item columns and 7 day rows.
I will have a try with that and see what happens.


Keith

 

[baltman]

Welcome back ramani

 

[audiopro]

The cross tab solution appears to involve more code than my original hard coded solution which is only 60 lines long.
I am trying to get my head round complex queries, so please be patient, but am falling down on some of the simple stuff. With regards to the solutions posted above,I am obviously doing something wrong here but have no idea what.

SELECT upper(left(cdow(date),3)) AS cCdow ,DATE,SUM(NVL(each*QTY,NTOM(0))) AS SALES;
from z:\d_day;
WHERE ISALPHA(LEFT(CODE,1)) AND ! ISALPHA(SUBSTR(CODE,2,1)) and date>=sunday and date<=sunday+7;
GROUP BY DATE;
into TABLE z:\SALES
brow

Gives me the info I require

SCATTER MEMVAR BLANK
FOR i = 1 to 7
   m.Date = sunday + i - 1
   INSERT INTO sales FROM MEMVAR
ENDFOR
brow

gives me the info I require in addition to 7 extra blank records, 1 for each day of the week.

SELECT cCDOW, DATE, SALES from Sales INTO CURSOR Sales ;
GROUP BY DATE ORDER BY Date 
BROW

Gives me 7 blank records.

I am also questioning, if this is the correct approach to this problem.
I am querying a single sales table and as I see it, I need to perform 5 seperate queries on the same table.
The query above and 4 more on the lines of

SELECT each AS REFUNDS;
from z:\d_day;
WHERE Z:\D_DAY.CODE="REFUND" and date>=sunday and date<=sunday+7;
GROUP BY DATE;

I tried to combine the queries but ended up with pure junk as results, clearly the wrong way to do it.
Should it be possible to combine joins on the same table using a different alias for each join ie.

SELECT DAT.DATE AS DATE,SUM(NVL(CDA.QTY*CDA.EACH, NTOM(0))) AS CHOSALE,;
SUM(NVL(CDB.EACH, NTOM(0))) AS PAYOUT,;
SUM(NVL(CDC.EACH, NTOM(0))) AS PAYIN;
FROM DATES DAT;
LEFT JOIN Z:\C_DAY CDA ON DAT.DATE = CDA.DATE AND ISALPHA(LEFT(CDA.CODE,1)) AND ! ISALPHA(SUBSTR(CDA.CODE,2,1));
LEFT JOIN Z:\C_DAY CDB ON DAT.DATE = CDB.DATE AND ALLTR(CDB.CODE)="PAID_OUT";
LEFT JOIN Z:\C_DAY CDC ON DAT.DATE = CDC.DATE AND ALLTR(CDC.CODE)="PAID_IN";
GROUP BY DAT.DATE;
ORDER BY DAT.DATE

or am I way off the mark?
If I am way off, please don't waste your valuable time, just say so and I'll get back to the drawing board.

Keith

 

[audiopro]

The following data shows the fields I am interested in, their are others such as time, operator, tendered etc. but they are not reported on in this instance.

date code qty each
12/5/06 A123 2 3.60
12/5/06 A342 3 2.65
12/5/06 S121 5 4.78
12/5/06 PAID_OUT 12.50
13/5/06 D121 1 134.67
13/5/06 J123 3 0.60
13/5/06 REFUND 1 2.56
13/5/06 K121 2 8.99
13/5/06 PAID_IN 1 25.00
13/5/06 J121 4 23.50
14/5/06 K111 2 23.45
14/5/06 L121 3 1.34
14/5/06 PAID_OUT 1 40.00
14/5/06 J112 2 2.30
16/5/06 G112 1 1.45
16/5/06 H116 2 12.45
16/5/06 G112 2 13.67
16/5/06 H113 3 12.78
16/5/06 PAID_OUT 1 20.00

At the moment the following report is hard coded but I am looking at using a query to create it, if it is going to be a messy process, I'll just leave it alone, as it is working ok.
The following values, dates and days may not tally but it is for demo only.

Day    Date    Sales    Paid Out    Paid In    Refunds
SUN   12/5/06   0.00     40.00        0.00       0.00
MON   12/5/06   2.89      0.00        0.00       0.00
TUE   12/5/06   0.00      0.00        0.00       0.00
WED   12/5/06   3.78      0.00       20.00       0.00
THU   12/5/06 114.67     40.00        0.00       3.47
FRI   12/5/06  34.67     34.89        0.00       0.00
SAT   12/5/06 112.65     45.89        0.00       0.00

All info is from a single table.


Keith

 

[audiopro]

All the dates should of course be different.

Keith

 

[baltman]

How does this work?

Brian

&&save settings
cDate=SET("Date")
cStrictDate=SET("Strictdate")

&& change settings for testing
SET STRICTDATE TO 0
SET DATE DMY

&&test data
CREATE TABLE test (date d, code c(10), qty n(5), each n(10,2))
	INSERT INTO test VALUES({12/5/2006}, "A123", 2, 3.60)
	INSERT INTO test VALUES({12/5/2006}, "A342", 3, 2.65)
	INSERT INTO test VALUES({12/5/2006}, "S121", 5, 4.78)
	INSERT INTO test VALUES({12/5/2006}, "PAID_OUT", 1, 12.50)
	INSERT INTO test VALUES({13/5/2006}, "D121", 1, 134.67)
	INSERT INTO test VALUES({13/5/2006}, "J123", 3, 0.60)
	INSERT INTO test VALUES({13/5/2006}, "REFUND", 1, 2.56)
	INSERT INTO test VALUES({13/5/2006}, "K121", 2, 8.99)
	INSERT INTO test VALUES({13/5/2006}, "PAID_IN", 1, 25.00)
	INSERT INTO test VALUES({13/5/2006}, "J121", 4, 23.50)
	INSERT INTO test VALUES({14/5/2006}, "K111", 2, 23.45)
	INSERT INTO test VALUES({14/5/2006}, "L121", 3, 1.34)
	INSERT INTO test VALUES({14/5/2006}, "PAID_OUT", 1, 40.00)
	INSERT INTO test VALUES({14/5/2006}, "J112", 2, 2.30)
	INSERT INTO test VALUES({16/5/2006}, "G112", 1, 1.45)
	INSERT INTO test VALUES({16/5/2006}, "H116", 2, 12.45)
	INSERT INTO test VALUES({16/5/2006}, "G112", 2, 13.67)
	INSERT INTO test VALUES({16/5/2006}, "H113", 3, 12.78)
	INSERT INTO test VALUES({16/5/2006}, "PAID_OUT", 1, 20.00)

&& temp table for join
CREATE TABLE tblDOW (day c(3), date d)
*dSunday=date()-(dow(date())-1)
dSunday={7/05/2006} &&used for demo purposes
*FOR i = 0 TO 7
FOR i = 0 TO 14 &&used for demo purposes
	INSERT INTO tblDOW VALUES (LEFT(CDOW(m.dSunday+i),3),m.dSunday+i)
ENDFOR

&& main query
SELECT tblDOW.day, tblDOW.date,;
	SUM(qty*IIF(INLIST(code,"PAID_OUT","PAID_IN","REFUND"),00000000.00,each)) as sales,;
	SUM(qty*IIF(INLIST(code,"PAID_OUT"),each,00000000.00)) as PAID_OUT,;
	SUM(qty*IIF(INLIST(code,"PAID_IN"),each,00000000.00)) as PAID_IN,;
	SUM(qty*IIF(INLIST(code,"REFUND"),each,00000000.00)) as REFUND ;
	FROM tblDOW LEFT OUTER JOIN Test ON tblDOW.date=Test.Date ;
	GROUP BY tblDOW.date, tblDOW.day ;
	INTO CURSOR curResult

BROWSE NOWAIT 

&& raw query for demo pururposes
SELECT LEFT(CDOW(date),3) as day, date,;
	SUM(qty*IIF(INLIST(code,"PAID_OUT","PAID_IN","REFUND"),00000000.00,each)) as sales,;
	SUM(qty*IIF(INLIST(code,"PAID_OUT"),each,00000000.00)) as PAID_OUT,;
	SUM(qty*IIF(INLIST(code,"PAID_IN"),each,00000000.00)) as PAID_IN,;
	SUM(qty*IIF(INLIST(code,"REFUND"),each,00000000.00)) as REFUND ;
	FROM Test GROUP BY date, day ;
	INTO CURSOR curRawResult

BROWSE NOWAIT 

&& clean-up and restore
DROP TABLE tblDOW
SET DATE &cDate
SET STRICTDATE TO &cStrictDate

 

[TamarGranor]

This definitely looks like a cross-tab. Use a query to do the multiplication, then call GenXTab to generate the cross-tab.

SELECT Date, ;
       IIF(INLIST(Code, "PAID_OUT", "PAID_IN", "REFUNDS", ;
           Code, "SALES"), ;
       Qty*Each AS Total ;
  FROM <Your Table Name > ;
  INTO CURSOR Temp

DO (_GENXTAB) WITH "Results", .T.

This should give you exactly what you want, except you'll need to pull the day of the week out and turn the new column names into good text.

Tamar

 

[audiopro]

First, thanks to you all for the help, much appreciated.
I am starting to uderstand this, I think.
I was about to ask, why the PI,PO and refund clauses are included in the sales figure and how I can get the refund total to be removed from the sales total when I realised just what was going on in the INLIST code.
The only thing I have a problem with now is getting values into the DAY and DATE fields of records with no totals.
They are being assigned as null values.

CREATE TABLE tblDOW (day c(3), date d, PAID_OUT n(8,2), PAID_IN n(8,2), REFUND n(8,2))

dSunday=date()-(dow(date())-1)-28

FOR i = 0 TO 6
    INSERT INTO tblDOW VALUES (LEFT(CDOW(m.dSunday+i),3),m.dSunday+i,0,0,0)
ENDFOR

use z:\d_day in 0 alias dda
SELECT LEFT(CDOW(dda.date),3) as day, dda.date,;
    NVL(0,SUM(qty*IIF(INLIST(code,"PAID_OUT","PAID_IN"),00000000.00,dda.each))) AS Sales,;
    NVL(0,SUM(qty*IIF(INLIST(code,"PAID_OUT"),each,00000000.00))) as PAID_OUT,;
    NVL(0,SUM(qty*IIF(INLIST(code,"PAID_IN"),each,00000000.00))) as PAID_IN,;
    NVL(0,SUM(qty*IIF(INLIST(code,"REFUND"),each,00000000.00))) as REFUND ;
    FROM tblDOW LEFT OUTER JOIN dda ON tblDOW.date=dda.Date and dda.DATE>=dSunday and dda.DATE<=dSunday+7;
    GROUP BY dda.date, day ;
    INTO CURSOR weeksales

BROWSE TITLE
DROP TABLE tblDOW

Keith

 

[audiopro]

Can I set them using the NVL method?
I have tried the following and other versions but no success.

SELECT NVL("day",LEFT(CDOW(dda.date),3)) as day, NVL(date(),dda.date),;

Keith

 

[audiopro]

The NVL function is only for numeric fields so last post not relevant.
The code on the previous code to that has suddenly stopped working, are there any 'set' settings which affect the operation of the NVL function?

Keith

 

[JimWWinter]

Keith,

The NVL() function works for any data type. But, your code is just reversed for the NVL() function. This code

SELECT NVL(LEFT(CDOW(dda.date),3),"day") as day , ;
       NVL(dda.date,DATE())

should work for you.

Regards,
Jim

 

[audiopro]

Thanks Jim
With a combination of replies, experiment and pulling my hair out, the weekly sales report is heading in the right direction. The reversed NVL syntax applied to the rest of the script too, but I am convinced it did work a couple of times, cheers.

Keith

 

[JimWWinter]

Keith,

Glad to help and thanks for the star.

Regards,
Jim

 

[audiopro]

You're welcome kind sir,
I don't like to see threads containing half a working script so this is the working version.
Note
The day and date are taken directly from the template table, obvious really, I suppose.

CREATE TABLE daisy (day c(3), date d)

dSunday=date()-(dow(date())-1)-28
? dsunday

FOR i = 0 TO 6
    INSERT INTO daisy VALUES (LEFT(CDOW(m.dSunday+i),3),m.dSunday+i)
ENDFOR

use z:\d_day in 0 alias dda
*SELECT NVL(LEFT(CDOW(dda.date),3),"day") as day , NVL(dda.date,DATE()),;

SELECT daisy.day , daisy.date,;
    NVL(SUM(qty*IIF(INLIST(code,"PAID_OUT","PAID_IN"),00000000.00,dda.each)),0) AS Sales,;
    NVL(SUM(qty*IIF(INLIST(code,"PAID_OUT"),each,00000000.00)),0) as PAID_OUT,;
    NVL(SUM(qty*IIF(INLIST(code,"PAID_IN"),each,00000000.00)),0) as PAID_IN,;
    NVL(SUM(qty*IIF(INLIST(code,"REFUND"),each,00000000.00)),0) as REFUND ;
    FROM daisy LEFT OUTER JOIN dda ON daisy.date=dda.Date and dda.DATE>=dSunday and dda.DATE<=dSunday+7;
    GROUP BY dda.date, day ;
    INTO CURSOR weeksales

BROWSE 
DROP TABLE daisy

Keith

 

[audiopro]

I now need to calculate line totals and column totals, I know I can do that within a report but out of interest, can the line totals be summed to an additional column?

SELECT DAISY.DAY , DAISY.DATE,;
	NVL(SUM(QTY*IIF(INLIST(CODE,"PAID_OUT","PAID_IN","PAY"),00000000.00,DDA.EACH)),0) AS SALES,;
	NVL(SUM(QTY*IIF(INLIST(CATEG,"REP"),EACH,00000000.00)),0) AS REPAIRS,;
	NVL(SUM(QTY*IIF(INLIST(CATEG,"ACC"),EACH,00000000.00)),0) AS ACCOUNTS,;
	NVL(SUM(QTY*IIF(INLIST(CATEG,"INV"),EACH,00000000.00)),0) AS INVOICE,;
	NVL(SUM(QTY*IIF(INLIST(CODE,"PAID_OUT"),EACH,00000000.00)),0) AS PAIDOUT,;
	NVL(SUM(QTY*IIF(INLIST(CODE,"PAID_IN"),EACH,00000000.00)),0) AS PAIDIN,;
	NVL(SUM(QTY*IIF(INLIST(CODE,"REFUND"),EACH,00000000.00)),0) AS REFUND;
	FROM DAISY LEFT OUTER JOIN DDA ON DAISY.DATE=DDA.DATE AND DDA.DATE>=DSUNDAY AND DDA.DATE<=DSUNDAY+7;
	GROUP BY DAISY.DATE, DAISY.DAY ;	
	INTO CURSOR WEEKSALES

Keith