Hi,
My data values are like below.
Months Start Date End Date
Mar-16 1-Mar-16 1-Mar-17
Mar-16 1-Jan-16 31-Dec-18
Mar-16 1-Jan-16 31-Dec-17
Mar-16 1-Jan-16 31-Dec-18
I want to check if the Months column values are equal or in between start date and end date. As Date formats are different I am feeling difficulty to achieve this . Can you guide me how to do it?
Regards
Quote from: Kid_in_Cognos on 22 Jun 2016 09:41:19 AM
Hi,
My data values are like below.
Months Start Date End Date
Mar-16 1-Mar-16 1-Mar-17
Mar-16 1-Jan-16 31-Dec-18
Mar-16 1-Jan-16 31-Dec-17
Mar-16 1-Jan-16 31-Dec-18
I want to check if the Months column values are equal or in between start date and end date. As Date formats are different I am feeling difficulty to achieve this . Can you guide me how to do it?
Regards
The first question is how much of the month needs to be in the range.
Mar-16 runs from the 01-Mar-16 to 31-Mar-16, so if the end date is (let's say) 18-Mar-2016 then is it between or not? Similarly, what if the start date is 5-Mar-16?
If the entire month must be within the range then I'd suggest you convert your Months value to a Month Start and Month End date. If this is a string field then you'll need to figure out the best way to cast it to a date data type for the 1st day of the month. There are built-in Cognos functions for casting, or you can use native database functions. It might be worth posting what database you are using to get specific advice.
There is a function called _last_of_month that you can use to easily find out the last day of the month, using your newly derived first of month value from the previous paragraph.
After that it is just a matter of comparing to see if the entire month is within the range. If the requirement isn't for the entire month then you'll need to provide some details on exactly what constitutes "between start date and end date."
[Months Start Date] between [Start Date] and [End Date]
and
[Months End Date] between [Start Date] and [End Date]
Hi Lynn,
Thanks for the reply.
Yeah. The range is for entire Month only and the database is Oracle. I will try to follow the suggestion and hope to succeed. :)
Cheers !!!
Quote from: Kid_in_Cognos on 22 Jun 2016 11:02:54 AM
Hi Lynn,
Thanks for the reply.
Yeah. The range is for entire Month only and the database is Oracle. I will try to follow the suggestion and hope to succeed. :)
Cheers !!!
If memory serves, you might be able to use Oracle's TO_DATE function to get the first day of the month. Haven't been in the Oracle realm in quite some time so take that with a grain or two of salt.
TO_DATE( '01-' || [Months], 'DD-MON-YY' )
Thanks Lynn.
I am trying to total the premium, if the record_creation_date between risk start date and risk end date . But I am getting exception error. Not sure what I am doing. Can you help me out?
case when
([RECORD_CREATION_TS] between [RISK_PE_START_DT] and [RISK_PE_END_DT])
then
(Sales and Claim Summary].[Sales and Cliam Summary].[PREMIUM])
end
Data is as like below :
RECORD_CREATION_TS RISK_PE_START_DT RISK_PE_END_DT
Mar 28, 2016 12:00:00 AM Jan 1, 2016 12:00:00 PM Dec 31, 2017 12:00:00 PM
Mar 28, 2016 2:14:12 AM Jan 1, 2016 12:00:00 PM Dec 31, 2017 12:00:00 PM
Jun 10, 2016 6:20:44 AM Jan 1, 2016 12:00:00 PM Dec 31, 2018 12:00:00 PM
Mar 28, 2016 12:00:00 AM Mar 1, 2016 2:18:16 AM Mar 1, 2017 2:18:16 AM
Regards
What is the error?
I don't see anything wrong with the expression, although adding "else 0" might be something to consider. I'd also set the aggregate function for that expression to total.
Posting the specific error message, including the details, would be helpful in diagnosing.
Hi Lynn,
I am extremely sorry for my mistake. In case condition after the 'then' stmt ,I missed a bracket somehow.
it was
(Sales and Claim Summary].[Sales and Cliam Summary].[PREMIUM])
the correct one should be
([Sales and Claim Summary].[Sales and Cliam Summary].[PREMIUM])
Thank you much for the help.
Hi,
The Month column value is like below. My db is Oracle.
MONTHS
---------
Dec-18
Dec-18
Dec-18
This is a string field. I want to convert this string variable into the date format and needs to get two data items.
one is for the start date as like Dec 01, 2018 12:00:00 PM and
second is for the end date as like Dec 31, 2018 12:00:00 PM.
Can you guide me how can I do it?
-------------------------------------------------
I got the logic.As of now the help not required :). Lynn already suggested to use
TO_DATE( '01-' || [Months], 'DD-MON-YY' ) which is working great.Thanks