Selecting/Using data from first member of a group
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22281
Printed Date: 05 May 2024 at 5:18am
Topic: Selecting/Using data from first member of a group
Posted By: cbaldwin
Subject: Selecting/Using data from first member of a group
Date 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 |
Replies:
Posted By: DBlank
Date 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' |
Posted By: cbaldwin
Date 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. |
Posted By: cbaldwin
Date Posted: 31 Mar 2017 at 3:48am
| The same DATE would never be associated with the ACCOUNT twice. |
Posted By: DBlank
Date 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 |
Posted By: cbaldwin
Date Posted: 31 Mar 2017 at 4:12am
| Thanks for the input. I will try to digest that and give it an attempt. |
Posted By: DBlank
Date 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. |
Posted By: cbaldwin
Date 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 |
Posted By: DBlank
Date Posted: 31 Mar 2017 at 5:01am
| what exactly does 'not happy' mean :) |
Posted By: DBlank
Date 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 |
Posted By: cbaldwin
Date Posted: 31 Mar 2017 at 5:05am
|
Failed to retrieve data from database.
ORA 00905 missing keyword |
Posted By: DBlank
Date Posted: 31 Mar 2017 at 5:06am
| what is your data source type and where did you try to use this? |
Posted By: cbaldwin
Date Posted: 31 Mar 2017 at 5:15am
|
We have an oracle database.
I inserted the SQL script into a Crystal Command table. When i attempt to save the Crystal Command table i get this error. |
Posted By: DBlank
Date Posted: 31 Mar 2017 at 5:18am
|
Sorry, I assumed you were using sql.
I don't use oracle but I know the syntax is different. Anyone else want to jump in for the correct syntax on this (assuming the logic still applies to that source type)? 
|
Posted By: cbaldwin
Date Posted: 31 Mar 2017 at 5:35am
| Thank you very much for your time and effort. |