Choosing rows after a date

Question

Post reply

Choosing rows after a date

astrid 69000

Hall of Fame

Points: 3208

June 12, 2019 at 6:19 pm

#3652870

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

June 24, 2019 at 7:12 am

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

pietlinden SSC Guru Points: 63503 More actions · Answer 2

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: 3969 More actions · Answer 3

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 Hall of Fame Points: 3208 More actions · Answer 4

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: 3969 More actions · Answer 5

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.