Crystal Datename formulas (1 Viewer)

[Emmanuel]

Hey guys,

I have a weird report that is not working in Crystal. Now this runs fine in straight SQL, but for some reason I created a command in Crystal and it gives me all kinds of errors. Is there any way of doing this in Crystal? Even if it is in a different way.

select a.ID_, a.Person_, a.Start_Time_, a.End_Time_, a.Description_, a.Worked /60 Worked

from

(select "ID_", "Person_", "Description_", "End_Time_", "Start_Time_",
datediff(n,"Start_Time_","End_Time_") 'Worked'
from team.LogTime l
where "Person_" in ('Tom', 'Michael', 'Larry', 'Steve')
and "Start_Time_" between
(case when datename(dw,getdate()) = 'Monday' then getdate()
when datename(dw,getdate()) = 'Tuesday' then getdate()-1
when datename(dw,getdate()) = 'Wednesday' then getdate()-2
when datename(dw,getdate()) = 'Thursday' then getdate()-3
when datename(dw,getdate()) = 'Friday' then getdate()-4
when datename(dw,getdate()) = 'Saturday' then getdate()-5
when datename(dw,getdate()) = 'Sunday' then getdate()-6
else 'Other' end)
and
(case when datename(dw,getdate()) = 'Monday' then getdate()+6
when datename(dw,getdate()) = 'Tuesday' then getdate()+5
when datename(dw,getdate()) = 'Wednesday' then getdate()+4
when datename(dw,getdate()) = 'Thursday' then getdate()+3
when datename(dw,getdate()) = 'Friday' then getdate()+2
when datename(dw,getdate()) = 'Saturday' then getdate()+1
when datename(dw,getdate()) = 'Sunday' then getdate()
else 'Other' end))a

 

[Emmanuel]

I think I got it. Just in case anyone else is asking themselves how this would work in Crystal. This is what you need to use inside your select editor.

{your field} in WeekToDateFromSun

This will give you the current weeks data. There are a whole bunch of formulas in ther to pull various date ranges.

Best Regards,

Webmanny.com