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

 

[german12]

What do you understand by an "equal week"?
Klaus

Peace worldwide - it starts here...

 

[Mike Lewis]

If you mean a calendar month, then obviously you can't do that. Except for February in a non-leap year, no month will ever contain four equal weeks.

But if you are recording sales in periods of four weeks (aka lunar months), then you can do this:

[tt]SELECT MonthySales / 4 AS WeeklySales FROM TheTable[/tt]

But I find it hard to believe that that is what you are asking.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

No idea what you want. I know calendarization as splitting up the year into weeks and determining the percentage of a week within a month, that's explained here, for example:

https://youtu.be/8JPPP5dMNjc

It's doing that in Excel and it doesn't look like it's equally done in SQL, but you can certainly also get all the values for each single day.

No idea, if you want that, though. So I'll just leave it here and ask you to specify what data you're actually interested in, there are so many things to compute. The starting dates of weeks, for example, within a year and within a month, starting from Sunday or Monday. The percentage of a week within a month, and so on. Or do you only want to know the whole weeks, then what about the gaps of all days within weeks that are split between two months?

Chriss

 

[Mike Lewis]

Saif, perhaps you could give us a simple example of your data and what you expect to achieve.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Are there new policies? I have posted something else that was deleted. I linked to a repository of calendar classes by a member here in the forum and such repository links were not removed here before. Why is that suddenly a problem? Edit: I know what happened, I added that post to another thread. Here's the repository I mean:

https://github.com/atlopes/calendar

Anyway, VFP surely has to offer helpful functions, mainly the WEEK() function numbering weeks of a year, which can even be finetuned on your regional norm for the week start day (eg Monday or Sunday are two norms I know of) and what defines the first week of a year. Look into the help topic of the WEEK() function in the VFP help. Besides that, there's not only the Western calendar and if you have to deal with other calendars VFP's calendar functions are not helpful, but then you also ask people who are mostly only familiar with the Western (Gregorian) calendar used mostly, internationally. The repository I linked to also supports other calendars.

The first step I'd do is build up a table with a record per day, then add in more data for each day like its weekday number - DOW() gives you that - marking weekends and regional holidays, and finally month number. Then you're having a basis to assign each day to a week of a month simply by scan/endscan through that data, for example. And there are many ways to do that. So be more specific about your needs. I mean, you could also split a month into 4 equally long intervals, as a quarter day is 6 hours and so there always are 4 date-times within each month that would be at a multiple of 6 hours from midnight of the first of the month. All I'm sure of is that you wouldn't want such a subdivision.

Chriss

 

[alisaif]

Thanks for the valuable reply!

I want like this:

Date Style: British

Month: June, 2023

Sales from:
1. 01/06/2023 to 07/06/2023
2. 08/06/2023 to 14/06/2023
3. 15/06/2023 to 21/06/2023
4. 22/06/2023 to 28/06/2023
----------------------------
Rest 2 days separately for 30 days
5. 29/06/2023 to 30/06/2023

Similarly, 3 days for 31 days and 1 for February (leap year).

Thanks

Saif

 

[Mike Lewis]

Still not completely clear.

It looks like this what you want:

[tt]1. Sales for 01/06/2023 to 07/06/2023
2. Sales for 08/06/2023 to 14/06/2023
3. Sales for 15/06/2023 to 21/06/2023
4. Sales for 22/06/2023 to 28/06/2023
5. Sales for 29/06/2023 to 30/06/2023[/tt]

But how is that four equal weeks?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

OK, let's assume that you want to get the total sales for each of the four weeks, plus another total for the odd days at the end of the month.

You said that you wanted to do it in SQL. I dare say there is some clever say of doing it in a single SQL SELECT statement, but, if so, my brain can't cope with it, so I will leave it to the brainier people here.

Instead, let me try to break it down into smaller steps.

First, we need a function that returns the date of the first day of a givent 7-day period:

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))

Next, we'll create a temporary cursor that contains those first days. I'm assuming that your existing sales table has fields for the sales amount and the sales date.

