|
Hi All
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? 
Thanks.
|
|
|
|
|
Francois Searle wrote: one I thought of is with a cursor
Stored procedures would probably be better performance-wise.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Yes, I agree it should be within a SP, but I was referring more to the SQL within the SP, the methodology as such.
|
|
|
|
|
If you are going the standard sql route and it is slow, maybe query analyzer could shed some light for you where bottlenecks are.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
A cursor is probably one of the worst ways you could do this. You should always attempt, where possible, to use set based queries as they are what database engines are designed to do.
|
|
|
|
|
I know cursors are the worst way, but currently my attempt at doing it using "normal" sql queries is running too long.
Surely this must be a common query? I mean there are so many scenarios where this type of "dataset" is needed.
|
|
|
|
|
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)"
What is the correct way to do this?
|
|
|
|
|
Sunday8PM wrote: I get really strange numbers in some months
Like what? From a quick glance your query looks okay, cannot tell for sure without seeing what kind of data you have in the tables...
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
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.
|
|
|
|
|
Hi,
Sunday8PM wrote: YEAR(timestamp) <= YEAR(?date) AND MONTH(timestamp) <= MONTH(?date)
this is logically incorrect; if you want to check date1 < date2
by year and month, you need the equivalent of
year1 < year2 OR (year1==year2 AND month1<month2)>
The way you did it you missed all months that have year1<year2 and month1>=month2
One way of doing it right in a single compare is by combining months and years:
(12*year1 + month1) < (12*year2 + month2)
modified on Saturday, June 28, 2008 4:21 PM
|
|
|
|
|
Thank you very much! I see now when you explain it! 
|
|
|
|
|
I have a table name victim in which there is a field called "Gender". It has three values 1.Male
2.Female
3.Child
What I want is to write a query that will count all entries and the total of all entries.
Something like below
Male Female Child Total
1 2 0 3
0 4 3 7
Can any 1 help me please on how to write this query?
|
|
|
|
|
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 count(*) Gender from Table Group By Gender
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi.
I think sub select is the simplest way:
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
Ugly but working...
Kjetil
|
|
|
|
|
Hi all
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.
Any help will be appreciated
Thanks
T
|
|
|
|
|
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).
|
|
|
|
|
Read up on Normalisation - this probably going to be the root issue. The common fields in the table should ONLY be IDs, if you are updating data in 3 different tables then your design is wrong.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Where can I found a x64 version from the SQLAGENT?
In the WebPage have a x86 version....
Thanks!
Rafelq2000
|
|
|
|
|
hi
i am created a store procedure like this for select top list user definlly
create procedure ss(@s int)
as
select top @s sno from summa1 order by newid()
it can not create and display error
how to create
with regards,
bretto
|
|
|
|
|
In you are using SQL Server 2000 then this is not possible.
|
|
|
|
|
You can use SET @@rowcount or
create procedure ss(@s int)
as
EXEC 'select top ' + CAST(@s AS CHAR) + ' sno from summa1 order by newid()'
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
SomeGuyThatIsMe wrote: You can use SET @@rowcount or
I'm pretty sure you're thinking of SET ROWCOUNT and not SET @@ROWCOUNT. @@ROWCOUNT tells you how many rows were affected by a statement.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
