Using dates from a different query as filters

[CognosBen]

Hi,

I want to do a count of computers we have in our system where the Query1.FirstDayOfMonth - Date is bigger than the Query2.StatusDate
By doing this, we will get a snapshot over how many active computers we had in our systems month by month back in time.

When I manually set det dates for Query 2 I get the result that I want, but I have not Idea of how I should use Query1 and the Query1.FirstDayOfMonth - field as a paramenter/filter and then group the count on Query1.Month

Any Cognos super experts here that can help me out with this?


Query 1: List with filters for the report.
Query 2: List of computers and timestamps on when the status is changed.

Query 1:
FirstDayOfMonth                  Month    Year
apr 1, 2014 12:00:00 AM        April        2 014
mai 1, 2014 12:00:00 AM        May        2 014
jun 1, 2014 12:00:00 AM        June        2 014
jul 1, 2014 12:00:00 AM         July         2 014


Query 2:
assettag                  StatusDate            Status_Year     Status
WS14900    jun 19, 2014 12:55:00 PM         2014                  OPERATING
WS200325   apr 5, 2013 12:54:38 PM          2013                  OPERATING
WS200488   aug 28, 2013 9:07:49 AM         2013                  OPERATING


Desired Output
FirstDayOfMonth                  Month    Year             CountComputersInOperating
apr 1, 2014 12:00:00 AM        April        2 014         2
mai 1, 2014 12:00:00 AM        May        2 014         2
jun 1, 2014 12:00:00 AM        June        2 014         3
jul 1, 2014 12:00:00 AM         July         2 014         3

As you see. For June/July the count is 3 computers since WS14900 was set to operating on jun 19, 2014 12:55:00 PM.

[BigChris]

How about something slightly different? Could you create columns in your report for each month..something along the lines of

if([Status Date] <= _add_months(?StartDate?,0)) then (1) else (0) -- operating at start of report
if([Status Date] <= _add_months(?StartDate?,1)) then (1) else (0) -- operating in month 1
etc.

You'd need to create a column for each month, but it wouldn't be too much trouble. You could even do something clever with render variables so that you only displayed the right number of columns

[CognosBen]

Hi,

Thank you for your answer.
It's a good ide, but I'm not sure if it solves my problem. I think this was more complicated than I thougt at first, since I now see that I have to compare different dates to calculate if an asset in this report should be counted or not.

[navissar]

How about joining the queries using >= (for example)? So you'd set the join  between status_date and first_day_of_month, but instead of (=) you'd use (>=) as your operator? Wouldn't that achieve what you're after?

[CognosBen]

How about joining the queries using >= (for example)? So you'd set the join  between status_date and first_day_of_month, but instead of (=) you'd use (>=) as your operator? Wouldn't that achieve what you're after?

Of course! I think this might work. Thank you!