SELECT SalesAmount, SalesDate, CTOD("  /  /  ") AS FirstW ;
  FROM SalesTable INTO CURSOR TempSales READWRITE 
SELECT TempSales 
REPLACE ALL FirstW WITH FirstOfWeek(SalesDate)

Finally, we can extract the sales per week like so:

SELECT SUM(SalesAmount) AS WeeklyAmount, FirstW ;
  FROM TempSales GROUP BY FirstW INTO CURSOR Results

This is completely off the top of my head. I haven't tested it in detail, and I don't claim that it is a complete working solution. But assuming that I have interpreted your question correctly, it should give you a start.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Well, if you define it that simple, then you group by year(date), month(date) and Int((day(date)-1)/7), which is the week numbered from 0 to 4 unless it's a February without a leap day which then only varies from 0 to 3.

For example, including to create some random sales data:

=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

PS: For the performance of such a query it helps to not only have an index on the salesdate column, but also on the Year(dSalesDate), Month(dSalesdate) and the Calculated Week Int((Day(dSalesDate)-1)/7).
To query just for one month add a where clause: WHERE Year(dSalesdate)=specificyear and Month(dSalesdate)=specificmonth, for example.

Chriss

 

[mjcmkrsr]

Hi,

Just a little suggestion.

You may want to replace

Int((Day(dSalesDate) -1) / 7) as Weeknumber

with

Int((Day(dSalesDate) -1 )/ 7) + 1 as Weeknumber

in order to number the weeks from 1 to 5 - week 0 as result isn't what we are used to expect

hth - Helicopter

MarK

 

[alisaif]

Thanks a lot for the reply!

I did not test that, but just wanted to show my requirement like this.

Thanks

Saif

 

[mjcmkrsr]

Hi,

I consider your request as a wish to show data with a running total per week + the month's total (see code below from my library + Chriss's code snippet to create the cursor). If additionally you want the totals per year, just adapt the code. Btw, don't put too much attention to the colors - you may adapt them to your needs.

*!*	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
*********************************************

hth

MarK

 

[alisaif]

Thanks Mr. Mark,

It is very near to my requirement, yet I need the output like this. How to extract the last running balance of each week.

Thanks

Saif

 

[mjcmkrsr]

Hi,

What do you mean by "extract"?

MarK

 

[alisaif]

Obtain last value of running balance of each week.

like;

Thanks

Saif

 

[mjcmkrsr]

Hi,

You have all the data you need in your csrTemp. Think about it: you summing up values. Since you don't have negative values you're looking for the highest value per week.

Little hint :

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]
...

hth

MarK

 

[alisaif]

Thanks for the hint, definitely I will go through it.

Thanks for the time sharing with me.

Saif

 

[alisaif]

Thanks for Mr. Mike and Chris.

Saif

 

[Chris Miller]

I agree with Mark. The heavy lifting is done once you have the totals grouped by week. Finding the latest day in the week with sales to have the most compact list is a luxury you should be able to do without, I'd just fill in 0 for days with no sale and attach the sum to the last day of each week (or the last day of the month for the rest).

Edit: The rest is aftermath. Actually, it isn't even hard to get that last day with sales. It means extending what I gave you with Max(dSalesdate). Just remind yourself that grouping data finds values per group, so what is the last day of a week? It's the one with the max date, and MAX() is an available aggregation function of group by queries.

Even when there are days without a sale that will automatically adjust to whatever max date in a week with sales. And in a week without any sales you even skip that week overall and have no record.

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

This data needs to be joined back into the sales data to have the totals in a new column only on the records with dSalesdate=LastDayOfWeekWithSales of this query, so you can make this the join condition of a full outer join (as you want to have all days records in the list, not just the aggregated ones).

The other solution available is called a rolling sum, that's far easier to do with MS SQL Server having more SQL capabilities in that regard.

What you want could also be easily done with a report using a report variable that you configure to be the sum of sales amounts. You can print that into every row or just in the group footer of a report, for example.

Chriss