[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to query documents by year of publication -- when DocumentPublicationDate is formatted "yyyy-mm-dd 00:00:00.0"?

Posted on 2011-05-06
20
Medium Priority
?
262 Views
Last Modified: 2012-06-27
ColdFusion 9
MS SQL Server 2005

Hi. I have a table of documents; each document has a publication date. The document publication dates are in this date-time stamp format:
2011-05-03 00:00:00.0
2010-09-15 00:00:00.0
2009-10-06 00:00:00.0

I need to do two tasks:

1) query for a simple list of document publication years, grouped by year, so that each year appears on a page in a simple list:
2011
2010
2009
2008
...etc.

2) click on each year, above, to display a new page with a list of documents published in that year.

This would be easy except for the date format in column DocumentPublicationDate. The format is not a simple year, but a date-time stamp. I've tried using the year() function.

How do you think I can accomplish the two tasks above? Thanks as always.

Eric
Query:
<cfquery name="getDocuments" datasource="#application.datasource#">
SELECT D.DocumentID
  	 , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentKeyword
     , D.DocumentImage
     , D.DocumentPublicationDate
FROM tbl_CEP_Documents
ORDER BY DocumentPublicationDate DESC
</cfquery>


Output:
<cfoutput query="getDocuments" group="DocumentPublicationDate">
<p><a href="index.cfm?DocumentPublicationDate=#year(DocumentPublicationDate)#">#year(DocumentPublicationDate)#</a></p>
</cfoutput>

Open in new window

0
Comment
Question by:Eric Bourland
  • 10
  • 8
  • 2
20 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 35709996
For task #1 you can use sql server's year() function. Just add a distinct to only return unique years

SELECT DISTINCT Year(DocumentPublicationDate) AS PublicationYear
FROM tbl_CEP_Documents
ORDER BY PublicationYear

For task #2 either use the year() function again in your WHERE clause

SELECT D.DocumentID
         , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentKeyword
     , D.DocumentImage
     , D.DocumentPublicationDate
FROM tbl_CEP_Documents
WHERE  year(D.DocumentPublicationDate) = <cfqueryparam value="#url.selectedYear#" cfsqltype="cf_sql_integer">
ORDER BY DocumentPublicationDate DESC

... OR use a date range filter. Often this is more efficient in terms of performance. But it depends on your indexes and amount of data in the table. Not tested but:

<cfset variables.startDate = createDate(url.selectedYear, 1, 1)>
...
SELECT D.DocumentID
         , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentKeyword
     , D.DocumentImage
     , D.DocumentPublicationDate
FROM tbl_CEP_Documents
WHERE  D.DocumentPublicationDate >= <cfqueryparam value="#variables.startDate#" cfsqltype="cf_sql_timestamp">
AND D.DocumentPublicationDate < <cfqueryparam value="#dateAdd('yyyy', 1, variables.startDate)#" cfsqltype="cf_sql_timestamp">
ORDER BY DocumentPublicationDate DESC
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35710001
_agx_, thank you! I will try it out and get back to you. Eric
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 2000 total points
ID: 35710008
ie

<cfquery name="getDocumentYears" ....>
SELECT DISTINCT Year(DocumentPublicationDate) AS PublicationYear
FROM tbl_CEP_Documents
ORDER BY PublicationYear
</cfquery>

<!--- display the years --->
<cfoutput query="getDocumentYears">
<p><a href="index.cfm?selectedYear=#PublicationYear#">#PublicationYear#</a></p>
</cfoutput>

Then use #url.selectedYear# on the index page query

<!--- get all documents for the selected year --->
<cfquery name="getDocumentsForSelectedYear" ....>
SELECT D.DocumentID
         , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentKeyword
     , D.DocumentImage
     , D.DocumentPublicationDate
FROM tbl_CEP_Documents
WHERE  year(D.DocumentPublicationDate) = <cfqueryparam value="#url.selectedYear#" cfsqltype="cf_sql_integer">
ORDER BY DocumentPublicationDate DESC
</cfquery>
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 52

Expert Comment

by:_agx_
ID: 35710013
Okay, good. Sorry our posts crossed ;-)
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35710756
_agx_,

Sorry to get back to you so late; it was a complicated evening. =)

The query to display the list of publication years is working great -- and, it makes sense to me. It is working here:

http://www.cep-dc.org/publications/

I'm confused about this part:

<!--- display the years --->
<cfoutput query="getDocumentYears">
<p><a href="index.cfm?selectedYear=#PublicationYear#">#PublicationYear#</a></p>
</cfoutput>

>>>Then use #url.selectedYear# on the index page query

Currently, the index.cfm file contains the output of query "getDocumentYears".

Shall I use another template -- like, displayPublication.cfm -- to display the result of query "getDocumentsForSelectedYear"?

Or -- as gdemaria likes to remind me -- can I re-use the code of index.cfm? =)

I will go ahead and set up a template, displayPublication.cfm, to display the result of query "getDocumentsForSelectedYear".

