|
Victor posted:Edit: moreover2, CHECK constraints cannot contain subqueries in SQL2005; try this (from the stackoverflow thread): code:
code:
|
# ? Dec 2, 2008 19:09 |
|
|
# ? May 15, 2024 04:35 |
|
Man I have one that is killing me. I have a table constructed thusly: CompanyID | EmployeeID | Payrate | ChangeDate Each row in the table records when the payrate for the employee changed and what it changed to and our payroll software uses the latest record for the current pay rate. My boss wants me to construct a report that shows him current rates plus what the rate was previously for each employee. Basically so he can see what impact raises are having on the company. So essentially I would need for each companyID and employeeID to see the latest payrate plus the one just below that as relates to dates. What is killing me is figuring out how to return a result set that shows both the current rate and the rate just before that on the same row. I have been farting around with top statements and subqueries for the last 4 hours and I am about ready to pull my eyes out. Any ideas? Syano fucked around with this message at 18:24 on Dec 3, 2008 |
# ? Dec 3, 2008 18:07 |
|
Syano posted:Man I have one that is killing me. I have a table constructed thusly: code:
EDIT: Oh, didn't see that you want them on the same row. Try using PIVOT. Give me a second to do something with that. Jethro fucked around with this message at 18:36 on Dec 3, 2008 |
# ? Dec 3, 2008 18:33 |
|
code:
|
# ? Dec 3, 2008 18:42 |
|
Brilliant. You have put me on the path. Specifically the row_number function was the kicker. Thank you very much Edit: Hahaha I am going to shoot my software vendor. Their stupid software does not support compatibility level 90. No PIVOT for me! Syano fucked around with this message at 19:57 on Dec 3, 2008 |
# ? Dec 3, 2008 19:15 |
|
Syano posted:Brilliant. You have put me on the path. Specifically the row_number function was the kicker. Thank you very much For grins, here's the (untested) Oracle analytic function solution to your problem: code:
|
# ? Dec 3, 2008 23:40 |
|
Markoff Chaney posted:For grins, here's the (untested) Oracle analytic function solution to your problem: i'm pretty sure you're using oracle since the row_number() analytic is oracle only, is it not? i dunno what the pivot command is, is that something in ms-sql? this should do what you want. code:
|
# ? Dec 4, 2008 02:23 |
|
row_number() and pivot are new to SQL2005.
|
# ? Dec 4, 2008 06:36 |
|
I have a table that will be filled with information as follows:code:
code:
|
# ? Dec 4, 2008 22:53 |
|
the code you posted is PHP. you could do it in the php, or you could do it in your select statement. if you want to do it in your select statement, just look at your DB's documentation for case statement, it should be really easy. so you would just include that in your select, like: (this is in oracle) code:
this might mess up your sorting or something else if you're using the years for that. i dont think mysql handles checking for null values the same as oracle so you might have to change that.
|
# ? Dec 4, 2008 23:59 |
|
Also posted in the Java thread, but I might get more of a response here. In an RDBMS, it's typical to normalize something like UserType (or PaymentMethod) into into its own table like 1=admin, 2=viewer (or 1=cash, 2=cheque, 3=creditcard), and other tables reference them by id. Such tables won't change, typically. In Java code, it makes sense to think of these as enums, so a Payment class would have a PaymentMethod field, where PaymentMethod is an enum: PaymentMethod.CASH, PaymentMethod.CHEQUE, etc. How can I use Hibernate to represent this common use case? The following links are what google has to say about the issue, but these solutions seem overly complex and/or are several years old. http://www.hibernate.org/265.html http://www.hibernate.org/265.html http://appfuse.org/display/APF/Java+5+Enums+Persistence+with+Hibernate http://www.hibernate.org/272.html http://snipplr.com/view/5379/java-5-enum-hibernate-mapping/ http://hibernate.org/203.html Does anyone have experience with this, and/or can vouch for one of the linked solutions? Enlighten me! (Don't get me wrong, I'm usually all for just trying things out, but the linked solutions look old, long, complicated and numerous, and this very well may be a case of someone here saying "oh just do X, it works great".)
|
# ? Dec 5, 2008 22:17 |
|
I have a table transactions: id | account_a | account_b | amount and want to calculate total balance for a given account I'm wondering if I somehow can combine these queries to get the total with just one query. code:
|
# ? Dec 6, 2008 13:05 |
|
code:
|
# ? Dec 6, 2008 17:28 |
|
epswing posted:
|
# ? Dec 7, 2008 02:42 |
|
How about :code:
|
# ? Dec 7, 2008 04:35 |
|
trekdanne posted:That's just ads up everything. What I need is debit-credit. Ahh, for some reason I thought one of the columns contained only negative numbers. Edit: Comedy case-when statement: code:
epswing fucked around with this message at 06:16 on Dec 7, 2008 |
# ? Dec 7, 2008 06:03 |
|
this is easy (once you know how to do it). you can use the result set of each query, join them together, then select from it. this is pretty basis sub-select / nested select stuff. code:
notice i'm not joining the credit and debit tables together, this is because you're only going to get 1 row back per sub query. if you added accountID in there, you'd want to join by that: code:
the previous solution will work fine too, and will be a little faster. if you need to do any more complicated things though, you might want to use sub-selects, like in my example. tayl0r fucked around with this message at 09:15 on Dec 7, 2008 |
# ? Dec 7, 2008 09:10 |
|
Here is some ugly SQL code that I'm working on for a report.code:
code:
code:
|
# ? Dec 8, 2008 22:51 |
|
Gary the Llama posted:stuff code:
code:
|
# ? Dec 8, 2008 23:09 |
|
Jethro posted:Assuming SQL2005, change That did the trick. Never seen that before, that's pretty awesome. Now to look it up so I can understand what it does. Thanks a million!
|
# ? Dec 8, 2008 23:20 |
|
Gary the Llama posted:That did the trick. Never seen that before, that's pretty awesome. Now to look it up so I can understand what it does. Thanks a million!
|
# ? Dec 8, 2008 23:55 |
|
Gary the Llama posted:
Okay, instead of showing the data like that, is there any way to show the RemainingPercent on a new row with SchedulingOption = 0? code:
|
# ? Dec 9, 2008 22:24 |
|
A simple union could probably do the trick.code:
Jethro fucked around with this message at 22:50 on Dec 9, 2008 |
# ? Dec 9, 2008 22:28 |
|
Jethro posted:A simple union could probably do the trick. I have to run to a mid-wife appointment, but that didn't have the intended affect. It produced this: code:
|
# ? Dec 9, 2008 22:46 |
|
I am working with a DBA that is refusing to allow me to delete information from a transactional database. His recommended approach is to consume the data and set a "active" field to zero. Once I consume the data it is no longer useful, not even for historical purposes. I am wondering why he would want a bloated database. It seems silly to just leave sometimes-redundant and no-longer-useful information in the database. We are using sql server 2005 if that makes any difference. Would replication cause any problems with deleting items form the database?
|
# ? Dec 9, 2008 23:46 |
|
milkaxor posted:I am working with a DBA that is refusing to allow me to delete information from a transactional database. His recommended approach is to consume the data and set a "active" field to zero. Once I consume the data it is no longer useful, not even for historical purposes. I am wondering why he would want a bloated database. It seems silly to just leave sometimes-redundant and no-longer-useful information in the database. We are using sql server 2005 if that makes any difference. Would replication cause any problems with deleting items form the database? 1. You never know when you might need the data regardless of how stupid it sounds. Questions come up eventually of 'can you cross post how many people ordered every 4 weeks, but only on Tuesdays?' and it turns out for some reason that would actually be excellent info to have. 2. Delete 1 record paves the way for the eventual delete statement with no 'where' clause. - 2a. On this subject, make sure you're running transactional backups every 30 minutes or less. - 2b. Try to make sure all things web are accessing your data via stored procedures and not direct sql statements. This makes performance better via a lot of nifty automatic metrics SQL uses, and it also prevents a lot of that nasty SQL injection you may have heard about. Oh, and it means an easier time of upgrading and future data maintenance, because you can change the layout of tables, databases and the application won't care as long as the stored procedure responds with the same info it always has. 3. Depending on the kind of volume you expect, the amount of disk space saved by actually deleting records vs archiving records is negligible. However since you are archiving data, it will be trivial at a later point to just split it off onto some sort of report database running the cheapest storage you can find to maintain functionality at low cost. 4. Databases are more difficult to restore than standard files. If for any reason at some point down the line something is deleted by mistake, you'll have to restore a 2nd instance of the whole database and run a compare, or identify and rollback the trans log (also undoing everything that wasn't deleted by mistake). This will be an endeavor and the whole time you'll be kicking yourself for not building some sort of undelete feature. In short... it's simply a future proofing practice. The purpose of this database as it is now may change in the future for something totally unknown. You may find yourself needing features that never seemed needed before. Halo_4am fucked around with this message at 23:54 on Dec 9, 2008 |
# ? Dec 9, 2008 23:52 |
|
milkaxor posted:I am working with a DBA that is refusing to allow me to delete information from a transactional database. His recommended approach is to consume the data and set a "active" field to zero. Once I consume the data it is no longer useful, not even for historical purposes. I am wondering why he would want a bloated database. It seems silly to just leave sometimes-redundant and no-longer-useful information in the database. We are using sql server 2005 if that makes any difference. Would replication cause any problems with deleting items form the database? The DBA is probably just nervous about you leaving off/screwing up a WHERE clause and clobbering too much stuff. Much easier to just flat-restrict your DELETE privileges and delete the rows in bulk manually every now and again.
|
# ? Dec 9, 2008 23:53 |
|
Gary the Llama posted:I have to run to a mid-wife appointment, but that didn't have the intended affect. It produced this: You basically need to get 1 query that gives you the rows for scheduling options 1 & 2, and then another query that gives you 1 row for scheduling option 0. Once you have those 2 queries, just union them together, as previously mentioned. code:
One of the concepts with SQL that a lot of programmers have a hard time grasping is that you can't just arbitrarily add rows to a result set. The rows have to come from somewhere.
|
# ? Dec 10, 2008 00:35 |
|
Praetorian42 posted:The DBA is probably just nervous about you leaving off/screwing up a WHERE clause and clobbering too much stuff. Much easier to just flat-restrict your DELETE privileges and delete the rows in bulk manually every now and again. That is a good point, but you could easily use write stored procedures for everything the application developers want to do to the database (inserts, updates, deletes), and then only give them access to select + run the stored procedures. The DBAs / database developers could own the stored procedures, or at least look over the code first to ensure the application developers weren't doing anything stupid with them. Plus, it's a lot more secure this way since less people will have insert / update / delete access to the database.
|
# ? Dec 10, 2008 08:27 |
|
Jethro posted:EDIT: That problem is due to an extra something in the GROUP BY part of the second half of the query. I don't have any idea about what your data means, so I don't know what it is, but I'd guess it's the tbl_SalesTool_DailyOptions.Title, so try taking that out of the second GROUP BY. That did the trick. Unfortunately, when I remove DailyOptions.title from the second group by, I no longer get DailyOptions.title in my result set, which I need. When I put it back in, I get two entries per user where SchedulingOption=0, when there should only be one. And yeah, SQL is my biggest weakest as a programmer. So frustrating.
|
# ? Dec 10, 2008 16:02 |
|
tayl0r posted:That is a good point, but you could easily use write stored procedures for everything the application developers want to do to the database (inserts, updates, deletes), and then only give them access to select + run the stored procedures. Logical deletes make a lot of sense, particularly if you are dealing with anything involving money. You really don't want to ever lose a record involving cash. It has less to do with worrying that you will screw up and more to do with keeping a history.
|
# ? Dec 10, 2008 17:13 |
|
tayl0r posted:You basically need to get 1 query that gives you the rows for scheduling options 1 & 2, and then another query that gives you 1 row for scheduling option 0. Okay, here's a stupid question - though I guess that's what this thread is for. How can I make that second query's results be added to my first query? When I run two separate queries I get two separate result sets, but that's not what I want.
|
# ? Dec 10, 2008 19:06 |
|
Gary the Llama posted:Okay, here's a stupid question - though I guess that's what this thread is for. How can I make that second query's results be added to my first query? When I run two separate queries I get two separate result sets, but that's not what I want. UNION =P if you do this: select A, B, C from foo UNION ALL select X, Y, Z from bar you get: A B C X Y Z The number of columns and the datatypes need to match up for a UNION to work. There is also a difference between UNION and UNION ALL. UNION will check your final result set and remove duplicates while UNION ALL will not. There's also MINUS, which removes any rows from the first result set that also appear in the second. Does that answer your question?
|
# ? Dec 10, 2008 19:41 |
|
If tayl0r's union vs. union all doesn't answer your question, Gary the Llama, I suggest you give us a reproducible, simplified example that exhibits your problem. Make it complete with data, either with create table and insert, or by using CTEs to fake tables, like this:code:
|
# ? Dec 10, 2008 20:18 |
|
The "left join" "inner join" "outer join" syntax always freaks me out. I much prefer using (+) for when I want to use an outer join, and leaving out any of the join keywords. Maybe that's an Oracle thing.
|
# ? Dec 10, 2008 20:25 |
|
tayl0r posted:The "left join" "inner join" "outer join" syntax always freaks me out. I much prefer using (+) for when I want to use an outer join, and leaving out any of the join keywords. Maybe that's an Oracle thing.
|
# ? Dec 10, 2008 22:17 |
|
Keep in mind that if you use the join keyword, you can effectively separate the projection you're making (e.g. all columns from all included tables) from the criteria being applied to that projection. Otherwise, it's all in one big mess, not necessarily sorted, and harder to parse. I suggest you have some patience with join -- I think you will start to get used to and then appreciate it.
|
# ? Dec 11, 2008 01:00 |
|
In SQL Server 2005, how do you check if a temporary object (table or procedure) already exists? They seem to lack object id's (rather, object_id returns null). Edit: It seems tempdb..sysobjects has them listed... Sort of. Edit^2: And it seems that their fully qualified names for object_id to work are tempdb..#table Triple Tech fucked around with this message at 00:32 on Dec 13, 2008 |
# ? Dec 13, 2008 00:28 |
|
MSSql - I have a database with soybean prices over the course of the year from several different elevators. Not all of these elevators had prices updated every day so I need a list of all the elevators with more than 100 days worth of data. How can I accomplish this? The columns pertinent to this problem are cashid (unique identifier for each elevator), and tdate (the date the price was entered). Any help would be super awesome. Thanks!
|
# ? Dec 14, 2008 02:20 |
|
|
# ? May 15, 2024 04:35 |
|
joojoo2915 posted:MSSql - I have a database with soybean prices over the course of the year from several different elevators. Not all of these elevators had prices updated every day so I need a list of all the elevators with more than 100 days worth of data. How can I accomplish this? The columns pertinent to this problem are cashid (unique identifier for each elevator), and tdate (the date the price was entered). Any help would be super awesome. Thanks! code:
|
# ? Dec 14, 2008 02:28 |