Tech Off Thread

8 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

SQL Advice

Back to Forum: Tech Off
  • User profile image
    PerfectPhase

    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

  • User profile image
    W3bbo

    SELECT DISTINCT combined with some variant of GROUP BY?

  • User profile image
    PerfectPhase

    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

  • User profile image
    PerfectPhase

    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

  • User profile image
    figuerres

    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.

  • User profile image
    Maurits

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

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

  • User profile image
    PerfectPhase

    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.

  • User profile image
    Matthew van Eerde

    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.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.