SQL Including date range in SQL Statement

Hi
I need to alter the following SQL query to include that the column called "[Performance].[Date]" is between two dates. How do I do this?

SELECT [Performance].[Shaft], [Performance].[Hole Number], Min([Performance].[Drilled From]) AS [From], Max([Performance].[Drilled To]) AS [To], Sum([Performance].AXT) AS AXT, Sum([Performance].BX) AS BX, Sum([Performance].[Drilled Total]) AS Advance, Sum([Performance].[Concrete Redrill]) AS [Concrete Redrill], Sum([Performance].Setup) AS Setups, Sum([Performance].[DWR Hours]) AS DWR, Sum([Performance].[Standing Time]) AS [Standing Time], Sum([Performance].[Grout Hours]) AS [Grout Hours], [Performance].[Delay Hours 1]
FROM [Performance]
GROUP BY [Performance].[Hole Number], [Performance].[Delay Hours 1], [Performance].Shaft, [Performance].Date
HAVING  Shaft = 'kopanang air' Or Shaft = 'moab air' Or Shaft = 'moab air_top mine' Or Shaft = 'moab air_middle mine' Or Shaft = 'moab air_atic' Or Shaft = 'two rivers air' Or Shaft = 'masimong air' Or Shaft= 'phakisa air' Or Shaft = 'tshepong air' Or Shaft = 'bambanani air' Or Shaft = 'target air' Or Shaft = 'joel air' Or Shaft = 'unisel air' Or Shaft = 'union air' Or Shaft  = 'khuseleka air' Or Shaft = 'siphumelele air' Or Shaft = 'thembelani air' Or Shaft = 'koffiefontein air'
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
In between the FROM line and the GROUP BY line, add this:

FROM [Performance]
WHERE [Performance].[Date] BETWEEN '2015-01-01' AND '2015-06-30'
GROUP BY [Performance].[Hole Number], [Performance].[Delay Hours 1], [Performance].Shaft, [Performance].Date

Open in new window

Those dates were only examples. Supply whatever you need. If you need the last date to be current date, use GETDATE() (without quotes).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
I don't think you need to use that having clause. Typically, that's used to filter an aggregate, like having Sum([Performance].Setup) > 100. Rather than the Having clause, you could use WHERE Shaft in ('kopanang air', 'moab air','moab air_top mine',...) or even, if applicable,
WHERE Shaft like '%air%' ( and, e.g., if it doesn't fit, not like 'hot_air'). Also, if [Performance].[Date] contains a time element, using dsacker's example, you should use where date >= '2015-01-01' and date < '2015-07-01'
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please replace the HAVING clause by a WHERE clause since is more logic for a SQL query.
In my example @date1 and @date2 are variables as from...to dates form. You need to replace them what your real date values:
SELECT [Performance].[Shaft], [Performance].[Hole Number], Min([Performance].[Drilled From]) AS [From], Max([Performance].[Drilled To]) AS [To], Sum([Performance].AXT) AS AXT, Sum([Performance].BX) AS BX, 
	Sum([Performance].[Drilled Total]) AS Advance, Sum([Performance].[Concrete Redrill]) AS [Concrete Redrill], Sum([Performance].Setup) AS Setups, Sum([Performance].[DWR Hours]) AS DWR, 
	Sum([Performance].[Standing Time]) AS [Standing Time], Sum([Performance].[Grout Hours]) AS [Grout Hours], [Performance].[Delay Hours 1]
FROM [Performance]
WHERE Shaft IN ('kopanang air','moab air','moab air_top mine','moab air_middle mine','moab air_atic','two rivers air','masimong air','phakisa air','tshepong air','bambanani air','target air','joel air','unisel air','union air','khuseleka air','siphumelele air','thembelani air','koffiefontein air')
	AND [Performance].[Date] between @date1 and @date2
GROUP BY [Performance].[Hole Number], [Performance].[Delay Hours 1], [Performance].Shaft, [Performance].Date

Open in new window

0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.