Select top N without SQL?
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21829
Printed Date: 02 May 2024 at 2:51am
Topic: Select top N without SQL?
Posted By: aliryder
Subject: Select top N without SQL?
Date Posted: 23 Dec 2015 at 2:52am
Hi there, I have a complex problem that I am trying to solve.
I have a report I am trying to generate that has 14 different tables that we are pulling from, and I can't get rid of any of them. Now most of them I don't actually need data from, so if I did a SQL query that would make things more streamlined, but the joins are a pain in the butt with so many tables. So I'm wondering if there's another way to accomplish what I want.
Each of our clients periodically fills out a survey (let's pretend it is a satisfaction survey). Clients periodically see staff, and what I want to do is to be able to compare how the different staff are doing.
What I would like to do is select only the most recent survey that each client has completed. Then, for each staff, I want to be able to average the results and display a chart or a table that compares the results from staff to staff.
I can do the first one easily by, say, grouping on client and displaying a summary field that's something like maximum(surveydate). Or I can suppress certain surveys that meet a formula.
I can do the second one easily by, say, displaying average(surveyresults,staffname), but that includes all surveys, not just most recent.
I can't do both at the same time, as far as I know, you can't get a maximum of averages, and if you just suppress data it's still pulled in the chart.
I was thinking about going about it a different way, to only look at surveys that happened in the past, say, month, but this is a challenge as well because the surveys are not completed on regular intervals. Sometimes you could have two done in the same month or then none for the next six months.
Anyone have any bright ideas?
|
Replies:
Posted By: hilfy
Date Posted: 23 Dec 2015 at 8:43am
Which version of Crystal are you using and what type of database are you connecting to?
You're not going to be able to do this by just using tables. You'll probably have to write a Command, which is just a SQL Select statement, to get the data.
Here is information about how to use commands: http://scn.sap.com/community/crystal-reports/blog/2015/04/01/best-practices-when-using-commands-with-crystal-reports
If you go to Database >> Show SQL Query in Crystal, copy the query and paste it here, I should be able to help you get the SQL that you need.
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: aliryder
Date Posted: 04 Jan 2016 at 2:59am
Hi hilfy, thanks for offering to help. Sorry I didn't respond, I was on holidays and couldn't access the computer that held the code I was working on.
Here is the code, and an explanation follows:
SELECT "HIFIS_People_CaseWorkers"."LastName", "HIFIS_People_CaseWorkers"."FirstName", "HIFIS_Organizations"."Name", "HIFIS_Cases"."DateCaseClosed", "HIFIS_Cases"."CaseID", "HIFIS_SPDAT_ScoringSummary"."TotalScore"
FROM ("HIFIS_prod"."dbo"."HIFIS_SPDAT_ScoringSummary" "HIFIS_SPDAT_ScoringSummary" INNER JOIN ((((("HIFIS_prod"."dbo"."HIFIS_Client_Services" "HIFIS_Client_Services_CM" INNER JOIN (("HIFIS_prod"."dbo"."HIFIS_Cases" "HIFIS_Cases" INNER JOIN "HIFIS_prod"."dbo"."HIFIS_Services" "HIFIS_Services_CM" ON "HIFIS_Cases"."ServiceID"="HIFIS_Services_CM"."ServiceID") INNER JOIN "HIFIS_prod"."dbo"."HIFIS_People" "HIFIS_People_CaseWorkers" ON "HIFIS_Cases"."CaseWorkerID"="HIFIS_People_CaseWorkers"."PersonID") ON "HIFIS_Client_Services_CM"."ServiceID"="HIFIS_Services_CM"."ServiceID") INNER JOIN "HIFIS_prod"."dbo"."HIFIS_Clients" "HIFIS_Clients" ON "HIFIS_Client_Services_CM"."ClientID"="HIFIS_Clients"."ClientID") INNER JOIN "HIFIS_prod"."dbo"."HIFIS_Client_Services" "HIFIS_Client_Services_SPDAT" ON "HIFIS_Clients"."ClientID"="HIFIS_Client_Services_SPDAT"."ClientID") INNER JOIN "HIFIS_prod"."dbo"."HIFIS_Services" "HIFIS_Services_SPDAT" ON "HIFIS_Client_Services_SPDAT"."ServiceID"="HIFIS_Services_SPDAT"."ServiceID") INNER JOIN "HIFIS_prod"."dbo"."HIFIS_SPDAT_Intake" "HIFIS_SPDAT_Intake" ON "HIFIS_Services_SPDAT"."ServiceID"="HIFIS_SPDAT_Intake"."ServiceID") ON "HIFIS_SPDAT_ScoringSummary"."IntakeID"="HIFIS_SPDAT_Intake"."IntakeID") INNER JOIN "HIFIS_prod"."dbo"."HIFIS_Organizations" "HIFIS_Organizations" ON "HIFIS_Services_CM"."OrganizationID"="HIFIS_Organizations"."OrganizationID"
WHERE "HIFIS_Cases"."DateCaseClosed" IS NULL
ORDER BY "HIFIS_Organizations"."Name"
The HIFIS_Clients database is the core, it has a list of clients.
When a service is provided, an item in HIFIS_Services is created. Now there are two duplicates of this table in my query, HIFIS_Services_CM and HIFIS_Services_SPDAT and that's because I'm looking at two things. I actually want to look up the SPDAT, for clients that have an open case. But both a case (that's what _CM means) and SPDAT are both considered services. Both the HIFIS_Services table is linked to HIFIS_Clients via HIFIS_Client_Services (and again there's a duplicate of this).
Over on the case side, we're showing the organization providing the service (HIFIS_Organization) which I want to filter by, only displaying cases of one organization. There's the HIFIS_Cases table which has more detail, and it connects to HIFIS_People_CaseWorkers. These are the staff I previously mentioned I want to compare results from.
On the SPDAT side, there's a table called SPDAT_Intake which I only want to see the most recent of, but I'm not sure where I want to SELECT TOP 1 of the table, since each HIFIS_SPDAT_Intake has a corresponding Services and Client_Services entry, but there may be other services in those tables that are not SPDAT_Intake entries, if that makes any sense. Finally, the value from the SPDAT is in SPDAT_ScoringSummary.
Thanks in advance for your help!
|
Posted By: hilfy
Date Posted: 04 Jan 2016 at 3:09am
What version of Crystal are you using and what type of database (e.g., SQL Server, Oracle, etc.) are you connecting to?
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: hilfy
Date Posted: 04 Jan 2016 at 3:36am
Also, since you're only pulling data where HIFIS_Cases.DateCaseClosed is null, what date field determines the "most recent" survey?
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: aliryder
Date Posted: 04 Jan 2016 at 4:15am
|
Originally posted by hilfy
What version of Crystal are you using and what type of database (e.g., SQL Server, Oracle, etc.) are you connecting to?
-Dell
OLE DB (ADO) using SQL Server Native Client 11.0
Crystal Reports 2013 Support Pack 1
Version 14.1.1.1036
|
Posted By: aliryder
Date Posted: 04 Jan 2016 at 4:17am
|
Originally posted by hilfy
Also, since you're only pulling data where HIFIS_Cases.DateCaseClosed is null, what date field determines the "most recent" survey?
-Dell
There are actually two, which is why I don't know where we would be selecting the top record.
HIFIS_Services_SPDAT.DateStart
HIFIS_SPDAT_Intake.StartDateTime
Both these values are identical.
|
Posted By: hilfy
Date Posted: 04 Jan 2016 at 4:32am
Which of the two tables has the fewest number of records?
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: aliryder
Date Posted: 04 Jan 2016 at 4:41am
|
Originally posted by hilfy
Which of the two tables has the fewest number of records?
-Dell
HIFIS_SPDAT_Intake.StartDateTime
|
Posted By: hilfy
Date Posted: 04 Jan 2016 at 5:22am
I think I have the correct query here. I'm using a "With" command to pull the most recent date for each service id into a cursor and then using that cursor to limit the data that's being pulled into the query.
With maxDates as --get the most recent date for each ServiceID
(
select ServiceID, max(StartDateTime) maxDate
from HIFIS_prod.dbo.HIFIS_SPDAT_Intake
group by ServiceID
)
SELECT
People_CaseWorkers.LastName,
People_CaseWorkers.FirstName,
Organizations.Name,
Cases.DateCaseClosed,
Cases.CaseID,
ScoringSummary.TotalScore
FROM HIFIS_prod.dbo.HIFIS_Clients Clients
INNER JOIN HIFIS_prod.dbo.HIFIS_Cases Cases
ON Clients.ClientID = Cases.ClientID
Inner Join maxDates
on Cases.ServiceID = maxDates.ServiceID
--Get only the most recent start date through the join to maxDates
INNER JOIN HIFIS_prod.dbo.HIFIS_Services Services_SPDAT
ON maxDates.ServiceID = Services_SPDAT.ServiceID
and maxDates.maxDate = Services_SPDAT.DateStart
INNER JOIN HIFIS_prod.dbo.HIFIS_Services Services_CM
ON maxDates.ServiceID = Services_CM.ServiceID
INNER JOIN HIFIS_prod.dbo.HIFIS_Organizations Organizations
ON Services_CM.OrganizationID = Organizations.OrganizationID
INNER JOIN HIFIS_prod.dbo.HIFIS_People People_CaseWorkers
ON Cases.CaseWorkerID = People_CaseWorkers.PersonID
INNER JOIN HIFIS_prod.dbo.HIFIS_Client_Services Client_Services_CM
ON maxDates.ServiceID = Client_Services_CM.ServiceID
INNER JOIN HIFIS_prod.dbo.HIFIS_Client_Services Client_Services_SPDAT
ON Clients.ClientID = Client_Services_SPDAT.ClientID
INNER JOIN HIFIS_prod.dbo.HIFIS_SPDAT_Intake Intake
ON Services_SPDAT.ServiceID = Intake.ServiceID
INNER JOIN HIFIS_prod.dbo.HIFIS_SPDAT_ScoringSummary ScoringSummary
ON SPDAT_Intake.IntakeID = ScoringSummary.IntakeID
WHERE Cases.DateCaseClosed IS NULL
ORDER BY Organizations.Name
If you have any other criteria that you need to filter the data, be sure to put that in the Where clause in SQL Server (not Crystal!) syntax instead of in the Select Expert.
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: aliryder
Date Posted: 04 Jan 2016 at 9:58am
|
Thanks a bundle, I was able to get it to work from there!
|
Posted By: hilfy
Date Posted: 04 Jan 2016 at 10:37am
Yay! I'm glad it worked for you!
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
|