Whoops, I didn’t notice the yesterday requirement. I would make my WHERE clause say
WHERE TRUNC( timestamp ) = TRUNC( SYSDATE - 1)
unless TRUNC is oracle-specific? (Not sure)
Also it might be necessary to add balance_start and balance_end to the group-by clause.
I’ve never seen a TRUNC function used in a date context, so it probably is. I’m not familiar with Oracle. I’ve done Rbase, Foxpro, MS-Access, SQL-Server, SQLite, and a bit of MySQL, so I’ve seen how things can vary a LOT between flavors, especially the date functions. Oracle is just one more. I believe the only functions covered in the SQL-92 standard are the aggregate ones (MIN, MAX, SUM. AVG, etc), so anything else has to be carefully checked for compatibilily… numeric, string, and date. I try to avoid using them in examples when I don’t know which DB-flavor the person is using.
*** Ponder
Since the question seems to be well-answered, I’m gonna offer a hijack …
It sure smells to me like the table design itself is faulty. This smells like a spreadsheet which was directly converted to a SQL table willy nilly.
Given a properly designed set of tables, then the query becomes easy. As should the code which maintains the table.
Hell, you can write a Turing machine in SQL; that doesn’t mean you should.
p.s. Yet another painful bit of nostalgia; I should have saved a printout of the APL*SV 1-line Turing machine I wrote so long ago. Lost forever, the bits long ago recycled to some higher, better use (or maybe just into a J-Lo Youtube clip; there is no justice.)
Here’s the fun hack-ish version with no correlated subquery. Quick explanation:
date/time gets put in left portion of a var, balance in the right, can do MIN and MAX and get proper results because date/time is in most significant portion, then extract balance after MIN and MAX found.
select
accountnumber
,total_amount
,cast(right(start_bal_char,20) as numeric(20,2)) as start_balance
,cast(right(end_bal_char,20) as numeric(20,2)) as end_balance
from (
select
accountnumber
,sum(amount) as total_amount
,min(convert(varchar,timestamp,121)+right('00000000000000000000'+cast(balance*100 as varchar),20)) as start_bal_char
,max(convert(varchar,timestamp,121)+right('00000000000000000000'+cast(balance*100 as varchar),20)) as end_bal_char
from thetable
where timestamp between from_datetime and to_datetime
group by accountnumber
) t1
And yes, I’ve had to do stuff like this before. An example was an SQL Server App with complex pass-through queries to Oracle that were too much for Oracle’s optimizer (version 8i)
I tried a few of the sqeries in this thread but they take far too long to run (well they didn’t run… I cancelled the jobs after seven minutes run time) this is on a super-fast SQL server ( a typical large query can run in 30 seconds)
I then went of on a tangent and worked on other things… hence not really replying much to this thread.
LSLGuy The table is based on an export file which comes off a wagering hub. It is pretty much a field-for-field copy of that data. And it was designed by a third-party.
There is zero relational-ness (is that a word?) to it.
To add a few complications… the ‘timestamp’ is not one field. there’s a tx_date field, and a time field. The system runs over midnight where I live so one ‘database’ date can span two transaction dates…
In other words using the time field for sorting is no good, because a transaction that occured at 23:30 is earlier than a transaction that occurs at 2:30.
what’s more, the date and time fields are text fields (date is ‘yy/mm/dd’ and time is ‘hh:mm:ss’)
Later on I’ll try some of the other queries supplied in this thread (altering them to suit my particular database) and see how they get on.
But in the meantime : Thanks for the help/replies so far.
That one was VERY SQLserver-specific. Anyone not familiar with SQL-server is going to look at that odd convert() function with its idiosyncratic numeric arguments and scratch their heads wondering what it’s supposed to do. I always had to look up the numbers in the help every time I needed to use it for anything other than ISO date format. M$ really put the “idiot” in “idiosyncrasy” when they designed that function… Arrgh.
*** Ponder
Sounds like you need some indexes? If you already have them, something’s wrong, obviously.
That’s normally a no-no in DB design, but it can make some queries simpler, like your requirement to fetch data from a specific date.
I take this to mean you’re in a different timezone than your data? And your local date you want to fetch spans more than one date as stored in your DB?
The simplest way to deal with this problem is to add a new proper timestamp field to your table that is a true SQL-server datetime, and convert the data into it on the import step. You can write a trigger on the database end if you have no control over the program doing the importing. An indexed datetime field will sort properly, regardless of the timezone issue. Use the dateadd() function with the hours argument to convert to your local timezone, and THEN do the necessary splitout of the day component to fetch the right date.
*** Ponder
The crap I wrote will run faster than anything with a correlated sub-query, it’s just not maintainable.
That’s not a big deal, just a little more effort, you have 2 fields to sort/compare etc. instead of 1.
Sort/comparing 2 fields (date, then time) isn’t a big deal, the bigger deal is figuring out what your datetime range should be.
I know you said “yesterday” but whose “yesterday”? Are you looking for that calc to be embedded in the query? If so, need more info. For example, if you are providing info to a user from NY who is using the system while on vaca in Hawaii but your query runs from CA, what is the appropriate timezone for the set of trans?
Doesn’t change the sorting unless the leading 0’s in each subfield have been suppressed, in which case you need to either: