SQL show monh to date numbers

Murray Brown
Murray Brown used Ask the Experts™

I use the following SQL statement to get the daily drill totals
 Sum([Performance].[Drilled Total]) AS Metres
I now have to get the month to date totals based on the [Start Date]

SELECT [Performance].[Date] As [Date],[Performance].[Shaft], Sum([Performance].[Drilled Total]) AS Metres,[vShafts_StartDate].[Target] * Count(DISTINCT [Performance].[Date]) As Target, Avg([Performance].[Drilled Total]) As [Ave M/Mach/Shift], Sum([Performance].[Delay Hours 1]) AS Delays, Sum([Performance].[DWR Hours]) AS DWR, Sum([Performance].[Standing Time]) AS [Standing Time], Sum([Performance].[Grout Hours]) AS [Grout Hours], Sum([Performance].[Concrete Redrill]) AS ReDrill, Sum([Performance].Setup) AS Setups, Sum([Performance].[Transport Hours]) as [Transport Hours], Sum([Performance].[Photographic Survey]) as [Photographic Survey], Sum([Performance].[Day Rate]) as [Day Rate], Sum([Performance].[Blank Cap]) as [Blank Cap], Sum([Performance].[Mobilise]) as [Mobilise], Sum([Performance].[Set Up Stope]) as [Set Up Stope], Sum([Performance].[Interhole Move]) as [Interhole Move], Sum([Performance].[Grouting Borehole]) as [Grouting Borehole], Sum([Performance].[Plugging]) as [Plugging], Sum([Performance].[Trp Equip UG]) as [Trp Equip UG], Cast([vShafts_StartDate].[StartDate] As Date) As [Month_Start], [vShafts_StartDate].[Target] as newTarget 
FROM [Performance] Inner Join [vShafts_StartDate] On [Performance].[Shaft] = [vShafts_StartDate].[Shaft]
Where [Date] >= [vShafts_StartDate].[StartDate] And [Date] <= '11 Mar 2016'And [Performance].[Shaft] = 'kopanang air'
GROUP BY [Performance].[Date],[Performance].Shaft,[vShafts_StartDate].[StartDate],[vShafts_StartDate].[Target]

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Brian CroweDatabase Administrator
Top Expert 2005

SELECT [Performance].[Date] As [Date],
	SUM([Performance].[Drilled Total]) AS Metres,
	[vShafts_StartDate].[Target] * Count(DISTINCT [Performance].[Date]) As Target,
	AVG([Performance].[Drilled Total]) As [Ave M/Mach/Shift],
	SUM([Performance].[Delay Hours 1]) AS Delays,
	SUM([Performance].[DWR Hours]) AS DWR,
	SUM([Performance].[Standing Time]) AS [Standing Time],
	SUM([Performance].[Grout Hours]) AS [Grout Hours],
	SUM([Performance].[Concrete Redrill]) AS ReDrill,
	SUM([Performance].Setup) AS Setups,
	SUM([Performance].[Transport Hours]) as [Transport Hours],
	SUM([Performance].[Photographic Survey]) as [Photographic Survey],
	SUM([Performance].[Day Rate]) as [Day Rate],
	SUM([Performance].[Blank Cap]) as [Blank Cap],
	SUM([Performance].[Mobilise]) as [Mobilise],
	SUM([Performance].[Set Up Stope]) as [Set Up Stope],
	SUM([Performance].[Interhole Move]) as [Interhole Move],
	SUM([Performance].[Grouting Borehole]) as [Grouting Borehole],
	SUM([Performance].[Plugging]) as [Plugging],
	SUM([Performance].[Trp Equip UG]) as [Trp Equip UG],
	CAST([vShafts_StartDate].[StartDate] As Date) As [Month_Start],
	[vShafts_StartDate].[Target] as newTarget,
	SUM(CASE WHEN YEAR([Performance].[Date]) = YEAR(GETDATE()) AND MONTH([Performance].[Date]) = MONTH(GETDATE()) THEN [Performance].[Drilled Total] ELSE 0 END) AS MTDDrillTodal
FROM [Performance]
INNER JOIN [vShafts_StartDate]
	ON [Performance].[Shaft] = [vShafts_StartDate].[Shaft]
WHERE [Performance].[Date] >= [vShafts_StartDate].[StartDate]
	AND [Performance].[Date] <= CAST(GETDATE() AS DATE)
	AND [Performance].[Shaft] = 'kopanang air'
GROUP BY [Performance].[Date], [Performance].Shaft, [vShafts_StartDate].[StartDate], [vShafts_StartDate].[Target]

Open in new window

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Just to make sure we're not missing anything, give us a data mockup of both your current data and your desired return set.
Murray BrownASP.net/VSTO Developer


Here is an example of the  two columns that I want to get a cumulative total on. I have also attached a spreadsheet showing what all columns would look like

Cumulative TotalsBook2.xlsx
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
SELECT [P].[Date] As [Date],[P].[Shaft],
    Cast(SS.[StartDate] As Date) As [Month_Start], SS.[Target] as newTarget,
    Sum([P].[Drilled Total]) AS Metres,SS.[Target] * Count(DISTINCT [P].[Date]) As Target, Avg([P].[Drilled Total]) As [Ave M/Mach/Shift], Sum([P].[Delay Hours 1]) AS Delays, Sum([P].[DWR Hours]) AS DWR, Sum([P].[Standing Time]) AS [Standing Time], Sum([P].[Grout Hours]) AS [Grout Hours], Sum([P].[Concrete Redrill]) AS ReDrill, Sum([P].Setup) AS Setups, Sum([P].[Transport Hours]) as [Transport Hours], Sum([P].[Photographic Survey]) as [Photographic Survey], Sum([P].[Day Rate]) as [Day Rate], Sum([P].[Blank Cap]) as [Blank Cap], Sum([P].[Mobilise]) as [Mobilise], Sum([P].[Set Up Stope]) as [Set Up Stope], Sum([P].[Interhole Move]) as [Interhole Move], Sum([P].[Grouting Borehole]) as [Grouting Borehole], Sum([P].[Plugging]) as [Plugging], Sum([P].[Trp Equip UG]) as [Trp Equip UG]
FROM [Performance] P
Inner Join [vShafts_StartDate] SS On [P].[Shaft] = [SS].[Shaft]
Where [P].[Date] >= [SS].[StartDate] And
      /*? SS.[StartDate] >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) And ?*/
      [P].[Date] >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) And
      [P].[Date] <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) And
      [P].[Shaft] = 'kopanang air'
GROUP BY [P].[Date],[P].Shaft,Cast([SS].[StartDate] As Date),[SS].[Target]
Murray BrownASP.net/VSTO Developer


Thanks very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial