如果另一列的条件是未满足的，则排除组

Question

这是我正在处理的简单示例。我是通过将另一个临时表与数据库中存储的表连接在一起来创建一个临时表（a）。我有以下临时表（a）的输出：

名称	日期	美元
a	2022-06-01	$ 500
a	2022-05-01	$ 250
$ 250 A	2022-04-01	$ 100 $ 100
A	2022-03-03-01	$ 475
B	20222-06-06-01	$ 180 $ 180
b	2022-05-01	$ 30
B	2022-04-01	$ 360
...	...	...

我的最终目标是按“美元”和“名称”组成。但是，如果该组的最早记录日期从今天的日期开始超过3个月，我只希望最终输出包括“名称”。因此，在上面的情况下，我想将“ a”包含在内，并将“美元”加在一起，但我想排除B，因为它不符合我的状况。我该怎么做？

这就是我希望我的最终输出是什么；

名称	日期	$
a	2022-06-01	1375
...	2022-06-01	...

Answer 1

您可以使用这样的案例指令

SELECT  
    case 
        when min(dt) <= dateadd(month,-3,getdate()) then (select name FROM example where name = e.name group by name)
    else null
    end as name,
    max(dt),
    case 
        when min(dt) <= dateadd(month,-3,getdate()) then (select sum(dollars) FROM example where name = e.name group by name)
        else null
    end as dollars          
from 
    example e
group by 
    name
 

Answer 2

create table #temp1 
(
 name char(1),
 date datetime,
 dollars int
);

insert into #temp1 (name,date,dollars) select 'A','06/01/2022',500
insert into #temp1 (name,date,dollars) select 'A','05/01/2022',250
insert into #temp1 (name,date,dollars) select 'A','04/01/2022',100
insert into #temp1 (name,date,dollars) select 'A','03/01/2022',475
insert into #temp1 (name,date,dollars) select 'B','06/01/2022',180
insert into #temp1 (name,date,dollars) select 'B','05/01/2022',30
insert into #temp1 (name,date,dollars) select 'B','04/01/2022',360

with cte
as
(
select name,min(date) as date1,GETDATE() as date2,sum(dollars) as dollars
from #temp1
group by name
having min(date)<DATEADD(month,-3,getdate()))

select name,date2 as date,dollars 
from cte 

这个可以工作吗？

Answer 3

您想报告所有几个月的每月总计，但仅针对那些带有数据恢复超过三个月的名称。

with data as (
    <your main data here>
), aggregated as (
    select name, max(date) as date, sum(dollars) as dollars,
        min(max(date)) over (partition by name) as first_date,
        count(*) over (partition by name) as month_count
    from data
    group by name
)
select name, date, dollars, first_date, month_count
from aggregated
where first_date < dateadd(month, -3, getdate());

或者

with data as (
    <your main data here>
)
select name, max(date) as date, sum(dollars) as dollars
from data d1
where exists (
    select 1 from data d2
    where d2.name = d1.name and d2.date < dateadd(month, -3, getdate())
)
group by name;