How to select last one week data from last date

I want sum of price in my table depended by ‘CorpCode’ from other table where ‘InvoiceDate’ between last week from Max(InvoiceDate)LastInvoiceDate

My table design is:

T1:
    CorpCode  AuthorNo
      C1         A23
      C1         A24
      C1         A25
      C1         A27
      C2         A30
      C2         A43
      C2         A55
      C3         A65
      C3         A66
      C3         A78
T2:
     AuthorNo   Price  InvoiceDate
       A23       145    2019-08-01
       A24       132    2019-08-02
       A25       140    2019-08-10
       A27       125    2019-08-14
       A30       115    2019-08-19
       A43       110    2019-08-21
       A55       100    2019-08-25
       A65       111    2019-09-23
       A66       133    2019-09-12
       A78       54     2019-09-10
Result:
     CorpCode    SumPrice
        C1          265
        C2          325
        C3          111

Answer

You can do that in multiple ways. ie:

with t (CorpCode, Price, days) as
(
select t1.CorpCode, t2.Price,  
  datediff(d, invoiceDate, 
  max(InvoiceDate) over (Partition by t1.CorpCode))
from t1 
inner join t2 on t1.AuthorNo = t2.AuthorNo
)
select CorpCode, Sum(Price) as TotPrice
from t
where days <= 7
group by CorpCode;

Advertisement

Answer