SQL Query help?

[ninefoureight]

I'm trying to create a query which will extract the chronological improvement in my data, i have used nested queries before to get a list of the bests (best being the smallest time duration) per category but this is stumping me. I am thinking i need set a variable to store the first matching value but thereafter im not really sure of my mechanism to list all the results - can anyone either point me in the direction of a good example (my googlefu is failing..) or help here please?

Example data
Date Duration
1/8/1999 28.99 seconds
7/12/2001 39.99 seconds
1/3/2012 24.54 seconds
1/3/2013 23.45 seconds
1/4/2013 25.35 seconds

Example result in rank
Date Duration
1/8/1999 28.99 seconds
1/3/2012 24.54 seconds
1/3/2013 23.45 seconds

Thanks in advance!

 

[davetherave2]

just so I have this clear are you looking for the best time in a given year?

What happens if you have two the same? Do you want to show all results or just the first occurrence?

and I take it date and duration are two separate fields in your table.

 

[davetherave2]

presuming the above is correct then this was a 10 minute query I wrote that should work for you:

--DELCARE MY DATA TABLE 
DECLARE @dateTable	TABLE 
(
    dateValue	DATETIME 
  , duration	FLOAT 
)


--INSERT SOME TEST DATA
INSERT INTO @dateTable
SELECT 
	  '01 August 1999'
	, 28.99

INSERT INTO @dateTable
SELECT 
	  '07 December 2001'
	, 39.99

	INSERT INTO @dateTable
SELECT 
	  '01 March 2012'
	, 24.54

	INSERT INTO @dateTable
SELECT 
	  '01 March 2012'
	, 24.54

INSERT INTO @dateTable
SELECT 
	  '01 March 2013'
	, 23.45

INSERT INTO @dateTable
SELECT 
	  '01 April 2013'
	, 25.35



--SELECT THE VALUES WE HAVE JUST INSERTED
SELECT 
	* 
FROM 
	@dateTable



--DECLARE A WORKING TABLE TO INSERT THE VALUES INTO 
DECLARE @workingTable TABLE 
(
	  dateValue			DATETIME 
	, duration			FLOAT 
	, yearValue			INT				NULL
	, LowestDuration	BIT				NULL
)

--INSERT VALUES INTO A WORKING TABLE 
INSERT INTO @WorkingTable (dateValue,duration,yearValue) 
SELECT 
	  dateValue
	, duration
	, datepart(year,dateValue)
FROM 
	@dateTable


SELECT 
	* 
FROM 
	@WorkingTable

--UPDATE THE WORKING TABLE WITH THE LOWEST FOUND VALUE 
UPDATE 
	@WorkingTable
	SET LowestDuration = CASE WHEN duration = minDuration THEN 1 ELSE 0 END 
FROM 
(
SELECT 
	  yearValue as selectedyear
	, min(duration) as minDuration 
FROM 
	@WorkingTable
GROUP BY 
	yearValue 
)SubData 
WHERE 
	yearValue = selectedyear

--SHOW ME THE FINAL WORKING TABLE 
SELECT 
	* 
FROM 
	@WorkingTable

 

[ninefoureight]

Yes they are two fields. However what I am looking for is a list of results where the previous best has been lowered. I will be grouping by day. So there could be forty occasions where the best has improved in a calendar year in none in another.

Thank you for your other quickly reply with the query though!

 

[davetherave2]

so rather than just finding the lowest value for a given year you want to break this down into months and days and then find the order of the results?

so for example:

1/3/2013 23.45 seconds (1st Place)
1/4/2013 25.35 seconds (2nd Place)
1/4/2013 25.36 seconds (3rd Place)

etc

where first place is the lowest value

Does that query do most of what you want?

 

[ninefoureight]

