PerfectPhase "This is not war, this is pest control!" - Dalek to Cyberman

Feb 23, 2008 at 10:26 AM

I have some data that looks like this, ordered by date desc.

Date                               Card #           Location
2007-06-28 16:32:12    966613554    Server Room
2007-06-05 12:25:06    966614310    Main Stores (Back Door)
2007-06-05 12:25:05    966614310    Main Stores (Back Door)
2007-06-05 12:25:04    966614310    Main Stores (Back Door)
2007-06-05 12:25:03    966614310    Main Stores (Back Door)
2007-06-05 12:25:03    966614310    Main Stores (Back Door)
2007-06-04 12:43:47    966613554    Server Room
2007-06-04 12:43:30    966613552    Server Room
2007-06-04 12:43:14    966613553    Server Room
2007-06-04 12:42:02    966613554    Server Room
2007-05-21 10:18:42    966613563    Alans Reader
2007-05-21 10:18:42    966613563    Alans Reader
2007-05-21 10:18:15    966613563    Alans Reader
2007-05-21 10:17:25    966613564    Alans Reader
2007-05-21 10:12:01    966613564    Alans Reader

And need to select the data in red.  The rules are I should only ever have one row for each card number and the row slected should be the most recent use of that card.

I've been toying with this for a while and still can't get it to work, anyone got any bright ideas?

Thanks

Stephen
W3bbo Work hard; increase production; prevent accidents, and be happy.

Feb 23, 2008 at 11:13 AM

SELECT DISTINCT combined with some variant of GROUP BY?
PerfectPhase "This is not war, this is pest control!" - Dalek to Cyberman

Feb 23, 2008 at 11:17 AM

Sorted It (this pulls just the last 5 uses)

CREATE FUNCTION [dbo].[GetLast5Cards] ()
RETURNS TABLE
AS
RETURN
(
    SELECT TOP(5) MAX(Date) AS LastUsedDate, LTRIM(RTRIM(Info1)) AS CardNumber
    FROM         History
    WHERE     (Info2 LIKE '%unknown%')
    GROUP BY Info1
    ORDER BY LastUsedDate DESC
)

CREATE FUNCTION [dbo].[GetLastUnknownUseOfCard]
(
@CardNumber nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
SELECT TOP 1 Info1 as Cardnumber, Date, RptSource as Location
  FROM History
  WHERE Info1 = @CardNumber
  ORDER BY Date DESC
)

SELECT
   p2.Date, p2.cardNumber, p2.Location
FROM
   GetLast5Cards () AS p1
   CROSS APPLY
   GetLastUnknownUseOfCard(p1.CardNumber) AS p2
ORDER BY p2.Date DESC
PerfectPhase "This is not war, this is pest control!" - Dalek to Cyberman

Feb 23, 2008 at 11:40 AM

Actually this is neater

SELECT TOP (5)
      Date,
      CardNumber,
      Location
FROM (
      SELECT
            h.Date,
            h.Info1 AS CardNumber,
            h.RptSource AS Location,
            ROW_NUMBER = ROW_NUMBER() OVER (
                  PARTITION BY h.Info1
                  ORDER BY h.Date DESC)
      FROM
            History AS h
      WHERE (Info2 LIKE '%unknown%')
      ) AS n
WHERE
      n.ROW_NUMBER <= 1
ORDER BY Date DESC
figuerres ???

Feb 24, 2008 at 8:36 AM

PerfectPhase wrote:

I have some data that looks like this, ordered by date desc.

Date                               Card #           Location
2007-06-28 16:32:12    966613554    Server Room
2007-06-05 12:25:06    966614310    Main Stores (Back Door)
2007-06-05 12:25:05    966614310    Main Stores (Back Door)
2007-06-05 12:25:04    966614310    Main Stores (Back Door)
2007-06-05 12:25:03    966614310    Main Stores (Back Door)
2007-06-05 12:25:03    966614310    Main Stores (Back Door)
2007-06-04 12:43:47    966613554    Server Room
2007-06-04 12:43:30    966613552    Server Room
2007-06-04 12:43:14    966613553    Server Room
2007-06-04 12:42:02    966613554    Server Room
2007-05-21 10:18:42    966613563    Alans Reader
2007-05-21 10:18:42    966613563    Alans Reader
2007-05-21 10:18:15    966613563    Alans Reader
2007-05-21 10:17:25    966613564    Alans Reader
2007-05-21 10:12:01    966613564    Alans Reader

And need to select the data in red.  The rules are I should only ever have one row for each card number and the row slected should be the most recent use of that card.

I've been toying with this for a while and still can't get it to work, anyone got any bright ideas?

Thanks

Stephen

as a different angle on this it looks like the data recordes several "duplicate events" that differ only by the last part of the "date and time".

you might write a TSQL scalar function that takes a datetime as the input parameter and returns a datetime as the result.

inside that function you can strip off the last bit of the time so that you get:

207-05-21 10:18:00.0000 for all times from 10:18:00 to 10:18:59.9999

that would allow a standard sql distinct to work very well for most cases.

it would at least filter out the "time jitter" I see in your sample data.

I have some functions I use for reporting that do a "Date" and "End Of Day" for my code.
so the user can select say 2/22/08 and I select for the 24 hour day of the 22nd.
Maurits AKA Matthew van Eerde

Feb 25, 2008 at 9:00 AM

SELECT
    Card,
    MAX(Date) AS LastUse
FROM
    CardUses
GROUP BY
    Card

Stupid question... do you want to include cards that have never been used?
PerfectPhase "This is not war, this is pest control!" - Dalek to Cyberman

Feb 25, 2008 at 10:53 AM

Maurits wrote:

SELECT
    Card,
    MAX(Date) AS LastUse
FROM
    CardUses
GROUP BY
    Card

Stupid question... do you want to include cards that have never been used?

No, just the last use as as they have never been used they are not in the source table.

The grouping falls down because I need the Location field of the most recent use as well.
Matthew van Eerde AKA Maurits

Feb 26, 2008 at 11:05 AM

CREATE VIEW EachCardLastUse AS
SELECT
    Card,
    MAX(Date) AS LastUse
FROM
    CardUses
GROUP BY
    Card

SELECT
    CardUses.*
FROM
    CardUses INNER JOIN EachCardLastUse ON
        CardUses.Card = EachCardLastUse.Card AND
        CardUses.Date = EachCardLastUse.LastUse

EDIT: In the biz this is known as a self-join.

Thread Closed

This thread is kinda stale and has been closed but if you'd like to continue the conversation, please create a new thread in our Forums,
or Contact Us and let us know.

SQL Advice

Thread Closed