Choosing rows after a date

  • 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

  • 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 4 years, 9 months ago by  Phil Parkin.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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).

  • 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.

  • 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

     

  • 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 6 (of 6 total)

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