• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1004
  • Last Modified:

Get half-yearly data

Hi,

I have 10 years of weekly data, I need to get Bi-yearly data.

I am using VB6, and Access as my back end.

ADODB connection and recordset to query my results to data grid on form.

I also need to have the option of offset the starting/ending month of the bi-yearly data.

But we can start with getting half-year data, such as grouping bi-yearly data.
0
Student_101
Asked:
Student_101
  • 10
  • 6
1 Solution
 
GRayLCommented:
Give this a try.  assuming a date field and a value field (use the real names for table and fields):

SELECT Year(fldDate) AS CalYear,  IIf(Format(fldDate,"q") In (1,2),1,2) AS CalHalf,
Sum(fldValue) as ValueSum
FROM myTable
GROUP BY Year(fldDate),IIf(Format(fldDate,"q") In (1,2),1,2)


0
 
Student_101Author Commented:
Hi Grayl,

This is my SQL Query for different seasonal means.
I need the exact same thing for different Bi-Yearly Date

I will verify your query meanwhile.

Regards :)
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
 
Student_101Author Commented:
Hi Grayl,

I tried it, and we are almost there.

All I need now is just to get the offset working, can you look at the previous code I sent you, and tell me If i can offest it the same way for different starting months for the half year?
SELECT Year(Date) AS Year,  IIf(Format(Date,"q") In (1,2),1,2) AS Half_Year, AVG(Value) as Average, Count(tbl_operation.Value) AS n, StDev(tbl_operation.Value) AS St_Dev
FROM tbl_operation
GROUP BY Year(Date),IIf(Format(Date,"q") In (1,2),1,2)

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
GRayLCommented:
Do you mean you would like half year intervals going from say Feb to Jul and Aug to Jan of next year?  That implies Offset values 1 to 5?  If so, a completely different kettle of fish.  I need to ponder some more.
0
 
Student_101Author Commented:
Yes, that is correct.

I used this approach for diff weeks and quarters, and it works fine.

Do you think it will have to be very different for different half year intervarls?
0
 
Student_101Author Commented:
I have this code that does the offset getting the values from the combo box,

all we need is the same offset, and I will specify the parameter from VB according to which half-year interval was selected.
Dim offset As Long
offset = 0
 
If cbo_Smonth.Text = "Jan - Feb - Mar" Then
Call cbo_OperationParam_Click
Exit Sub
ElseIf cbo_Smonth.Text = "Feb - Mar - Apr" Then
offset = 1
ElseIf cbo_Smonth.Text = "Mar - Apr - May" Then
offset = 2
ElseIf cbo_Smonth.Text = "Apr - May - June" Then
offset = 3
End If
 
OpenConnection ' Set connection string to database
 
  Dim cmd_Results As New ADODB.Command
  Dim RS_Results As New ADODB.Recordset
 
  With cmd_Results
    .ActiveConnection = m_Conn
    .CommandText = "Select * from qry_DiffSeasonalMonth"
    .CommandType = adCmdText
    .Parameters(0) = offset
  End With
 
  With RS_Results
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmd_Results
  End With

Open in new window

0
 
Student_101Author Commented:
Forgot to mention, that is a sample for how i do the different seasonal quarters.

We can do the similar thing for the different half years.
0
 
GRayLCommented:
Firstly, you query at http:#a23674388 does not need the IIF().  It simplifies to:

SELECT
DATEPART("q",DATEADD("m",-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;

Now replace the first field Quarter with my Half below

IIF(Month(DateAdd("m", -Offset, [Date])) BETWEEN 1 AND 6, 1, 2) AS Half

and the query becomes:

SELECT
IIF(Month(DateAdd("m", -Offset, [Date])) BETWEEN 1 AND 6, 1, 2) AS Half,
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(Month(DateAdd("m", -Offset, [Date])) BETWEEN 1 AND 6, 1, 2), tbl_operation.cYear
ORDER BY tbl_operation.cYear;

BTW, you should never store a derived value like year in a table.  Delete the field and replace tbl_operation.cYear with Year([tbl_operation.[Date]).  And you should never use an Access Reserved Word like [Date] as a field name as you then need to constantly wrap the name in brakets.  Why not something like OpDate?  Similarly I hate underscores in names.  tblOperation is just as readable as tbl_Operation and easier to type.   Just my 2¢.
0
 
Student_101Author Commented:
Hi GRayl,

Your code works perfectly. Thank you very much.

I thank you for your constructive criticism as well

1) The Year is original data, I am not using Year(date) to get that column of data in the table

2) I will change the date to OpDate

3) I am just used to underscores. Lol.

Thank you for your help, really appreciate it :)
0
 
Student_101Author Commented:
Excellent !
0
 
GRayLCommented:
Thanks, glad to help.  BTW, what do you do if ever Year(OpDate) does not equal cYear?  Bummer eh!
0
 
Student_101Author Commented:
oo.. you did get my thinking,

but that's not possible

becuase this is how I get my OpDate

RS_AddToNew_2!Date = RS_Convert_2!cDay & "/" & RS_Convert_2!cMonth & "/" & RS_Convert_2!cYear

So it has to contain cYear by default :)
0
 
GRayLCommented:
Somewhere, somebody had to take a date apart to get Day, Month and Year.  Somewhere else, unlike Humpty Dumpty, you've got the tools to put them back together again ;-)
0
 
Student_101Author Commented:
LOLLL

As long as it works :D
0
 
Student_101Author Commented:
now I am wondering why I just didn't use cMonth, cDay, cYear
Lol

Oh well, got a lot more to finish in this application, so ill move along ;)
0
 
GRayLCommented:
BTW a better way to re-assemble a date is by using DateSerial(y,m,d).  Using the way you showed in http:#a23685062 leave you open to format issues.  Thanks, and good luck with the project.
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 10
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now