Solved

Include only the most recent date in an already complex query.

Posted on 2008-06-09
9
153 Views
Last Modified: 2010-03-20
Does any one have any suggestions on how I can filter the following query so that if the ClientFileNo repeats that only the record with the most recent FundFromDate is added to the make table?  
I've tried MAX and Last on the FundFromDate but the ClientFileNo is still repeating.
Thanks in advance!

SELECT dbo_tblClients.ClientFileNo, "" AS CountyLkUpID, dbo_tblFunds.FundFromDate, dbo_tblFunds.FundSourceLkUpID, Count(dbo_tblClients.ClientFileNo) AS Mth, 0 AS Target, 0 AS YTD, 0 AS 1stQtr, 0 AS 2ndQtr, 0 AS 3rdQtr, 0 AS 4thQtr INTO tblMTClientsServedRpt
FROM dbo_tblClients INNER JOIN dbo_tblFunds ON dbo_tblClients.ClientFileNo = dbo_tblFunds.ClientFileNo
GROUP BY dbo_tblClients.ClientFileNo, dbo_tblFunds.FundFromDate, dbo_tblFunds.FundSourceLkUpID
HAVING (((dbo_tblFunds.FundFromDate) Between ([Forms]![frmRptsByRange]![txtFrom]) And ([Forms]![frmRptsByRange]![txtTo])));
0
Comment
Question by:BobRosas
  • 5
  • 4
9 Comments
 
LVL 19

Expert Comment

by:elimesika
ID: 21746830
try this
SELECT TOP 1 dbo_tblClients.ClientFileNo, "" AS CountyLkUpID, dbo_tblFunds.FundFromDate, dbo_tblFunds.FundSourceLkUpID, Count(dbo_tblClients.ClientFileNo) AS Mth, 0 AS Target, 0 AS YTD, 0 AS 1stQtr, 0 AS 2ndQtr, 0 AS 3rdQtr, 0 AS 4thQtr INTO tblMTClientsServedRpt

FROM dbo_tblClients INNER JOIN dbo_tblFunds ON dbo_tblClients.ClientFileNo = dbo_tblFunds.ClientFileNo

GROUP BY dbo_tblClients.ClientFileNo, dbo_tblFunds.FundFromDate, dbo_tblFunds.FundSourceLkUpID

ORDER BY dbo_tblClients.ClientFileNo, dbo_tblFunds.FundFromDate, dbo_tblFunds.FundSourceLkUpID DESC

HAVING (((dbo_tblFunds.FundFromDate) Between ([Forms]![frmRptsByRange]![txtFrom]) And ([Forms]![frmRptsByRange]![txtTo])));

Open in new window

0
 

Author Comment

by:BobRosas
ID: 21746937
Thank you for your quick response.  I apologize if my request wasn't very clear but instead of showing each ClientFileNo once it only shows the first ClientFileNo.  I'd like all records (which include many different ClientFileNo's) to show but have only the most recent date if the ClientFileNo repeats.  I've made the changes you suggested and attached them to make sure I didn't mess something up but the code only produces 1 record.
Thanks again.

SELECT TOP 1 dbo_tblClients.ClientFileNo, "" AS CountyLkUpID, dbo_tblFunds.FundFromDate, dbo_tblFunds.FundSourceLkUpID, Count(dbo_tblClients.ClientFileNo) AS Mth, 0 AS Target, 0 AS YTD, 0 AS 1stQtr, 0 AS 2ndQtr, 0 AS 3rdQtr, 0 AS 4thQtr INTO tblMTClientsServedRpt

FROM dbo_tblClients INNER JOIN dbo_tblFunds ON dbo_tblClients.ClientFileNo = dbo_tblFunds.ClientFileNo

GROUP BY dbo_tblClients.ClientFileNo, dbo_tblFunds.FundFromDate, dbo_tblFunds.FundSourceLkUpID

HAVING (((dbo_tblFunds.FundFromDate) Between ([Forms]![frmRptsByRange]![txtFrom]) And ([Forms]![frmRptsByRange]![txtTo])))

ORDER BY dbo_tblClients.ClientFileNo, dbo_tblFunds.FundFromDate DESC , dbo_tblFunds.FundSourceLkUpID;

Open in new window

0
 
LVL 19

Expert Comment

by:elimesika
ID: 21748925
Can you send me the CREATE statement of your tables?
0
 

Author Comment

by:BobRosas
ID: 21752141
I apologize for being at a loss.  "CREATE statement"?  
The tables in the query are from a SQL backend that I link to.  Is that what you are asking?  They are not make tables and I don't have any CREATE statements.  Maybe that's why it's not working?
Thanks again!
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 19

Expert Comment

by:elimesika
ID: 21752173
OK , never mind , I will handle it myself later .....
0
 
LVL 19

Accepted Solution

by:
elimesika earned 250 total points
ID: 21776429
sorry for the delay

Please check the follwoing and see what results you got , I have omitted the HAVING to see if it is the cause of the problem.
SELECT TOP 1 dbo_tblClients.ClientFileNo, "" AS CountyLkUpID, dbo_tblFunds.FundFromDate, dbo_tblFunds.FundSourceLkUpID, Count(dbo_tblClients.ClientFileNo) AS Mth, 0 AS Target, 0 AS YTD, 0 AS 1stQtr, 0 AS 2ndQtr, 0 AS 3rdQtr, 0 AS 4thQtr INTO tblMTClientsServedRpt

FROM dbo_tblClients INNER JOIN dbo_tblFunds ON dbo_tblClients.ClientFileNo = dbo_tblFunds.ClientFileNo

GROUP BY dbo_tblClients.ClientFileNo, dbo_tblFunds.FundFromDate

ORDER BY dbo_tblClients.ClientFileNo, dbo_tblFunds.FundFromDate DESC

Open in new window

0
 

Author Comment

by:BobRosas
ID: 21780534
Thank you for sticking with me.  
I tried the above code as is and got the error...
"You tried to execute a query that does not include the specified expression 'FundSourceLkUpID' as part of an aggregate function."

I tried leaving the field in the query but just unchecking it so it didn't show in the results and it will run but it only gives me one record again.  

I thought that what I was trying to do was not that complicated and should work in one query but maybe not.  I'm trying to see if I can make it work with 2 querys.  Any input is appreciated.
Thanks!
0
 
LVL 19

Expert Comment

by:elimesika
ID: 21786287
HI

I will need more info to proceed :

1) tables names + column names and types of each table

2) Primary key of each table

If you send me that (or the sql script that create those table), I will be able to test it and give you a complete solution.

Thanks
0
 

Author Comment

by:BobRosas
ID: 21794482
No need.
By separating into 2 queries and not grouping on dbo_tblFunds.FundSourceLkUpID until the 2nd query (after I filtered out the 1st record for each ClientNo), I was able to get the results I needed.
Thank you so much for all your help.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now