Hello,
I've a table which contains the nest following data:
Date Sales Price
01-01-07 5,00
31-03-07 6,00
16-04-07 5.75
26-04-07 6.25
For example, today is 18-04-07. In my report I want to show the most recent Sales Price, but not the price of next week. I want to see the SalesPrice of 5.75.
How can I get it?
Thx!
This may work... there's probably an easier way.
select top 1 [date], [salesprice]
from table
group by [date], [salesprice]
having max(convert(varchar(10),[date],112)) < convert(varchar(10),current_timestamp,112)
order by [date] desc
cheers,
Andrew
|||Here's a version without the grouping. Try this:
select top 1 SalesPrice
from PriceInfoTable
where Date < getdate()
order by Date desc
Jarret
|||Jarret,
Your solutions works, but I discovered that my problem is a little bit more complex. I still have the same table, but in the table I have more items. My table looks like (for example):
No. Date Sales Price
001 01-01-07 5,00
001 31-03-07 6,00
001 16-04-07 5.75
001 26-04-07 6.25
002 21-01-07 7,00
002 27-02-07 7,00
002 06-04-07 7.75
002 26-04-07 7.25
003 11-01-07 2,00
003 17-02-07 1,00
003 01-04-07 0.75
003 26-04-07 1.25
If I use your code, I only get ItemNo. 001 with a price of 5.75. But I want for every item the most recent price:
001 5.75
002 7.75
003 0.75
I've tried some options, but I can't get the solution. Do you have any suggestions?
|||Here's two ways you could do it...
select distinct t1.id,
( select top 1 price
from PriceInfoTable
where Date < getdate()
and t1.id = #t.id
order by Date desc
) price
from PriceInfoTable t1
select t1.id, t1.price
from PriceInfoTable t1
inner join (
select id, max(date) as MaxDate
from PriceInfoTable
where Date < getdate()
group by id
) t2 on t1.id = t2.id and t1.date = t2.MaxDate
order by t1.id
Hope this helps.
Jarret
|||Jarret,
I don't get the first option.
Can you tell me what the function of 't1.id' is? And also from 't1.id=#t.id.'? Maybe them I can implement it to my query......
thx
|||No the first option has a type, it is actually:
select distinct t1.id,
( select top 1 price
from PriceInfoTable t2
where Date < getdate()
and t1.id = t2.id
order by Date desc
) price
from PriceInfoTable t1
You can also use this query which is the 3rd option:
SELECT [No], SalesPrice
FROM PriceInfoTable p1
WHERE Date = (SELECT MAX(Date) FROM PriceInfoTable p2 WHERE p2.No = p1.No AND Date < GETDATE())
Shyam
|||Sorry,
I was working with my own sample query to get the results you needed and was using a temp table (#t) to hold the data.
The t1.id corresponds to your "No." field and the #t corresponds to the PriceInfoTable in the subquery. Shyam's post has my query with it fixed. Sorry for the inconvenience.
Jarret
|||Jarret/Shyam,
I got all the 3 solutions implemented and they are working 
But the next step for me is to add another column at the "select top"-selection.
In my table I have also a 'Sales Code'. In my report the user must set a filter on this 'Sales Code' (with a parameter). The table looks like:
No. StartingDate Sales Price Sales Code
001 01-01-07 5,00 SC01
001 31-03-07 6,00 SC01
001 16-04-07 5.75 SC02
001 26-04-07 6.25 SC01
If the user select SC01, the price of 6.00 has to be displayed. By selecting SC02, 5.75.
I have tried to insert 'Sales Code' in one of your suggestions on this way,
select distinct t1.id,
( select top 1 price, Sales Code
from PriceInfoTable
where Date < getdate()
and t1.id = #t.id
order by Date desc
) price
from PriceInfoTable t1
but an error returns.
Also inserting 'Sales Code' after the 'Select distinct t1.id'-part, wasn't the right way. By doing this the same price was given to every sales code.
Can you help me out?
Thx a lot!
|||Try this:
select t1.id, t1.price, t1.[sales code]
from PriceInfoTable t1
inner join (
select id, max(date) as MaxDate
from PriceInfoTable
where Date < getdate()
and [Sales Code] = @.SalesCodeParameter
group by id
) t2 on t1.id = t2.id and t1.date = t2.MaxDate
order by t1.id
Hope this helps.
Jarret
|||Jarret,
thx for your help. Your suggestion is working!
But I see that I've me an error by posting an example of the table. The field StartingDate is not only the date but also the time, it's the starting date and time. So the code-part of
WHERE([Starting Date Time]) < GETDATE
has to be something else. I have tried GETDATETIME, but that was not correct. How can I solve it?
Starting Date Time is a combined field. The fields Starting Date and Starting Time are also seperated available, if necessary.
Thx
|||Try this:
WHERE([Starting Date Time]) < GETDATE()
Jarret
|||Jarret,
I did that suggestion and it didn't worked out.
The problem is that the report is filtering only on date, not on date and time....
Do you have a suggestion?
Thx
|||This will remove the time from the current date time.
WHERE([Starting Date Time]) < convert(varchar, GETDATE(), 101)
If you need to remove the time part off of [Starting Date Time], just wrap it in the convert statement as well.
Hope this helps.
Jarret
|||Jarret,
More than one price for a date can be inserted In my table. I can insert a price and give it a starting time of 22.00 tonight. Of course I want to see the price of now if it is not yet 22.00.
I think I need more something like GETTIME or someting like that.
I was thinking of a statement with
WHERE(([Starting Date Time]) < GETDATE() and Starting Date Time]) < GETTIME()
in it.
But this one is not working.
Do you have any suggestion?
Thx
No comments:
Post a Comment