Not sure how to do this. I want all GUIDs that contain both eventA and eventB in in the same month, and it can be any month.

Given the query on this table:

I expect this results, since those GUIDs contain both eventA and eventB in March and April respectively.

EXPECTED VALUE:

Thanks.

CodePudding user response：

This query will return what you want:

select GUID
from YourTable y1
where exists (
  select * from YourTable y2 where month(y1.date) = month(y2.date) and EventName = 'eventA'
) and exists (
  select * from YourTable y2 where month(y1.date) = month(y2.date) and EventName = 'eventB'
)
group by GUID

DB Fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c4cab3182e0bdca5b139668c78c396ab

It is not an optimized query for many reasons, but it is quite simple. If your table is big and has many records, we could (and should) improve the query.

CodePudding user response：

Try this,

create table #temp(GUID varchar(50),[Date] datetime,EventName varchar(30))
insert into #temp([GUID] ,[Date] ,EventName)
values
('abc-guid-1','2013-03-06 00:00:00.000','eventA')
,('abc-guid-1','2013-03-31 00:00:00.000','eventB')
,('def-guid-2','2013-04-06 00:00:00.000','eventA')
,('def-guid-2','2013-04-15 00:00:00.000','eventB')
,('ghi-guid-3','2013-05-27 00:00:00.000','eventA')
,('ghi-guid-3','2013-05-01 00:00:00.000','eventC')

select [GUID] ,format([Date],'yyyyMMM') from #temp
where EventName not in ('eventC')
group by [GUID] ,format([Date],'yyyyMMM')
having count(distinct EventName)=2

drop table #temp

CodePudding user response：

You may filter events you needed, parse year and month out of the date and the count distinct values:

select guid
  from test_data
 where event_name in ('eventA', 'eventB') -- filters only values you need
 group by guid, concat(year(date_col), month(date_col)) -- parses year and month from the date
having count( distinct event_name) = 2 -- counts distinct values from the column.

CodePudding user response：

The most performant way to group by month is to use EOMONTH.

You also don't need to COUNT (DISTINCT, which can also be slow. Instead use conditional aggregation

SELECT
  [GUID],
  Month = EOMONTH([Date])
FROM YourTable
WHERE EventName IN ('eventA','eventB')
GROUP BY
  [GUID],
  EOMONTH([Date])
HAVING COUNT(CASE WHEN EventName = 'eventA' THEN 1 END) > 0
   AND COUNT(CASE WHEN EventName = 'eventB' THEN 1 END) > 0;

db<>fiddle

CodePudding user response：

I think this SQL request can do what you are looking for:

select GUID from GUIDs G1 where G1.EventName = 'eventA' and exists
  (select 1 from GUID G2 where G2.EventName = 'eventB'
    and G2.GUID = G1.GUID and to_char(G1.Date,'MM')= to_char(G2.Date,'MM')
  );