Solved

getting results between two seperate dates

Posted on 2004-10-14
7
187 Views
Last Modified: 2013-12-24
Greetings...I have two html form fields on a page. Each field requires a date in the format of yyyy-mm-dd to be entered. I want results to be retrieved from my mySQL db that are between these two user defined dates.

The field in the database that holds the date stamp is called datestampid.

Any idea on how to achieve this?

Thanks!
0
Comment
Question by:bluskyGuy
[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
  • 3
  • 3
7 Comments
 
LVL 5

Accepted Solution

by:
umbrae earned 500 total points
ID: 12316094
<cfquery name="getDateInfo" datasource="yourdb">
SELECT *
from dateDB
where datestampid BETWEEN <cfqueryparam value="#form.date1#" cfsqltype="cf_sql_date"> AND <cfqueryparam value="#form.date2#" cfsqltype="cf_sql_date">
</cfquery>

will return a query with the required data.
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 12316113
Hello bluskyGuy!

You can simply have your query like this :

<cfquery name="GetRecord" datasource="DSN">
SELECT *
FROM TableName
WHERE datestampid BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#defined_date1#">
  AND <cfqueryparam cfsqltype="cf_sql_date" value="#defined_date2#">
</cfquery>

Hope this helps!

Goodluck!
eNTRANCE2002 :-)
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 12316131
Ooops!  

I think you should ignore my comment since umbrae posted it first.  We have the same solution being provided for you.

I apologize...  I hope you will consider it umbrae.

I must have to reload the question first before submitting my comment.  I thought, nobody has posted a comment.


Regards!
eNTRANCE2002 :-)
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:bluskyGuy
ID: 12316243
Thanks all. I'll give your suggestions a shot and see what works.

Thanks!
0
 

Author Comment

by:bluskyGuy
ID: 12316479
Great, that worked!
Now, I have one other task to perform. Basically I have two queries. One query that gets a list of salespeople from the database. Another query that is supposed to generate how ever many sales a perspective salesperson had.

Here's the format I'm going after:

Salesperson             Total # of sales
Sam                        1
Mark                       4
etc                          etc

Basically I can get the output query for the salespeople to function fine. It's the issue with getting their total sales. Here's the script I have so far...

//this works perfect!
 <cfquery name="getDateInfo" datasource="bsc_db">
SELECT *
from memberinfo
where timestampid BETWEEN <cfqueryparam value="#form.date1#" cfsqltype="cf_sql_date"> AND <cfqueryparam value="#form.date2#" cfsqltype="cf_sql_date">
</cfquery>

//this is getting the list of salespeople correctly as well...
<cfquery name="Recordset1" datasource="BSC_DB">
SELECT *
FROM admintbl WHERE
salesName != ''
ORDER BY admintbl.salesName ASC</cfquery>

<body>

 

  <table width="66%" border="0" align="center" cellpadding="4" cellspacing="0" class="border">
    <tr bgcolor="#CCCCCC">
      <td width="100%" class="style3 style2"><div align="center"><span class="style12">Salesperson Reporting Module</span></div></td>
    </tr>
    <tr bgcolor="#FFFFFF">
      <td class="style3 style2"></td>
    </tr>
    <tr bgcolor="#FFFFFF">
      <td height="47" class="style3 style2"><p class="style10">Total Sales Between <cfoutput><strong>#Form.date1#</strong> and <strong>#Form.date2#</strong></cfoutput>: <span class="style14"><cfoutput>#getDateInfo.RecordCount#</cfoutput></span></p>
      </td>
    </tr>
    <tr bgcolor="#FFFFFF">
      <td height="20" bgcolor="#CCCCCC" class="style2 style13"><div align="center"><strong>Breakdown by Salesperson </strong></div></td>
    </tr>
    <tr bgcolor="#FFFFFF">
      <td height="21" class="style2 style13"><table width="100%" height="41" border="0" align="center" cellpadding="0" cellspacing="0">
        <tr bgcolor="#003366">
          <td width="42%"><cfoutput><span class="style3">Salesperson</span></cfoutput></td>
          <td width="58%"><span class="style3">Total for this Period </span></td>
        </tr>
        <cfoutput query="Recordset1">
            <cfquery name="GetTotal" datasource="BSC_DB">
    SELECT * FROM memberinfo WHERE memberinfo.salesid = '#Recordset1.salesusername#'
    </cfquery>
          <tr>
            <td><span class="style3 style13"><strong>#Recordset1.SalesUsername#</strong></span></td>
            <td><cfoutput><span class="bottomborder"><span class="style8">#GetTotal.RecordCount#</span></span></cfoutput></td>
          </tr>
        </cfoutput>
            </table></td>
    </tr>
    <tr bgcolor="#FFFFFF">
      <td class="style3 style2">&nbsp;</td>
    </tr>
    <tr bgcolor="#FFFFFF">
      <td class="style3 style2">&nbsp;</td>
    </tr>

   
  </table>

</body>
</html>
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 12316531
Hello bluskyGuy!

I think you need something like this :

<cfparam name="TotalSales" default="0"> <!--- Put this on top of your page --->

<!--- On the part of looping your query --->
        <cfoutput query="Recordset1">
          <cfquery name="GetTotal" datasource="BSC_DB">
          SELECT * FROM memberinfo WHERE memberinfo.salesid = '#Recordset1.salesusername#'
          </cfquery>
          <cfset TotalSales = TotalSales + GetTotal.recordcount>
          <tr>
            <td><span class="style3 style13"><strong>#Recordset1.SalesUsername#</strong></span></td>
            <td><span class="bottomborder"><span class="style8">#GetTotal.RecordCount#</span></span></td>
          </tr>
        </cfoutput>

Then you can check it by adding this code :
        <cfoutput>The total sales is : #TotalSales#</cfoutput>

Just try.


Goodluck!
eNTRANCE2002 :-)
0
 

Author Comment

by:bluskyGuy
ID: 12316763
That worked perfectly!
Since this was a two question post with umbrae providing the first answer the points will be given to him.
I'm going to post another question that you provided the answer too. Post the response again and I'll give you the full 500 points.

Thanks again!
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

728 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