CODEWITHSUNDEEP
sqlsql-server
Invisible
Invisible

Reputation: 69

Min Date from one column multiple rows

My apologies, I should have added every column and complete problem not just portion.

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

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

Edited***

Upvotes: 0

Views: 135

Answers (4)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

You can use row_number to assign sequence number within each cltid in the order of decreasing date and then filter to get the first row for each cltid which will be the row with latest date for that cltid:

select *
from (
    select A.*,
        row_number() over (
            partition by a.cltid order by a.date desc
            ) rn
    from table.A as A
    ) t
where rn = 1;

It will return one row (with latest date) for each client. If you want to return all the rows which have latest date, use rank() instead.

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

There are many ways to do this. Here are some of them:

test setup: http://rextester.com/VGUY60367

with common_table_expression as () using row_number() 

with cte as (
  select  *
      , rn = row_number() over (
              partition by cltid, Invnum
              order by [date] desc
            )
    from a
)
select cltid, Invnum, Cash, [date]
  from cte
  where rn = 1

cross apply version:

 select distinct
     a.cltid
   , a.Invnum
   , x.Cash
   , x.[date]
  from a
    cross apply (
      select top 1
            cltid, Invnum
          , [date]
          , Cash
        from a as i
        where i.cltid =a.cltid
          and i.Invnum=a.Invnum
        order by i.[date] desc
       ) as x;

top with ties version:

select top 1 with ties
    *
  from a
  order by 
    row_number() over (
      partition by cltid, Invnum
          order by [date] desc
      )

all return:

+-------+--------+---------------------+------+
| cltid | Invnum |        date         | Cash |
+-------+--------+---------------------+------+
|    70 |    112 | 12.03.2012 00:00:00 | -500 |
|    90 |    124 | 20.01.2012 00:00:00 | -550 |
+-------+--------+---------------------+------+

Upvotes: 1

Kinchit Dalwani
Kinchit Dalwani

Reputation: 398

You can achieve the desired o/p by this:

Select 
   a.cltid, a.invnumber,a.cash, max(a.date) [date]
from  
   YourTable a
group by 
   a.cltid, a.invnumber, a.cash, a.date

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Use a ranking function to get all the columns:

select a.*
from (select a.*,
             row_number() over (partition by cltid order by date desc) as seqnum
      from a
     ) a
where seqnum = 1;

Use aggregation if you only want the date. The issue with your query is that the group by clause has too many columns:

select a.cltid, max(a.date) as date 
from table.A as A
group by a.cltid;

And the fact that min() returns the first date not the last date.

Upvotes: 1

Related Questions