Advance aggregate CASE WHEN

Advance aggregate CASE WHEN

  

Hi , can you help with my problem in using case when , heres my code,

"(Select 

CASE

WHEN ({GateOut}.[DateEncoded]) = (convert(date,'1900-01-01'))

Then (Select Datediff(day,Convert(date,'2017-08-01'),Convert(date,'2018-01-12')))

ELSE (Select Datediff(day,Convert(date,'2017-08-01'),Convert(date,'2018-01-10')))

END

)" the problem is always goes to else result even i change the condition .. thanks

Hi Tams,

At the time of converting the date please try to give type of date like as 'YYYY-MM-DD'

You need to convert text to date like below:

TO_DATE('15/August/2009,4:30 PM'
                ,'DD/Month/YYYY,HH:MI AM')
       ,'DD-MM-YYYY')


Regards

-SK-

Can I ask why you need an advanced SQL?
You can get this extra column in an aggregate as well, using an IF in a calculated column...
With the advantage you will be able to use the NullDate()-

Cheers,
Eduardo Jauch

Check the dateformat of your server and create your date fields alike in text-format. You don't need any conversion. I do prefer an InputParameter 'NullDate' which will be filled with NullDate()

SELECT CASE WHEN {GateOut}.[DateEncoded] = @NullDate

THEN DateDiff(day,'2017-08-01','2018-01-12')

ELSE DateDiff(day,'2017-08-01','2018-01-10')

END as DateSelected

FROM {GateOut}


Where do the other dates come from? Are they hardcoded?

Kind regards,

Remco Dekinga

Eduardo Jauch wrote:

Can I ask why you need an advanced SQL?
You can get this extra column in an aggregate as well, using an IF in a calculated column...
With the advantage you will be able to use the NullDate()-

Cheers,
Eduardo Jauch

Hi , 


i need to use advance query to make a customize exportToExcel , above question is the only problem in my query to move forward,


THanks,Tams


Tams Katigbak wrote:

Eduardo Jauch wrote:

Can I ask why you need an advanced SQL?
You can get this extra column in an aggregate as well, using an IF in a calculated column...
With the advantage you will be able to use the NullDate()-

Cheers,
Eduardo Jauch

Hi , 


i need to use advance query to make a customize exportToExcel , above question is the only problem in my query to move forward,


THanks,Tams


Hi Tams,

No, you don't :)
At least no if you are talking about the widget List To Excel.

You can do an aggregate, than do an assign of the aggregates output list to a local list whose data type is the structure you need, it will enable the "mapping", where you say which attribute of the output list will go in which attribute of the local list, than pass the local list to the List To Excel widget.

I think this would make the code much clear, easier to mantain, etc.

Cheers,
Eduardo Jauch


Remco Dekkinga wrote:

Check the dateformat of your server and create your date fields alike in text-format. You don't need any conversion. I do prefer an InputParameter 'NullDate' which will be filled with NullDate()

SELECT CASE WHEN {GateOut}.[DateEncoded] = @NullDate

THEN DateDiff(day,'2017-08-01','2018-01-12')

ELSE DateDiff(day,'2017-08-01','2018-01-10')

END as DateSelected

FROM {GateOut}


Where do the other dates come from? Are they hardcoded?

Kind regards,

Remco Dekinga

Hi ,


I used manually input the date for testing purposes to see the result instantly. 

but do you have any idea why my query always goes to ELSE statement.


Thanks,

Wendell


Eduardo Jauch wrote:

Tams Katigbak wrote:

Eduardo Jauch wrote:

Can I ask why you need an advanced SQL?
You can get this extra column in an aggregate as well, using an IF in a calculated column...
With the advantage you will be able to use the NullDate()-

Cheers,
Eduardo Jauch

Hi , 


i need to use advance query to make a customize exportToExcel , above question is the only problem in my query to move forward,


THanks,Tams


Hi Tams,

No, you don't :)
At least no if you are talking about the widget List To Excel.

You can do an aggregate, than do an assign of the aggregates output list to a local list whose data type is the structure you need, it will enable the "mapping", where you say which attribute of the output list will go in which attribute of the local list, than pass the local list to the List To Excel widget.

I think this would make the code much clear, easier to mantain, etc.

Cheers,
Eduardo Jauch



Hi 


yes im talking to list to excel, but when i use that, the result is not in order what column first i want.


THanks 

Shashi kant Shukla wrote:

Hi Tams,

At the time of converting the date please try to give type of date like as 'YYYY-MM-DD'

You need to convert text to date like below:

TO_DATE('15/August/2009,4:30 PM'
                ,'DD/Month/YYYY,HH:MI AM')
       ,'DD-MM-YYYY')


Regards

-SK-

Hi 

i already converted the sample value to date, but the problem is my query goes to else result.


THanks,

Wendell


Hi Wendell,

IT seems to be working for me.
See attached OML, please.

Cheers.

Hum... The OML seems to be taking a while...

This is how we do. Create a structure (used in the variable ToExportList) with the fields in the order (and labels) we want , than we assign the result of the aggregate to this local list, doing the map, and use this local list as the source to the Record List to Excel widget.

Cheers,
Eduardo Jauch