Avatar of Mark Wilson
Mark Wilson asked on

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
Crystal ReportsSQL

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
Jim Horn

>go in on the first day of every month and change the month number.
Please elaborate on what this means.
PortletPaul

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

PortletPaul

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SOLUTION
Mark Wilson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Mike McCracken

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
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PortletPaul

Removed Topic: MS SQL Server

PortletPaul
Topic Advisor