February 14, 2013 at 2:12 pm
I have the following formula in a crystal report, just not sure how to setup in a SP to calculate the same .
I wanted to add to my select
Thanks..
//
Local Datevar Start;
Local Datevar End;
If (cdate({APPOINTMENT.EffDate}) <= {?date}-27 And cdate({APPOINTMENT.Expdate}) in {?date}-27 to {?date}-21)
Then Start:={?date}-28
else
if (cdate({APPOINTMENT.EffDate}) < {?date}-27 and cdate({APPOINTMENT.Expdate})> {?date}-21)
then start:={?date}-28
else
if (cdate({APPOINTMENT.EffDate}) in {?date}-27 to {?date}-21)
then
start:=cdate({APPOINTMENT.EffDate}-1) ;
// end
If ({APPOINTMENT.EffDate} <= {?date}-27 And {APPOINTMENT.Expdate} in {?date}-27 to {?date}-21)
Then
End:=cdate({APPOINTMENT.Expdate})
else
if ({APPOINTMENT.EffDate} < {?date}-27 and {APPOINTMENT.Expdate}> {?date}-21)
then
end:={?date}-21
else
if (cdate({APPOINTMENT.EffDate}) in {?date}-27 to {?date}-21 and cdate({APPOINTMENT.Expdate}) > {?date}-21)then
end:={?date}-21;
//tart:=end
Datediff("d",Start, End)
February 14, 2013 at 2:42 pm
Can you provide sample data?
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Do you understand what the crystal code is doing and can you state that in plain english or are you looking for someone to help you understand what the code is doing?
February 14, 2013 at 2:50 pm
Yes sorry...
The report has 4 formulas this is one of the four (same except for the date ranges)
sp has one parameter for a date
First part calculated which date to use as a start date depending on the criteria of the if's it decides which date to use
Second part cals which to use for the end date
Then it writes out the daysdif in days beween start and end dates
I was thinking I could come up witha daysdiff which a buch of if's in it?
Thanks
February 14, 2013 at 3:05 pm
CREATE TABLE #Test
(
EffectiveDate DATE,
ExpiredDate DATE
)
INSERT INTO #Test(EffectiveDate,ExpiredDate) VALUES ('2013-02-14 00:00:00.000','2013-02-20 00:00:00.000')
INSERT INTO #Test(EffectiveDate,ExpiredDate) VALUES ('2013-01-01 00:00:00.000','2013-01-02 00:00:00.000')
INSERT INTO #Test(EffectiveDate,ExpiredDate) VALUES ('2013-02-03 00:00:00.000','2014-02-20 00:00:00.000')
INSERT INTO #Test(EffectiveDate,ExpiredDate) VALUES ('2010-12-31 00:00:00.000','2013-02-20 00:00:00.000')
SELECT
EffectiveDate,
ExpiredDate,
CASE
WHEN EffectiveDate <= GETDATE()-27 THEN DATEADD(day,-28,EffectiveDate)
--WHEN.....
--WHEN.....
--ELSE --if you need it
END
AS StartDate
FROM #Test
DROP TABLE #Test
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply