Get the oldest date SQL Complexity

advertisements

I have a problem which I cannot resolve no matter what without using code, instead of SQL SCRIPT.

I have 2 tables

Person
 ID      Name        Type
  1       A           A1
  2       B           A2
  3       C           A3
  4       D           A4
  5       E           A6

PersonHomes

HOMEID         Location     PurchaseDate   PersonID
   1            CA           20160101         1
   2            CT           20160202         1
   3            DT           20160101         2
   4            BT           20170102         3
   5            CT           20160303         1
   6            CA           20160101         2

PersonID is foreign key of Person Table

There are no other rowz in the tables

So, we have to show detail of EACH person WITH home

The rule to write output is

  • IF Person has SINGLE entry in PersonHomes then use it
  • IF Person has MORE than ONE entry in PersonHomes then we have to look at purchase date, IF they are different then USE the PersonHomes ROW with OLDEST date in it. AND DELETE OTHER ROWS OF HIM
  • IF Person has MORE than ONE entry in PersonHomes then we have to look at purchase date, and IF DATES are SAME then USE the ROW with LOWER ID AND DELETE THE OTHER ROWS of HIM

This is very easy to do in code but using SQL it is complex

What I tried was to

WITH PERSON (
SELECT * FROM Person)

SELECT * FROM PERSON
INNER JOIN PersonHomes ON Person.ID = PersonHomes.PersonID
WHERE PersonHomes.PersonID = CASE WHEN (COUNT (*) FROM PersonHomes...)

Then I think I can write SQL function ?

I am stuck, Please help!

SAMPLE OUTPUT for PERSON A

ID  NAME Type HOMEID  Location    PurchaseDate

1    A    A1     5      CT          20160303

For PERSON B

ID  NAME Type HOMEID  Location    PurchaseDate

1    A    A2     3      DT          20160101

Aiden


It is not so easy to get desired output with SQL. we should write more than one sql queries.

First I created a temp table which consists of home details:

select PersonID, count(*) as HomeCount, count(distinct PurchaseDate) as
PurchaseDateCount, min(PurchaseDate) oldestPurchaseDate, min(HOMEID) as
LowerHomeID into #PersonHomesAbstractTable from PersonHomes group by PersonID

Then for the output of your first rule:

select p.ID, p.NAME, p.Type, ph.HOMEID, ph.Location, ph.PurchaseDate from Person p
inner join #PersonHomesAbstractTable a on p.ID = a.PersonID
inner join PersonHomes ph on p.ID = ph.PersonID
where a.HomeCount = 1

For the output of your second rule:

select p.ID, p.NAME, p.Type, ph.HOMEID, ph.Location, ph.PurchaseDate
from Person p inner join #PersonHomesAbstractTable a on p.ID = a.PersonID
inner join PersonHomes ph on p.ID = ph.PersonID and
ph.PurchaseDate = a.oldestPurchaseDate
where a.HomeCount > 1 and a.PurchaseDateCount <> 1

And finally for the output of your third rule:

select p.ID, p.NAME, p.Type, ph.HOMEID, ph.Location, ph.PurchaseDate
from Person p inner join #PersonHomesAbstractTable a on p.ID = a.PersonID
inner join PersonHomes ph on p.ID = ph.PersonID and
ph.HOMEID = a.LowerHomeID
where a.HomeCount > 1 and a.PurchaseDateCount = 1

Of course there are some other ways, but now this way is come to my mind.

If you want to delete undesired rows, you can use scripts below:

delete from PersonHomes where HOMEID in
(
   select ph.HOMEID from #PersonHomesAbstractTable a
   inner join PersonHomes ph on a.PersonID = ph.PersonID and
   ph.PurchaseDate <> a.oldestPurchaseDate
   where a.HomeCount > 1 and a.PurchaseDateCount <> 1

   union

   select p.HOMEID from #PersonHomesAbstractTable a
   inner join PersonHomes ph on a.PersonID = ph.PersonID and
   ph.HOMEID <> a.LowerHomeID
   where a.HomeCount > 1 and a.PurchaseDateCount = 1
)