Emmanuel
Master Tech
- Local time
- Today, 05:49
- Joined
- Sep 4, 2002
- Messages
- 88
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
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