Different Starting/Ending Months for quarters

Hi,

I have hourly data for many years, and I get different outputs in hourly, daily, weekly, monthly, quarterly, bi-yearly and yearly averages, their counts, st_dev and etc.

Most of it works fine, but I need help with this part.

1) The user wants to select different months for the quarter, for example, instead of the default JFM (jan, feb, mar) as quarter 1, they may select FMA (Feb, Mar, Apr), I have 2  text boxes to get the start and end in the format of MM/YYYY from starting at ending, but I dont know how to change these settings for the quarters.

Similarly I need to change the starting days for the weeks as well.

2) Is there any way to gey bi-yearly (half year) data, such as average, and the same problem as above, different starting and ending months.

This is the code I have for the quarterly data.
SELECT DatePart("q",[date]) AS Quarter, tbl_operation.cYear, Avg(tbl_operation.Value) AS Average, Count(tbl_operation.Value) AS n, StDev(tbl_operation.Value) AS St_Dev
FROM tbl_operation
GROUP BY DatePart("q",[date]), tbl_operation.cYear
ORDER BY tbl_operation.cYear;

Open in new window

Student_101Asked:
Who is Participating?
 
reb73Connect With a Mentor Commented:
No, use the following code where Offset is the parameter that is set from combobox.index - 1

SELECT IIF(Offset < 1, DATEPART("q",[date]), DATEPART("q",DATEADD("m",-1*Offset , [date])))  AS Quarter, tbl_operation.cYear, Avg(tbl_operation.Value) AS Average, Count(tbl_operation.Value) AS n, StDev(tbl_operation.Value) AS St_Dev
FROM tbl_operation
GROUP BY IIF(Offset < 1, DATEPART("q",[date]), DATEPART("q",DATEADD("m",-1*Offset , [date]))), tbl_operation.cYear
ORDER BY tbl_operation.cYear;

Open in new window

0
 
reb73Commented:
Weeks, months and quarters always start from Jan 01 for a year, there is no arbitrary configuration to change this explicitly..

What you could do is have a system of month offsets  - say 1 for FMA, 2 for MAM, 3 for AMJ and then calculate using rolled-up integer division by 3 to get the adjusted quarter or mod the result by 6 to get the adjusted half-year value.

See the sample code below to get an idea of using monthly offsets -
-- sample quarter calculation using month offset
declare @monthoffset int, @currentdate datetime
 
select   @monthoffset = 1 -- 1 for FMA, 2 for MAM, 3 for AMJ and so on
	,@currentdate = '31-Jul-2009 23:59'
 
select   AdjQuarter = ceiling(((case when month(@currentdate) <= @monthoffset then month(@currentdate) + 12 else month(@currentdate) end) - 1) / 3.0) 
	,AdjHalfYear = ceiling(((case when month(@currentdate) <= @monthoffset then month(@currentdate) + 12 else month(@currentdate) end) - 1) / 6.0) 

Open in new window

0
 
Student_101Author Commented:
Hi,

Sounds good, but I am using VB6 and access to query my results, how do I use this from VB6 ??

Thanks for the prompt reply !
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
reb73Commented:
If its Access or VB, just change the 'case when' construct to IIF() construct.. The function names and the calculation should be the same anyway..
0
 
Student_101Author Commented:
ok

I am working on it right now,

I'll get back to you shorty.
Thanks
0
 
Student_101Author Commented:
ok,

So I added a combo box on my form, and added this the list

1 - JFB
2 - FMA
3 - MAM
4 - AMJ
...... and so on for

now when the user clicks go, this is how I used to query my old results for the regular means by calling the query itself from VB6

but now I cant call the query, because it wont recognize the combo box function from VB6

and the ceiling function is not present in VB6

So can I just use that /3 part, or advance the month in the quarter by 1 every time as I have all the different options present?

So I will only need to change the first month of the quarter, how can I do this?

and the case will already be prespecified from my If statements in the combo box, so all I need to do now is just change the staring month for the quarter.



0
 
reb73Commented:
The ceiling function can be implemented in VB6 in a module as follows -

Public Function Ceiling(Number As Double) As Long
    Ceiling = -Int(-Number)
End Function


Use the (index-1) of the combo items to give you the offset for the quarter, so the offset for FMA (index = 2) would be 1, offset for MAM (index = 3) would be 2 and so on..

Just note that if the month offset is 0, then use the regular datepart(q, <datetime>) sql function to give you the quarter..
0
 
Student_101Author Commented:
ok,

So by the index - 1 I will be able to start the quarter 1 month forward?

and would I do something like this for FBM

Sorry for the confusion...
SELECT DatePart("q",[month(date) -1]) AS Quarter, tbl_operation.cYear, Avg(tbl_operation.Value) AS Average, Count(tbl_operation.Value) AS n, StDev(tbl_operation.Value) AS St_Dev
FROM tbl_operation
GROUP BY DatePart("q",[date]), tbl_operation.cYear
ORDER BY tbl_operation.cYear;

Open in new window

0
 
Student_101Author Commented:
Perfect !!

works fine, I verified it as well.

Thank you Very very Much !
0
 
Student_101Author Commented:
Thank you :)
0
 
Student_101Author Commented:
Forgot to ask,

For the bi-yearly part, would I just add the /6 in to the group by clause?
0
 
Student_101Author Commented:
I tired using this code for the biyearly part, but it resulted in error

"Expression to complex"
SELECT IIF(Offset < 1, DATEPART("mm",[date]), DATEPART("mm",DATEADD("mm",-1*Offset , [date]))/6)  AS Bi_Yearly, tbl_operation.cYear, Avg(tbl_operation.Value) AS Average, Count(tbl_operation.Value) AS n, StDev(tbl_operation.Value) AS St_Dev
FROM tbl_operation
GROUP BY IIF(Offset < 1, DATEPART("mm",[date]), DATEPART("mm",DATEADD("mm",-1*Offset , [date]))/6), tbl_operation.cYear
ORDER BY tbl_operation.cYear;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.