Professional Web Applications Themes

Interesting Query- Need Help - Microsoft SQL / MS SQL Server

Hi, Separate by Id(=GROUP BY Id) SELECT Id, MIN(time) AS MinTime, MAX(time) AS MaxTime FROM mydatas GROUP BY Id Separate the records by 30 mintute duration (=GROUP BY Id,30 mintute duration) SELECT Id, MIN(time) AS MinTime, MAX(time) AS MaxTime FROM mydatas GROUP BY ID, (DATEDIFF(minute, '00:00', time)/30) ------- SHINICHI YONEDA ne.jp Microsoft Most Valuable Professional MVP for SQL Server 2002-2003 "LIN" <com> wrote in message news:%phx.gbl... ...

  1. #1

    Default Re: Interesting Query- Need Help

    Hi,

    Separate by Id(=GROUP BY Id)

    SELECT Id, MIN(time) AS MinTime, MAX(time) AS MaxTime FROM mydatas
    GROUP BY Id

    Separate the records by 30 mintute duration
    (=GROUP BY Id,30 mintute duration)

    SELECT Id, MIN(time) AS MinTime, MAX(time) AS MaxTime FROM mydatas
    GROUP BY ID, (DATEDIFF(minute, '00:00', time)/30)
    -------
    SHINICHI YONEDA ne.jp
    Microsoft Most Valuable Professional
    MVP for SQL Server 2002-2003

    "LIN" <com> wrote in message
    news:%phx.gbl... 

    Shinichi Guest

  2. #2

    Default Re: Interesting Query- Need Help

    Thanks a lot. However I still have some trouble.

    It cannot be assumed that every session will start from 00:00. currenly
    using this query it showing me that i have 4 sessions ..

    -- ------- ---------
    1 10:10 10:13
    2 10:14 10:15
    1 10:58 10:59
    1 11:00 11:01

    now the last 2 sessions should have been one.

    Let me know if this can be worked out as well.

    thanks for the help.

    regards,

    LIN




    "Shinichi Yoneda" <ne.jp> wrote in message
    news:phx.gbl... 
    >[/ref]


    LIN Guest

  3. #3

    Default Re: Interesting Query- Need Help

    Can I assume that Time is the Primary Key?

    CREATE TABLE Sometable (id INTEGER NOT NULL, time DATETIME PRIMARY KEY)

    INSERT INTO Sometable VALUES ( 1, '1900-01-01 10:10:00')
    INSERT INTO Sometable VALUES ( 1, '1900-01-01 10:11:00')
    INSERT INTO Sometable VALUES ( 1, '1900-01-01 10:12:00')
    INSERT INTO Sometable VALUES ( 2, '1900-01-01 10:13:00')
    INSERT INTO Sometable VALUES ( 2, '1900-01-01 10:14:00')
    INSERT INTO Sometable VALUES ( 3, '1900-01-01 10:15:00')
    INSERT INTO Sometable VALUES ( 1, '1900-01-01 10:58:00')
    INSERT INTO Sometable VALUES ( 1, '1900-01-01 10:59:00')
    INSERT INTO Sometable VALUES ( 1, '1900-01-01 11:00:00')
    INSERT INTO Sometable VALUES ( 1, '1900-01-01 11:01:00')
    INSERT INTO Sometable VALUES ( 1, '1900-01-01 11:02:00')

    SELECT id, MIN(time) AS mintime, MAX(time) AS maxtime
    FROM
    (SELECT S1.id, S1.time , MIN(S2.time) AS nextime
    FROM Sometable AS S1
    LEFT JOIN Sometable AS S2
    ON S1.time<S2.time AND S1.id<>S2.id
    GROUP BY S1.id, S1.time) AS S
    GROUP BY id, nextime
    ORDER BY mintime

    If this doesn't help, please post the DDL for this table.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  4. #4

    Default Re: Interesting Query- Need Help

    Hello at all !

    CAUTION !!! This will work for the examples given, but try to delete the ID
    2 and 3 from the table, so only ID 1 is remaining.
    You will get only ONE Record even there must be two.

    I have written a Cursor (can be put in a stored procedure to retrieve Data,
    which will give you all results from the query.
    (Perhaps i have missed something too, if yo, please post here)

    --------

    CREATE TABLE #TimeTable_Temp([ID] int, MINTIME datetime,MAXTIME datetime)

    DECLARE ID INT
    DECLARE LASTID INT

    DECLARE TIME datetime
    DECLARE MINTIME datetime
    DECLARE MAXTIME datetime
    DECLARE LASTTIME datetime


    DECLARE Test CURSOR FOR
    SELECT [ID],Time from TimeTable ORDER BY [ID]
    OPEN TEST
    FETCH NEXT FROM TEST INTO ID,Time

    SET MinTime = Time
    SET MaxTime = TIME
    SET LASTID = ID

    WHILE Fetch_Status=0
    BEGIN

    IF (LASTID<>ID) OR (Datediff(mi,MinTime,Time)>30)
    BEGIN
    INSERT INTO #TimeTable_Temp VALUES (LastID,MinTime,Maxtime)
    SET MinTime = Time
    SET MaxTime = TIME
    SET LASTID = ID
    END
    ELSE
    SET MaxTime = TIME


    FETCH NEXT FROM TEST INTO ID,Time

    END

    INSERT INTO #TimeTable_Temp VALUES (LastID,MinTime,Maxtime)

    CLOSE Test
    DEALLOCATE TEST

    Select * from #TimeTable_Temp order by ID

    Drop Table #TimeTable_Temp
    -----


    HTH, Jens Süßmeyer.





    Jens Guest

  5. #5

    Default Re: Interesting Query- Need Help

    > CAUTION !!! This will work for the examples given, but try to delete the
    ID 

    Good point. I don't know whether Lin wants one row or two in that instance.
    Based on the example given I was assuming the requirement was for contiguous
    "blocks" of one id but it's unclear whether the intention was to treat a gap
    (absence of an id) as requiring a new row in the output. Only Lin can tell
    us...

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  6. #6

    Default Re: Interesting Query- Need Help

    Hi,

    Actually I want the output to know when an id is started accessing the
    particular page and what time he stopped accessing. Each and every time he
    access the page a entry will be made in the table. I just want to track
    what is his starting time and his last access time. If he remains quiet for
    more than 30 minutes i assume that he is closed the session.

    regards,
    LIN


    "David Portas" <org> wrote in message
    news:phx.gbl... 
    > ID 
    >
    > Good point. I don't know whether Lin wants one row or two in that[/ref]
    instance. 
    contiguous 
    gap 


    LIN Guest

  7. #7

    Default Re: Interesting Query- Need Help

    OK, got it. Just to prove there's an alternative to using a cursor:

    SELECT id, MIN(time) AS mintime, MAX(time) AS maxtime
    FROM
    (SELECT id, time,
    (SELECT MIN(S2.time)
    FROM Sometable AS S2
    WHERE S2.id = S1.id AND S2.time>S1.time
    AND (SELECT DATEDIFF(MINUTE,MAX(S3.time),S2.time)
    FROM Sometable AS S3
    WHERE S3.time < S2.time AND S3.id = S2.id)>30)
    AS nextime
    FROM Sometable AS S1) AS S
    GROUP BY id, nextime
    ORDER BY mintime

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  8. #8

    Default Re: Interesting Query- Need Help

    Here's another shot at it for you:

    select
    T1.id,
    T1.time,
    min(T3.time)
    from Sometable T1, Sometable T3
    where not exists (
    select * from Sometable T2
    where (T1.id = T2.id
    and T2.time < T1.time
    and T2.time > T1.time - '00:30')
    or (T3.id = T2.id
    and T2.time > T3.time
    and T2.time < T3.time + '00:30')
    )
    and T1.id = T3.id
    and T1.time <= T3.time
    group by T1.id, T1.time
    go

    -- Steve Kass
    -- Drew University
    -- Ref: 35495BC0-B0BF-4D12-A9F1-1C90637C9DB0

    LIN wrote:
     

    Steve Guest

Similar Threads

  1. Replies: 2
    Last Post: November 8th, 10:45 AM
  2. Very interesting.....
    By Scott Fletcher in forum PHP Development
    Replies: 0
    Last Post: September 5th, 06:21 PM
  3. Guru Advice Needed for Query on Interesting Data Scenario
    By Anith in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: August 22nd, 08:37 PM
  4. interesting
    By Ursula Baranowski in forum Photography
    Replies: 0
    Last Post: July 10th, 05:57 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139