Solved

getting results between two seperate dates

Posted on 2004-10-14
7
180 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Scale it in WD Gold

With up to ten times the workload capacity of desktop drives, WD Gold hard drives employ advanced technology to deliver among the best in reliability, capacity, power efficiency and performance.

Join & Write a Comment

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 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

15 Experts available now in Live!

Get 1:1 Help Now