CASE function

Hi,
I have a date column where the application users puposely enter a date way
in the future as part of their business rule. For instance, entering the yea
r
2033 if the given value for this date column is unknown.
I need to programmatically retrieve this date and represent those
out-of-whack dates to show as the current date + 10 days.
I created the following SELECT stmt. using the CASE function:
SELECT ....
CASE post_datetime
WHEN post_datetime > getdate()+365 THEN getdate()+10
...
However, SQL QA returns an error (Incorrect syntax near '>') when I try to
execute this stmt. What am I doing wrong here. Please help. Thanks.
Regards,
- Rob.Rob wrote:
> Hi,
> I have a date column where the application users puposely enter a date way
> in the future as part of their business rule. For instance, entering the y
ear
> 2033 if the given value for this date column is unknown.
> I need to programmatically retrieve this date and represent those
> out-of-whack dates to show as the current date + 10 days.
> I created the following SELECT stmt. using the CASE function:
> SELECT ....
> CASE post_datetime
> WHEN post_datetime > getdate()+365 THEN getdate()+10
> ...
> However, SQL QA returns an error (Incorrect syntax near '>') when I try to
> execute this stmt. What am I doing wrong here. Please help. Thanks.
> Regards,
> - Rob.
Try this instead:
CASE WHEN post_datetime > GETDATE() + 365 THEN GETDATE() + 10 ELSE
post_datetime END|||> SELECT ....
> CASE post_datetime
> WHEN post_datetime > getdate()+365 THEN getdate()+10
There are two general forms of the CASE expression (it is not a function).
You can either say
CASE [expression] WHEN [value] THEN [value] END
or
CASE WHEN [expression][operator][value] THEN [value] END
You combined the two in a way I don't recall ever seeing (and as you have
found out, the syntax is invalid). You need the latter, because you are
testing a more complex expression than simple equality.
Try:
SELECT
CASE WHEN post_datetime > getdate()+365 THEN getdate()+10 ENDsql

CASE and DATE statement - Need Help :)

Hi

Can someone help please.

I need to create a new column with a name of "AdvertPurchasePrice" based upon a Date Value which is held in a column called "AdvertCreationDate".

If the date is before the 10th of April 2007 then the column "AdvertPurchasePrice" needs to be 299 else it needs to be 399

Can anyone help?

Steve

Quote:

Originally Posted by opusid

Hi

Can someone help please.

I need to create a new column with a name of "AdvertPurchasePrice" based upon a Date Value which is held in a column called "AdvertCreationDate".

If the date is before the 10th of April 2007 then the column "AdvertPurchasePrice" needs to be 299 else it needs to be 399

Can anyone help?

Steve

use the CASE...WHEN...END statement...

select AdvertPurchasePrice = case when AdvertCreationDate is before 04/10/2007 then 299
else 399
end
from mytable

this is a pseudocode, not a working code...but if you search the help on the CASE-WHEN-END syntax, you'll see what i mean

Cascading Parameter issue with RS2005... It worked in RS2000

Our company is attempting to convert several RS2000 reports to RS2005. We have 4 default parameters on every report.

1) Date Range (dropdown) – populated via Non-Queried Available values (Ex. Today, Week to Date, Month to Date, Fiscal Period)
2) Fiscal Period (cascading dropdown) – populated via Query Available values (if Date Range = ‘Fiscal Period’, select the last 24 fiscal months, else select Date Range value)
3) Start Date (cascading textbox) – populated via Query Default values (depending on Fiscal Period value calculate Start Date)
4) End Date (cascading textbox) – populated via Query Default values (depending on Fiscal Period value calculate End Date)

The user starts by selecting a Date Range which auto populates the Fiscal Period the auto populates the 2 Date textboxes. This works great in RS2000; however it’s not 100% in RS2005. Here’s what happens:

