Date calculation

27 Jul 2006 7:43 PM

Hitesh

Hi,

I have a table named tblLog with following columns and example data:
ID   TimeStamp   DateStamp UserID Version
1    10:00:01       2006-01-01   abc1     1.0.3.1
2    10:00:01       2006-01-10   xyz1     1.0.3.1
3    11:00:01       2006-01-31   abc1     1.0.3.1
4    10:00:01       2006-02-01   abc2     1.0.3.1
5    12:22:23       2006-03-11   abc1     1.0.3.1
6    01:22:27       2006-04-01   abc3     1.0.3.1
7    16:38:01       2006-05-01   xyz1     1.4.3.1
8    10:00:01       2006-06-01   abc4     1.0.3.1
9    15:00:01       2006-07-01   abc1     1.4.3.1
10 10:00:01       2006-01-01   abc5     1.0.3.1
11 10:00:01       2006-01-01   abc2     1.0.3.1
12 10:00:01       2006-01-01   abc3     1.4.3.1

This is the data being created whenever user logs into particular
server.

and I want to create a view that will give me the last login datestamp,
timestamp and what version....

I created view something like this that will give me DISTINCT users but
don't know how to get last login...

CREATE VIEW dbo.LastLogin_vw
AS
SELECT     DISTINCT UserID, Version, TImeStamp, DateStamp
FROM         dbo.TblLog

any hints on Last login dates ??

Thank you,
hj

27 Jul 2006 7:53 PM

Roy Harvey

One of these should do the job.

SELECT *
FROM TblLog as L
WHERE TimeStamp =
       (select max(TimeStamp) from TblLog as S
         where L.UserID = S.UserID)

SELECT *
FROM TblLog as L
WHERE ID =
       (select max(ID) from TblLog as S
         where L.UserID = S.UserID)

Usually this sort of thing is controlled by a column like the
timestamp, but as shown it only goes to the second, and that could
possibly give a duplicate some how some day. Assuming that ID is an
Identity column it should serve the same purpose without that
possibility.

Note that an index on (UserID, Timestamp) or (UserID, ID) will go a
long way to making this perform reasonably.

Roy Harvey
Beacon Falls, CT

Show quote

On 27 Jul 2006 12:43:52 -0700, "Hitesh" <hitesh***@gmail.com> wrote:

>Hi,
>
>I have a table named tblLog with following columns and example data:
>ID   TimeStamp   DateStamp UserID Version
>1    10:00:01       2006-01-01   abc1     1.0.3.1
>2    10:00:01       2006-01-10   xyz1     1.0.3.1
>3    11:00:01       2006-01-31   abc1     1.0.3.1
>4    10:00:01       2006-02-01   abc2     1.0.3.1
>5    12:22:23       2006-03-11   abc1     1.0.3.1
>6    01:22:27       2006-04-01   abc3     1.0.3.1
>7    16:38:01       2006-05-01   xyz1     1.4.3.1
>8    10:00:01       2006-06-01   abc4     1.0.3.1
>9    15:00:01       2006-07-01   abc1     1.4.3.1
>10 10:00:01       2006-01-01   abc5     1.0.3.1
>11 10:00:01       2006-01-01   abc2     1.0.3.1
>12 10:00:01       2006-01-01   abc3     1.4.3.1
>
>
>This is the data being created whenever user logs into particular
>server.
>
>and I want to create a view that will give me the last login datestamp,
>timestamp and what version....
>
>I created view something like this that will give me DISTINCT users but
>don't know how to get last login...
>
>CREATE VIEW dbo.LastLogin_vw
>AS
>SELECT     DISTINCT UserID, Version, TImeStamp, DateStamp
>FROM         dbo.TblLog
>
>any hints on Last login dates ??
>
>Thank you,
>hj

27 Jul 2006 7:59 PM

Aaron Bertrand [SQL Server MVP]

The fact that you have separated date and time (why would you do that?)
makes this query a little more complex than it has to be.

Let's try this.

SELECT
    log.UserID,
    sub.LastLogin,
    log.Version
FROM
    tblLog
INNER JOIN
(
    SELECT
        UserID,
        LastLogin = MAX(CONVERT(DATETIME, DateStamp+' '+[TimeStamp]))
    FROM
        dbo.tblLog
    GROUP BY
        UserID
) sub
ON sub.UserID = log.UserID
AND CONVERT(DATETIME, log.DateStamp + ' ' + log.[TimeStamp]) =
sub.LastLogin;

