ID (PK), userID (FK), from [Datetime], to[datetime], type [int] , value [nvarchar]
I want to select the column "to" which is a datetime-column for all users within the table.
One user can have multiple registered rows in "mytable", so I want to select the row that contains the newest "from" date.
The newest "from"-column for a specific user is where I am struggling... Because if the "to"-column of any of the registered rows by the current user is equal to the newest "from -1 day" column for the user, I want to select the second newest from-date.
Something along these lines:
DECLARE @from DATETIME //Current Selected datetime [the newest of the current user doing the query]
DECLARE @id INT //userID
DECLARE @tbl TABLE (from datetime, userid int)
SET @from = dbo.GetNewestFrom(@id)
INSERT INTO @tbl(from, userid) VALUES ( @from, @id) //Inserting it to then later select it... Could have just selected it, details for now, move on...:)
SELECT * FROM @tbl //Returns the result of the current user...
CREATE FUNCTION GetNewestFrom @id int
DECLARE @from DATETIME
SET @from = SELECT MAX(from) FROM table WHERE userID = @id AND type = 1 //The query for persons without type = 1, is just selecting the MAX(from) FROM table...
//It is if Type = 1, then I shall check for other registered dates for the user, so from here on out, pretending Type = 1, without further checks...
DECLARE @end INT
SET @end = 0
DECLARE @from2 DATETIME
WHILE (@end = 0)
SET @from2 =
(SELECT from FROM table WHERE userID = @id AND @from IN (SELECT table.to + 1 FROM table WHERE userID = @id)
IF @from IS NULL
@end = 1 //we have selected the newest from, that did not have a "to-date" less than a day... Which mean the rows had a gap of at least 2 days between the registering...
IF @from IS NOT NULL //Else :P
@from = @from2
///Continue on reading....
SELECT @from //Or is it return @datetime, anyways...
So, for user ID 199, it shall return 2012.11.23, because he is Type != 1...
But for user ID 201 [all users with Type = 1], I need to run through some more code, to figure out which date we want... Which date do I want? The "from"-date that is the newest, but if "to" is just 1 day before "from", I want to select the "from" at that row instead... Example with numbers [integers] instead of Date:
From - To
I want to select "10" in the from column as the maximum "from"-integer, and not 16. Because in the "To" column we have a match in the "from" column on the condition "To + 1" [15+ 1 == 16], so then I want the "from" value where "To" equals 15, which is 10. The top row I do not want, because the difference between From and To [10 - 8 is larger than 1]...
This means that the Image shown... I am first selecting the "1", then I do some checks, if the "to" for the user is 1 day earlier than the MAX(from) I grab a new "from" value...
What I need help with, I think, or I hope I'll eventually solve it anyways, but struggling with thinking straight, grabbing the "from"-cell recursivly, based on "to"-cell for a user...
Maybe looking at this the wrong way... Thinking of it as a programming language,I mean, C++/C#, which gives me a headache when trying to get it to work in SQL...Oh well, its weekend, saturday night...
Maybe I can do something similar to this???
SELECT MIN (t1.from) FROM table as t1
SELECT t2.to+ 1 FROM table as t2
WHERE t2.userID = @id