Select data where sum for last 7 from max-date is greater than x

I have a data set as such:

Date          Value    Type
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

​x
 
Date          Value    Type2020-06-01    103      B2020-06-01    100      A2020-06-01    133      A2020-06-11    150      A2020-07-01    1000     A2020-07-21    104      A2020-07-25    140      A2020-07-28    1600     A2020-08-01    100      A​

Like this:

Type    ISHIGH
A       1
B       0

 
Type    ISHIGHA       1B       0​

Here’s the query i tried,

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

 
select  type, case when sum(value) > 10 then 1 else 0 end as total_usagefrom table_awhere (select sum(value) 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?

Answer

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]

 
withcte 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 ISHIGHfrom   cte c       inner join tableA a on  a.[type]  = c.[type]                           and a.[Date] >= DATEADD(DAY, -7, c.MaxDate)group by c.[type]​

Advertisement

Answer