Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

JanHumanitas

Technical User
Feb 14, 2008
8
NL
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
 
use the month function to extract the month number of date and use this -1 in a filter definition
 
To take account for changes in the year, you need to expand this logic to something like:

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

for the year:

Code:
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

 
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é
 
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

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

soi la, soi carré
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top