?
Solved

getting results between two seperate dates

Posted on 2004-10-14
7
Medium Priority
?
188 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 2000 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

741 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