Flat File Report from Detail Table

[aimskee]

Can someone clue me in for an SQL or efficient way to extract detail records and place them along a horizontal continuum for a report? I have to do calculations on multiple review_dates and time elapsed between each review. right now it's in a table like such: The dates have to go from farthest away to recent on report and only the latest 4 detail records; over time there could be dozens per client.
client review_date
1 10/25/2005
1 11/15/2005
1 12/2/2005
2 9/16/2005
2 9/28/2005

etc.
The report, however, has to look like this:
btwn1&2 btwn2&3
Client Reviewdate1 Reviewdate2 diff ReviewDate3 diff
1 10/25/2005 11/15/2005 26 12/2/2005 17

thanks for any guidance out there!

 

[MajP]

This is how I did it, not eloquent but works. I added a field called event number to my table. You can probably write a query to do this, but I am a better programmer then query writer. So I wrote a quick piece of code to add an event number for each person's event. However, the last event is 1, the next to last is 2, etc. I added spaces for visibility
My table looks like this

ID  eventnumber date
1	6	10/1/2005
1	5	10/20/2005
1	4	11/10/2005
1	3	12/10/2005
1	2	1/1/2006
1	1	1/1/2006

2	4	1/11/2005
2	3	2/11/2005
2	2	3/10/2005
2	1	3/15/2005

3	2	3/3/2005
3	1	12/31/2005

Then I made a cross tab query

TRANSFORM First(tblDates.date) AS FirstOfdate
SELECT tblDates.intClientID
FROM tblDates
GROUP BY tblDates.intClientID
PIVOT tblDates.eventnumber In (4,3,2,1);

Now each event is in columns and you can make another query
that calculates the difference.

 

[aimskee]

MajP,
This sounds doable. Eloquent can take a day off. I'll give this procedure a try. I haven't been programming too much lately and your helpful tip is highly appreciated.
Aimskee

 

[MajP]

aimskee,
Here is a solution that does not require any code. It took me 4 queries to do it though. I am a sql dummy, so someone smart on this site could probably do it all in one.

Step 1: Make a non equal query that returns each date and the dates that are equal to or greater than it for each ID. The most recent date returns itself, the next most recent returns itself and the most recent date, etc.

QryDatesAfter:

SELECT tblDates.intClientID, tblDates.date AS [Date], tblDates_1.date AS JuniorDate
FROM tblDates, tblDates AS tblDates_1
WHERE (((tblDates.date)<=[tblDates_1].[date]) AND ((tblDates_1.intClientID)=[tblDates].[intClientID]))
ORDER BY tblDates.intClientID, tblDates.date DESC , tblDates_1.date DESC;

step 2: Do an aggregate query to count how many dates are greater than or equal to a given date. The most recent has 1, the second 2, the 10th has 10, etc. Return the ones that have 1,2,3or 4 dates after them. Corresponding to the last, next to last, next next to last etc.

qryCountAfter:

SELECT qryDatesAfter.intClientID, qryDatesAfter.Date, Count(qryDatesAfter.JuniorDate) AS CountOfJuniorDate
FROM qryDatesAfter
GROUP BY qryDatesAfter.intClientID, qryDatesAfter.Date
HAVING (((Count(qryDatesAfter.JuniorDate)) Between 1 And 4))

step 3: Build the crosstab

tblDates_Crosstab:

TRANSFORM Min(qryCountAfter.Date) AS MinOfDate
SELECT qryCountAfter.intClientID
FROM qryCountAfter
GROUP BY qryCountAfter.intClientID
ORDER BY qryCountAfter.intClientID
PIVOT qryCountAfter.CountOfJuniorDate In (1,2,3,4)

step 5: Calculate the Period in between 1 and 2, and 3 and 4

qryPeriodBetween:

SELECT tblDates_Crosstab.intClientID, tblDates_Crosstab.[1], tblDates_Crosstab.[2], DateDiff("d",[tblDates_Crosstab]![2],[tblDates_Crosstab]![1]) AS [Period1&2], tblDates_Crosstab.[3], tblDates_Crosstab.[4], DateDiff("d",[tblDates_Crosstab]![4],[tblDates_Crosstab]![3]) AS [Period3&4]
FROM tblDates_Crosstab

the fields "juniorDate" is confusing because it really is the date that is greater, but I never went back to fix it.

 

[aimskee]

MajP,
Thanks for your sql based solution. I have not tried it yet, but I tried your first solution and it worked perfectly. I needed to first add an eventcounter field to my table and then created a procedure to number the event field. I put the table in clientid field order ascending, with eventdate data descending. Then ran procedure to put desc. numbers in this field because there will continuously be new event dates added by client id, so I'll have to run this procedure on start up of app then I run the queries with the pivot table before the rpt. Then I created a report from the pivot table results.
Getting back to your newest thing, once my brain is rejuventated (cd. take awhile!) I'll try this and see if it's easier.
Thanks again for all your help,
Aimskee