Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
FUNCTION FirstOfWeek
LPARAMETERS tdDate
LOCAL ldDay
ldDay = DAY(tdDate)
RETURN DATE(YEAR(tdDate), MONTH(tdDate), ;
ICASE( ;
BETWEEN(ldDay, 1, 7), 1, ;
BETWEEN(ldDay, 8, 14), 8, ;
BETWEEN(ldDay, 15, 21), 15, ;
BETWEEN(ldDay, 22, 28), 22, ;
ldDay >= 29, 29))
SELECT SalesAmount, SalesDate, CTOD(" / / ") AS FirstW ;
FROM SalesTable INTO CURSOR TempSales READWRITE
SELECT TempSales
REPLACE ALL FirstW WITH FirstOfWeek(SalesDate)
SELECT SUM(SalesAmount) AS WeeklyAmount, FirstW ;
FROM TempSales GROUP BY FirstW INTO CURSOR Results
=Rand(-1) && randomize (initialize) the random number generator.
Create Cursor salesdata (Id I autoinc, ;
dSalesDate D default Date(Year(Date()),1,1)+Rand()*365,;
ySalesamount Y default Rand()*$1000.00)
For lnI = 1 To 1000
Append Blank
EndFor
* Now your query (grouping):
Select Year(dSalesDate) as Year, Month(dSalesDate) as Month, Int((Day(dSalesDate)-1)/7) as Weeknumber, ;
Sum(ySalesamount) Group by 1,2,3 From salesdata into cursor weeksales
browse
Int((Day(dSalesDate) -1) / 7) as Weeknumber
Int((Day(dSalesDate) -1 )/ 7) + 1 as Weeknumber
*!* grid_calculatedcolumn.prg
PUBLIC oform1
oform1=NEWOBJECT("form1")
oform1.Show
Read Events
Close all
Clear All
RETURN
**************************************************
DEFINE CLASS form1 AS form
AutoCenter = .T.
Caption = "Grid with calculated columns"
Width = 510
Height = 420
MinHeight = This.Height
MinWidth = This.Width
ADD OBJECT grid1 AS grid WITH ;
ColumnCount = -1, ;
Left = 10, ;
Top = 36, ;
Width = ThisForm.Width - 20, ;
Height = ThisForm.Height - 42, ;
RecordSource = "csrSalesData", ;
Anchor = 15
PROCEDURE grid1.Init
WITH This.Column1
.ReadOnly = .T.
.ControlSource = "ID"
.Header1.Caption = "ID"
ENDWITH
WITH This.Column2
.ReadOnly = .T.
.ControlSource = "dSalesDate"
.Header1.Caption = "Date"
ENDWITH
WITH This.Column3
.ControlSource = "ySalesAmount"
.Header1.Caption = "Amount"
ENDWITH
ENDPROC
ADD OBJECT lblDate as Label WITH ;
Left = 224, Top = 9, Caption = "Date :"
ADD OBJECT lblSalesMonth as Label WITH ;
Left = 342, Top = 9, Caption = " ", Autosize = .T.
ADD OBJECT txtDate as TextBox WITH ;
Left = 266, Top = 6, Width = 72, Value = DATE()
ADD OBJECT cmdUndo AS CommandButton WITH ;
Left = 120, Top = 6, Height = 24, Caption = "Undo"
PROCEDURE cmdUndo.Click()
With ThisForm.Grid1
.ColumnCount = -1
.Recordsource = "csrSalesData"
ENDWITH
ThisForm.lblSalesMonth.Caption = ""
ThisForm.Refresh()
ENDPROC
ADD OBJECT cmdDoit AS CommandButton WITH ;
Left = 10, Top = 6, Height = 24, Caption = "Calculate"
PROCEDURE cmdDoit.Click()
Local Array laCalc[1,1], laSum[1]
Select dSalesDate, INT((DAY(dSalesDate) - 1)/7) + 1 as iWeek, ySalesAmount, ySalesAmount as yWeeklySales ;
FROM csrSalesData ;
WHERE YEAR(dSalesDate) = YEAR(ThisForm.txtDate.Value) AND MONTH(dSalesDate) = MONTH(ThisForm.txtDate.Value) ;
ORDER by 1 into Array laCalc
For i = 2 to ALEN(laCalc, 1)
If laCalc[i , 2] = laCalc[i - 1 , 2]
laCalc[i, ALEN(laCalc, 2)] = laCalc[i, ALEN(laCalc, 2)] + laCalc[i - 1, ALEN(laCalc, 2)]
EndIF
EndFor
CREATE CURSOR csrTemp (dDate D, iWeek I, ySalesAmount Y, yWeeklySales Y)
APPEND FROM ARRAY laCalc
LOCATE
With ThisForm.Grid1
.ColumnCount = -1
.Recordsource = "csrTemp"
.SetAll("DynamicBackColor", "ICASE(iWeek = 1, RGB(255,255,0), iWeek = 2, RGB(255,0,255), iWeek = 3, RGB(0,255,255), iWeek = 4, RGB(0,255,0), RGB(180,180,180))", "Column")
.Column1.Header1.Caption = "Date"
.Column2.Header1.Caption = "Week"
.Column2.Text1.Inputmask = "9"
.Column3.Header1.Caption = "Amount"
.Column3.Text1.Inputmask = "999.9999"
.Column4.Width = 132
.Column4.Sparse = .F.
.Column4.Header1.Caption = "Running Sum/Week"
.Column4.Text1.Inputmask = "999,999.9999"
ENDWITH
SELECT SUM(ySalesAmount) FROM csrTemp INTO ARRAY laSum
ThisForm.lblSalesMonth.Caption = "Sales in " + CMONTH(ThisForm.txtDate.Value) + " : " + ALLTRIM(TRANSFORM(laSum[1],"999,999,999.9999"))
ThisForm.Refresh()
ENDPROC
PROCEDURE Destroy
Thisform.Release()
CLOSE ALL
Clear Events
ENDPROC
PROCEDURE Load
LOCAL lnI
CREATE CURSOR csrSalesData (Id I autoinc, ;
dSalesDate D DEFAULT DATE(YEAR(DATE()), 1, 1) + RAND() * 365, ;
ySalesAmount Y DEFAULT Rand() * $1000.00)
For lnI = 1 To 500
Append Blank
EndFor
LOCATE
ENDPROC
ENDDEFINE
*********************************************
Local Array laWeeklySales[5]
Select MAX(yWeeklySales) from csrTemp where (the rest is up to you) into Array laWeeklySales
*!* Total sales week 1 = laWeeklySales[1]
...
Select Year(dSalesDate) as Year, Month(dSalesDate) as Month, Int((Day(dSalesDate)-1)/7)+1 as Weeknumber, ;
Sum(ySalesamount), Max(dSalesDate) as LastDayOfWeekWithSales Group By 1,2,3 From salesdata into cursor weeksales