Then, in index.cfm, I will change my output code to this:

<!--- display the years --->
<cfoutput query="getDocumentYears">
<p><a href="displayPublication.cfm?selectedYear=#PublicationYear#">#PublicationYear#</a></p>
</cfoutput>

... but I am curious to know if I can use index.cfm to display the result of query "getDocumentsForSelectedYear".

Thanks again, hope your evening is going well.

Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35720533
Sorry I got busy this w/e and am just catching up :)

Shall I use another template ... or can I re-use the code of index.cfm?

Either's fine. It just depends on what you want.  You could easily put everything on one page.  I think all you'd need to do is set a default for #url.selectedYear#:

<cfparam name="url.selectedYear" default="#year(now())#">
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35720561
Hey agx,  John doesn't want my help,  he's waiting for you...  

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_27023581.html#a35720434

I think he's got a little crush on you ;)


Hi Eric - sorry to borrow you post..
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35720736
Haha.  Maybe he's trying not to split points? Anyway .. I've got a meeting coming up in 30 ;-) Like I said, I'd take your advice over some of the others around here!
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35721881
*LOL*

_agx_ it turned into a very busy weekend and morning. I am getting back to this task now and will let you know my results in a little while. Thanks, and hope your weekend was great.

~waves to gdemaria~

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35723445
_agx_,

I've been looking at this for a while. I'm not sure how to get the output to work on my index.cfm page.

Right now I have this output, which lists document publication years -- per your last post. This works great:

<!--- display the years --->
<cfoutput query="getDocumentYears">
<p><a href="index.cfm?selectedYear=#PublicationYear#">#PublicationYear#</a></p>
</cfoutput>


But when I click on it, I get the same output .... a list of document publication years.

So,my query name="getDocumentYears" is being used, but my query name="getDocumentsandFiles" is being ignored in the output.

I have a feeling I am missing something very obvious. =)<br>

Eric

queries:

<!--- query to select document publication years --->
<cfquery name="getDocumentYears" datasource="#application.datasource#">
SELECT DISTINCT Year(DocumentPublicationDate) AS PublicationYear
FROM tbl_CEP_Documents
ORDER BY PublicationYear DESC
</cfquery>


<!--- query getDocumentsandFiles: this query selects columns from tables tbl_CEP_Documents D, tbl_Document_Has_Topic H, tbl_CEP_files F, and organize them by document publication year --->


<cfparam name="url.sortBy" default="DocumentPublicationDate">
<cfparam name="url.sortDir" default="desc">
<cfparam name="url.selectedYear" default="#year(now())#">

<cfquery name="getDocumentsandFiles" datasource="#application.datasource#">
SELECT DISTINCT D.DocumentID
  	 , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentKeyword
     , D.DocumentImage
     , D.DocumentPublicationDate
     , F.DocumentID 
     , F.FileID
     , F.FileName
     , F.FileLinkText
     , F.FileExtension
     , F.FileType
     , F.FileSize
     , F.FileLinkTextSortOrder
FROM tbl_CEP_Documents D
INNER JOIN tbl_Document_Has_Topic H
ON D.DocumentID = H.DocumentID

<cfif val(url.DocumentSubSubTopicID)>
AND H.DocumentSubSubTopicID = <cfqueryparam value="#url.DocumentSubSubTopicID#" cfsqltype="cf_sql_integer">

<cfelseif val(url.DocumentSubTopicID)>
AND H.DocumentSubTopicID = <cfqueryparam value="#url.DocumentSubTopicID#" cfsqltype="cf_sql_integer">

<cfelseif val(url.DocumentTopicID)>
AND H.DocumentTopicID = <cfqueryparam value="#url.DocumentTopicID#" cfsqltype="cf_sql_integer">
</cfif>
LEFT OUTER JOIN tbl_CEP_files F
ON D.DocumentID = F.DocumentID
AND year(D.DocumentPublicationDate) = <cfqueryparam value="#url.selectedYear#" cfsqltype="cf_sql_integer">

ORDER BY
<cfswitch expression="#url.sortBy#">
        <cfcase value="DocumentPublicationDate">D.DocumentPublicationDate</cfcase>
        <cfcase value="DocumentTitle">D.DocumentTitle</cfcase>
        <cfcase value="DocumentAuthor">D.DocumentAuthor</cfcase>
        <cfdefaultcase>D.DocumentPublicationDate</cfdefaultcase>
       </cfswitch>
       <cfif url.sortDir eq "DESC">
             DESC
       <cfelse>
             ASC
       </cfif>
        , D.DocumentID, F.FileLinkTextSortOrder ASC
</cfquery>

Open in new window

0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 2000 total points
ID: 35723619
But when I click on it, I get the same output .... a list of document publication years.

