Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: convert sql to formula Post Reply Post New Topic
Author Message
awakendream
Newbie
Newbie


Joined: 23 Mar 2009
Online Status: Offline
Posts: 7
Quote awakendream Replybullet Topic: convert sql to formula
     Posted: 25 Mar 2009 at 4:19am
Hi All,

Any idea how to convert this sql to formula? or is there any way to just add this sql statement directly?

select * from t_subscribed_products where PRINT_START_DATE <> START_DATETIME and START_DATETIME >= (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))) + 1) and START_DATETIME < (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))) + 1)

Thanks a lot... Smile
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 25 Mar 2009 at 6:19am
you can add it to the record filter.  By default Crystal is going to return all records, this would filter out the what you don't want.  It would seem that sysdate would change to 'now', the Add_months would be changed to dateadd(), I don't know offhand how to convert last_day, you might need to create a helper formula for that, and trunc, I don't know what it is doing, just getting rid of the time element?
 
to see if you filter is working correctly, you could create a formula that does your filtering that return true or false, then place it on the details line with start_datetime and see if the value is correct, then you can call the formula from Report/Selection Formulas/Record
 
Hope this helped in some little way.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Mar 2009 at 6:36am

Seconding on Lockwelle's notion if you can restate this in terms of the data elements and your seletion criteria on those I am sure some one can assist you in writing the select statement. It is hard to decipher without knowing your tables and reference points. I think you want records that were in the last month from today's date...

table.PRINT_START_DATE <> table.START_DATETIME and
datediff("M",{table.START_DATETIME},currentdate)=1
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum