|
I have a table called CostAverage which stores the average value of an item and the date that it was calculated on. Each item has its cost average calculated on different days, and only when changes are made to the item. Since the OnDate could be anything, and the table retains the history, I am looking for a way to get the most recent cost average for each item in the table. Here is a snapshot of the table:
CostAverage
PK CostAverageID
FK UserItemID
CostAverage
OnDate
At first glance, I threw down something like this:
SELECT UserItemID, CostAverage, MAX(OnDate)
FROM CostAverage
GROUP BY UserItemID, CostAverage
Since the CostAverage is always different, that of course returns nearly the entire table. I can't really use an aggregate on the CostAverage since I need the most recent.
Does anyone have a suggestion? I am sure that I wrote a similar query once in the past, but I can't seem to find it (or remember it)
|
|
|
|
|
Try this
SELECT UserItemID, CostAverage,OnDate
FROM CostAverage ca1
where OnDate = (SELECT MAX(OnDate)
FROM CostAverage ca2 where ca2.UserItemID = ca1.UserItemID)
Personally, I would add a flag to indicate the current record and maintain it via a trigger as the above may be slow depending on your indexing.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thank you, that works quite well. The query runs around ~2 seconds, this table should be weeded out in the next 6 months during a refactor so I'm not too worried about performance yet.
Again, thanks.
|
|
|
|
|
No problem
Bob
Ashfield Consultants Ltd
|
|
|
|
|
suppose iam having a string like ...
ramu was a good boy .he is studying in ngbs school .cultatta and he studies very well.
now this is the string now how can i find a string--- ramu was a good boy is existed or not from the above string .
|
|
|
|
|
Use Patindex
Where PATINDEX('%ramu was a good boy%', 'ramu was a good boy .he is studying in ngbs school .cultatta and he studies very well') !=0
Bob
Ashfield Consultants Ltd
|
|
|
|
|
dear ashfield thank u for immediate reply...u gave
Where PATINDEX('%ramu was a good boy%', 'ramu was a good boy .he is studying in ngbs school .cultatta and he studies very well') !=0
is this works in oracle then please the exact how i can use this query in oracle
|
|
|
|
|
Sorry, no idea - you didn't say you were using Oracle. I expected SQL Server like 99% of the questions.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
declare @val as varchar (255)<br />
set @val='ramu was a good boy .he is studying in ngbs school .cultatta and he studies very well.'<br />
<br />
select charindex('ramu was a good boy',@val)
result:
1 = string exists
0 = string doesn't exists
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
In Oracle use INSTR (or it's variants):
WHERE 0 < INSTR('ramu was a good boy .he is studying in ngbs school .cultatta and he studies very well',
'ramu was a good boy')
Mika
|
|
|
|
|
we know that when we insert data,update data and delete data against any table then that record is stored in transaction log of sql server. so how can i see those data in a very friendly way. please help with sample sql code.
tbhattacharjee
|
|
|
|
|
As far as I know, there is no way through SQL to read the transaction log of any database. You might find some tools out there to read the transaction log, but you would need to be very careful that you don't interfere with the running database.
Why don't you create an audit table that would hold these before and after values of the data ? You can implement this logic by creating triggers that would write to this table during an insert or delete. This is very standard practice when developing a system that requires auditing functionality.
|
|
|
|
|
First, apologies for hijacking this thread...
What kind of table layout do you use for the Audit tables? Right now, we have some old legacy apps that are being rewritten and we are going to be adding auditing to them via triggers. Is there a good / standard practice format for the tables?
|
|
|
|
|
The format I've seen before is typically an exact copy of the original table being audited along with some additional info such as datatime, userID and transaction code (Insert,Update,Delete). We used to name these tables something like EMPLOYEE_AUDIT. By keeping the format close to the original table format, it made it easier to implement.
Take into consideration the size of these audit tables !
I have seen audit tables grow at 500MB per month, which can cause serious performance drain on your DB server. Google around for other ideas on how to implement auditing in your new system.
|
|
|
|
|
hi,
I m trying to get values from three tables.
1) its has the sale id and product id
2) it has the product details based on id from 1st table,
3) it has the quantity of product being selected by the query.
My problem is I need to check the count of the product based on various sizes and if its more than 0 i need to display available else sold out.
I tried the following query.
if I remove the count it works well to get the details but If I m gonna get the count it throws error.
any suggestion where this query is wrong??
SELECT SaleProduct_tb.ProductId, Product_tb.BasePrice, Product_tb.SalePrice, Product_tb.PName, Product_tb.Image1, COUNT(ProductSizeQty.Quantity)
AS Expr1
FROM SaleProduct_tb INNER JOIN
Product_tb ON SaleProduct_tb.ProductId = Product_tb.ProductId INNER JOIN
ProductSizeQty ON Product_tb.ProductId = ProductSizeQty.ProductId
WHERE (SaleProduct_tb.SaleId = @SaleId) AND (SaleProduct_tb.CatId = @CatId) AND (SaleProduct_tb.SubCatId = @SubCatId) AND
(Product_tb.BrandId = @BrandId) ORDER BY saleProduct_tb.ProductId
Vijay V.
Yash Softech
|
|
|
|
|
Don't cross post, its rude.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
to be fair the guy was told to post here after posting in the asp.net forum.
|
|
|
|
|
someone suggested me to put that here. So I added here too.
sorry for that.
Vijay V.
Yash Softech
|
|
|
|
|
add a GROUP BY clause (after the WHERE clause adn before the ORDER BY one) listing all the fields in the SELECT clause excpet the one you're COUNTing
|
|
|
|
|
hi,
thanks for the help.
Vijay V.
Yash Softech
|
|
|
|
|
Hi
I hav got a stored procedure inside which im fetrching data from four tables..I want to genrate an excel file Populated with these results in the Stored procedure itself
Is it Possible? If yes then how can i do that?
Thnx and Regards
Joe
|
|
|
|
|
No, it is not...
You need to do this at the application level.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
You can write a DTS (or SSIS if using SQL Server 2005 or above) to run the stored procedure and export the results to an Excel file
====================================
Transvestites - Roberts in Disguise!
====================================
|
|
|
|
|
How about starting from Excel and pulling the data from the database into Excel ?
In Excel, go to the Data menu, Import External Data and use New Database Query to run the necessary query in your database.
Just a thought.
|
|
|
|
|
Hi,
I need to have a column value from multiple rows as one value
e.g. select name from employee
returns> name
-----
david
malcom
rave
I need to have this value as
name
----
david, malcom,rave (in one row)
Please note that I am on sql server 2000
Can anyone help in generating such sql script.
regards
Vijay
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
