SQL Query beyond my grasp 2

[rancidmeat]

I need help with doing the following: First here is the data

Cust_ID Acct_Op_Dte Trans_Date Trans_Amt Store_Loc
12345 2004350 2004350 $160.00 4
12345 2004350 2004355 $160.00 4
54321 2004303 2004306 $0.07 1
54321 2004303 2004306 $2,000.00 1
54321 2004303 2004306 $0.00 1
54321 2004303 2004335 $0.73 1
54321 2004303 2004335 $0.00 1
54321 2004303 2004366 $0.67 1
11111 2004357 2004358 $0.03 1
11111 2004357 2004358 $3,000.00 1
11111 2004357 2004358 $0.00 1
11111 2004357 2004366 $0.33 1
11111 2004357 2004366 $0.00 1
11111 2004357 2005031 $1.01 1
11111 2004357 2005031 $0.00 1
55555 2004357 2004358 $0.59 5
55555 2004357 2004358 $54,000.00 5
55555 2004357 2004358 $0.00 5
55555 2004357 2005031 $18.31 5

Goal: Sum the trans_amt using the trans_amt with the first Trans_Date for each Cust_ID. The legacy DB I am working with does not have this information. I had to create this DB by adding the Acct_Op_Dte from the customer record file to the transaction history file. The DB I created for all accounts opened in October 2004 has over 156,000 records of history for just over 6,300 unique Cust_ID's.

So what I am looking to do is get the following results from a SQL query:

Cust_ID First_Trans_Amt
12345 $160.00
54321 $2,000.07
11111 $3,000.03
55555 $54,000.59

I am too embarassed to share my first several attemps since they did not work.

Thanks in advance for your help.

 

[r937]

select Cust_ID
     , sum(Trans_Amt) as First_Trans_Amt
  from rancidmeat as R
 where Trans_Date
     = ( select min(Trans_Date)
           from rancidmeat
          where Cust_ID = R.Cust_ID )
group
    by Cust_ID

tested and works on your sample data



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[rancidmeat]

Rudy,

I was unable to get it to work. I let it run for over two hours. I tried it on both the sample data and my live data. I did not get any errors. The query would just not complete.

 

[r937]

you gots any indexes on that table?

if Cust_ID isn't the leftmost column in the primary key, it'll need an index

and of course Trans_Date should have one too

if neither has an index, then declare a compound index on Cust_ID,Trans_Date,Trans_Amt and theoretically the database engine shouldn't need to access the table at all

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[PHV]

If you still have performance issue you may consider a temporary table:
SELECT Cust_ID, Min(Trans_Date) As First_Date INTO tblFirstDates
GROUP BY Cust_ID;

Make Cust_ID the Primary Key for this table.
In your real table create a composite index allowing dups on Cust_ID,Trans_Date
And now your query:
SELECT R.Cust_ID, Sum(R.Trans_Amt) As First_Trans_Amt
FROM yourRealTable R INNER JOIN tblFirstDates F
ON R.Cust_ID = F.Cust_ID And R.Trans_Date = F.First_Date
GROUP BY Cust_ID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rancidmeat]

Rudy,

Thanks. As far as my table having indexes? Duh! But wait I looked at the extract I brought home from and DOH! I did not add the indexes (only the tables at work have indexes - damn those AS400 extracts). ANyway after I re-indexed the tables and ran the Access utilities (Compact and Repair - I always run out of habit). The query ran as expected on my old P1-266 Laptop with Access 2000 in just under 45 minutes.

PHV, your optimization did help. Doing all your steps while watching TV and talking on the phone took just under 10 minutes.

Both you guys did a great job thanks. Now I can moce on and use Store_Loc to link to the reagion table and create region totals and try to figure out again how to convert the Acct_Op_Dte to monthly sub-totals. So do not be surprised to see me posting here again.

Thanks again Rudy and PHV.