Solved

calculate YTD based on end of month

Posted on 2015-02-09
7
104 Views
Last Modified: 2015-09-22
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?
0
Comment
Question by:al4629740
7 Comments
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 100 total points
ID: 40599659
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
 

Author Comment

by:al4629740
ID: 40599722
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 400 total points
ID: 40599756
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40599763
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
 

Author Comment

by:al4629740
ID: 40599765
That is what I was just looking at also.  Good idea.  

Fiscal year is July1 to June30
0
 

Author Comment

by:al4629740
ID: 40599871
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40988988
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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Counting connections to SQL Server through C# 3 27
Update data using formula 22 19
SQL Pivot add row totals 2 3
Sql Query 4 0
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

919 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now