calculate YTD based on end of month

I have a query in sql that I am using via VB6.

esql = "SELECT SUM(Hours) from tblOrgHours where Month(activitydate) < " & SelectedMonth & " And ParticipantHour = 1 And AgencyID = " & AgencyID & " and Fiscal = " & Fiscal

Open in new window


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?
al4629740Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PortletPaulConnect With a Mentor freelancerCommented:
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.

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.

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)
0
 
Martin LissConnect With a Mentor Older than dirtCommented:
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)

Open in new window

0
 
al4629740Author Commented:
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)
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
PortletPaulfreelancerCommented:
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
0
 
al4629740Author Commented:
That is what I was just looking at also.  Good idea.  

Fiscal year is July1 to June30
0
 
al4629740Author Commented:
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

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
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.