I am trying to create a new list report in Cognos Analytics that should display the item details.
In my item detail table , I have the item id, order date and location.
An item could have more than one row that was in different locations.
I need to show the old and new warehouse in one line for each item ordered by order date
Logic for old warehouse : Show the top warehouse where OrderDate <= (getdate()-20)
Logic for new warehouse : Show the top warehouse where OrderDate <= (getdate()-4)
I am including the SQL script to simulate this in SQL.
I just don't know whether this is possible in Cognos Analytics as I don't have away to enter custom SQL queries.
Would like to know whether this is possible.

--------------------
CREATE TABLE #master([IT_ID] [varchar](100) NULL,[Name] [varchar](100) NOT NULL ) ON [PRIMARY]
insert into #master values('V001','Item1')
insert into #master values('V201','Item2')
insert into #master values('V112','Item3')
insert into #master values('V333','Item4')

CREATE TABLE #detail([IT_ID] [varchar](100) NULL,[OrdDate] [datetime] NOT NULL, [Location] [varchar](100) NULL ) ON [PRIMARY]
insert into #detail values('V001','2022-01-10','Warehouse 1')
insert into #detail values('V001','2022-01-11','Warehouse 2')
insert into #detail values('V001','2022-01-12','Warehouse 3')
insert into #detail values('V201','2023-07-01','Warehouse 23')
insert into #detail values('V201','2023-07-25','Warehouse 56')
insert into #detail values('V112','2020-12-10','Warehouse 4')
insert into #detail values('V112','2023-01-15','Warehouse 67')
insert into #detail values('V112','2023-04-25','Warehouse 3')
insert into #detail values('V123','2022-01-10','Warehouse 1')
insert into #detail values('V333','2000-12-10','Warehouse 4')
insert into #detail values('V333','2010-01-15','Warehouse 67')
insert into #detail values('V333','2015-02-25','Warehouse 3')
insert into #detail values('V333','2016-12-10','Warehouse 4')
insert into #detail values('V333','2017-01-15','Warehouse 67')
insert into #detail values('V333','2023-07-25','Warehouse 3')

select rd.Name, rd.*
, [New Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-4)))

, [Old Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-20)))

from #master rd
drop table #detail
drop table #master

Thanks for the response. Yes, I did that. But it didn't work the way I want. I am including the steps I followed.
1. I created the main query with distinct item codes. 
2. Created one query for past and another for the latest. 
3. Linked the main query with the 2 detail ones. 
4. Added the main query item code to the page header
5. Added a Table with 2 columns to page body
6. Added list control to each column and set the query for each to past and latest
7. Did the page structure configuration

Now report displays one item for each page. Since we have 1000s of items, this report looks huge. 

1. Is there away to show multiple items on the same page?
2. Is it possible to compare the values in the 2 queries. i need to show only when the past and latest are not the same.