MIN(DATE) on lots of dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MIN(DATE) on lots of dates

Author

Topic

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2013-03-14 : 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,

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2013-03-14 : 05:40:23

Just reading up on internet and looks like I need to use RANK...

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2013-03-14 : 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
Yak Posting Veteran

85 Posts

Posted - 2013-03-14 : 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
Yak Posting Veteran

85 Posts

Posted - 2013-03-14 : 06:44:10

Hmmm proving very difficult this one to workout....

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 06:50:59

[code]
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[/code]

--
Chandu

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-03-14 : 06:53:16

[code]
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
[/code]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-03-14 : 06:55:07

face slamp**

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2013-03-14 : 06:55:57

Thanks Bandi and Waterduck, trying it out now.

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2013-03-14 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 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
Yak Posting Veteran

85 Posts

Posted - 2013-03-14 : 07:31:57

OK thanks Chandu will give it a test on all data and let you know the outcome.

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2013-03-15 : 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
Yak Posting Veteran

85 Posts

Posted - 2013-03-15 : 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
Yak Posting Veteran

85 Posts

Posted - 2013-03-15 : 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
Yak Posting Veteran

85 Posts

Posted - 2013-03-15 : 10:18:47

Thanks Chandu have tried your code on main table lots of rows and works great.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-18 : 01:58:57