Cognos - date prompts in SQL Query

[ELISAMENDES]

Hi experts,

I am a beginner on Cognos so I would appreciate your help with my "rookie" questions.

I would like to filter transactions to only show the last transaction date per customer. As an example, if a customer has 4 transactions, on the 01Jan,02Feb, 15Marc I would only select the 15 March. And create a new variable with this filter calculation, let's say LastTrx.

I would like to link this query to a date prompt, where I can select a data range. I need to add further calculations to this query, so if a user selects a data range from 01Jan2014 to 31March2014(data prompt) the query will select the 1096 days before the starting date (01jan2014-1096days) to 31March2014=Jan2011 to Marc2014.

Finally I need to create a new variable based on these values calculation. If a last transactions was made between 364 days and 0 days Segment=Active; >365 Segment<=1095=Inactive; >1096=Lost.

The final table will have customer_id, LastTrx and Segment columns.
So I think the steps will be like this:
-   Date prompt: User selects a data range from 01Jan2014 to 31March2014
o   I am able to run this data prompt range but I don't know how can I create and select only the last transaction;
-   Date Filter: Filter data will calculate the 1096 previous to the starting date to the end date =(01jan2014-1096days) to 31March2014=Jan2011 to Marc2014;
o   How can I create this query linked with the data prompt?
o    I am able to select a date range on the date prompt, but it gives me error when I try to do calculations on data dimensions – as an example subtract 1096 days.
-   SQL Query: Calculate the last transaction of members during this period and create the final variable label Segment.
o   How can I link this query with the previous ones?

Sorry I know it is a long list. I would apppreaciate your comments/help in any of the above questions.

Elisa

[bdbits]

You have posted in the Query Studio forum. But I think your questions sound more like you are actually using Report Studio? If so, you might want to ask MFGF to move this for you.

A lot about how to do what you are asking depends on whether you have a relational or a dimensional package. Which is it?

Also, version sometimes matters and you are in the older 8.x forums. I only ask because you are new, as you said, and you might get more of a response if the question is in the version 10 forums. I do not think version will matter in this particular case, I just wanted to make you aware.

[ELISAMENDES]

Thanks I will try my luck on Report Studio group :)