I think this is a Top Values question…. (Access 2002)

Topic

New Reply

Hi All!

I have a table that lists client names and sales calls to the client (the table actually includes all contacts to a client, but I am able to filter the table to get actual sales calls because there is a field for that (yay!)).

ClientID ClientName CallDate CallType
1 AAA company 1/1/2001 Sales
1 AAA company 3/1/2001 Info
1 AAA company 5/1/2001 Sales
2 BBB company 11/7/1999 Sales
2 BBB company 1/2/2001 Sales
2 BBB company 3/2/2001 Sales
2 BBB company 11/2/2002 Info
2 BBB company 12/2/2002 Sales

I can use Top Values to pull up the three most recent sales calls for a particular client, but what I’d like to do is pull up the three most recent sales calls for each client, like this:
ClientID ClientName CallDate CallType
1 AAA company 1/1/2001 Sales
1 AAA company 5/1/2001 Sales
2 BBB company 1/2/2001 Sales
2 BBB company 3/2/2001 Sales
2 BBB company 12/2/2002 Sales

I’d like to do this in as few queries as possible, because this is part of a “download” that goes into a backend.

Has anyone done this before? Is there an easy way to get started? I’m really stumped.

TIA!

Cecilia

Reply | Quote

Replies

Top Values (you can set the value to 3) should do the trick for you if you restrict the client to a single name, but that means you would need to run the query multiple times in some sort of loop and append the results to a temporary table. In your case that may work, as you are gong to download the data anyhow. You might look at the idea of a subquery as well – I haven’t tinker with your situation, but it seems like a possibility. I did play with a GroupBy query, but that didn’t seem to hold much promise.

Reply | Quote

Hi Wendell!

I don’t think running the query multiple times will work….I kinda really contrived the example to be a lot simpler than what I’m trying to do, but the number of customers in my table is in the thousands (not to mention that I have to select only customers that were active since 1999 in the query, further mucking up the works). I’ve also tried playing with the group by thing, it didn’t seem to help, I can still only do a single customer.

Any ideas on how I would set up a subquery for this? This is one idea that I saw elsewhere on this board, but I couldn’t figure out how to start it.

Another idea I had was to set up a cur field (to count down the current record for each customer), but I haven’t come up with a formula for that either. Maybe I’m just pooped and fresh out of ideas

Reply | Quote

Hi Cecilia,
I seem to be stuck on this at the moment – it seems to me it’s a trick that solves this problem in SQL, but I haven’t been able to find it. I’ll keep digging, but perhaps someone else can come up with it. I played with a subquery within a subquery, trying to get three – it’s fairly easy to get the top (most recent) record for each customer but that’s as far as I get.

Reply | Quote

You can do this either by using a subquery or creating a somewhat convoluted user-defined function. To use subquery (simpler approach) I first had to create a select distinct (unique values) query to select unique ClientID’s from main table. This query is joined to main table on ClientID field in query that will select top 3 CallDates for each client. SQL for Select Distinct query:

SELECT DISTINCT Table1.ClientID AS Client
FROM Table1;

This query is named “qryClientID”. Note use of alias for ClientID field. SQL for 2nd query:

SELECT Table1.ClientID, Table1.ClientName, Table1.CallDate, Table1.CallType
FROM Table1 INNER JOIN qryClientID ON Table1.ClientID = qryClientID.Client
WHERE (((Table1.CallDate) In (Select Top 3 [CallDate] From Table1 Where [ClientID]=[Client] Order By [CallDate] Desc)) AND ((Table1.CallType)=”Sales”))
ORDER BY Table1.ClientName, Table1.CallDate DESC;

This query will select top 3 CallDates in descending order for each ClientID in Table1. To select different number modify TOP predicate to specify different value. When I tried to do this without join to the Select Distinct query did not work, even if I aliased ClientID; you need some way to distinguish the ClientID used in subquery criteria from ClientID in main query if main query does not involve 2nd table joined on this field. So I resorted to this trick. You may be able to adapt this example for your project.

HTH

Reply | Quote

