Re: SQL for this query
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 16 Nov 2011 07:09:23 -0800 (PST)
Message-ID: <a92fad02-a58d-4990-bd62-d28ea3546c95_at_x30g2000prh.googlegroups.com>
On Nov 15, 11:45 am, joel garry <joel-ga..._at_home.com> wrote:
> On Nov 15, 8:28 am, "hilljm1..._at_gmail.com" <hilljm1..._at_gmail.com>
> wrote:
>
>
>
>
>
> > I have a sessions table like the following:
>
> > SID UserID TimeIn TimeOut
> > 1 10 Aug-10-2011 11:30:45 Aug-10-2011 11:32:13
> > 2 11 Aug-10-2011 11:30:55 Aug-10-2011 11:31:01
> > 3 54 Aug-10-2011 11:31:02 Aug-10-2011 11:33:48
> > 4 21 Aug-10-2011 11:34:11 Aug-10-2011 11:35:19
>
> > I need a query to return the count of active users for each records
> > time range, such that:
>
> > for SID 1, the count would be 3 because there are 3 records whose
> > TimeIn <= SID 1's TimeOut
> > for SID 2, the count would be 2 because there are 2 records whose
> > TimeIn <= SID 2's TimeOut
>
> > etc.
>
> Some people may answer this, but in general, you will get a better
> response for this kind of question if you provide table creation and
> data loading statements. That way, people can start from the same
> place you are, and play around until they get what you say you want.
> This also helps reduce ambiguity with a very small data set. If you
> show what you tried, that might help some people think you aren't just
> asking for people to do your work for you.
>
> jg
> --
> _at_home.com is bogus.
> Robertson doesn't get it, again:http://www.signonsandiego.com/news/2011/nov/13/privacy-history/- Hide quoted text -
>
> - Show quoted text -
Date: Wed, 16 Nov 2011 07:09:23 -0800 (PST)
Message-ID: <a92fad02-a58d-4990-bd62-d28ea3546c95_at_x30g2000prh.googlegroups.com>
On Nov 15, 11:45 am, joel garry <joel-ga..._at_home.com> wrote:
> On Nov 15, 8:28 am, "hilljm1..._at_gmail.com" <hilljm1..._at_gmail.com>
> wrote:
>
>
>
>
>
> > I have a sessions table like the following:
>
> > SID UserID TimeIn TimeOut
> > 1 10 Aug-10-2011 11:30:45 Aug-10-2011 11:32:13
> > 2 11 Aug-10-2011 11:30:55 Aug-10-2011 11:31:01
> > 3 54 Aug-10-2011 11:31:02 Aug-10-2011 11:33:48
> > 4 21 Aug-10-2011 11:34:11 Aug-10-2011 11:35:19
>
> > I need a query to return the count of active users for each records
> > time range, such that:
>
> > for SID 1, the count would be 3 because there are 3 records whose
> > TimeIn <= SID 1's TimeOut
> > for SID 2, the count would be 2 because there are 2 records whose
> > TimeIn <= SID 2's TimeOut
>
> > etc.
>
> Some people may answer this, but in general, you will get a better
> response for this kind of question if you provide table creation and
> data loading statements. That way, people can start from the same
> place you are, and play around until they get what you say you want.
> This also helps reduce ambiguity with a very small data set. If you
> show what you tried, that might help some people think you aren't just
> asking for people to do your work for you.
>
> jg
> --
> _at_home.com is bogus.
> Robertson doesn't get it, again:http://www.signonsandiego.com/news/2011/nov/13/privacy-history/- Hide quoted text -
>
> - Show quoted text -
1974, I second what Joel said. Also I cannot tell from the sample but are your sure that the Time-In does not need to be accounted for when performing the count. Just counting sessions that have a Time-In less that another session's Time-Out seems pretty useless unless the data is limited in the duration of time being covered to begin with.
HTH -- Mark D Powell -- Received on Wed Nov 16 2011 - 09:09:23 CST