?
Solved

Select from a select sql server 2005 coldfusion report builder

Posted on 2008-10-04
9
Medium Priority
?
467 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
  • 3
  • 3
7 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
What You Need to Know when Searching for a Webhost Provider
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
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…

864 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