Date variables (last months data)

I am using CR2008

I am connecting to an excel spread sheet

I am using a SQL command as below

select [Assessee Name:], count([Unique Ref]) as 'Total','No of Assessments' as 'Type'
from [Temp Database$A1:AB10000]
where [Skill Category] = 'Total' and month([Date & Time]) = 8 and year([Date & Time]) = 2013
group by [Assessee Name:]

The problem is I want to look at the last months worth of data. At the moment I am using
month([Date & Time]) = 8 and year([Date & Time]) = 2013. This is not good as I would have to go in on the first day of every month and change the month number.

Is there anyway I could add date variables or dates to look back at the last month without me having to change anything?

Thanks
Mark WilsonBI 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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>go in on the first day of every month and change the month number.
Please elaborate on what this means.
0
PortletPaulfreelancerCommented:
This (by: James0628) comes from a CR related question I participated in  ID: 39296140
To use parameters in a CR Command:

 Create the parameters in the Command edit window.  It has to be there, as opposed to the main CR editor window.  Once the parameters are created, they can be edited from the main window.

 Then, basically, insert the parameter name where you need it in the SQL code.  The simplest thing is to position the cursor where you want the parameter and then double-click the parameter name in the parameter list on the right.  When CR runs the query, it will simply replace the parameter name with its value.  If the parameter value could be a string, put the parameter name in single quotes.  For a date or datetime, I don't think you'll need them.
So you will then insert those params where you have 8 and 2013 below
SELECT
        [Assessee Name:]
      , COUNT([Unique Ref]) AS 'Total'
      , 'No of Assessments' AS 'Type'
FROM [Temp Database$A1:AB10000]
WHERE [Skill Category] = 'Total'
        AND MONTH([Date & Time]) = 8 -- param here
        AND YEAR([Date & Time]) = 2013 -- and another param here
GROUP BY [Assessee Name:]

Open in new window

0
PortletPaulfreelancerCommented:
mmmmm, OR you are asking for this to be "automatic" based on the server's date

maybe, it's this you want:
SELECT
        [Assessee Name:]
      , COUNT([Unique Ref]) AS 'Total'
      , 'No of Assessments' AS 'Type'
FROM [Temp Database$A1:AB10000]
WHERE [Skill Category] = 'Total'
        AND MONTH([Date & Time]) = MONTH(getdate()) -- "auto month"
        AND YEAR([Date & Time]) = YEAR(getdate()) -- "auto year"
GROUP BY [Assessee Name:]

Open in new window

0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Mark WilsonBI DeveloperAuthor Commented:
Thanks for the answers I am asking for this to be automatic

I have tried

WHERE [Skill Category] = 'Total'
        AND MONTH([Date & Time]) = MONTH(getdate()) -- "auto month"
        AND YEAR([Date & Time]) = YEAR(getdate()) -- "auto year"


I get the error message Undefined function getdate in expression
0
mlmccCommented:
getdate is an MS SQL function.  Since you are connecting to an Excel spreadsheet you need to use Excel functions

WHERE [Skill Category] = 'Total'
        AND MONTH([Date & Time]) = MONTH(Now()) -- "auto month"
        AND YEAR([Date & Time]) = YEAR(Now()) -- "auto year"

That will get you the current month data.

If you need last month's data it is more complex because you need to handle the year crossing

mlmcc
0
mlmccCommented:
Try using

(
Month(Now()) = 1
AND
 Month(`Note Date`) = 12
AND Year(`Note Date`) = Year(Now())- 1
)
OR
(
 Month(`Note Date`) = Month(Now())- - 1 AND
Year(`Note Date`) = Year(Now())
)

mlmcc
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
PortletPaulfreelancerCommented:
Removed Topic: MS SQL Server

PortletPaul
Topic Advisor
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
Crystal Reports

From novice to tech pro — start learning today.