If a user selects ‘Today’ or ‘Month to Date’ in Date Range, everything works fine. At this point there is only 1 value in Fiscal Period dropdown. If the user selects ‘Fiscal Period’ in the Date Range, the Fiscal Period is populated with 24 values (1 for the last 24 fiscal months) and is set to the top value (June – 2006). The 2 Date textboxes are also populated correctly.

The problem is when the user changes the value in the Fiscal Period dropdown. Let's say the pick 'Mar - 2004'. The 2 Date fields are not refreshed or updated. If the user changes the selection back in the first dropdown (Date Range), everything is refreshed and updated appropriately. I don’t under stand why the first dropdown will refresh all the dependent parameters but the second dropdown won’t refresh the 2 Date fields below it?

Any input would be very helpful. I’ve tested this on 3 different development boxes and 2 different RS2005 servers. All tests had the same results.

Thanks,
Nick

Any ideas why this thing works in RS2000, but not in RS2005? Any input would be valued. Thanks,

Nick

Cascading Date for Filter

Hi,

I have a Date Dimension with these attributes:

Datekey

FullDate (eg. 1/1/2007, 1/1/2008)

Year,

Month (eg. Feb 2006, March 2006, April 2006)

If i put the Year on the filter and select 2005, I want the Month filter to show only months with 2005 as the year.

Is it possible?

cherriesh

Hi,

It somewhat depends what tool you are using to query your cube, which tool/s are you using?

Matt

|||my cube is connected to ms excel 2007|||

Hi,

I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.

I have seen this kind of thing implemented successfully, Panorama has been able to do this for years. ProClarity, I've seen it work, but when we got a later release it stop working. And a few custom made applicantions.

HTH

Matt

|||

Hi,

I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.

So you mean in my Date Dimension, the month attribute there should only be like February, March etc instead of February 2007, March 2007 etc?

cherriesh

|||

Hi,

Yeah. e.g.

Year= 2006

Measure

Month

Jan 20

Feb 30

March 40

april 50

May 60

..

Year= 2007

Measure

Month

Jan 90

Feb 80

March 75

april 60

May 40

..

This way the month is independent of the year, if you don't slice by year you will get ALL year values for that month. So Jan will be 2005+2006+2007... values.

Year= ALL

Measure

Month

Jan 110

Feb 110

March 115

april 110

May 100

If you have a copy of the adventure works database, the "month of year" attribute is the one you want to create. They only really use Month+Year in the hierarchies and have year, month, day .. as a seperate attributes.

HTH

Matt

Cascading Date for Filter

Hi,

I have a Date Dimension with these attributes:

Datekey

FullDate (eg. 1/1/2007, 1/1/2008)

Year,

Month (eg. Feb 2006, March 2006, April 2006)

If i put the Year on the filter and select 2005, I want the Month filter to show only months with 2005 as the year.

Is it possible?

cherriesh

Hi,

It somewhat depends what tool you are using to query your cube, which tool/s are you using?

Matt

|||my cube is connected to ms excel 2007|||

Hi,

I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.

I have seen this kind of thing implemented successfully, Panorama has been able to do this for years. ProClarity, I've seen it work, but when we got a later release it stop working. And a few custom made applicantions.

HTH

Matt

|||

Hi,

I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.

So you mean in my Date Dimension, the month attribute there should only be like February, March etc instead of February 2007, March 2007 etc?

cherriesh

|||

Hi,

Yeah. e.g.

Year= 2006

Measure

Month

Jan 20

Feb 30

March 40

april 50

May 60

..

Year= 2007

Measure

Month

Jan 90

Feb 80

March 75

april 60

May 40

..

This way the month is independent of the year, if you don't slice by year you will get ALL year values for that month. So Jan will be 2005+2006+2007... values.

Year= ALL

Measure

Month

Jan 110

Feb 110

March 115

april 110

May 100

If you have a copy of the adventure works database, the "month of year" attribute is the one you want to create. They only really use Month+Year in the hierarchies and have year, month, day .. as a seperate attributes.

