sql query Change tracking for rows should get results also for other row not affected but on mapping

Question

I have this Inventory table for Rooms named tblavailable :

this is my room mapping table named externalRoomMap :

I am trying to get the sum of intqty on the inventory table above for both room on same date when there is any update or insert done on tblavailble table for room svr or ovb :

Please note that I implemented change tracking on tblavailble table to check if there is an insert or update done on the tblavailble table

This is what i have so far :

select  
    a.dtm as InvDate,  
    a.intResortID as ResortId,  
    am.RoomId_1 as RoomType,  
    sum(a.intQty) as InvCount,  
    am.externalRoomId  
  
  from changetable(changes dbo.tblAvailable a )  
    join dbo.tblResorts r on r.intResortID = a.intResortID  
    join bbtest.externalResortMap arm on arm.ResortID = a.intResortID  
   join bbtest.externalRoomMap am  a.strRoomType in (am.RoomId_1   
  ,am.RoomId_2)  
group by  
   a.dtm,  
    a.intResortID,  
    am.RoomId_1,     
    am.externalRoomId  
order by  
    invDate;  

The expected result is:

But the current output of the above sql query is when there is an update for one of those room on mapping table :

Answer 1

Hi @steffimeister ,

Welcome to the microsoft TSQL Q&A forum!

If you want to sum the rows that exist in the externalRoomMap table but are not affected by Change tracking, you need to use right join to connect all the roomcodes first.

In addition, in the final summation, you need to use sum() over() instead of sum group.

Please refer to:

create table tblavailable(dtm date,hotelid int,roomcode char(15) primary key ,intqty int)  
insert into tblavailable values('2000-01-04',23,'ovb',9)  
create table externalRoomMap  
(hotelid int,roomcode1 char(15),roomcode2 char(15),externalroom int)  
insert into externalRoomMap values(23,'svr','ovb',023)  
  
create table externalResortMap  
(hotelid int,externalresortid int,hotelname char(15))  
insert into externalResortMap values(23,234,'testhotel')  
  
create table tblResorts  
(hotelid int,hotelname char(15))  
insert into tblResorts values(23,'testhotel')  
  
ALTER TABLE tblAvailable  
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON);  
  
insert into tblavailable values('2000-01-04',23,'svr',9)  
                                
  
select distinct a.dtm as InvDate,a.hotelid as ResortId,am.roomcode1 as RoomType,  
sum(a.intqty) over(partition by a.dtm,a.hotelid) as InvCount,a.hotelid as externalRoom  
from changetable(changes dbo.tblAvailable,0) t  
right join tblAvailable a on t.roomcode=a.roomcode  
join dbo.tblResorts r on r.hotelid = a.hotelid  
join externalResortMap arm on arm.hotelid =a.hotelid  
join externalRoomMap am  on a.roomcode=am.roomcode1 or a.roomcode=am.roomcode2  

Output：

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Please also remember to accept the answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.