March 6, 2012 at 12:59 pm
Here's the basics of where I'm coming from. I'll do some more coding later on, so ignore the variable stuff below as actual syntax and more of pseudocode.
WHERE MODIFY_TIMESTAMP = CREATE_TIMESTAMP
SET VARIABLE_1 CREATE_TIMESTAMP
WHERE MODIFY_TIMESTAMP <> CREATE_TIMESTAMP
SET VARIABLE_2 MAX(CREATE OR MODIFY TIMESTAMP) -- the largest of the two values
Now I have a History table. I need to select the code from history where (and this is where I get lost) "the largest value between variable_1 and variable_2 listed above". Both variables are datetime data types. Both variables will be set with a select statement. So for the sake of sanity and this forum, lets say variable 1 = '2011-04-07 17:58:05.000' and variable 2 = '2010-06-14 12:07:43.093'. How do I get the where clause to pick the > datetime (or '2010-06-14 12:07:43.093') when it returns the results from variables???
March 6, 2012 at 1:10 pm
Can you explain a little more clearly what the issue is?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 6, 2012 at 1:16 pm
Are you trying to figure out which column would have a more recent date between the date the row was created and the date the row was modified?
Isnull(modified, created)
This would get the created date when it the row has not been modified. And assuming your programming logic is not completely absurd you can't modify a row before it is created. Even if you populate modified and created on creation (which I don't typically do because it can't be modified the same time it is created) this logic would still work.
Does that help answer your question??
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2012 at 5:50 am
Disregarding the fact that indeed create date is normally before the modification date, it is easy to determine the latest of two (or more) date values from a single row using this little trick:
select t.*, x.LatestDate
from #tbl t
cross apply (
select max(dt) as LatestDate
from (
select t.CreateDate as dt
union all
select t.UpdateDate as dt
) x
March 7, 2012 at 7:15 am
You can user ROW_NUMBER() OVER() trick:
--=== Prepare data
IF OBJECT_ID('tempdb..#history') IS NOT NULL drop table #history
CREATE TABLE #history
(HistoryId int IDENTITY PRIMARY KEY,
ItemId int NOT NULL, -- FOREIGN KEY to item this history is for
MODIFY_TIMESTAMP datetime
)
INSERT INTO #history(ItemId, MODIFY_TIMESTAMP)
VALUES (1,'2012-03-07 16:00'), (1,'2012-03-07 17:00'), (1,'2012-03-07 18:00'),
(2,'2012-03-07 15:30'), (2,'2012-03-07 16:30')
--=== Get latest record in range
DECLARE @MinDate datetime='2012-03-07 16:00', @MaxDate datetime='2012-03-07 17:00'
SELECT t.*
FROM
(SELECT h.*,
rnr = ROW_NUMBER() OVER(PARTITION BY h.ItemId ORDER BY h.MODIFY_TIMESTAMP DESC, h.ItemId DESC)
FROM #history h
WHERE h.MODIFY_TIMESTAMP BETWEEN @MinDate AND @MaxDate
) t
WHERE t.rnr=1
March 7, 2012 at 7:25 am
A CASE statement should be faster, because it doesn't have to do the aggregrate calculation (even though there are only two values in the aggregate for each row). My initial test indicates that the savings is about 5%, but I didn't have time to do a thorough analysis.
SELECT t.*, CASE WHEN t.Updated > t.Inserted THEN t.Updated ELSE t.Inserted END
FROM #Tbl AS t
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply