SQLTeam.com | Weblogs | Forums

Identified qty of last sales by each salesperson from sql table

sql2008

#1

select max(invoiceno), salesperson, qty
from salesmaster
where
invoicedate between '2016-01-01' and '2016-04-01' and delflag = 'N'
group by salesperson


#2

Try this:

with cte
  as (select invoiceno
            ,salesperson
            ,qty
            ,row_number() over(partition by salesperson
                               order by invoiceno desc
                              )
             as rn
        from salesmaster
       where invoicedate>=cast('2016-01-01' as date)
         and invoicedate<cast('2016-04-02' as date)
         and delflag='N'
     )
select invoiceno
      ,salesperson
      ,qty
  from cte
 where rn=1
;

Or this (I'm guessing invoiceno can hold only one salesperson):

select b.invoiceno
      ,b.salesperson
      ,b.qty
  from (select max(invoiceno) as invoiceno
          from salesmaster
         where invoicedate>=cast('2016-01-01' as date)
           and invoicedate<cast('2016-04-02' as date)
           and delflag='N'
         group by salesperson
       ) as a
       inner join salesmaster as b
               on b.invoiceno=a.invoiceno
;