Add count from different table

Question

I have data in tableA which only holds tickets that were reopened.

ticket   date        owner  reopencount
123     2020-01-02   SP        1
124     2020-01-04   SP        1
234     2020-02-04   PS        1

And tableB has all ticket values:

ticket   date        owner
123     2020-01-01    SP
123     2020-01-02    SP
124     2020-01-03    SP
124     2020-01-04    SP
125     2020-01-04    SP
232     2020-02-02    PS
234     2020-02-03    PS
234     2020-02-04    PS

What I expect to use is tableA along with the total count of tableB in monthly counts.

Expected result:

ticket   date        owner  month_overall_count
123     2020-01-02    SP           3
124     2020-01-04    SP           2    
234     2020-02-04    PS           3

Im trying to select everything from tableA and only the count from tableB, as tableB has so much data and I don't really want to load it every time.

What I tried is :

select *
from tableA a
inner join (
    select format(date, 'MMM-yyyy') as month, count(*)
    from tableB
) b on format(a.date, 'MMM-yyyy') = format(b.date, 'MMM-yyyy')

But this will not give me field wise count if I need it later. Do I have to provide every field I'm using on tableA?

How do I get this?

I don't understand the expected result. There are three tickets reopened (123, 124, 234), but your result shows only two of them (123, 234). Why? Do you pick one per month? If so, by what rule? — Thorsten Kettner
– Thorsten Kettner, Commented Dec 22, 2020 at 8:44
What do you mean "field wise count"? The question is not clear. — Gordon Linoff
– Gordon Linoff, Commented Dec 22, 2020 at 14:04

Abolfazl · Accepted Answer · 2020-12-22 08:32:40Z

1

this also could be interesting, In following way you are not forced to use group by

SELECT TOP 1 WITH TIES *
from tableA a OUTER APPLY
(
select  count(*) month_overall_count
 from tableB b
WHERE  format(a.date, 'MMM-yyyy') = format(b.date, 'MMM-yyyy')
) res
ORDER BY ROW_NUMBER() OVER(PARTITION BY a.ticket,a.date,a.OWNER ORDER BY a.date desc)

answered Dec 22, 2020 at 8:32

Abolfazl

1,75017 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SteveC · Accepted Answer · 2020-12-22 14:11:42Z

It seems like you're looking for something like (one of) these 2 approaches.

Using OUTER APPLY

select a.*, isnull(b.mo_count, 0) month_overall_count
from tableA a
     outer apply (select count(*) mo_count
                  from tableB b
                  where eomonth(a.[date])=eomonth(b.[date])
                        and a.[owner]=b.[owner]) b
order by a.ticket, a.[date];

Using CTE

In the CTE: summarize date count (mo_count) by end-of-month date (eomonth()) and [owner] from tableB

In the Query: left join CTE on end-of-month date and [owner] to tableA to determine the month_overall_count

with b_month_cte(eom_dt, [owner], mo_count) as (
    select eomonth([date]), [owner], count(*)
    from tableB
    group by eomonth([date]), [owner])
select a.*, isnull(b.mo_count, 0) month_overall_count
from tableA a
     left join b_month_cte b on eomonth(a.[date])=b.eom_dt
                                and a.[owner]=b.[owner];

Popeye · Accepted Answer · 2020-12-22 05:16:29Z

0

Do you need union all and group by?

SELECT TICKET, MAX(DATE), OWNER, COUNT(1) AS NUM_OF_TICKETS
FROM (SELECT ticket, date, owner FROM TABLEA 
      UNION ALL SELECT ticket, date, owner FROM TABLEB) T
GROUP BY TICKET, OWNER, dateadd(m, datediff(m, 0, DATE), 0)

edited Dec 22, 2020 at 5:16

answered Dec 22, 2020 at 4:41

Popeye

36k4 gold badges12 silver badges31 bronze badges

2 Comments

AlisonGrey Over a year ago

I may not be able to use union all coz i have other fields in tableA that aren't there in tableB, added an example in tableA. @Popeye

Popeye Over a year ago

Ok, Then use the column names as updated in the answer

Collectives™ on Stack Overflow

Add count from different table

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related