query help!!!

[21128]

the query looks like this

select max(DECODE(STA,2,RD,NULL)) "MW_Previous",
max(DECODE(STA,5,RD,NULL)) "MV_Previous",
max(DECODE(STA,8,RD,NULL)) "P2300-3 Previous",
max(DECODE(STA,11,RD,NULL)) "P2300-4 Previous"
from history where tour=18 and dt={?SelectDate}-1 and shift_no=(select max(shift_no) from history where tour=18 and rev_no=1 and dt={?SelectDate}-1)

it works perfect and gives the previous day value.
what do i have to do to get values of 2 days back or may be 3 days back or may be 7 days back and so on?

Thanks

 

[Madawc]

You could do it by using a parameter in the selection. If you want separate values for each day, use DateDiff to find the number of days between the record date and the current date.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[21128]

If i can use DateDiff then how it looks like
i have never used that function

 

[Madawc]

Format is

DateDiff ("d", {date1},  {date2})

It returns the number of days, months, years or whatever. Choices are yyyy, Year. q, Quarter (3-month period). m, Month. y, Day of year. d, Day. w, Weekday. ww, Week (7-day period). If you added a month to 31st January, you would get 28th February, or 29th in a leap-year.

For ww, it is possible to look for whole weeks starting on some other day than Sunday. Thus DateDiff ("ww", {date1}, {date2} crWednesday) would give the number of Wednesdays between the two dates.

Note also that there is an inconsistency between the abbreviations used by DatePart / DateAdd / DateDiff and the abbreviations used for date formatting, where m means minute.

You should be able to get other details using Help

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[CoSpringsGuy]

Will your user request how many days back you want to return data for? Or do you want all the data for today plus X number of days back every time the report runs. If it is a run time option add another parameter indicating the number of days back you want.

currently you have
from history where tour=18 and dt={?SelectDate}-1 and shift_no=(select max(shift_no) from history where tour=18 and rev_no=1 and dt={?SelectDate}-1)
which is correct to select records 1 day prior to the parameter date

with a paramter it would look like this
from history where tour=18 and dt={?SelectDate}-{?daysback} and shift_no=(select max(shift_no) from history where tour=18 and rev_no=1 and dt={?SelectDate}-{?daysback})

Of course if this method is what you are looking for you could just take out the -1 and use the date you are looking for.

If it is a range you are looking for

from history where tour=18 and dt={?SelectDate} and dt>{?SelectDate}-{?daysback} shift_no=(select max(shift_no) from history where tour=18 and rev_no=1 and dt={?SelectDate} and dt>{?SelectDate}-{?daysback})
It is a little unclear, at least to me, what you are trying to do.


_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[21128]

what i am trying to do is, i need the last value entered on selected date. what i did was check for 1 day before and if there is value entered it gives me the rd value of last shift_no. There is a case where there might not be any rd value for 1 day before the selected date, meaning that no data entered. If its the case then it should go to the 2 day before date and check for the rd value of last shift_no. If there is no value then it again has to go to 3 days before and check the same thing and what i need is the last rd value entered.

what i could do is
select max(DECODE(STA,2,RD,NULL)) "MW_Previous",
max(DECODE(STA,5,RD,NULL)) "MV_Previous",
max(DECODE(STA,8,RD,NULL)) "P2300-3 Previous",
max(DECODE(STA,11,RD,NULL)) "P2300-4 Previous"
from history where tour=18 and dt={?SelectDate}-1 or dt={?SelectDate}-2 or dt={?SelectDate}-3 or dt={?SelectDate}-4 or dt={?SelectDate}-5 and shift_no=(select max(shift_no) from history where tour=18 and rev_no=1 and dt={?SelectDate}-1 or dt={?SelectDate}-2 or dt={?SelectDate}-3 or dt={?SelectDate}-4 or dt={?SelectDate}-5)

it checks for five days back value and returns me the rd value that was last entered

what if the value was entered 30 days back?
i just want to simplify that

 

[Madawc]

What you need to do is write a formula field with DateDiff. In your selection, check that its value is within a suitable range.

You can vary this using parameters.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP