| Author |
 Topic  |
|
|
rogerclerkwell
Starting Member
United Kingdom
24 Posts |
 Posted - 03/14/2013 : 05:24:19
|
Hoping this is one of those when you know it's is simple!
Having trouble working out how to get the MIN DATE everytime the Date changes, i.e here's the test table:
PRODUCT COLOUR THE_STATE DATE
Bike12 Green Full Priced 01-Jan-13
Bike12 Green Full Priced 05-Jan-13
Bike12 Green Full Priced 07-Jan-13
Bike12 Green Full Priced 10-Jan-13
Bike12 Green In the Sale 12-Jan-13
Bike12 Green In the Sale 14-Jan-13
Bike12 Green In the Sale 15-Jan-13
Bike12 Green Full Priced 17-Jan-13
Bike12 Green Full Priced 18-Jan-13
Bike12 Green Full Priced 19-Jan-13
Bike12 Green In the Sale 20-Jan-13
Bike12 Green In the Sale 21-Jan-13
Bike12 Green In the Sale 22-Jan-13
Bike12 Green Full Priced 23-Jan-13
Bike12 Green Full Priced 24-Jan-13
Bike12 Green Full Priced 25-Jan-13
I'm doing this select:
Select PRODUCT, COLOUR, THE_STATE, MIN(DATE)
FROM myTestTable
GROUP BY PRODUCT, COLOUR, THE_STATE
Which returns:
PRODUCT COLOUR THE_STATE DATE
Bike12 Green Full Priced 01-Jan-13
Bike12 Green In the Sale 12-Jan-13
But I need the MIN(Date) everytime it changes in the table so returns this:
PRODUCT COLOUR THE_STATE DATE
Bike12 Green Full Priced 01-Jan-13
Bike12 Green In the Sale 12-Jan-13
Bike12 Green Full Priced 17-Jan-13
Bike12 Green In the Sale 20-Jan-13
Bike12 Green Full Priced 23-Jan-13
All ideas welcome?
Thanks,
|
Edited by - rogerclerkwell on 03/14/2013 05:24:46
|
|
|
rogerclerkwell
Starting Member
United Kingdom
24 Posts |
Posted - 03/14/2013 : 05:40:23
|
| Just reading up on internet and looks like I need to use RANK... |
 |
|
|
rogerclerkwell
Starting Member
United Kingdom
24 Posts |
Posted - 03/14/2013 : 05:46:54
|
Think I may have worked this out doing something like this from: http://stackoverflow.com/questions/1003174/tsql-return-rows-with-earliest-dates
WITH AllRows AS (
SELECT id, status, date_this_status,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY date_this_status ASC) AS row,
FROM (SELECT * FROM Table1 UNION SELECT * FROM Table1_hist) Both_tables
)
SELECT id, status, date_this_status
FROM AllRows
WHERE row = 1
ORDER BY id; |
|
|
|
rogerclerkwell
Starting Member
United Kingdom
24 Posts |
Posted - 03/14/2013 : 06:28:57
|
| Nope that ROW_NUMBER didn't work as it only puts ROW_NUMBER = 1 against the first in the list not each time it changes from example above.. |
|
|
|
rogerclerkwell
Starting Member
United Kingdom
24 Posts |
Posted - 03/14/2013 : 06:44:10
|
| Hmmm proving very difficult this one to workout.... |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 03/14/2013 : 06:50:59
|
DECLARE @RowsProcsed TABLE(PRODUCT VARCHAR(10), COLOUR VARCHAR(15), THE_STATE VARCHAR(30), DATE DATE)
INSERT INTO @RowsProcsed
SELECT 'Bike12', 'Green', 'Full Priced', '01-Jan-13' union all
SELECT 'Bike12', 'Green', 'Full Priced', '05-Jan-13' union all
SELECT 'Bike12', 'Green', 'Full Priced', '07-Jan-13' union all
SELECT 'Bike12', 'Green', 'Full Priced', '10-Jan-13' union all
SELECT 'Bike12', 'Green', 'In the Sale', '12-Jan-13' union all
SELECT 'Bike12', 'Green', 'In the Sale', '14-Jan-13' union all
SELECT 'Bike12', 'Green', 'In the Sale', '15-Jan-13' union all
SELECT 'Bike12', 'Green', 'Full Priced', '17-Jan-13' union all
SELECT 'Bike12', 'Green', 'Full Priced', '18-Jan-13' union all
SELECT 'Bike12', 'Green', 'Full Priced', '19-Jan-13' union all
SELECT 'Bike12', 'Green', 'In the Sale', '20-Jan-13' union all
SELECT 'Bike12', 'Green', 'In the Sale', '21-Jan-13' union all
SELECT 'Bike12', 'Green', 'In the Sale', '22-Jan-13' union all
SELECT 'Bike12', 'Green', 'Full Priced', '23-Jan-13' union all
SELECT 'Bike12', 'Green', 'Full Priced', '24-Jan-13' union all
SELECT 'Bike12', 'Green', 'Full Priced', '25-Jan-13'
SELECT Product, COLOUR, THE_STATE, MIN(DATE) AS StartDate
FROM @RowsProcsed t
OUTER APPLY (SELECT MIN(DATE) AS MinDate
FROM @RowsProcsed
WHERE DATE > t.DATE
AND THE_STATE <> t.THE_STATE
AND COLOUR = t.COLOUR
AND PRODUCT= t.PRODUCT
)t1
GROUP BY Product, colour, THE_STATE,MinDate
ORDER BY StartDate
--
Chandu |
|
|
|
waterduck
Aged Yak Warrior
Malaysia
791 Posts |
Posted - 03/14/2013 : 06:53:16
|
declare @a table(PRODUCT varchar(20), COLOUR varchar(20), THE_STATE varchar(20), [DATE] DATE)
insert into @a select
'Bike12','Green','Full Priced','01-Jan-13' union all select
'Bike12','Green','Full Priced','05-Jan-13' union all select
'Bike12','Green','Full Priced','07-Jan-13' union all select
'Bike12','Green','Full Priced','10-Jan-13' union all select
'Bike12','Green','In the Sale','12-Jan-13' union all select
'Bike12','Green','In the Sale','14-Jan-13' union all select
'Bike12','Green','In the Sale','15-Jan-13' union all select
'Bike12','Green','Full Priced','17-Jan-13' union all select
'Bike12','Green','Full Priced','18-Jan-13' union all select
'Bike12','Green','Full Priced','19-Jan-13' union all select
'Bike12','Green','In the Sale','20-Jan-13' union all select
'Bike12','Green','In the Sale','21-Jan-13' union all select
'Bike12','Green','In the Sale','22-Jan-13' union all select
'Bike12','Green','Full Priced','23-Jan-13' union all select
'Bike12','Green','Full Priced','24-Jan-13' union all select
'Bike12','Green','Full Priced','25-Jan-13'
select *
from (
select *
, rn2 = ROW_NUMBER() over (partition by rn order by [date])
from (
select *
, rn = ROW_NUMBER() over (order by [date])- row_number() over (partition by the_state order by the_state)
from @a
)src
)src
where rn2 = 1
|
|
|
|
waterduck
Aged Yak Warrior
Malaysia
791 Posts |
Posted - 03/14/2013 : 06:55:07
|
| face slamp** |
|
|
|
rogerclerkwell
Starting Member
United Kingdom
24 Posts |
Posted - 03/14/2013 : 06:55:57
|
| Thanks Bandi and Waterduck, trying it out now. |
|
|
|
rogerclerkwell
Starting Member
United Kingdom
24 Posts |
Posted - 03/14/2013 : 07:06:55
|
| Thanks looks like they both work OK but I think waterduck your code may run faster, although I need to test it out a lot of data. |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 03/14/2013 : 07:23:44
|
I have checked Execution plan for sample data.. Mine took 10% less than Waterduck's solution....
And let me know the execution cost for actual data... we will get to know the optimised solution for future purpose
--
Chandu |
|
|
|
rogerclerkwell
Starting Member
United Kingdom
24 Posts |
Posted - 03/14/2013 : 07:31:57
|
| OK thanks Chandu will give it a test on all data and let you know the outcome. |
|
|
|
rogerclerkwell
Starting Member
United Kingdom
24 Posts |
Posted - 03/15/2013 : 04:54:51
|
hmmm sorry Waterduck your solution doesn't work when there are other products and colours i.e if you also add to the table another product set:
'Bike44','Green','Full Priced','01-Jan-13' union all select
'Bike44','Green','Full Priced','05-Jan-13' union all select
'Bike44','Green','Full Priced','07-Jan-13' union all select
'Bike44','Green','Full Priced','10-Jan-13' union all select
'Bike44','Green','In the Sale','12-Jan-13' union all select
'Bike44','Green','In the Sale','14-Jan-13' union all select
'Bike44','Green','In the Sale','15-Jan-13' union all select
'Bike44','Green','Full Priced','17-Jan-13' union all select
'Bike44','Green','Full Priced','18-Jan-13' union all select
'Bike44','Green','Full Priced','19-Jan-13' union all select
'Bike44','Green','In the Sale','20-Jan-13' union all select
'Bike44','Green','In the Sale','21-Jan-13' union all select
'Bike44','Green','In the Sale','22-Jan-13' union all select
'Bike44','Green','Full Priced','23-Jan-13' union all select
'Bike44','Green','Full Priced','24-Jan-13' union all select
'Bike44','Green','Full Priced','25-Jan-13'
Also I guess will do the same if another colour too... |
|
|
|
rogerclerkwell
Starting Member
United Kingdom
24 Posts |
Posted - 03/15/2013 : 04:55:41
|
| Chandu will try your code now on more than one product and colour to see if it does the same as Waterducks... |
|
|
|
rogerclerkwell
Starting Member
United Kingdom
24 Posts |
Posted - 03/15/2013 : 05:03:39
|
Tried code on other products, colours and dates and i think Chandu (bandi) your codes is working OK, going to try it on main data which has millions of rows.
thanks.
'Bike44','Green','Full Priced','01-Jan-13' union all select
'Bike44','Green','Full Priced','05-Jan-13' union all select
'Bike44','Green','Full Priced','07-Jan-13' union all select
'Bike44','Green','Full Priced','10-Jan-13' union all select
'Bike44','Green','In the Sale','12-Jan-13' union all select
'Bike44','Green','In the Sale','14-Jan-13' union all select
'Bike44','Green','In the Sale','15-Jan-13' union all select
'Bike44','Green','Full Priced','17-Jan-13' union all select
'Bike44','Green','Full Priced','18-Jan-13' union all select
'Bike44','Green','Full Priced','19-Jan-13' union all select
'Bike44','Green','In the Sale','20-Jan-13' union all select
'Bike44','Green','In the Sale','21-Jan-13' union all select
'Bike44','Green','In the Sale','22-Jan-13' union all select
'Bike44','Green','Full Priced','23-Jan-13' union all select
'Bike44','Green','Full Priced','24-Jan-13' union all select
'Bike44','Green','Full Priced','25-Jan-13'
'Bike44','Black','Full Priced','01-Jan-13' union all select
'Bike44','Black','Full Priced','05-Jan-13' union all select
'Bike44','Black','Full Priced','07-Jan-13' union all select
'Bike44','Black','Full Priced','10-Jan-13' union all select
'Bike44','Black','In the Sale','12-Jan-13' union all select
'Bike44','Black','In the Sale','14-Jan-13' union all select
'Bike44','Black','In the Sale','15-Jan-13' union all select
'Bike44','Black','Full Priced','17-Jan-13' union all select
'Bike44','Black','Full Priced','18-Jan-13' union all select
'Bike44','Black','Full Priced','19-Jan-13' union all select
'Bike44','Black','In the Sale','20-Jan-13' union all select
'Bike44','Black','In the Sale','21-Jan-13' union all select
'Bike44','Black','In the Sale','22-Jan-13' union all select
'Bike44','Black','Full Priced','23-Jan-13' union all select
'Bike44','Black','Full Priced','24-Jan-13' union all select
'Bike44','Black','Full Priced','25-Jan-13'
'Bike44','Purple','Full Priced','03-Jan-13' union all select
'Bike44','Purple','Full Priced','04-Jan-13' union all select
'Bike44','Purple','Full Priced','05-Jan-13' union all select
'Bike44','Purple','Full Priced','06-Jan-13' union all select
'Bike44','Purple','In the Sale','09-Jan-13' union all select
'Bike44','Purple','In the Sale','11-Jan-13' union all select
'Bike44','Purple','In the Sale','12-Jan-13' union all select
'Bike44','Purple','Full Priced','17-Jan-13' union all select
'Bike44','Purple','Full Priced','18-Jan-13' union all select
'Bike44','Purple','In the Sale','19-Jan-13' union all select |
|
|
|
rogerclerkwell
Starting Member
United Kingdom
24 Posts |
Posted - 03/15/2013 : 10:18:47
|
| Thanks Chandu have tried your code on main table lots of rows and works great. |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 03/18/2013 : 01:58:57
|
quote:
Originally posted by rogerclerkwell
Thanks Chandu have tried your code on main table lots of rows and works great.
Welcome rogerclerkwell....
--
Chandu |
|
|
| |
Topic |
|
|
|
MIN(DATE) on lots of dates
