[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Different Starting/Ending Months for quarters

Posted on 2009-02-09
12
Medium Priority
?
465 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:Student_101
  • 8
  • 4
12 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 23590150
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
 

Author Comment

by:Student_101
ID: 23590182
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
 
LVL 25

Expert Comment

by:reb73
ID: 23590232
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:Student_101
ID: 23590268
ok

I am working on it right now,

I'll get back to you shorty.
Thanks
0
 

Author Comment

by:Student_101
ID: 23590389
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
 
LVL 25

Expert Comment

by:reb73
ID: 23591476
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
 

Author Comment

by:Student_101
ID: 23591553
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
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 23591823
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
 

Author Comment

by:Student_101
ID: 23591910
Perfect !!

works fine, I verified it as well.

Thank you Very very Much !
0
 

Author Closing Comment

by:Student_101
ID: 31544494
Thank you :)
0
 

Author Comment

by:Student_101
ID: 23592538
Forgot to ask,

For the bi-yearly part, would I just add the /6 in to the group by clause?
0
 

Author Comment

by:Student_101
ID: 23600979
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question