?
Solved

Select from a select sql server 2005 coldfusion report builder

Posted on 2008-10-04
9
Medium Priority
?
446 Views
Last Modified: 2013-12-24
I am trying to create a report using cold fusion report builder. My query so far extracts the correct records but there are multiple records for each pid. I need to extract one reocrd for each pid, the one with the most recent date. I was hoping there was some way I could query the results of the query I have created. Here is the query so far:

SELECT   distinct clientlist.pid as apid, security.name as aname, security.unitsize, securityvalue.securityvalue, securityvalue.valuationmethod, securityvalue.nasdsource, securityvalue.nasddescription, securityvalue.valuedate
FROM      dbo.ClientList, dbo.Client, dbo.Security, dbo.SecurityValue
WHERE     dbo.ClientList.ClientID = dbo.Client.ClientID
  AND     dbo.ClientList.SecurityID = dbo.Security.SecurityID
  AND     dbo.Security.SecurityID = dbo.SecurityValue.SecurityID
  and Client.ClientName <> 'Full RASCO Valuation List' and (Client.Active = 1)
      and (Client.ReportType = 'NASD' or (Client.ReportType = 'SEMI'  and  #MONTH(now())# in (6,12)) or (Client.ReportType = 'QTR' and #MONTH(now())# in (3,6,9,12))) and security.lbrstatus is NULL and securityvalue.valuationmethod <> 'N/A' and securityvalue.valuationmethod <> 'REF' and ((securityvalue.valuationmethod = 'PAR' and security.closingdate is null and datediff("m",securityvalue.valuedate,#now()#) < 13) or (securityvalue.valuationmethod = 'PAR' and security.closingdate is not null and datediff("m",security.closingdate,#now()#) < 13) or (datediff("m",securityvalue.valuedate,#now()#) < 19))
      order by  security.name, securityvalue.valuedate desc
Thanks
0
Comment
Question by:easkew
[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
9 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22642981
If I am understanding the above correctly, the securityvalue table appears to be the one with dates, so if that table is the one with multiple values then this should work:
SELECT   clientlist.pid as apid, security.name as aname, security.unitsize, sv.securityvalue, sv.valuationmethod, sv.nasdsource, sv.nasddescription, sv.valuedate 
FROM      dbo.ClientList, dbo.Client, dbo.Security, (SELECT *, row_number() over (PARTITION BY SecurityID ORDER BY valuedate DESC) as svRow FROM dbo.SecurityValue) AS sv
WHERE     dbo.ClientList.ClientID = dbo.Client.ClientID
  AND     dbo.ClientList.SecurityID = dbo.Security.SecurityID
  AND     dbo.Security.SecurityID = sv.SecurityID AND sv.svRow = 1
  and Client.ClientName <> 'Full RASCO Valuation List' and (Client.Active = 1)
      and (Client.ReportType = 'NASD' or (Client.ReportType = 'SEMI'  and  #MONTH(now())# in (6,12)) or (Client.ReportType = 'QTR' and #MONTH(now())# in (3,6,9,12))) and security.lbrstatus is NULL and sv.valuationmethod <> 'N/A' and sv.valuationmethod <> 'REF' and ((sv.valuationmethod = 'PAR' and security.closingdate is null and datediff("m",sv.valuedate,#now()#) < 13) or (sv.valuationmethod = 'PAR' and security.closingdate is not null and datediff("m",security.closingdate,#now()#) < 13) or (datediff("m",sv.valuedate,#now()#) < 19))
      order by  security.name, sv.valuedate desc

Open in new window

0
 
LVL 5

Expert Comment

by:adlink_la
ID: 22643268
If you need to query the results you can use a CTE:

WITH x AS (
<Put your query here>
)

SELECT ...
FROM x

If you are using ColdFusion components you can also cache the query by defining the timespan that you want the query to be cached for in the CFQUERY tag:

<cfquery name="qMyCachedQuery" datasource="myDSN" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
    SELECT  ...
    FROM ...       
</cfquery>

Then you can query the cached query like:

<cfquery name="qMyQuery" dbtype="query">
    SELECT ...
    FROM  qMyCachedQuery
    WHERE ...
</cfquery>


0
 

Author Comment

by:easkew
ID: 22643877
mwvisa1 - I tried your solution but CF report builder would not save the query. Error 'row-number' is not a recognized function name.
also will the sv part be applied after the other selection criteria have been applied? if not the logic will not work for me. I need the value from the first record that matches all the criteria.

0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

Author Comment

by:easkew
ID: 22644012
Just to clarify, it isn't the value record with the most recent date I need, It is the value record with the most recent date after the elimination of any value records with unacceptable criteria done by

securityvalue.valuationmethod <> 'N/A' and securityvalue.valuationmethod <> 'REF' and ((securityvalue.valuationmethod = 'PAR' and security.closingdate is null and datediff("m",securityvalue.valuedate,#now()#) < 13) or (securityvalue.valuationmethod = 'PAR' and security.closingdate is not null and datediff("m",security.closingdate,#now()#) < 13) or (datediff("m",securityvalue.valuedate,#now()#) < 19))
0
 

Author Comment

by:easkew
ID: 22644479
Looks to me like I need to use partion but it doesn't seem to be an option. I am using sql server 2005 express but I do have a license. will installing the real thing mean I can use partion?

I tried in cold fusion and cold fusion report builder and neither would accept row_number() over or partion

any suggestions would be greatly appreciated

thanks
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22644880
That would be a nice method -- to answer your question, you would move the row_number portion out to the main query and then use that as a derived table to select * where row = 1.  But since that is not working, we can try a correlated query (query in where clause to match up record to max date from same query) OR an INNER JOIN on same query -- this would look a whole lot prettier using a common table expression.

Will it let you use this:
;WITH svCTE AS (
SELECT   distinct clientlist.pid as apid, security.name as aname, security.unitsize, securityvalue.securityvalue, securityvalue.valuationmethod, securityvalue.nasdsource, securityvalue.nasddescription, securityvalue.valuedate 
FROM      dbo.ClientList, dbo.Client, dbo.Security, dbo.SecurityValue 
WHERE     dbo.ClientList.ClientID = dbo.Client.ClientID
  AND     dbo.ClientList.SecurityID = dbo.Security.SecurityID
  AND     dbo.Security.SecurityID = dbo.SecurityValue.SecurityID
  and Client.ClientName <> 'Full RASCO Valuation List' and (Client.Active = 1)
      and (Client.ReportType = 'NASD' or (Client.ReportType = 'SEMI'  and  #MONTH(now())# in (6,12)) or (Client.ReportType = 'QTR' and #MONTH(now())# in (3,6,9,12))) and security.lbrstatus is NULL and securityvalue.valuationmethod <> 'N/A' and securityvalue.valuationmethod <> 'REF' and ((securityvalue.valuationmethod = 'PAR' and security.closingdate is null and datediff("m",securityvalue.valuedate,#now()#) < 13) or (securityvalue.valuationmethod = 'PAR' and security.closingdate is not null and datediff("m",security.closingdate,#now()#) < 13) or (datediff("m",securityvalue.valuedate,#now()#) < 19))
)
SELECT a.*
FROM svCTE a INNER JOIN (SELECT apid, MAX(valuedate) AS maxValuedate FROM svCTE GROUP BY apid) b
ON a.apid = b.apid AND a.valuedate = b.maxValuedate

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22644902
If not, try to create a stored procedure/view in SQL server that does either of the queries provided as your only CF code in query I can see is #now# and #MONTH(now())# and you can do this on SQL server with getdate() and Month(getdate()), respectively.

There is also a Quarter(getdate()) function you can play with there too that will tell you what quarter you are in given a date. :)

Then in your CF Report you just SELECT * FROM viewName OR EXEC sp_Name.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…
Suggested Courses

771 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