Select data that is greater than a value in the lat 7 days from max date-CodePudding

I have a data set as such:

Date            Value     type
2020-01-01     0         A
2020-01-04    102        A
2020-02-01    111        B
2020-02-03    10         A
2020-01-11    133        B
2020-04-01    1433       A
2020-04-23    102        A
2020-05-01    1045       A
2020-06-01    103        B
2020-06-01    100        A
2020-06-01    133        A
2020-06-11    150        A
2020-07-01    1000       A
2020-07-21    104        A
2020-07-25    140        A
2020-07-28    1600       A
2020-08-01    100        A

I am trying to get rows only if the sum of "values" in the last 7 days from the max date available is over 1000. Like this:

Type    ISHIGH
A        1
B        0

Here's the query i tried,

select  type, case when sum(usage) > 1000 then 1 else 0 end as total_usage from tableA 
where (select sum(usage) as usage from tableA where date = max(date)-7)
group by type, date

This is clearly not right. What is a simple way to do this?

CodePudding user response：

It is a simply group by except that you need to be able to access max date before grouping:

select type
     , max(date) as last_usage_date
     , sum(value) as total_usage
     , case when sum(case when date >= cutoff_date then value end) >= 1000 then 'y' end as [is high!]
from t
cross apply (
    select dateadd(day, -6, max(date))
    from t as x
    where x.type = t.type
) as ca(cutoff_date)
group by type, cutoff_date

If you want just those two columns then a simpler approach is:

select t.type, case when sum(value) >= 1000 then 'y' end as [is high!]
from t
left join (
    select type, dateadd(day, -6, max(date)) as cutoff_date
    from t
    group by type
) as a on t.type = a.type and t.date >= a.cutoff_date
group by t.type

CodePudding user response：

Find the max date by type. Then used it to find last 7 days and sum() the value.

with
cte as
(
    select [type], max([Date]) as MaxDate
    from   tableA
    group by [type]
)
select c.[type], sum(a.Value),
       case when SUM(a.Value) > 1000 then 1 else 0 end as ISHIGH
from   cte c
       inner join tableA a on  a.[type]  = c.[type]
                           and a.[Date] >= DATEADD(DAY, -7, c.MaxDate)
group by c.[type]

CodePudding user response：

This can be done through a cumulative total as follows:

;With CTE As (
Select [type], [date], 
       SUM([value]) Over (Partition by [type] Order by [date] Desc) As Total,      
       Row_Number() Over (Partition by [type] Order by [date] Desc) As Row_Num
From Tbl)
Select Distinct CTE.[type], Case When C.[type] Is Not Null Then 1 Else 0 End As ISHIGH
From CTE Left Join CTE As C On (CTE.[type]=C.[type]
                                And DateDiff(dd,CTE.[date],C.[date])<=7
                                And C.Total>1000)
Where CTE.Row_Num=1

CodePudding user response：

I think you are quite close with you initial attempt to solve this. Just a tiny edit:

select type, case when sum(value) > 1000 then 1 else 0 end as total_usage 
from tableA 
where date > (select max(date)-7 from tableA)
group by type