Cognos Impromptu report --> date function (last month) 3

[JanHumanitas]

Hello,

I want to generate a report that shows (montly) the
"salary mutation date" of the salaries that have been mutated last month.

For example: On May 1th 2008 I want to see that Mr. X has been promoted on April 15th 2008. But NOT Mr. Y who got a promotion on March 21th 2008.

I hope someone can help me with this question.

Thanks!
Jan

 

[goldsmt]

use the month function to extract the month number of date and use this -1 in a filter definition

 

[blom0344]

To take account for changes in the year, you need to expand this logic to something like:

CASE WHEN
MONTH(<SOMEDATE>) = 1 THEN 12 
ELSE MONTH(<SOMEDATE>)-1 END

for the year:

CASE WHEN
MONTH(<SOMEDATE>) = 1 THEN year(<somedate>)-1
ELSE year(<SOMEDATE>) END

Possibly replace CASE construct by If ..then..else
if this is supported by Impromptu

Ties Blom

 

[drlex]

That's a bit ugly; can you not use nest date add functions to solve it?

If [TODAY] is the system date, ADD-DAY([TODAY], -DAY([TODAY])) will give you the last day of the prior month; ADD-DAY([TODAY], 1-DAY([TODAY])) will give the first of the current month. Deduct a month from this to give you the beginning of the prior month
i.e. SELECT * from

where [table.date] between ADD-MONTH(ADD-DAY([TODAY],1-DAY([TODAY]))) and ADD-DAY([TODAY], -DAY([TODAY]))


(Some Cognos applications offer FIRST-OF-MONTH and LAST-OF-MONTH functions)

soi la, soi carré

 

[blom0344]

The point I tried to make is that simply subtracting 1 from the monthnumber is not sufficient..
Obviously there are other solution that may be more elegant

Ties Blom

 

[drlex]

Ties,
Fair point; I'd taken your code to indicate tacit approval...


soi la, soi carré