This is a simplified example of what I'm dealing with. I'm creating a temp table (a) by joining a different temp table with a table stored in the database. I have the following output for temp table (a):

My ultimate goal is to sum 'dollars' and group by 'name'. However, I only want my final output to include 'name' if the earliest recorded date for the group is more than 3 months from today's date. So in the case above, I would want to include 'A' and sum the 'dollars' together, but I want to exclude B because it does not meet my condition. How can I do that?

This is what I want my final output to be;

CodePudding user response：

You can use the case instruction like this

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
 

CodePudding user response：

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