×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Divide Monthly Sales in 4 equal weeks.

## Divide Monthly Sales in 4 equal weeks.

(OP)
Hi,

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

Thanks

Saif

### RE: Divide Monthly Sales in 4 equal weeks.

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

Peace worldwide - it starts here...

### RE: Divide Monthly Sales in 4 equal weeks.

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:

SELECT MonthySales / 4 AS WeeklySales FROM TheTable

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

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

### RE: Divide Monthly Sales in 4 equal weeks.

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

### RE: Divide Monthly Sales in 4 equal weeks.

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

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

### RE: Divide Monthly Sales in 4 equal weeks.

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

### RE: Divide Monthly Sales in 4 equal weeks.

(OP)

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

### RE: Divide Monthly Sales in 4 equal weeks.

Still not completely clear.

It looks like this what you want:

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

But how is that four equal weeks?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

### RE: Divide Monthly Sales in 4 equal weeks.

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:

#### CODE -->

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.

#### CODE -->

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:

#### CODE -->

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)

### RE: Divide Monthly Sales in 4 equal weeks.

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:

#### CODE

=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 ### RE: Divide Monthly Sales in 4 equal weeks. Hi, Just a little suggestion. You may want to replace #### CODE --> Int((Day(dSalesDate) -1) / 7) as Weeknumber  with #### CODE --> 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 ### RE: Divide Monthly Sales in 4 equal weeks. (OP) Thanks a lot for the reply! I did not test that, but just wanted to show my requirement like this. Thanks Saif ### RE: Divide Monthly Sales in 4 equal weeks. 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. #### CODE --> *!* 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

### RE: Divide Monthly Sales in 4 equal weeks.

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

### RE: Divide Monthly Sales in 4 equal weeks.

Hi,

What do you mean by "extract"?

MarK

### RE: Divide Monthly Sales in 4 equal weeks.

(OP)
Obtain last value of running balance of each week.

like;

Thanks

Saif

### RE: Divide Monthly Sales in 4 equal weeks.

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 :

#### CODE -->

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

### RE: Divide Monthly Sales in 4 equal weeks.

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

Thanks for the time sharing with me.

Saif

### RE: Divide Monthly Sales in 4 equal weeks.

(OP)
Thanks for Mr. Mike and Chris.

Saif

### RE: Divide Monthly Sales in 4 equal weeks.

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.

#### CODE

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

(OP)
Thanks Chriss

### RE: Divide Monthly Sales in 4 equal weeks.

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.

#### CODE -->

*!*	grid_calculatedcolumn.prg
PUBLIC oform1

oform1=NEWOBJECT("form1")
oform1.Show

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, ;

PROCEDURE grid1.Init
WITH This.Column1
ENDWITH

WITH This.Column2
ENDWITH

WITH This.Column3
ENDWITH
ENDPROC

ADD OBJECT grdWeeklySales AS grid WITH ;
Left = 324, ;
Top = 36, ;
Width = 168, ;
Height = 126, ;
DeleteMark = .F., ;
RecordMark = .F., ;
ScrollBars = 0, ;
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"

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]

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

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

ENDWITH

SELECT iWeek, SUM(ySalesAmount) as yWeeklyTotal FROM csrTemp GROUP BY 1 INTO CURSOR csrWeeklyTotals
SELECT SUM(ySalesAmount) FROM csrTemp INTO ARRAY laSumMonth

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.

.Column2.Sparse = .F.
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

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

### RE: Divide Monthly Sales in 4 equal weeks.

(OP)
Thanks Mark, Superb!

Saif

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!