Solved

getting results between two seperate dates

Posted on 2004-10-14
7
182 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
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.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Redundant SQL Servers Without Clustering 7 92
JKS to store upstart data 2 84
UPLOAD FILE TO Web API USING POST 5 105
SSL sertificate 5 57
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…
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 …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

912 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

20 Experts available now in Live!

Get 1:1 Help Now