Solved

Previous Month Results in Query

Posted on 2004-10-23
167 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
Question by:Lee R Liddick Jr
    2 Comments
     
    LVL 17

    Accepted Solution

    by:
    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
    Perfect!  Thank you.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Product Review - Android Remix

    Come along for the ride with our Senior Product Manager, Brian Matis, as he reviews the Android Remix.

    Suggested Solutions

    This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
    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…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    934 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now