Solved

Select from a select sql server 2005 coldfusion report builder

Posted on 2008-10-04
9
436 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
9 Comments
 
LVL 59

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
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.

 

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 59

Accepted Solution

by:
Kevin Cross earned 500 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 59

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
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.
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…

777 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