Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Selecting/Using data from first member of a group Post Reply Post New Topic
Page  of 2 Next >>
Author Message
cbaldwin
Groupie
Groupie


Joined: 09 Apr 2014
Online Status: Offline
Posts: 81
Quote cbaldwin Replybullet Topic: Selecting/Using data from first member of a group
    Posted: 31 Mar 2017 at 3:21am
I would like to select for only the first DATE of an ACCT group and use the associated NUMBER.

Sample Data:

Acct   Date            Number
1      01-01-2016      5
1      03-06-2016      12
1      07-12-2016      2
2      02-15-2016      4
2      05-16-2016      10
3      04-16-2016      25

Of the data set above i am interested in selecting/using only the following data. My particular interest is using the NUMBER field data in a crosstab.

Acct   Date            Number
1      01-01-2016      5
2      02-15-2016      4
3      04-16-2016      25

I do not know if i can accomplish this with my crystal select statement or with a crystal formula or a select statement in SQL?

Any help is appreciated.

Chuck
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 Mar 2017 at 3:40am
i would use sql for that but it partly depends on if you are really wanting
1) the first record or the minimum date record
2) can there ever be an account with two days that are the same minimum date with two different 'numbers'
IP IP Logged
cbaldwin
Groupie
Groupie


Joined: 09 Apr 2014
Online Status: Offline
Posts: 81
Quote cbaldwin Replybullet Posted: 31 Mar 2017 at 3:45am
It is actually a Date/Time field. I used Date for simplicity of concept. I am interested in the NUMBER associated with the minimum DATETIME for the ACCOUNT.
IP IP Logged
cbaldwin
Groupie
Groupie


Joined: 09 Apr 2014
Online Status: Offline
Posts: 81
Quote cbaldwin Replybullet Posted: 31 Mar 2017 at 3:48am
The same DATE would never be associated with the ACCOUNT twice.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 Mar 2017 at 4:05am
maybe this...?
select t1.* from table1 t1
join (select Acct,MIN(Date) as MinDate from table1 group by Acct) as t2 on t1.acct=t2.Acct and t1.date = t2.MinDate

Edited by DBlank - 31 Mar 2017 at 4:06am
IP IP Logged
cbaldwin
Groupie
Groupie


Joined: 09 Apr 2014
Online Status: Offline
Posts: 81
Quote cbaldwin Replybullet Posted: 31 Mar 2017 at 4:12am
Thanks for the input. I will try to digest that and give it an attempt.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 Mar 2017 at 4:16am
just joining the table to itself but on the 'second version' of the table doing a group by with the min date per acct. this limits the data rows to what you wanted. The join back gets you the unique 'number' value for that date from that acct.

Edited by DBlank - 31 Mar 2017 at 4:17am
IP IP Logged
cbaldwin
Groupie
Groupie


Joined: 09 Apr 2014
Online Status: Offline
Posts: 81
Quote cbaldwin Replybullet Posted: 31 Mar 2017 at 4:33am
This is what i tried using actual table names and fields. It was not happy. Input?

SELECT

dd.*

FROM Donor_Draw dd

JOIN (select donor_id,MIN(draw_start_datetime) as MinDate from Donor_Draw group by donor_id) as dd2 on dd.donor_id=dd2.donor_id and dd.draw_start_datetime = dd2.MinDate
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 Mar 2017 at 5:01am
what exactly does 'not happy' mean :)
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 Mar 2017 at 5:02am
and where did you do this?
this is a sql select statement, meaning it should be used as a view or stored proc in sql as the data source, or you can use it as a crystal command object as your data source
IP IP Logged
Page  of 2 Next >>
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.031 seconds.