CODEWITHSUNDEEP
sqlsql-server
Invisible
Invisible

Reputation: 69

Min Date after applying filter

I have a table A which stores all invoices issued(id 1) payments received (id 4) from clients. Sometimes client pay in 2-3 installments. I want to find dateifference between invoice issued and last payment collected for the invoice. My data looks like this

**a.cltid**|**A.Invnum**|A.Cash|A.Date    | a.type| a.status
70         |112         |-200  |2012-03-01|4      |P
70         |112         |-500  |2012-03-12|4      |P
90         |124         |-550  |2012-01-20|4      |P
70         |112         |700   |2012-02-20|1      |p
55         |101         |50    |2012-01-15|1      |d
90         |124         |550   |2012-01-15|1      |P

I am running

Select *, Datediff(dd,T.date,P.date)
from (select a.cltid, a.invnumber,a.cash, min(a.date)date 
      from table.A as A
where a.status<>'d' and a.type=1
group by a.cltid, a.invnumber,a.cash)T
join 
Select *
from (select a.cltid, a.invnumber,a.cash, min(a.date)date 
      from table.A as A
where a.status<>'d' and a.type=4
group by a.cltid, a.invnumber,a.cash)P
on

T.invnumb=P.invnumber and T.cltid=P.cltid

d= deleted How can I make it work? So it shows me

70|112|-500|2012-03-12|4|P 70|112|700|2012-02-20|1|p|22
90|124|-550|2012-01-20|4|P 90|124|550|2012-01-15|1|P|5

Upvotes: 1

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I think you need conditional aggregation:

select a.cltid, a.invnum,
       max(case when a.type = 1 then a.date end) as issue_date,
       max(case when a.type = 4 then a.date end) as last_payment_date,
       datediff(day, 
                max(case when a.type = 1 then a.date end),
                max(case when a.type = 4 then a.date end)
               ) as diff
from a
where  a.status <> 'd'
group by a.cltid, a.invnum;

Upvotes: 1

Related Questions