HTH

Matt

Cascading Date for Filter

Hi,

I have a Date Dimension with these attributes:

Datekey

FullDate (eg. 1/1/2007, 1/1/2008)

Year,

Month (eg. Feb 2006, March 2006, April 2006)

If i put the Year on the filter and select 2005, I want the Month filter to show only months with 2005 as the year.

Is it possible?

cherriesh

Hi,

It somewhat depends what tool you are using to query your cube, which tool/s are you using?

Matt

|||my cube is connected to ms excel 2007|||

Hi,

I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.

I have seen this kind of thing implemented successfully, Panorama has been able to do this for years. ProClarity, I've seen it work, but when we got a later release it stop working. And a few custom made applicantions.

HTH

Matt

|||

Hi,

I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.

So you mean in my Date Dimension, the month attribute there should only be like February, March etc instead of February 2007, March 2007 etc?

cherriesh

|||

Hi,

Yeah. e.g.

Year= 2006

Measure

Month

Jan 20

Feb 30

March 40

april 50

May 60

..

Year= 2007

Measure

Month

Jan 90

Feb 80

March 75

april 60

May 40

..

This way the month is independent of the year, if you don't slice by year you will get ALL year values for that month. So Jan will be 2005+2006+2007... values.

Year= ALL

Measure

Month

Jan 110

Feb 110

March 115

april 110

May 100

If you have a copy of the adventure works database, the "month of year" attribute is the one you want to create. They only really use Month+Year in the hierarchies and have year, month, day .. as a seperate attributes.

HTH

Matt

Cascade parameter and sharepoint 2007

Hi,

I have an issue with my report when deploy to sharepoint 2007. Basically my report have 3 parameters, start date date picker, end date date picker and event dropdown. The data displayed in the event dropdown will depends on the start date and end date specified by user.

The problem is the event dropdown some how get "cached". I have to manually click on apply in sharepoint report veiwer for the event list to be updated. This doesn't happend in Visual Studio and also report server but only happend when I deloyed to sharepoint thus I suspect it's the sharepoint issue.

Anyone encounter similar problem before?

Ok. Seems like it's related to the date picker used by sharepoint 2007. If I manual change the date by editing the date text box, the post back works. But if i change the date using the popup calendar, the post back doesn't work. Can someone from Microsoft confirm that there are issues with the calendar control?

Capture date and timestamp of a file?

Hi,

I am pulling files from the FTP site using the FTP task. I want to also capture the date and timestamp of each of these files so that I can insert the values into a database and track when are these files get created normally on the FTP server.

Any ideas?

Thanks in advance for your help.

$wapnil

Doesn't the transfer of files from the FTP server preserve the date and time? You could inspect that with a custom script.|||

No the transfer of files do not preserve the date and time. When i download the files all the files show the same date and timestamp.

Can the FTP task be configured to preserve the date and time?

Thanks!

$wapnil

|||

spattewar wrote:

No the transfer of files do not preserve the date and time. When i download the files all the files show the same date and timestamp.

Can the FTP task be configured to preserve the date and time?

Thanks!

$wapnil

Well.... You can write a custom script to perhaps issue an 'ls' command inside the FTP session. Remember, FTP isn't designed for file interrogation, it's designed to transport files.|||

Thanks.

I am using a ForEach loop container which iterates through a list of file names to be downloaded. In this container I have an FTP task that uses an FTP connection manger to connect and download the file name passed to it by the container. If the FTP is successful then I update the database using an Execute SQL task to tell that the file has been downloaded.

Where should I keep the custom script so that it runs inside the FTP session? Also can I use the File System task in some way? I am pretty new to his and hence may be asking very rudimentary questions..please be patient.

Thanks agains for your time.

$wapnil

|||The script would be your FTP session. It would replace your FTP task.|||

I will try this out and will let you know in case there are any problems.

Thanks.

$wapnil