Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Find missing values in a range Post Reply Post New Topic
Author Message
rkylemyers
Newbie
Newbie
Avatar

Joined: 15 Jun 2012
Location: United States
Online Status: Offline
Posts: 1
Quote rkylemyers Replybullet Topic: Find missing values in a range
     Posted: 15 Jun 2012 at 1:00pm
Hi everyone. I have been on this all day and can't seem to figure this thing out. I have a list of Asset IDs. For the sake of this question let's say there's 5 of them. Now every ID is formatted the same way:
HHC01-0001
HHC01-0002
HHC01-0003
HHC01-0004
HHC01-0007

Now I was able to extract the HHC, 01, and 0001 which are important to other facets of the report (Grouping and what not). What I want to be able to do is take that last value (0001, 0002, etc.) and find the min and max to create a range. I want to be able to see what numbers have been skipped in the range. Meaning in my 5 IDs I have from 0001-0004, and I have 0007. I would like the report to pull back that 0005 and 0006 are available for use. Is this possible? I feel so close!
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 18 Jun 2012 at 3:48am
This is difficult to do unless you have the data somewhere - especially since you're grouping on this field.  Depending on your database, you might be able to create a stored procedure or function that returns all of the numbers in the range and left outer join from that to your data to get all of the numbers.  An alternative is to create a table that has all of the numbers and left join from that, but then the table has to be maintained.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 18 Jun 2012 at 3:51am
you need a table of all the numbers...or perhaps just numbers that you can left join on to find the missing values.
 
I guess you could do something like build a string with all the numbers between the min and max values and then as you encounter a number you would remove it from the string, then at the end you can display the values that remain.
 
I am not a 100% sure of how to go about this, and it wouldn't be pretty, and it would have alot of string manipulation in it....but it might be an idea.
 
a simpler solution would be to build a stored proc, find a table of numbers to left join against and 'fill in' / identify the missing numbers
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.