No sorry (probably why I haven't been able to Google for answer, my description is poor! )

I want to return only the the values which better the oldest best I'll call this O. Then when the best NEW1 betters OLD then the next value returned is the next better value NEW2 than the new best (NEW2 < NEW1). There can be values in chronological order in the data between these values which will not be returned because they don't improve the last.

If not clear still I'll post a bigger dataset and desired result in the morning. Thanks for your help and patience!

 

[davetherave2]

I guess the question really is how are you currently recording the best value are you storing this in a table or are you wanting to recalculate this every time you run this process.

Storing a reference to the best value at point of entry will make things easier for you.

so for example adding two bit fields to the data such as

Date Duration CurrentBest PreviousBest



So when saving the values you can then check to see if there is a best value and if not set this value as the best and then next time you add a value if that is better than the first value and within the required date range it can then become best and the first value will change to second best.

If a third value is added and either knocks one of the values off or is worse then the current best and second best then neither setting is set and then you can ignore it when pulling back the final dataset.

Hopefully that makes sense otherwise email in trust with a bigger dataset of what it is your after and I will see what I can do for you.

 

[fretted]

I would use cursors; I presume we are talking about ms sql?

 

[visibleman]

Am i missing something? Surely you just store the date and time of the when the record was inserted along with the 'users' (if it's specific to a user then you'll obviously want a user ID column and include that in the select) time/duration and then just SELECT the rows based on the date/time range you're wanting, sort(/ORDER) on the users time/duration and record date/time columns and throw in a LIMIT if need be - it'll return the top best results on your date/time range.

 

[ninefoureight]

I think the key word is the progression of the improvement over all time. In a time line fashion. So from this data:

Value, Date.

12 01/12/2013
12 01/12/2013
12 02/12/2013
12 02/12/2013
12 03/12/2013
12 04/12/2013
13 04/12/2013
13 05/12/2013
13 07/12/2013
12 08/12/2013
12 09/12/2013
12 10/12/2013
12 11/12/2013
14 12/12/2013
12 13/12/2013
13 14/12/2013
12 14/12/2013
13 15/12/2013
14 16/12/2013

I would want to return (..assuming my data was already in a chronological order, but lets not complicate things further just now! ;])

12 01/12/2013
13 04/12/2013
14 12/12/2013

The lowest value and earliest date. The next instance where it is bettered and finally the last in this dataset where it is improved.

Does this assist further?

 

[visibleman]

Why not just use three separate queries (as you've said, you feed in the 'value' result from the previous query)? Certainly the easiest solution and if indexed correctly then execution time is minimal.

Edit - Union and set variables?

 

[fretted]

How about this

Using cursors to achieve what the OP asked for:

/** We need some dummy data to play around with, so use example data 
	given.  The dates have been entered using USA date format.
**/

DECLARE @data TABLE (
	value				FLOAT,
	datevalue			DATE
)

INSERT INTO @data
VALUES
	(12,N'12/01/2013'),
	(12,N'12/01/2013'),
	(12,N'12/01/2013'),
	(12,N'12/02/2013'),
	(12,N'12/03/2013'),
	(12,N'12/04/2013'),
	(13,N'12/04/2013'),
	(13,N'12/05/2013'),
	(13,N'12/07/2013'),
	(12,N'12/08/2013'),
	(12,N'12/09/2013'),
	(12,N'12/10/2013'),
	(12,N'12/11/2013'),
	(14,N'12/12/2013'),
	(12,N'12/13/2013'),
	(13,N'12/14/2013'),
	(12,N'12/14/2013'),
	(13,N'12/15/2013'),
	(14,N'12/16/2013')

/**	Use cursors to iterate through a sorted table of example data and 
	build a new table of values we are interested in.
**/
	
DECLARE @bestvalue	FLOAT=0,
		@value		FLOAT,
		@datevalue	DATE
		
DECLARE @finaltable TABLE (
		value		FLOAT,
		datevalue	DATE
		)

DECLARE db_cursor CURSOR FOR
SELECT value,datevalue

FROM @data
ORDER BY datevalue ASC

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @value,@datevalue

WHILE @@FETCH_STATUS=0
BEGIN
	IF @value > @bestvalue
		BEGIN
			SET @bestvalue= @value
			INSERT INTO @finaltable
			VALUES(@value,@datevalue)
		END
	FETCH NEXT FROM db_cursor INTO @value,@datevalue
END

CLOSE db_cursor
DEALLOCATE db_cursor

/** Output the final table **/

SELECT
	value,
	datevalue
FROM
@finaltable

You could also achieve this using recursive CTEs but that stuff blows my mind