I thought I'd put this to you, my fellow devs, as I just cant decide the most efficient route!
Suppose I have 2 tables, 1: id, productid, datedelivered, qty AND 2: id, productid, datesold, qty
I would like to end up having a result with: productid, monthvalue, openingbal, delivered, sold, closingbal
(monthvalue being something like 2008-01-01, i.e. each like is the stock recon for the month)
Now I know there are a few ways of doing this, one I thought of is with a cursor. But not being too clued up with the new features that 2005 provides I am not sure what the most efficient way would be ... any ideas?
I'm using ASP.NET and MySQL. I want to count all posts that were added before or in a specific month. The query is run in a loop and the results will be put into a graph but I get really strange numbers in some months.
"SELECT COUNT(*) FROM posts WHERE YEAR(timestamp) <= YEAR(?date) AND MONTH(timestamp) <= MONTH(?date)"
Well, the height of each bar in the graph are supposed to increase from left to right so I can see what the total was after each month. The result I'm getting though is not a steady increase. It's going up and down, up and down, up and down and then there's obviously something wrong because it's impossible that it would go down if it worked correctly.
why there is two rows? is this result of group by?
BTW I think the simplest way could be declaring function that take some argument and return your desired result.
i don't know the logic but if you have some unique fields at least in group by sets you can also use derived queries(or views)
I Wish the Life Had CTRL-Z
Wizard's First Rule : People are fool,they believe what they want to believe or what they afraid to believe www.subaitech.blogspot.com
select (Select count(*) from Table where Gender = 1) as MaleCount, (Select count(*) from Table where Gender = 2) as FemaleCount, (Select count(*) from Table where Gender = 3) as ChildCount, count(*) as TotalCount from Table
I'm trying to put a condition on a the background color for a matrix subtotal and for some reason it's not working, it always assign the same color for the rest the columns to be the same. It seems that it check fro the first column condition and then assign that color.
I have three different tables in a SQL DB which have at least two common fields amongst each other.
Should I just create a new table that is made of these common fields and have these three tables point to it for those fields?
I don't think there is a right or wrong answer to this question. It depends on the data. If the data is vital and it's very important that it's always updated and the same across the three files, you may want to separate and make a fourth table out of it so you only have to update in one location. However, if it's not that vital of information or it very rarely changes you may want to keep it in each file because it's easier to access. Hope this helps.
Depending on your domain model you could approach this as a reference, or an inheritance relationship. Pick the most appropriate.
Reference is say pulling out "Address1, Address2, Address3" into a Address table, and have Customer.Address, Supplier.Address -> Address.ID with a fk relationship.
Inheritance is where you take out the Address1-3 columns into a Person table, and have Customer.Id -> Person.ID, and Supplier.ID -> Person.ID. (So every record in supplier or customer must have a record in person - this maps directly to inheritance in your business layer, which can be handy).