Problem with query
-
Hi All,
It is possible to resolve my problem without using cursors ?
Problem:
I have this table:
Item |StartDate |EndDate | Price
1 1/1/2004 31/1/2004 100
1 1/2/2004 31/2/2004 100
1 1/3/2004 31/3/2004 150
2 1/1/2004 31/1/2004 200
2 1/2/2004 31/2/2004 200
1 1/4/2004 31/4/2004 250
1 1/5/2004 31/5/2004 250
1 1/6/2004 31/6/2004 250
And I would like recive table as below:
Item |StartDate |EndDate | Price
1 1/1/2004 31/2/2004 100
1 1/3/2004 31/3/2004 150
2 1/1/2004 31/2/2004 200
1 1/4/2004 31/6/2004 250
Have you got any idea ??
-
It appears that you are looking for the minimum start date and maximum end date by item and price. If that is correct, then :
Select Item, Min(StartDate) as StartDate, Max(EndDate) as EndDate, Price
From Table
Group By Price, Item
Order By Price, Item
-
Exactly, great thanks !!
-
Unfortunately, I wouldn't be so sure this will work. It would, if the StartDate and EndDate are datetime columns... however, a look at posted values tells me that this is in fact a varchar column. Datetime wouldn't store February 31, so if these are real values and not result of carelessness when posting the example, it has to be varchar.
To make things worse, it is stored as DD/MM/YYYY and without leading zeros. In such situation, MIN and MAX will yield incorrect results quite often - this is ordered ASCENDING:
1/1/2005
1/12/2004
1/9/2005
31/11/2003
31/4/2003
31/6/2001
Please let us know whether the values in your post were real; if yes, you won't be able to use the posted solution.
-
Hi, I have column datatime, but I have problem in this case:
Item |StartDate |EndDate | Price
1 1/1/2004 31/1/2004 100
1 1/2/2004 31/2/2004 100
1 1/3/2004 31/3/2004 150
1 1/4/2004 31/4/2004 100
Instead of:
Item |StartDate |EndDate | Price
1 1/1/2004 31/2/2004 100
1 1/3/2004 31/3/2004 150
1 1/4/2004 31/4/2004 100
I have:
Item |StartDate |EndDate | Price
1 1/1/2004 31/4/2004 100
1 1/3/2004 31/3/2004 150
Hmm...any Idea, how resolve this problem ??
Thanks.
-
Vladan - how can you tell this is a varchar column, and not just different date settings? I'm in the UK, and dobrzak's posting is exactly how I would see date columns (due to the language setting).
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Having said that, I've just noticed that some of the 'dates' are invalid dates (such as 31 Feb). Dobrzak - can you explain?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Sorry for confused you
This is only example, I write this myself and didint't notice that date was wrong, I'm really sory. But in my table this column is correct (smalldatetime type).
Can you help me with my problem ?
Best regards,
dobrzak
-
Ryan, I'm from Europe as well, so DD/MM/YYYY seems normal to me
it was really just the dates like 31/2/2004 that made me think it has to be varchar (or an error when posting, which is much better :-)).dobrzak,
please explain what is the required result. From your originally posted data it looked that this (what you now post as being incorrect) is probably what you want.
Do you want to show the entire price history for each item, only "summing" consecutive months when the price remains unchanged?
What are the data like? Is there an entry for each item and month, or are gaps between end of one price and start of another for the same item possible? Is start date always the 1st of a month and EndDate last day of a month? If there are gaps, what to do with them (no entry means no price this month, or unchanged from last month?)
How do you want to order the result? (initial post implied ORDER BY Price, but obviously that wasn't required.. it is really hard to tell on a set of 4 rows what is required and what is just a coincidence). Why is in your original post the row with item 2 on the place where it is?
The more you explain about the problem, the better we can help you!
-
Hi,
I dont have any gaps between data, startdate can be different than 1st of a month and EndDate can be different than 31st. I want show the entire price history for each item, only "summing" consecutive time period when the price remains unchanged
Example:
Item |StartDate |EndDate | Price
1 1/1/2004 31/1/2004 100
1 1/2/2004 31/2/2004 100
1 1/3/2004 31/3/2004 150
2 1/1/2004 31/1/2004 200
2 1/2/2004 31/2/2004 200
1 1/4/2004 31/4/2004 250
1 1/5/2004 31/5/2004 250
1 1/6/2004 31/6/2004 100
If run this query:
Select Item, Min(StartDate) as StartDate, Max(EndDate) as EndDate, Price
From Table
Group By Price, Item
Order By Price, Item
I get:
Item |StartDate |EndDate | Price
1 1/1/2004 31/6/2004 100
1 1/3/2004 31/3/2004 150
2 1/1/2004 31/2/2004 200
1 1/4/2004 31/5/2004 250
Instead of which I want to receive:
Item |StartDate |EndDate | Price
1 1/1/2004 31/2/2004 100
1 1/3/2004 31/3/2004 150
2 1/1/2004 31/2/2004 200
1 1/4/2004 31/5/2004 250
1 1/6/2004 31/6/2004 100
It is possible, without using cursor ?
Thanks.
-
Please explain the position of the red row :
Item |StartDate |EndDate | Price
1 1/1/2004 31/2/2004 100
1 1/3/2004 31/3/2004 150
2 1/1/2004 31/2/2004 200
1 1/4/2004 31/5/2004 250
1 1/6/2004 31/6/2004 100
As you probably know, rows in tables are not stored in any special order and can also be returned in any order, unless you specify ORDER BY clause. If you are placing the red row where it is just because it is the place where it appeared in the original (probably unsorted) data, you'll have to reconsider your approach. There is no way to make sure that you'll get this from SQL in the same order every time, not even with a cursor.
-
I don't much like it, but here's one possibility...
--This SQL script is safe to run
--Create and populate @ItemPeriod table
declare @ItemPeriod table (Item int, StartDate datetime, EndDate datetime, Price int)
insert @ItemPeriod
select 1, '1 jan 2004', '31 jan 2004', 100
union select 1, '1 feb 2004', '29 feb 2004', 100
union select 1, '1 mar 2004', '31 mar 2004', 150
union select 2, '1 jan 2004', '31 jan 2004', 200
union select 2, '1 feb 2004', '29 feb 2004', 200
union select 1, '1 apr 2004', '30 apr 2004', 250
union select 1, '1 may 2004', '31 may 2004', 250
union select 1, '1 jun 2004', '30 jun 2004', 100
--Create an ordered version of @ItemPeriod
declare @OrderedItemPeriod table (id int identity(1, 1), Item int, StartDate datetime, EndDate datetime, Price int)
insert @OrderedItemPeriod
select
Item, StartDate, EndDate, Price
from
@ItemPeriod
order by
Item, StartDate
--Create and populate a 'bin' table which will hold the 'bins' the various item periods can fit in
declare @Bin table (id int identity(1, 1), Item int, StartDate datetime, EndDate datetime)
insert into @Bin (Item, StartDate)
select a.Item, a.StartDate
from @OrderedItemPeriod a left outer join @OrderedItemPeriod b
on a.Item = b.Item and a.id = b.id + 1 and a.price = b.price and a.StartDate = b.EndDate + 1
where b.id is null
order by a.Item, a.StartDate
--update the bin end dates
update a set EndDate = isnull(b.StartDate, '31 Dec 9999') - 1
from @Bin a left outer join @Bin b on a.Item = b.Item and a.id = b.id - 1
--Join @ItemPeriod to our bins table to get the final results
select a.Item, min(a.StartDate) as StartDate, max(a.EndDate) as EndDate, price
from @ItemPeriod a
inner join @Bin b on a.Item = b.Item and a.StartDate between b.StartDate and b.EndDate
group by a.Item, b.StartDate, Price
order by a.Item, b.StartDate --or whatever you want to order by
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
I've order by Item, StartDate.
-
If so, then the result should be following... is this what you need?
Item |StartDate |EndDate | Price
1 1/1/2004 31/2/2004 100
1 1/3/2004 31/3/2004 150
1 1/4/2004 31/5/2004 250
1 1/6/2004 31/6/2004 100
2 1/1/2004 31/2/2004 200
-
Yes, exactly.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply