I need a little help with this, I'm sure it's fairly basic but I'm a bit stumped.
I have a derived table that looks like the following:
Key Col1 Col2 Col3
2 user2 user2
4 user1 user1
5 user3 user3
What I need to do is count rows where a given username value appears in that row in one or more columns.
If there are only three columns you can use a brute force method:
select count(case when col1 = col2 or col1 = col3 or col2 = col3 then 1 end)
from (your derived table) as dt
This is very close, but the answer set I need should be a count by user as in
The users id can be in any of the columns, any combination.
Here you go...
SEL COALESCE(COL1, COL2, COL3) as _USER, count(*)
WHERE _USER IS NOT NULL
GROUP BY 1
Sorry, i didn't understand correctly what you wanted.
Doa cross join to a table with three rows in it to split the row in three:
WHEN t.x = 1 THEN col1
WHEN t.x = 2 THEN col2
WHEN t.x = 3 THEN col3
(your derived table) AS dt CROSS JOIN
(SELECT ROW_NUMBER() OVER (ORDER BY InfoKey) AS x FROM dbc.dbcinfo) AS t
GROUP BY 1
You need an outer join using BETWEEN:
select c.id, c.EventFrom, c.EventTo, count(e.EventDate)
from customers as c
left join Events as e
on e.ID = c.ID
and e.EventDate between c.EventFrom and c.EventTo
group by 1,2,3