Query formation for date comparison and Grouping results

[showman13]

Good Morning All,

I'm about to write a script that will actually be run in a CRON job weekly. I created an overview of the process so you would understand what I'm doing but I have questions with 2 of the MySql queries that will be required, and wondered if someone could help me get them formed correctly...

trans_earnings table holds individual earnings transactions that are generated at time of product sales
this script is intended to accumulate all the individual earnings and create a single weekly distribution record for them.

These are the basic query structures that I plan to use, but need help with defining the 'Previous Saturday at Midnight' in the first UPDATE query
And the Group by clause so I can get a count of the earnings transactions for a member along with a total of those transactions, in the first SELECT query.

I think with a little assistance in those areas, I'll be able to accomplish this task.

But if you have a suggestion regarding a better means of managing this process, I'd be open to hearing that as well.

Thanks in advance for your help
Douglas

UPDATE trans_earnings
SET status = ‘P’
WHERE status = ‘E’
AND earned_date < previous Saturday at midnight (datetime field)
AND abbrev = ’FSB’
//(this should MARK all new earnings trans matching that code, that were generated last week, for processing)

//Then I need to do a query that will get the totals per mem_id, but not the individual transactions
SELECT earnings_id,  mem_id,  earned_date,  abbrev,  amount
FROM trans_earnings
WHERE status = ‘P’
ORDER BY mem_id
//With a count of the earnings_id - 
GROUP BY mem_id

//and a total of the amount – 
GROUP BY mem_id

//So I can take those results, and generate a single transaction for each member for distribution of the funds

INSERT into trans_distribute (dist_id,  mem_id,  create_date,  earnings_ct,  amount,  status)
VALUES(‘’, mem_id, $created, earnings_count, earnings_total, ‘P’);

//Then query to get the dist_id that was just generated

SELECT dist_id 
FROM trans_distribute
WHERE mem_id = $mem_id
AND status = ‘P’
LIMIT 1

//and as I generate each distribution record, I need to update all records in the trans_earnings table that belong to that mem_id, setting the status to 'D'istributed, and setting the dist_id field to the dist_id just received from the query.

UPDATE trans_earnings
SET status = ‘D’, dist_id=$dist_id
WHERE mem_id = ‘$mem_id’
AND status = ’P’

[showman13]

So, was my question to detailed? To vague? To arbitrary? Or is it just something that nobody has an answer for...

Maybe I just made it look harder than it actually is by providing all the additional information wrapped around the 2 questions that I was asking.

I'll seperate them out and maybe that way I will get some sort of response.

Question 1 : Is there a way to specify a comparison date as the previous Saturday at midnight, or maybe, just < previous Sunday? This is so it doesn't matter which day of the week this script is run on it will get the same results.

UPDATE trans_earnings
SET status = ‘P’
WHERE status = ‘E’
AND earned_date < previous Saturday at midnight (datetime field)
AND abbrev = ’FSB’
//(this should MARK all new earnings trans matching that code, 
//    that were generated last week, for processing)

Question 2 : Can someone help me to do a query that will get the total transaction count and total transaction accumulated total from the trans_earnings table, GROUPED by per mem_id

The table will hold hundreds/thousands of transactions and each member could have dozens of commission transactions to total up. I just need to be able to pay them and tell them how many transactions the payment covers and the total.

SELECT earnings_id, mem_id, earned_date, abbrev, amount
FROM trans_earnings
WHERE status = ‘P’
ORDER BY mem_id
//With a count of the earnings_id - and a total of the amount
GROUP BY mem_id

My thoughts are that I should be able to generate an array with a single result for each mem_id that has 1 or more transactions to be paid for, along with the total number of trans, and the total of the amounts of those trans.

Hopefully that clarified what I was asking and someone will have the answers I'm looking for.

thanks again,

Douglas

[showman13]

Well, I just realized what the answer to #2 is... Simple Answer - must have been experiencing a brain blockage of some sort... was making it much harder than it needed to be.

SELECT mem_id, count(earnings_id), sum(amount)
FROM trans_earnings
WHERE status = 'P'
GROUP BY mem_id

Now if I can just get #1 answered, I'll be in business

[showman13]

This has become sort of a one sided Q and A session...

I guess sometimes it helps to just verbalize the question to someone else, and it gets the creative juices flowing to come up with the answer...

Here is what I came up with.

UPDATE trans_earnings
SET status = 'P'
WHERE status = 'E'
AND YEARWEEK(earned_date, 0) < YEARWEEK(NOW(), 0)
AND abbrev = 'FSB'

It appears to do what I want it to do...

BUT, if anyone sees any issues with either of the two solutions I came up with, please feel free to point them out, and suggest an alternative if you have one.

Thanks again
Douglas

Guess I'll mark this one as solved... wonder if I'll get credit for solving my own question... LOL