In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
calculate YTD based on end of month
Posted on 2015-02-09
I have a query in sql that I am using via VB6.
the only problem I am having is that SelectedMonth needs to be the last day of that month. so if SelectedMonth = 9 Then the selection needs to be adjusted to be 9/30 instead.
How can I accomplish this?
esql = "SELECT SUM(Hours) from tblOrgHours where Month(activitydate) < " & SelectedMonth & " And ParticipantHour = 1 And AgencyID = " & AgencyID & " and Fiscal = " & Fiscal
the only problem I am having is that SelectedMonth needs to be the last day of that month. so if SelectedMonth = 9 Then the selection needs to be adjusted to be 9/30 instead.
How can I accomplish this?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
- +1
7 Comments
Active today
Without knowing more about the structure of your table (and maybe not even then) I don't think I can help except to give you this which finds the last day in any month.
Dim dteAnyDate As Date
dteAnyDate = Now ' or any date
MsgBox Day(DateSerial(Year(dteAnyDate), Month(dteAnyDate) + 1, 1) - 1)
Martin,
The variable that is passing through is the number of the month. So if I have September, then SelectedMonth would be 9.
In your example, I don't need the year, since I have a fiscal already. All I need is the last day of the month
Would it be
Day(DateSerial( SelectedMonth + 1, 1) - 1)
The variable that is passing through is the number of the month. So if I have September, then SelectedMonth would be 9.
In your example, I don't need the year, since I have a fiscal already. All I need is the last day of the month
Would it be
Day(DateSerial( SelectedMonth + 1, 1) - 1)
Active today
SELECT SUM(Hours) from tblOrgHours where Month(activitydate) < " & SelectedMonth
You are using the TSQL function MONTH() on every row of data. that function returns an integer.
So I do not understand your question really.
You should aim to NOT ALTER the (many rows of) DATA to suit your (single) parameter, instead do the reverse.
Alter the single parameter to suit the many rows of data.
So, if your parameter is 9, you need a routine that will give you the FIRST DAY of month 10
with the only wrinkle being if the parameter is 12 you need the FIRST DAY of month 1 and the year incremented however as you don't reveal how the financial year is established this might not be a problem.
Let's illustrate this:
assuming [activitydate] is date or datetime(2)
2014-09-01 is less than 2015-10-01
...
2014-09-30 is less than 2015-10-01
so you will be accurate by using less than (the first day of the next month)
You are using the TSQL function MONTH() on every row of data. that function returns an integer.
So I do not understand your question really.
What you should be doing is NOT USING month() see: sargable: Search ARGument ABLE
http://en.wikipedia.org/wiki/Sargable
Rules of thumb
Avoid applying functions on data values in a sql condition.
Avoid non-sargable predicates and replace them with sargable equivalents.
http://en.wikipedia.org/wiki/Sargable
Rules of thumb
Avoid applying functions on data values in a sql condition.
Avoid non-sargable predicates and replace them with sargable equivalents.
You should aim to NOT ALTER the (many rows of) DATA to suit your (single) parameter, instead do the reverse.
Alter the single parameter to suit the many rows of data.
So, if your parameter is 9, you need a routine that will give you the FIRST DAY of month 10
with the only wrinkle being if the parameter is 12 you need the FIRST DAY of month 1 and the year incremented however as you don't reveal how the financial year is established this might not be a problem.
Let's illustrate this:
assuming [activitydate] is date or datetime(2)
2014-09-01 is less than 2015-10-01
...
2014-09-30 is less than 2015-10-01
so you will be accurate by using less than (the first day of the next month)
Active today
by the way, if useful, in SQL Server, you can safely use a string in the form of YYYYMMDD to compare to dates/datetimes
e.g.
SELECT SUM(Hours) from tblOrgHours where activitydate < '20151001'
how you achieve this in VB I won't attempt to answer
e.g.
SELECT SUM(Hours) from tblOrgHours where activitydate < '20151001'
how you achieve this in VB I won't attempt to answer
I ended up doing
If SelectedMonth >= 7 And SelectedMonth <= 12 Then
YTDate = CDate(SelectedMonth & "/01/" & Fiscal3)
YTDate = DateAdd("m", 1, YTDate)
YTDate = DateAdd("d", -1, YTDate)
End If
If SelectedMonth >= 1 And SelectedMonth <= 6 Then
YTDate = CDate(SelectedMonth & "/01/" & Fiscal)
YTDate = DateAdd("m", 1, YTDate)
YTDate = DateAdd("d", -1, YTDate)
End IfIf SelectedMonth >= 7 And SelectedMonth <= 12 Then
YTDate = CDate(SelectedMonth & "/01/" & Fiscal3)
YTDate = DateAdd("m", 1, YTDate)
YTDate = DateAdd("d", -1, YTDate)
End If
If SelectedMonth >= 1 And SelectedMonth <= 6 Then
YTDate = CDate(SelectedMonth & "/01/" & Fiscal)
YTDate = DateAdd("m", 1, YTDate)
YTDate = DateAdd("d", -1, YTDate)
End If
Active today
al - Not sure if this is where you were going with this question, but I just kicked out an article that deals specifically with Fiscal calendar planning --> SQL Server Calendar Table: Fiscal Years. Let me know if this helps you, and if yes please click on the 'Good Article' button and provide some feedback. Thanks.
Featured Post
At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month5 days, 22 hours left to enroll
Earn Certification
CompTIA IT Fundamentals
$49.00$44.10
626 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.