Solved

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

Posted on 2008-06-09
9
155 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to send multiple emails at the same time in PHP 12 57
How to join on ID, with prefix? 15 58
SQL Server 2012 rs - Field1 and Field2 add 1 to Total 4 18
SQL Help 27 40
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

805 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