Record Selection YTD

[mirogak]

Folks,

I have a very simple query.

Using: Oracle 8i and CR XI R2
-------------------------------

I am pulling data with the following record selection:

{TABLE.INVOICE_DATE} in YearToDate

however, I don't exactly want YTD which pulls until today, I want to pull ONLY until yesterday

I tried: {TABLE.INVOICE_DATE} in YearToDate -1 but didn't work.

Any suggestions!

cheers,
Mirogak

 

[KeertanaCR]

Hi

I guess you can say

{TABLE.INVOICE_DATE} in YearToDate
and {TABLE.INVOICE_DATE}<>currentdate

which gives the result set for YTD and filters the ones where Invoice date is today's date

thank you
Keerthana

 

[synapsevampire]

Keep in mind tht Crystal date ranges allow for minimum and maximum, so try:

{TABLE.INVOICE_DATE} in minimum(YearToDate) to maximum(YearToDate)-1

I did a test using Keerthanas example and the sql passed was:

SELECT `Orders`.`Order Date`, `Orders`.`Customer ID`, `Orders`.`Payment Received`
FROM `Orders` `Orders`
WHERE ((`Orders`.`Order Date`>={ts '2007-01-01 00:00:00'} AND `Orders`.`Order Date`<{ts '2007-01-23 00:00:00'}) OR (`Orders`.`Order Date`>={ts '2007-01-24 00:00:00'} AND `Orders`.`Order Date`<{ts '2007-01-24 00:00:00'}))

Since it's using conflicting statements, try to avoid that approach.

-k

 

[mirogak]

Your'e right Synapevampire.

Keerthana! your solution worked as well.

However, synapsevampire's solution is more efficient.

Thanks to the both of you.

cheers,
mirogak

 

[synapsevampire]

The above SQL indicates that Keerthanas solution did not work as the second OR allows for the current date.

And there's no significant difference in terms of efficiencies, however you may return bad data...

-k

 

[KeertanaCR]

Thanks Syn for correcting my solution. I am pretty new to crystal and learning alot on this form.

thank you
Keerthana

 

[synapsevampire]

Nae worries, one of the weirder parts of Crystal is passing SQL, check out my FAQ:

faq767-3825

-k