Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Previous Month Results in Query

Posted on 2004-10-23
2
Medium Priority
?
187 Views
Last Modified: 2013-12-24
I need to create a query that is going to give me a list of last month results.  I know how to get the mtd results, but how do I get last months?  In my previous mtd query, it is giving me last month results but it is also including the first day of the current month...but I only want results for the month of September only.

QUERY INFORMATION:

<cfset TodayDate = DateFormat(Now(), "M/D/YYYY")>
<cfset FirstOfMonth = Month(Now()) & "/1/" & Year(Now())>
<cfset FirstOfLastMonth = Month(Now())-1 & "/1/" & Year(Now())>

<CFQUERY name="sepempsmtd" datasource="myds">
      SELECT *
      FROM qsel_employee_info
      WHERE Active=0
            AND AWS_EndDate1 BETWEEN ###FirstOfMonth### AND ###TodayDate###
      ORDER BY AWS_EndDate1 asc
</CFQUERY>
<CFQUERY name="sepempsprevmtd" datasource="myds">
      SELECT *
      FROM qsel_employee_info
      WHERE Active=0
            AND AWS_EndDate1 BETWEEN ###FirstOfLastMonth### AND ###FirstOfMonth###
      ORDER BY AWS_EndDate1 asc
</CFQUERY>
0
Comment
Question by:Lee R Liddick Jr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 17

Accepted Solution

by:
anandkp earned 1000 total points
ID: 12389780
try this :

<CFOUTPUT>
<CFSET PreviousMonth = Month(DateAdd('m',-1,Now()))>
<CFSET PreviousYear = Year(DateAdd('m',-1,Now()))>
<CFSET PreviousDays = DaysInMonth(DateAdd('m',-1,Now()))>

<CFSET StartDate = CreateDate(PreviousYear,PreviousMonth,1)>
<CFSET EndDate = CreateDate(PreviousYear,PreviousMonth,PreviousDays)>

#StartDate# - #EndDate#

</CFOUTPUT>


<CFQUERY name="sepempsmtd" datasource="myds">
     SELECT *
     FROM qsel_employee_info
     WHERE Active=0
          AND AWS_EndDate1 BETWEEN
              <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#StartDate#">
              AND
              <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#EndDate#">
     ORDER BY AWS_EndDate1 asc
</CFQUERY>
Similarly for the other query as well

K'Rgds
Anand
0
 

Author Comment

by:Lee R Liddick Jr
ID: 12402382
Perfect!  Thank you.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

610 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