[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Previous Month Results in Query

Posted on 2004-10-23
2
Medium Priority
?
191 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
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 …
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…
How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…
Suggested Courses
Course of the Month8 days, 16 hours left to enroll

590 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