Divide Monthly Sales in 4 equal weeks. 1

[alisaif]

Hi,

I want to divide the whole month sales into 4 equal weeks, how can I use SQL to perform this task.

Thanks

Saif

 

[alisaif]

Thanks Chriss

 

[mjcmkrsr]

Hi,

Since you don't seem to favor the running totals you may opt for a second grid in the form showing the weekly totals + a label with the monthly total. The important code snippets are marked green - all the rest is visual stuff. Enjoy.

*!*	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 = 570
	Height = 420
	MinHeight = This.Height
	MinWidth = This.Width
	MaxWidth = This.Width
 
	ADD OBJECT grid1 AS grid WITH ;
		ColumnCount = -1, ;
		Left = 10, ;
		Top = 36, ;
		Width = 300, ;
		Height = ThisForm.Height - 42, ;
		RecordSource = "csrSalesData", ;
		Anchor = 15, ;
		ReadOnly =.T.
 
		PROCEDURE grid1.Init
			 WITH This.Column1
				.Header1.Caption = "ID"
			 ENDWITH

			 WITH This.Column2
				.Header1.Caption = "Date"
			 ENDWITH

			 WITH This.Column3
				.Header1.Caption = "Amount"
			 ENDWITH
		 ENDPROC 
		 
	ADD OBJECT grdWeeklySales AS grid WITH ;
		Left = 324, ;
		Top = 36, ;
		Width = 168, ;
		Height = 126, ;
		DeleteMark = .F., ;
		RecordMark = .F., ;
		ScrollBars = 0, ;
		REadOnly =.T., ;
		Visible = .F.
 
	ADD OBJECT lblDate as Label WITH ;
		Left = 324, Top = 9, Caption = "Enter date :", AutoSize = .T.

	ADD OBJECT txtDate as TextBox WITH ;
		Left = 408, Top = 6, Width = 84, Value = DATE()

	ADD OBJECT lblSalesMonth as Label WITH ;
		Left = 324, Top = 174, Caption = "Sales in " + CMONTH(DATE()) + " " + ALLTRIM(STR(YEAR(DATE()))), Autosize = .T., FontBold = .T., Visible = .F.

 	ADD OBJECT cmdUndo AS CommandButton WITH ;
		Left = 120, Top = 6, Height = 24, Caption = "Revert"
	
		PROCEDURE cmdUndo.Click()
			With ThisForm.Grid1
				.ColumnCount = -1
				.Recordsource = "csrSalesData"
				
				.Column1.Header1.Caption = "ID"

				.Column2.Header1.Caption = "Date"

				.Column3.Header1.Caption = "Amount"
			ENDWITH

			WITH ThisForm
				.lblSalesMonth.Caption = ""
				.grdWeeklySales.Visible = .F.
				.Refresh()	
			ENDWITH 
		ENDPROC

	ADD OBJECT cmdDoit AS CommandButton WITH ;
		Left = 10, Top = 6, Height = 24, Caption = "Calculate"
	
		PROCEDURE cmdDoit.Click()
			Local Array laSumMonth[1]
			
[highlight #73D216]			Select dSalesDate, INT((DAY(dSalesDate) - 1)/7) + 1 as iWeek, ySalesAmount ;
				FROM csrSalesData ;
				WHERE YEAR(dSalesDate) = YEAR(ThisForm.txtDate.Value) AND MONTH(dSalesDate) = MONTH(ThisForm.txtDate.Value) ;
				ORDER by 1 INTO CURSOR csrTemp 
	[/highlight]		
			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"
				
			ENDWITH 
			
[highlight #73D216]			SELECT iWeek, SUM(ySalesAmount) as yWeeklyTotal FROM csrTemp GROUP BY 1 INTO CURSOR csrWeeklyTotals
			SELECT SUM(ySalesAmount) FROM csrTemp INTO ARRAY laSumMonth
[/highlight]
			WITH ThisForm.grdWeeklySales
				.ColumnCount = -1
				.RecordSource = "csrWeeklyTotals"
				.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")
				.Visible = .T.
								
				.Column1.Header1.Caption = "Week"

				.Column2.Sparse = .F.
				.Column2.Header1.Caption = "Total"
				.Column2.Text1.Inputmask = "999,999.9999"
			 ENDWITH  

			WITH ThisForm
				.lblSalesMonth.Visible = .T.
				.lblSalesMonth.Caption = "Sales in " + CMONTH(ThisForm.txtDate.Value) + " " + ALLTRIM(STR(YEAR(ThisForm.txtDate.Value))) + " : " + ALLTRIM(TRANSFORM(laSumMonth[1], "999,999,999.9999"))
				.Refresh()
			ENDWITH 
		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() * 900, ;
	   ySalesAmount Y DEFAULT Rand() * $1000.00)
   
	For lnI = 1 To 1500
	   Append Blank
	EndFor
	
	LOCATE 
ENDPROC

ENDDEFINE
*********************************************

hth

Mark

 

[alisaif]

Thanks Mark, Superb!

Saif