Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Select from a select sql server 2005 coldfusion report builder

Posted on 2008-10-04
9
Medium Priority
?
453 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
The purpose of this video is to demonstrate how to insert an Iframe into 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 : Open Page or Post…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…

650 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