Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 951
  • 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
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

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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