case for how to display punchout time today() where punch out time is null or nothing??

[Former Member]

hello everyone,

this is my crystal report query regarding above issue,

select punchin, punchout,

(case when ( (DATEPART(hh,punchout)=0) and (DATEPART(hh,punchin))=0) and ((DATEPART(ss,punchout)-DATEPART(ss,punchin)>0) or (DATEPART(mm,punchout)-DATEPART(mm,punchin)>0))  then (RegRate * cast((datediff(mi, punchin, punchout)  / 60.0) as FLOAT) + OTWage)  else ((case when (DATEPART(hh,punchout)-DATEPART(hh,punchin))=0 then 0 else

(case when (0 between DATEPART(hh,punchin) and DATEPART(hh,punchout))

and (0 = DATEPART(hh,punchin)) then 0

when (0 between DATEPART(hh,punchin) and DATEPART(hh,punchout)) then

(RegRate * cast((datediff(mi, punchin, punchout)  / 60.0) as FLOAT) + OTWage)/(DATEPART(hh,punchout)-DATEPART(hh,punchin))

else 0 end) end)) end ) as TP2,(case when (DATEPART(hh,punchout)-DATEPART(hh,punchin))=0 then 0 else 0  end) as TP1,

(case when (1 = DATEPART(hh,punchin)) then 0  ELSE 0 END)  as [1]

so please guide me for the case where i want todays() time on which  the punchout time is null.

[Former Member]

hey Abhilash,did u got my problem??

help me.

[abhilash_kumar]

I'm not too sure what you're trying to do here.

Do you have an example please?

-Abhilash

[Former Member]

ok,let me explain it,

i have records of number of employees having their punchin & punchout time.

there are several records where punchout time is not present or null,so i just want to replace that punchout time by the todays or now() time.

so the case for required solution must be updated with now() function.

refer my case in above query & tell me how could i get it.