(Keep in mind you will get funny results if ties are found.)

If you really really really really really want datestamp and timestamp to
come back in a separate column, then you can change the SELECT list to:

SELECT
    log.UserID,
    log.DateStamp,
    log.[TimeStamp],
    log.Version

However my recommendation is to fix the table and keep the date and time
information together. I don't know of any good reason to split them (I just
know that it is a very common mistake). And if you're going to fix the
table, don't use TimeStamp... that's a data type which, incidentally, does
not store anything time- or date-related.

Show quote

"Hitesh" <hitesh***@gmail.com> wrote in message
news:1154029432.057600.72060@m73g2000cwd.googlegroups.com...
> Hi,
>
> I have a table named tblLog with following columns and example data:
> ID   TimeStamp   DateStamp UserID Version
> 1    10:00:01       2006-01-01   abc1     1.0.3.1
> 2    10:00:01       2006-01-10   xyz1     1.0.3.1
> 3    11:00:01       2006-01-31   abc1     1.0.3.1
> 4    10:00:01       2006-02-01   abc2     1.0.3.1
> 5    12:22:23       2006-03-11   abc1     1.0.3.1
> 6    01:22:27       2006-04-01   abc3     1.0.3.1
> 7    16:38:01       2006-05-01   xyz1     1.4.3.1
> 8    10:00:01       2006-06-01   abc4     1.0.3.1
> 9    15:00:01       2006-07-01   abc1     1.4.3.1
> 10 10:00:01       2006-01-01   abc5     1.0.3.1
> 11 10:00:01       2006-01-01   abc2     1.0.3.1
> 12 10:00:01       2006-01-01   abc3     1.4.3.1
>
>
> This is the data being created whenever user logs into particular
> server.
>
> and I want to create a view that will give me the last login datestamp,
> timestamp and what version....
>
> I created view something like this that will give me DISTINCT users but
> don't know how to get last login...
>
> CREATE VIEW dbo.LastLogin_vw
> AS
> SELECT     DISTINCT UserID, Version, TImeStamp, DateStamp
> FROM         dbo.TblLog
>
> any hints on Last login dates ??
>
> Thank you,
> hj
>

27 Jul 2006 8:58 PM

Roy Harvey

On Thu, 27 Jul 2006 15:59:25 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@dnartreb.noraa> wrote:

>The fact that you have separated date and time

Oops! I mised that completely!

Roy

27 Jul 2006 8:10 PM

Hitesh

Thank you guys for your suggestions... I wrote a small python script to
parse the log and didnot know how to compbine the date and time data so
I created two separate columns.. but you're right It is better to have
both of them in same column..

I will try again to combine those fields in one column...

Hitesh wrote:
Show quote

> Hi,
>
> I have a table named tblLog with following columns and example data:
> ID   TimeStamp   DateStamp UserID Version
> 1    10:00:01       2006-01-01   abc1     1.0.3.1
> 2    10:00:01       2006-01-10   xyz1     1.0.3.1
> 3    11:00:01       2006-01-31   abc1     1.0.3.1
> 4    10:00:01       2006-02-01   abc2     1.0.3.1
> 5    12:22:23       2006-03-11   abc1     1.0.3.1
> 6    01:22:27       2006-04-01   abc3     1.0.3.1
> 7    16:38:01       2006-05-01   xyz1     1.4.3.1
> 8    10:00:01       2006-06-01   abc4     1.0.3.1
> 9    15:00:01       2006-07-01   abc1     1.4.3.1
> 10 10:00:01       2006-01-01   abc5     1.0.3.1
> 11 10:00:01       2006-01-01   abc2     1.0.3.1
> 12 10:00:01       2006-01-01   abc3     1.4.3.1
>
>
> This is the data being created whenever user logs into particular
> server.
>
> and I want to create a view that will give me the last login datestamp,
> timestamp and what version....
>
> I created view something like this that will give me DISTINCT users but
> don't know how to get last login...
>
> CREATE VIEW dbo.LastLogin_vw
> AS
> SELECT     DISTINCT UserID, Version, TImeStamp, DateStamp
> FROM         dbo.TblLog
>
> any hints on Last login dates ??
>
> Thank you,
> hj