Choosing rows after a date

  • astrid 69000

    SSCrazy

    Points: 2982

    Hi,

    I have a table and I would like to choose the rows after a a specific date or event.

    CREATE TABLE #MYTABLE
    (
    CustomerID NVARCHAR(15),
    DateCreated DATE,
    EntryType NVARCHAR(15),
    Amount DECIMAL (19,4)
    )

    insert into #MYTABLE (CustomerID, DateCreated, EntryType, Amount)
    values ('HOUSE1023','1/17/2018', 'AB-645', -446),
    ('HOUSE1023','2/17/2018', 'Expired', -58749.40),
    ('HOUSE1023','6/27/2018', 'AB-644', -1064.1),
    ('HOUSE1023','1/12/2018', 'GA-50204', -44.5),
    ('HOUSE1023','7/5/2018', '693141', -247.4),
    ('HOUSE1023','2/17/2018', 'FUNDS - 2018', 77423.8),
    ('HOUSE1055','1/17/2018', 'AC-645', -446),
    ('HOUSE1056','2/19/2018', 'Expired', -58749.4),
    ('HOUSE1057','6/27/2018', 'AB-655', -1064.16),
    ('HOUSE1058','1/12/2018', 'GA-502', -44.5),
    ('HOUSE1059','7/5/2018', '693141', -247.4),
    ('HOUSE1060','2/17/2018', 'FUNDS - 2018', 77423.8);

    I would like to select all the rows FROM the date that the EntryType = 'Expired' but I don't want that expired row. The other rows on previous date from the customer do not needs to be included.

    In customer HOUSE1023 I would NOT like to include the Amount -446 and -58749.40.

    Now all the customers have the EntryType = 'Expired' the same date.

    There are other EntryType that can occur in the same day as EntryType = 'Expired' that needs to be included .

    Thanks

  • Phil Parkin

    SSC Guru

    Points: 243549

    Like this?

    WITH ExpiredDates
    AS (SELECT m.CustomerID
    ,m.DateCreated
    FROM #MYTABLE m
    WHERE m.EntryType = 'Expired')
    SELECT *
    FROM #MYTABLE m
    JOIN ExpiredDates ed
    ON ed.CustomerID = m.CustomerID
    WHERE m.DateCreated >= ed.DateCreated
    AND m.EntryType <> 'Expired'
    ORDER BY m.CustomerID
    ,m.DateCreated;

    • This reply was modified 2 months, 1 week ago by  Phil Parkin.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • pietlinden

    SSC Guru

    Points: 62395

    Something like this?

    SELECT *
    FROM CustInfo ci
    WHERE DateCreated IN ( SELECT DateCreated
          FROM CustInfo
          WHERE EntryType = 'Expired')
    AND ci.EntryType != 'Expired';

    (Sorry, I renamed your table to CustInfo).

  • rVadim

    Hall of Fame

    Points: 3897

    It would be nice to have desired output based on sample data. It's not clear what should happen to customers that don't have 'Expired'. Are there multiple 'Expired' rows for the same customer?

    Anyway, something like this should do:

    WITH ExpiredCTE AS (
    SELECT
    CustomerID,
    ExpiredDate = DateCreated
    FROM #MYTABLE
    WHERE EntryType = 'Expired'
    )
    SELECT * FROM #MYTABLE AS MT
    INNER JOIN ExpiredCTE AS CTE
    ON MT.CustomerID = CTE.CustomerID
    WHERE MT.DateCreated >= CTE.ExpiredDate
    AND EntryType <> 'Expired'

    --Vadim R.

  • astrid 69000

    SSCrazy

    Points: 2982

    Thanks for your reply, each customer has one Expired on 2017 and that is the year I will be checking.

    I am looking to do a sum that will not include the Expired transaction nor the transactions before that date, so if a customer doesn't have an Expired transaction, then all his transactions will be counted.

    I am checking virtual credit. it expired every year (but we are checking 2017), i want to sum all their credit except the expired credit or every transaction done the day before the virtual credit was expired, sum per customer.

     

    thanks

     

  • rVadim

    Hall of Fame

    Points: 3897

    This?

    WITH ExpiredCTE AS (
    SELECT
    CustomerID,
    ExpiredDate = DateCreated
    FROM #MYTABLE
    WHERE EntryType = 'Expired'
    )
    SELECT * FROM #MYTABLE AS MT
    LEFT JOIN ExpiredCTE AS CTE
    ON MT.CustomerID = CTE.CustomerID
    WHERE (MT.DateCreated >= CTE.ExpiredDate OR CTE.ExpiredDate IS NULL)
    AND EntryType <> 'Expired'

    or with grouping

    WITH ExpiredCTE AS (
    SELECT
    CustomerID,
    ExpiredDate = DateCreated
    FROM #MYTABLE
    WHERE EntryType = 'Expired'
    )
    SELECT MT.CustomerID, Total = SUM(MT.Amount)
    FROM #MYTABLE AS MT
    LEFT JOIN ExpiredCTE AS CTE
    ON MT.CustomerID = CTE.CustomerID
    WHERE (MT.DateCreated >= CTE.ExpiredDate OR CTE.ExpiredDate IS NULL)
    AND EntryType <> 'Expired'
    GROUP BY MT.CustomerID

    --Vadim R.

  • This was removed by the editor as SPAM

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply