• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

Select from a select sql server 2005 coldfusion report builder

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
easkew
Asked:
easkew
  • 3
  • 3
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
adlink_laCommented:
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
 
easkewAuthor Commented:
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 expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
easkewAuthor Commented:
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
 
easkewAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now