If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Hello - Selecting the Lowest DateTime if it meets some conditions

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jul 2010
Location: Norway
Posts: 41
Hello - Selecting the Lowest DateTime if it meets some conditions

Hello

My table:

http://s9.postimage.org/65cvk8n0f/Capture.png

ID (PK), userID (FK), from [Datetime], to[datetime], type [int] , value [nvarchar]
...


Conditions
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:

Code:
//Handwritten
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...
Code:
//Handwritten
CREATE FUNCTION GetNewestFrom @id int
RETURN DATETIME
BEGIN
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)
BEGIN
     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 
     BEGIN
          @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...
     END
   
     IF @from IS NOT NULL //Else :P
     BEGIN
         @from = @from2
     END
///Continue on reading....

END


SELECT @from //Or is it return @datetime, anyways... 
END
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:
Code:
 From - To 
 5      8
10    15
16    20
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???
Code:
SELECT MIN (t1.from) FROM table as t1
WHERE
t1.from IN
(
	SELECT t2.to+ 1 FROM table as t2
	WHERE t2.userID = @id
)


Last edited by ManyTimes; 12-08-12 at 18:32.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Sep 2010
Posts: 12
Please provide table definition with sample data and logic to archive expect result. Your explanation is very unclear.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jul 2010
Location: Norway
Posts: 41
Yes, my explanation was indeed unclear, hopefully I've cleared it up a bit... I've edited the first post.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Oct 2009
Location: 221B Baker St.
Posts: 487
It would still be more helpful if you post the table definition, some sample data, and the rules (in sentence form).
Reply With Quote
  #5 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,781
This probably isn't what you want, but it might send you in the right direction:
Code:
DECLARE @t TABLE (
   [id]     INT
,  [userid] INT
,  [from]   DATE
,  [to]     DATE
,  [type]   INT
,  [value]  NVARCHAR(9)
   );
   
INSERT INTO @t
   VALUES
      (1, 201, '11/11/2012', '11/28/2012', 1, 'a string')
,     (2, 199, '11/23/2012',         null, 2, 'a string')
,     (3, 201, '11/29/2012', '12/01/2012', 1, 'a string')
,     (4, 201, '10/03/2012', '10/15/2012', 3, 'a string')
,     (5, 201, '12/02/2012',         null, 1, 'a string');

WITH a AS (
   SELECT b.[from], b.[to], b.[userid]
      FROM @t AS b
      WHERE b.[to] IS NOT NULL
   UNION ALL SELECT c.[from], c.[to], c.[userid]
      FROM @t AS c
      JOIN a AS d
         ON DATEADD(day, 1, c.[to]) = d.[from]
      WHERE  c.[userid] = d.[userid]
         AND c.[type] = 1)
SELECT MIN(a.[from]) AS 'from'
,  a.[to]
   FROM a
   GROUP BY a.[to];
-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On