locked
Comparing dates in two views: Help Needed RRS feed

  • Question

  • Hi there and thanks for looking at my post:

    The background is essentially that I have a database where I track employees and items (mainly computer serial numbers) that are checked out for their use.  We have a service bench that is also considered an "employee" in the database. When a computer needs work we will "check in" the computers serial number from the employee and "check out" the same serial number to the "bench employee".

    The database consists primarily of these tables:  customers (customer id, name, customer number), item (item id, description, item number, serial number), and transactions (transaction id"pk", item id"fk", customer id"fk", transaction type, transaction date)  This is based off of memory and may not be 100% accurate.

    My requirement is a report that will show (customer name, serial number) for items that are "currently" checked out to bench.

    My select statement currently works but will ignore serial numbers that have been checked out to bench more than once.  This obviously doesn't work, so my solution was to make two views:

    View 1 show all serial numbers and dates for items checked out to bench

    View 2 show all serial numbers and dates for items checked in from bench

    Then I want to compare the two views to show each record of "checked in" which occured AFTER the record for that serial number in the "checked out" view.

    Basically:

    View 1: (serial numbers checked out to bench)

    SN   |  DATE

    sn1 | 3/1

    sn2 | 3/1

    sn3 | 3/1

    sn1 | 3/7

    sn5 | 3/3

    sn6 | 3/12

    sn1 | 3/15

    View 2: (serial numbers checked in from bench)

    sn1 | 3/2

    sn2 | 3/3

    sn3 | 3/2

    sn1 | 3/5

    sn5 | 3/5

    My issue is now:  the way my query is set up, SN1 would not show up even though it is checked out to bench because my query filters out serial numbers that have a checked in from bench record at all.  

    I need a way to compare the dates to report sn1 as checked out to bench because its (MAX)checkoutdate > is (MAX)checkindate.

     

    I have been fighting with this requirement for a long time and I know that my explanation is probably really vague, but I hope that someone out their can understand my requirement and help me!  

    Bottom line, I need to reference two tables that have similar serial numbers and compare their date feilds PER serial number to show only serial numbers who have dates greater than the max date of that serial number as referenced in another table....  WOW this is confusing!

    Thanks all for your help!

    Jalgl

     

    Monday, March 21, 2011 6:45 PM

Answers

  • For this kind of question, it is better to post table schema, sample data in the form of "insert" statements, and expected result, toguether with a brief description of the problem. This way we do not have to guess the schema (dat types, constraints, indexes, etc) or waste time populating the schema to be able to work in our side.

    > My requirement is a report that will show (customer name, serial number) for items that are "currently" checked out to bench.

    You can pull this information from the transaction table, but I donot know if this is what you are looking for (no sample data and no expected result)

    with rs as (
    select
        T.transaction_id,
        T.item_id,
        T.customer_id,
        T.transaction_type,
        T.transaction_date,
        C.customer_name,
        I.serial_number,
        row_number() over(partition by T.item_id order by T.transaction_date DESC) as rn
    from
        transactions as T
        inner join
        customers as C
        on T.customer_id = C.customer_id
        inner join
        items as I
        on T.item_id = I.item_id
    )
    select
        B.customer_name,
        B.serial_number
    from
        rs as A
        inner join
        rs As B
        on A.item_id = B.item_id
        A.rn = 1 and A.transaction_type = 'check out' and A.customer_name = 'bench'
        and B.rn = 2 and B.transaction_type = 'check in' and A.customer_name <> 'bench';

    The idea is to enumerate the transacctions per each item and considering the most recent as number 1 (descending by transaction_date). Then joining this enumerated set to it self considering that the last transaction was a "check out" by "bench" and the previous a "check in" by other than bench.

    This is an idea and not the final solution, since I have tested nothing.

     


    AMB

    Some guidelines for posting questions...


    • Proposed as answer by Ai-hua Qiu Sunday, March 27, 2011 5:16 AM
    • Marked as answer by Ai-hua Qiu Tuesday, March 29, 2011 9:05 AM
    Monday, March 21, 2011 7:22 PM

All replies

  •  

    Select v1.SN
    From View1 v1
    Left Outer Join View2 v2 On v1.SN = v2.SN
    Group By v1.SN
    Having Max(v1.DATE) > Coalesce(Max(v2.Date), '19000101');
    
    Tom

    Monday, March 21, 2011 7:08 PM
  • You're making this too tough... Something like this should do the trick for you.

     

    ;WITH CTE AS (
    SELECT 
    ROW_NUMBER() OVER(PARTITION BY ItemID ORDER BY TransactionDate DESC) AS RN,
    ItemID,
    CustomerID,
    TransactionType,
    TransactionDate
    FROM Transactions
    WHERE CustomerID = 312 -- hypothetical assignment of 312 to the Bench user --
    
    SELECT
    c.CustomerName,
    i.SerialNumber,
    CTE.TransactionDate AS CheckedOutDate
    FROM CTE
    INNER JOIN Customers AS c ON CTE.CustomerID = c.CustomerID
    INNER JOIN Items AS i ON CTE.ItemID = i.ItemID
    WHERE CTE.RN = 1
    AND CTE.TransactionType = 'CheckedOut'
    

     


    Jason Long
    Monday, March 21, 2011 7:08 PM
  • For this kind of question, it is better to post table schema, sample data in the form of "insert" statements, and expected result, toguether with a brief description of the problem. This way we do not have to guess the schema (dat types, constraints, indexes, etc) or waste time populating the schema to be able to work in our side.

    > My requirement is a report that will show (customer name, serial number) for items that are "currently" checked out to bench.

    You can pull this information from the transaction table, but I donot know if this is what you are looking for (no sample data and no expected result)

    with rs as (
    select
        T.transaction_id,
        T.item_id,
        T.customer_id,
        T.transaction_type,
        T.transaction_date,
        C.customer_name,
        I.serial_number,
        row_number() over(partition by T.item_id order by T.transaction_date DESC) as rn
    from
        transactions as T
        inner join
        customers as C
        on T.customer_id = C.customer_id
        inner join
        items as I
        on T.item_id = I.item_id
    )
    select
        B.customer_name,
        B.serial_number
    from
        rs as A
        inner join
        rs As B
        on A.item_id = B.item_id
        A.rn = 1 and A.transaction_type = 'check out' and A.customer_name = 'bench'
        and B.rn = 2 and B.transaction_type = 'check in' and A.customer_name <> 'bench';

    The idea is to enumerate the transacctions per each item and considering the most recent as number 1 (descending by transaction_date). Then joining this enumerated set to it self considering that the last transaction was a "check out" by "bench" and the previous a "check in" by other than bench.

    This is an idea and not the final solution, since I have tested nothing.

     


    AMB

    Some guidelines for posting questions...


    • Proposed as answer by Ai-hua Qiu Sunday, March 27, 2011 5:16 AM
    • Marked as answer by Ai-hua Qiu Tuesday, March 29, 2011 9:05 AM
    Monday, March 21, 2011 7:22 PM