Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Trying to pull data that meets specific criteria Post Reply Post New Topic
Author Message
Tonyak74
Newbie
Newbie
Avatar

Joined: 24 Apr 2013
Online Status: Offline
Posts: 28
Quote Tonyak74 Replybullet Topic: Trying to pull data that meets specific criteria
    Posted: 25 Apr 2013 at 7:26am
I am running a work order report for equipment with several different inspection types.
I am trying to pull only the equipment were the last 3 inspection meet the specific criteria. (Equipment may have 20 or more different inspections associated to it, I only want the one where the last 3 inspection were missing(service.servicecode = 20) so that the equipment can be removed from inventory)
Initially I was trying to go to select expert and just report the work orders where service.servicecode = 20 and was then going to supress records that count was less then 3.
This did not work becase I was only looking at that service code not the last 3 service codes..
New to crystal reports.. Any help would be much appreciated..
Thanks,
TK
IP IP Logged
Gurbs
Senior Member
Senior Member
Avatar

Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
Quote Gurbs Replybullet Posted: 25 Apr 2013 at 10:49pm
Are the services inspections on fixed dates, or do they occur random? If they occur every week for example, you could say that the service date has to be bigger then currentdate-21
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 26 Apr 2013 at 3:48am
How are your SQL skills?  Unfortunately, this is not the type of filter that can be handled well using Crystal functionality.  So, you're going to have to either write a Command, which is just a SQL Select statement using the syntax from your database, or create a Stored Procedure in your database that will return the data that you're looking for.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
Tonyak74
Newbie
Newbie
Avatar

Joined: 24 Apr 2013
Online Status: Offline
Posts: 28
Quote Tonyak74 Replybullet Posted: 26 Apr 2013 at 5:06am
The equipment inspections are on a set schedule usually a weekly, monthly, semi-annual or annual. However, I have over 200 different types of equipment and the schedules for each type varies.
 
I am reporting on over 200,000 peices of equipment
How I have narrowed it down so far.
When Service.servicecode = 20 it does change the equipment.equipmentstatkey to 9. So in select expert I have it so that I am now only displaying equipment where the  equipment.equipmentstatkey  = 9 (This took my report from over 5,000 pages down to 80 Tongue)
I still would like to only see equipment where the last 3 inspections done used service.servicecode = 20 so any suggestions are very much appreciated.
 
IP IP Logged
Tonyak74
Newbie
Newbie
Avatar

Joined: 24 Apr 2013
Online Status: Offline
Posts: 28
Quote Tonyak74 Replybullet Posted: 26 Apr 2013 at 5:15am

SQL skills are not good, but hopefully I have a colleague that will be able to assist..

Thank you...
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 26 Apr 2013 at 7:44am
Here some possible logic to use in nested select statements:
Select Equipment ID and any other fields needed for the report from
(
  Select Equipment ID, sum(case servicecode = 20, 1, 0) from
  (
    Select Equipment ID, Service Date from
    (
      Select Equipment ID, Service Date from service order by Service Date descending.
    )
    where rownum <= 3
  )
  having sum(case servicecode = 20, 1, 0) = 3
) join to any other tables you need to get the data.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.