Right.  It could well be me missing something obvious ;-) When you said you wanted to use the same page, I assumed you meant you wanted to display all available years AND the document listing on the same page:  ie The only difference being both queries (and outputs) would reside on the same page.  

      <cfquery name="getDocumentYears">....</cfquery>
      <!--- display the years ---><cfoutput query="getDocumentYears">....</cfoutput>

      <cfquery name="getDocumentsandFiles">....</cfquery>
      <!--- display the documents---><cfoutput query="getDocumentsandFiles">....</cfoutput>

So users could just click on a link to switch years.  But maybe that's not what you're after? :)
              2011 | 2010 | 2009 | .....
              Documents for selected year
              ......document x .....
              ......document x .....
              ......document x .....
              ......document x .....
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35724048
This is exactly the result that I want. =)

      <cfquery name="getDocumentYears">....</cfquery>
      <!--- display the years ---><cfoutput query="getDocumentYears">....</cfoutput>

      <cfquery name="getDocumentsandFiles">....</cfquery>
      <!--- display the documents---><cfoutput query="getDocumentsandFiles">....</cfoutput>

So users could just click on a link to switch years.  But maybe that's not what you're after? :)
              2011 | 2010 | 2009 | .....
              Documents for selected year
              ......document x .....
              ......document x .....
              ......document x .....
              ......document x .....
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35724606
Both queries and outputs can reside on the same page -- that's what I was wondering.

So, I think I can set up a CFIF statement:

<CFIF val(selectedYear)>

<!--- display documents for a given year --->

<!--- begin output from query getDocumentsandFiles; list Document Title, Author, Abstract, Publication Date; and CEP Files associated with those documents; and download links to those files; within a given year selected by url.selectedYear --->
<cfoutput query="getDocumentsandFiles" group="DocumentTitle">


<!--- output group on DocumentID --->
<cfoutput group="DocumentID">


<!--- output Document Thumbnail Image on DocumentID --->
<cfif DocumentImage NEQ "">
<div class="float-left-img"><img src="/document_image.cfm?Attachment=#URLEncodedFormat(DocumentImage)#" class="border1" /></div>
</cfif>

<!--- output Document metadata on DocumentID --->
<h3>#DocumentTitle#</h3>
<div class="documentText">
<em>Author(s):</em> <span class="black">#DocumentAuthor#</span><br />
<em>Published:</em> <span class="black">#DateFormat(DocumentPublicationDate, "mmmm d, yyyy")#</span>
#DocumentAbstract#


<cfif getDocumentsandFiles.FileName NEQ "">
<h4>Download files:</h4>

<!--- output of query filename --->
 <cfoutput group="FileName">
 
<a href="/cfcontent_file.cfm?Attachment=#URLEncodedFormat(FileName)#">#FileLinkText#</a>
(#ucase(FileExtension)# format, #byteConvert(FileSize, "KB")#)

 <span class="hideme" class="nounderline"><a href="javascript:void(0)" class="nounderline">*</a></span>
 
              <span class="showme">Direct link:
              <a href="/cfcontent_file.cfm?Attachment=#URLEncodedFormat(FileName)#">http://www.cep-dc.org/cfcontent_file.cfm?Attachment=#URLEncodedFormat(FileName)#</a>
              </span>

<br />

<!--- /output of query filename --->
</cfoutput>
</cfif>

<!--- /output group on DocumentID --->
    </cfoutput>
   
<!--- /output from query getDocumentsandFiles --->
</cfoutput>

<CFELSE>

<!--- display the years --->
<cfoutput query="getDocumentYears">
<p><a href="index.cfm?selectedYear=#PublicationYear#">#PublicationYear#</a></p>
</cfoutput>

</CFIF>

I will try that and let you know what I get.

Eric
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 2000 total points
ID: 35724633
I was thinking show both at the same time. Though I'd probably show the available years in drop down list. Just to preserve space.




0
 
LVL 52

Expert Comment

by:_agx_
ID: 35724712
Either works, but the page seems a little empty when just displaying the years :)
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35725112
I think you're right.

I think I will add the dropdown menu to the navigation bar above the main area of the page.

Working on this. More later this evening. E
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35726122
_agx_,

I've got it basically working:

http://www.cep-dc.org/publications/index.cfm?selectedYear=2008

I'm going to do a little more testing tomorrow then close this question. The WHERE clause

WHERE  year(D.DocumentPublicationDate) = <cfqueryparam value="#url.selectedYear#" cfsqltype="cf_sql_integer">

and the year function that you suggested are working great. Thank you for your help! I'll wrap this up tomorrow.

Eric
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35729549

Looks nice guys, good job.  

Eric, if someone clicks a year, when the page refreshes, why don't you highlight the selected year with a yellow background.. or something like that..   Would look nice and be clear to the user what they are seeing..

Just an idea..


(no points pls, seriously)
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 35731464
gdemaria, I had the same thought.

I am working on that. There are a couple of adjustments I need to make in this application ... but I am gonna open a new question if I cannot resolve those adjustments myself.

Thank you; hope you're well.

Eric
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
ID: 35731470
Thank as always to _agx_.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month20 days, 8 hours left to enroll

868 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