Okay, here’s how to do this with ONE Query….. (I must admit, I had fun doing this….I knew it was possible, I just had to blow a few cobwebs out of my head to remember how!)

Okay, before I post the SQL, here’s what I did. First, I added a Primary Key field. Just an AutoNumber. I’m sure your ‘real’ table structure has a key field for each ‘call’, if it doesn’t, it should. Then, I used a little used SQL feature which lets you put SQL within SQL. (Not an Union Query, but an SQL subquery. SQL Subqueries let you compare data in another SQL statement, without having to have another stored query to do so. Look for the Keyword ‘In’ and also ‘Subqueries’ (Use a subquery to define a field of define criteria for a field)(Access 97 help files are the best place to look for this stuff). ‘In’ is just one of the methods you can build a subquery with.)

Anyhow, I built a table just like your example, calling it tblCalls. Then I added the AutoNumber field called PKey. Then this SQL produces what you want:

SELECT T1.ClientID, T1.ClientName, T1.CallDate, T1.CallType
FROM tblCalls AS T1
WHERE T1.PKey In
(SELECT TOP 3 PKey As CheckKey
FROM tblCalls
WHERE (T1.ClientID=tblCalls.ClientID) AND (tblCalls.CallType=”Sales”)
ORDER BY tblCalls.CallDate DESC)
ORDER BY T1.ClientID,T1.CallDate DESC;

If you tear this SQL apart, you’ll see that the ‘internal’ SQL statement (the subquery) lists the top three PKey’s by calldate (the OrderBy in the subquery), and where the Calltype is “Sales” and the ClientID matches the ClientID from the primary query. Note, to pull that off, you have to Alias the Primary Query’s From clause, so it doesn’t get confused.

Also note, that this does not use grouping. There is no need to. You are already ‘grouping’ by only pulling up records that match the top 3.

Essentially, what is happening when this query runs, is that when it starts, the JET engine is going to kick the subquery in, with the first ClientID and come up with the first 3 PKey’s for that ClientID (which are Sales) (if there aren’t three, you get however many there are.). It should ‘cache’ those values (not entirely sure how JET is working internally on this kind of stuff), so that when it compares the records in the Primary query to the In statement, it determines if the records qualify or not.

Anyhow, thanks for the fun problem tonight….I needed it!

Reply | Quote

>>Okay, here’s how to do this with ONE Query….. (I must admit, I had fun doing this….I knew it was possible, I just had to blow a few cobwebs out of my head to remember how!)

Okay, before I post the SQL, here’s what I did. First, I added a Primary Key field. Just an AutoNumber. I’m sure your ‘real’ table structure has a key field for each ‘call’, if it doesn’t, it should. <<

Well, it's late, but I just had to tell you, I read your post and was laughing completely out loud! It's just way TOO familiar!

I can tell you right off that there is no real primary key for this table. Maybe it should, but hte bigger point is that this is from a mainframe database that I have no control over (I work for the gov't, I'm used to having no control over things *grin*).

I'm going to try this first thing tomorrow, if I get anywhere I will post results (it sounds exactly like what I'm looking for, whether I can do it or not is the bigger question), but in any case, I severely need to thank you ALL for your help!

Good nite (for now),

Reply | Quote

I hear ya on the legacy stuff. Well, government run/legacy….same thing! .

Here’s the SQL for the same data, I copied tblCalls to tblCalls2 and removed the PKey (Autonumber) field. This SQL does the same thing then:

SELECT T1.ClientID, T1.ClientName, T1.CallDate, T1.CallType
FROM tblCalls2 AS T1
WHERE T1.CallDate In
(SELECT TOP 3 CallDate
FROM tblCalls2
WHERE (T1.ClientID=tblCalls2.ClientID) AND (tblCalls2.CallType=”Sales”)
ORDER BY tblCalls2.CallDate DESC) And CallType=”Sales”
ORDER BY T1.ClientID, T1.CallDate DESC;

