query to Get sum of two different items that is mapped onto same code

I have this Inventory table for Rooms :

dtm	hotelid	roomcode	intqty
2000-01-04	23	svr	9
2000-01-04	23	ovb	9

this is my mapping table :

hotelid	roomcode1	roomcode2	externalroom
23	svr	ovb	023

I am trying to get the sum of intqty on the inventory table above for both room on same date: This is what i have so far :

select      a.dtm as InvDate,      a.intResortID as ResortId,      a.strRoomType as RoomType,      sUM(a.intQty) as InvCount,      am.externalRoomId  from      dbo.tblAvailable c      join dbo.tblAvailable a on a.dtm = c.dtm         and a.intResortID = c.intResortID         and a.strRoomType = c.strRoomType      join dbo.tblResorts r on r.intResortID = a.intResortID      join bbtest.externalResortMap arm on arm.ResortID = a.intResortID      join bbtest.externalRoomMap am on am.RoomId = a.strRoomType  group by      a.dtm,      a.intResortID,      a.strRoomType,      a.intQty,      am.externalRoomId  order by      invDate;  

The expected result is:

Invdate	ResortId	RoomType	InvCount	externalRoomId
2000-01-04	23	svr	18	023

Query output :

Invdate	ResortId	RoomType	InvCount	externalRoomId
2000-01-04	23	svr	9	023
2000-01-04	23	ovb	9	023

https://stackoverflow.com/questions/67310130/query-to-get-sum-of-two-different-items-that-is-mapped-onto-same-code April 29, 2021 at 10:09AM