Given a table of transactions with a common date. I want to get a sum of some fields, and the first value and last value of one other field.
I want a sum of the credit field and the debit field (to show total added to the account and total taken away) In the same results I want the balance at the beginning of the day, and the balance at the end of the day… this is basically the first and last values of ‘balance_forward’
this seems simple but I am having the hardest time trying to get this.
Okay, this time, rather than trying to come up with a query and handing it to you, I’ll start asking questions and making vague suggestions.
First, you’ll need some way to group by day - this might be easy or harder depending on how the date and time are encoded. If there’s a field that’s just date with no transaction-time component you can just group by this, otherwise you’ll need to come up with an expression or a function that will return the same value for each day. I have a regular function for removing the time component of an MSSQL datetime that I can probably dig up and post if it would be handy.
Now, within the result set for any particular day, is there a field that you can do min() and max() of to uniquely get the ‘first’ and ‘last’ record for the day? An identity primary key will probably be best. The transaction_time field might have more than one hit at the same second.
Then you can run the select min(unique_field) from table group by day as a subquery, and join from there to the full results based on unique_field. Ta-da! You could probably even do it on min and max at the same time - joining to seperate copies of the table. And throw in those other things that you want to aggregate on per day.
Does that much help you out?
I tried that, pretty much as you describe. But I get a message - error converting from type char to datetime.
I know the conversion works because I’ve used it to show the date in the mssql format, but when used in the way above, I get the error.
I’m at home now so can’t work on it.