The difference is that it is still matching the ID between the primary and subquery, but now is determining what records to show based on if the Date is a match in the subquery. To prevent a ‘goof’ match between an indentical date with a different call type, it forces another criteria on the primary query of making sure the CallType=”Sales”. This of course is going to give you more then 3 records per ClientID if you have identical dates for the CallType of “Sales”. With the fields given, it’s the closest I can get. If there are more fields that you can use as ‘identifies’, then you may be able to make a stronger ‘key’.

Have fun! (P.S.–How’s the bay area? I used to live in Alameda (in the US NAVY….worst…I mean best 6 years of my life!)

Reply | Quote

both to Drew and Mark – for what ever reason I had a brain freeze yesterday. It’s a reasonably common problem, but more than a bit tricky for users to figure out.

Reply | Quote

Wendell, that goes for all three of you,

It may be just a simple trick to all you guys, but for those of us struggling to learn in the trenches, it’s a life saver!

Reply | Quote

Off Topic Again: You picked right time to visit DC – our biggest snowstorm since ’96! –tho be advised, this place can come to halt if there’s snow flurries, let alone a full-scale snowstorm…. I also work for gov’t – on “Snow Days” like this (as a lowly “non-essential” personnel) I get to stay home and, when not out playing in the snow, get to work on some of my Access programs that I don’t have time to work on at “work” due to other “priorities” and “hot projects” (& they’re ALL “hot”)….

Reply | Quote

Ugggh, I’m originally from NY, and let me tell you, we’d never get stopped by these little flurries! My boss (same org, but NY office) made me drive home in 4 feet of snow once….LOLOL Anyway, bad weather seems to follow me. My first year in SF was their very first snow in 40 years. Now I get here, and the whole city stops at my feet again! My friends here didn’t seem worried by the snow so much as worried about losing their parking spaces *g*

This time around, I’m thankful for the extra day it gave me. I’m still not caught up, but at least I had some extra time.

Drew: Do you know if Lincoln’s Tavern is still there? I’m on the south side of the island, away from the tubes. I hear the north side is much more happenin’

On topic: The query works fine when it’s local. When it’s over DB2, it completely stalls. I kind of “fluffed” the data for the DB2 fill, so I’m not even going to bother with it for that, but I still have a SQL Server portion that I’m hoping it will work on tomorrow.

Reply | Quote

I don’t know if Lincoln’s Address is still there, I haven’t been in Alameda since 96.

Reply | Quote

Hi Drew–

Now that I’ve had a good night’s sleep (sorta–see below), let me see if I understand….

The subquery calls the top three dates from the table, at the point where the subquery ID matches the main query’s ID. So then the main query just calls the data that I want out of that result (limiting the call type to Sales)? That’s pretty cool.

I don’t think there are going to be any identical dates for any particular ID, simply because the mainframe system doesn’t allow it, so technically, if you have a match between ID and date, that should be enough. Having the Type in there is definitely an added protection and should be plenty.

I’m running the query against DB2 now…could be waiting forever for this big thing to happen….

In the meantime, Off Topic: I actually “live” in Alameda–And I love it! They are in the process of converting the naval base to condos. Hope that’s not going to ruin too many of your … um … memories. Right now I’m at our headquarters, in DC. Over the weekend we were hit with about 18″ of snowfall, bringing this city pretty much to a halt (on Sunday the metro was running at pretty much 2-hour intervals, yesterday they said 1-hour, today the gov’t is still closed). The only bright spot is that they’ve stopped with the constant Terrorist Alerts on TV. (Plus I get an extra day on the two-tier application I’m supposed to deliver start to finish in three weeks….YAY!)

Reply | Quote

On topic, the Sales is a criteria in the subquery, because you said you wanted the last three ‘SALES’ calls. If that criteria wasn’t there, you would get the last three calls, regardless of their type.

Off topic….oh good ol’ Alameda NAS. I was stationed on the USS Carl Vinson. I’ll have to admit the ‘Webster Street Crawls’ were definitely fun for a ‘bored out of his gourd’ sailor. I can’t remember my old address, but I lived a few blocks behind the tavern ‘Lincoln’s Address’.

Reply | Quote

Well, I tried running the query on my DB2 source….No luck. Just to verify that I was doing it properly, I ran it on a local source. It was so slow, I went to lunch, came back, and still waited a few for it to run. I was hoping that at the very least it would run on my SQL97 datasource, but with this slow result locally, I don’t think it’ll be much better.

Oh, well. Two weeks to go If anyone can think of a better way to do this, I certainly would appreciate it!

Reply | Quote

How many records are you expecting from the DB2 source, ie. how many clients are there?
Pat

Reply | Quote

A lot. I know, that’s my problem

Reply | Quote

How many is a lot?
Pat

Reply | Quote

Well, in order to go far enough back in time, I am guessing I need about three years’ worth of data, which is around 30,000 records. The table (in DB2) has at least 15, maybe 20, years worth of records, plus record types that I’m not pulling at all.

When I ran it off the “local” table, it was the one with 30,000 records. The table in SQLServer should have about a million records, but should be returning about 15,000 records.

Reply | Quote

You did say further back in the posts that:
>>the number of customers in my table is in the thousands (not to mention that I have to select only customers that were active since 1999 in the query>>
This reduces the number of records that you need to look at.

Why not build an Append Table query selecting the records you want. This table should hold all the data you need from the DB2 table and a special field that will hold an increasing value for each customer.

Then with some VBA code go thru the table backwards for each customer painting in the ever increasing number (starting at 1 for each customer).

You then need a query that selects all records from this table where this special number is less than 4.

Pat

Reply | Quote

>>Then with some VBA code go thru the table backwards for each customer painting in the ever increasing number (starting at 1 for each customer).

That’s exactly it. My DB2 table has in the millions of records. I’m only pulling 30 thousand. I started with this approach but couldn’t figure out how to do it. This is the equivalent of a cur field, no? Where an open record would be 0, the first full year would be 1, and count back until 4 for each year. But there isn’t one record per year, there are just dates that I have to order.

Suggestions on an approach? My idea was to either do the top values thing on the DB2 table, or at least pull down a subset of the DB2 data and then run the query on the local table, but both take way too long.

Uggggh.

(BTW, thank you for your efforts on this, this is more than a mind bender for me at the moment.)

Reply | Quote

>>This is the equivalent of a cur field, no? Where an open record would be 0, the first full year would be 1, and count back until 4 for each year. But there isn’t one record per year, there are just dates that I have to order. >>

No, all I meant was to pull down the data based upon a date since 1999 and for the “Sales” type.

If this is taking way too long (by the way, have you timed this with the “sales” type and dates since 1999), then can you ask the DBA’s to put an index on the DB2 table for you which would extract the data much quicker.

Then you can do what has been suggested or the painting of the special number.

Pat

Reply | Quote

If the cur field isn’t the special number, then what is the special number?

>>If this is taking way too long (by the way, have you timed this with the “sales” type and dates since 1999), then can you ask the DBA’s to put an index on the DB2 table for you which would extract the data much quicker.

I wish. I have two weeks left to develop the entire thing, the people that do this stuff will tell me it takes 3 months and twice our budget. Oh well. Thanks, anyway.

Reply | Quote

What’s the cur field you keep talking about?

2 weeks to develop what, more than this query I presume?

What I had proposed was to extract the data from the DB2 table into an Access table which would have an Autonumber field, you would extract in the Order of Customer, Date. Next via a query is you extract the top 3 from the Access table, but not the way the others have mentioned.

Pat

Reply | Quote

I am going to try this next. I think it will give a cur field…cur means current, so if everything were in proper order, the most current record would be 0, the next would be 1, the next would be 2, and on.

This is just the download from the mainframe for the backend. I still have to build an entire user interface. (But at least I get to save the taxpayers a few $$ *grin*)

Reply | Quote

How long does it take to download the data from the DB2 database to Access. Do you care how long it takes?
The reason I ask is that the other queries to run should not take long at all to get what you want.

How often do you have to run this?

What’s involved in the user interface?

Pat

Reply | Quote

Well, with the roundabout way you are checking for valid data, if your data store is immense, it is going to take a while. However, are you pulling the data a new every time, or is it a one time deal (dumping to a table)? If you need it faster, one method would be to program a Class with collections inside, that loads the sub query data into memory, then runs the main query to pull off of the ‘code’ collection. That would run much faster, but it would take longer to develop.

Reply | Quote

Hmmm. I’m doing a data dump, I guess. The class thing sounds like it goes way beyond my lack of expertise.

Thanks for your help,

Reply | Quote

From the posts above, why not just run a make table query, to pull the data since 1999 into a local Access table. Then run your data pull on the Access table you just made. (Index the date field and ID fields, that will speed things up).

Reply | Quote

Hi Drew,

I tried that. The query took _forever_.

Uggggh.

Reply | Quote

>>The query took _forever_.<<

What took forever, the data dump from DB2 or the top 3 query?

Pat

Reply | Quote

Both, actually.

The top 3 query seems to just stall. I haven’t been patient enough to let it run longer than half an hour. And that’s on my local data, after I’ve dumped it into the Access db.

Reply | Quote

So the Top query is taking a long time on your local machine, is that your PC?
How big is the Access database with the 30,000 or so records.
Would you zip it and email it to me, my email address is in my profile.

Pat

Reply | Quote

I have been doing some investigation. My machine is an AMD300 (5 years old) with 64Mb RAM.
What I have is a table 26,500 records that is not indexed.

I run the Top 3 (modified for my table’s fields) query supplied by Drew and it’s been going for an hour and ten minutes so far and still going strong.

I put an index on the table and I run the Top 3 query in 45 seconds.

Pat

Reply | Quote

WOW!!! Patt, I could not possibly ask for, or receive, better assistance with this. If I didn’t say it before, THANK YOU for your help. This is way above and beyond!

I am exhausted today, but will get back to this tomorrow and report my results. I will index the two fields that identify the record (Customer ID and the date). I’ll report my findings then.

Again, Thanks!

Reply | Quote

I stopped that Top 3 query after 3 hours, it was still going strong.

Pat

Reply | Quote

Yep indexes REALLY speed things up. Especially with the subquery pull within the query. Without an index, your ‘query speed’ is going to drag immensely, based on the number of records involved. With an index, your subquery is going to pull in a fraction of a second, you’ll probably hit hundreds of records per second (thus 45 seconds for thousands of records). If it took even half a second per record without an index, with 30,000 records you are looking at 15000 seconds to run the query. That would be roughly 4 hours and ten minutes.

Reply | Quote

You are ABSOLUTELY right! This has been an amazing lesson for me. My table of over 30,000 records ran in about ten seconds with just three indices. I haven’t gotten to the part of doing it on the SQL server (there’s a hold up with that), but I am confident that it will run in a reasonable amount of time.

I can’t believe this query works so well!

How can I thank you all for this help???

My final task is to create indexes on the fields after creating the tables in the data dump. I found this example in the Help file:

Sub CreateIndexX()

Dim dbsNorthwind As Database
Dim tdfEmployees As TableDef
Dim idxCountry As Index
Dim idxFirstName As Index
Dim idxLoop As Index

Set dbsNorthwind = OpenDatabase(“Northwind.mdb”)
Set tdfEmployees = dbsNorthwind!Employees

With tdfEmployees
‘ Create first Index object, create and append Field
‘ objects to the Index object, and then append the
‘ Index object to the Indexes collection of the
‘ TableDef.
Set idxCountry = .CreateIndex(“CountryIndex”)
With idxCountry
.Fields.Append .CreateField(“Country”)
.Fields.Append .CreateField(“LastName”)
.Fields.Append .CreateField(“FirstName”)
End With
.Indexes.Append idxCountry

‘ Create second Index object, create and append Field
‘ objects to the Index object, and then append the
‘ Index object to the Indexes collection of the
‘ TableDef.
Set idxFirstName = .CreateIndex
With idxFirstName
.Name = “FirstNameIndex”
.Fields.Append .CreateField(“FirstName”)
.Fields.Append .CreateField(“LastName”)
End With
.Indexes.Append idxFirstName

‘ Refresh collection so that you can access new Index
‘ objects.
.Indexes.Refresh

Debug.Print .Indexes.Count & ” Indexes in ” & _
.Name & ” TableDef”

‘ Enumerate Indexes collection.
For Each idxLoop In .Indexes
Debug.Print ” ” & idxLoop.Name
Next idxLoop

‘ Print report.
CreateIndexOutput idxCountry
CreateIndexOutput idxFirstName

‘ Delete new Index objects because this is a
‘ demonstration.
.Indexes.Delete idxCountry.Name
.Indexes.Delete idxFirstName.Name
End With

dbsNorthwind.Close

End Sub

Is going through this procedure (after all my make table queries) better than just leaving the table structures alone and deleting and appending the data that is dumped? What are the advantages/disadvantages?

Reply | Quote

Lookin’ GOOD! Based on your code, I presume you intend the automate the whole thing so a total novice can run the process and see the result.

Reply | Quote

The power of indexes !
In answer to your question, I would certainly not have any indexes on the table you are dumping to (if you are doing the dump via a Make table query then this does not apply, only applies if using an Append query) at the time of the dump, but certainly add the indexes after the dump.

You can also use ALTER TABLE to add or delete indexes, not too sure if it’s still available in A2002 as I don’t have A2002, but I would be surprised if it’s not.

I hope that answers your question.
HTH
Pat

Reply | Quote

First of all, you’re quite welcome, glad I could help. You can thank me with a beer! (Just kidding).

As for your ‘finishing touches’, how is this process going to work? The factors you need to look at are how often are you going to need to ‘dump’ this data from the DB2 system, how much data is going to be pulled (not including the first pull you just did…first pulls are usually the largest), and how competent are the people that are going to run this system when you are done.

If this is only going to be

Reply | Quote

I’m a bit short on time at the moment, so let me answer THE most important part of this message:

http://www.yuengling.com/%5B/url%5D

The rest we’ll chat about later *grin*

Reply | Quote

Where is the gift certificate?

Reply | Quote

LOLOL You forget, I’m a fed gov’t employee–

I’ll assume it’s wherever my “yearly bonus” goes….

Seriously, next time you guys are in SF, a beer is definitely in order!

Reply | Quote

This thread has been locked because of it’s length – you may find continuing discussion in [post#=234456}.

Reply | Quote

Welcome to the world of large databases. When you are dealing with a massive numbers of records, you can look forward to long data pulls. There are a few tricks of the trade that you can use. First of all, data dumps of related info. Sure, it may take forever to pull the data, but once pulled, you now have a much smaller pool to work with. Next, indexing. Indexing fields is critical. In fact, an index is a database in and of itself. When you index a field, you are telling your database that you want practically instant data pulls based on a particular field. Thus, the database will create internal tables to ‘mark’ where and how that field is listed.

Reply | Quote

Hi Drew–

If I’m pulling data directly from a DB2 linked table with a query, would I be able to index the data in the query? Or do I need to write separate code to go back and index the fields I want indexed?

Also, do you have suggestions on which fields to index, just the primary key or other fields as well?

More reading for me

TIA!

Reply | Quote

(Edited by WendellB on 26-Feb-03 09:14. Add question about doing top 3 query in SQL Server)

Welcome back – we were wondering if you had been trapped in the maws of DB2!
As to your index, the index really needs to be in the server to begin with, and on the date field and any other field being used in the extraction query, whether it is in DB2 or SQL Server. It would speed the return of data enormously – we routinely query million+ record tables and get data back with subsecond response time. Once you get the data into Access, you want to put indexes on any of the fields you are grouping by – date and client for sure, as well as having a primary key. Hope this makes sense.

After re-reading your first post this morning, it occurred to me that you could run the TOP query in SQL Server directly and would probably get better performance than trying to run it an Access – that assumes that your server is a nice robust beast of course. Just an after-thought.

Reply | Quote

Hi Wendell–

Thanks for the welcome back! I was just slightly lost somewhere between SF and DC….Wondering IF I should come back to DC based on how this project is going *g*

>>that assumes that your server is a nice robust beast of course<>As to your index, the index really needs to be in the server to begin with, and on the date field and any other field being used in the extraction query, whether it is in DB2 or SQL Server. It would speed the return of data enormously – we routinely query million+ record tables and get data back with subsecond response time.<>Once you get the data into Access, you want to put indexes on any of the fields you are grouping by – date and client for sure, as well as having a primary key. Hope this makes sense.<<

The table I'm working with does not have a single primary key, but two fields together make a primary key. As for the indexes, is there a way to do that through the make table query or do I need to write separate code for that?

Okay, a little more research on my part is needed here, I will be back when I think I've gotten tot he point of discussing this semi-intelligently….

Reply | Quote

>>No idea if the DB2 stuff has indices, and if it doesn’t, I’m not going to get it anyway. But for kicks, would I be able to tell this through Access? <>The table I’m working with does not have a single primary key, but two fields together make a primary key. As for the indexes, is there a way to do that through the make table query or do I need to write separate code for that?<<
Is the table in Access? Are you importing data from DB2 into this table? I would delete the indexes on the Access table before importing data from DB2 then put them back on after the dump,as well as any other indexs required for the Top3 or however you are going to solve this problem.

I would like to see the Access database.
Pat

Reply | Quote

You can’t index fields in a query. The fields must be indexed in the tables. The query ‘engine’ is going to use whatever indexes are setup.

Reply | Quote

How are you going with this problem?

Is the data dump (as Drew calls it, good name actually) taking the time?

Pat

Reply | Quote

Sorry, was travelling this weekend. I’m back, working on it. We’re going to try the Top 3 thing from SQL Server this week sometime. I gave up on doing it from DB2.

Reply | Quote

Quick note:

I didn’t find it necessary to have the CallType=”Sales” statement in the outer query. It doesn’t seem to speed it up to have it there (at least not that I’ve noticed yet).

SELECT T1.ClientID, T1.ClientName, T1.CallDate, T1.CallType
FROM tblCalls2 AS T1
WHERE T1.CallDate In
(SELECT TOP 3 CallDate
FROM tblCalls2
WHERE (T1.ClientID=tblCalls2.ClientID) AND (tblCalls2.CallType=”Sales”)
ORDER BY tblCalls2.CallDate DESC)
ORDER BY T1.ClientID, T1.CallDate DESC;

Reply | Quote

It shouldn’t have sped it up at all. I just had it in there because if I remember right, your original post was only looking for that type of ‘calltype’. However, if you are doing a data dump, you can dump it based on that criteria in the first place. (details just kept creeping in on this one….it’s a good thing we aren’t contract support! )

Reply | Quote

LOLOL! But you’re so GOOD at it!

What I meant was that I only needed the calltype criteria in the inner query. Having it in both the outer query and the inner query made no difference in either the run time or the results. I’m not using the query for the data dump because in DB2 (where I’m pretty sure my fields aren’t indexed) it took several hours to run.

Still working on it….

Reply | Quote

Oh, I had to look back at my original SQL post to see what you mean. I did have that there for a reason. IF you have two calls with the same ClientID and the same date, (from your ‘specs’), then the outer query would return values that were not of a CallType of Sales. If this never occurs, the query doesn’t need that criteria. Since you are using the ClientID and Date as a ‘key’, it is looking for that key in the subquery, which does filter out all non ‘Sales’ call types. However, if you have a non Sales call type with the same date and clientID, it would show up in the Outer Query, because it would meet the ‘In’ clause statement.

Does that make sense?

Technically, if you are doing a data dump, you should be filtering for all non Sales records (unless you need the other records dumped too). That way you can strip the CallType=”Sales” from both the inner and outer query.

Another ‘if’, is if your date is really a date/time. The odds of having two calls from the same client at the same time would be remote at best. Thus the outer criteria wouldn’t be needed.

There is a rhyme to my reason!

Reply | Quote