SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 MIN(DATE) on lots of dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rogerclerkwell
Yak Posting Veteran

United Kingdom
72 Posts

Posted - 03/14/2013 :  05:24:19  Show Profile  Reply with Quote
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
Yak Posting Veteran

United Kingdom
72 Posts

Posted - 03/14/2013 :  05:40:23  Show Profile  Reply with Quote
Just reading up on internet and looks like I need to use RANK...
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

United Kingdom
72 Posts

Posted - 03/14/2013 :  05:46:54  Show Profile  Reply with Quote
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;
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

United Kingdom
72 Posts

Posted - 03/14/2013 :  06:28:57  Show Profile  Reply with Quote
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..
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

United Kingdom
72 Posts

Posted - 03/14/2013 :  06:44:10  Show Profile  Reply with Quote
Hmmm proving very difficult this one to workout....
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 03/14/2013 :  06:50:59  Show Profile  Reply with Quote

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
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 03/14/2013 :  06:53:16  Show Profile  Reply with Quote

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
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 03/14/2013 :  06:55:07  Show Profile  Reply with Quote
face slamp**
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

United Kingdom
72 Posts

Posted - 03/14/2013 :  06:55:57  Show Profile  Reply with Quote
Thanks Bandi and Waterduck, trying it out now.
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

United Kingdom
72 Posts

Posted - 03/14/2013 :  07:06:55  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 03/14/2013 :  07:23:44  Show Profile  Reply with Quote
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
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

United Kingdom
72 Posts

Posted - 03/14/2013 :  07:31:57  Show Profile  Reply with Quote
OK thanks Chandu will give it a test on all data and let you know the outcome.
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

United Kingdom
72 Posts

Posted - 03/15/2013 :  04:54:51  Show Profile  Reply with Quote
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...
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

United Kingdom
72 Posts

Posted - 03/15/2013 :  04:55:41  Show Profile  Reply with Quote
Chandu will try your code now on more than one product and colour to see if it does the same as Waterducks...
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

United Kingdom
72 Posts

Posted - 03/15/2013 :  05:03:39  Show Profile  Reply with Quote
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
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

United Kingdom
72 Posts

Posted - 03/15/2013 :  10:18:47  Show Profile  Reply with Quote
Thanks Chandu have tried your code on main table lots of rows and works great.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 03/18/2013 :  01:58:57  Show Profile  Reply with Quote
quote:
Originally posted by rogerclerkwell

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


Welcome rogerclerkwell....

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000