October 12, 2010 at 5:47 am
Hi All,
I have a table column called MatchingOff(varchar) which will have value like 17:00,16:00,17:00,Off,Vac,17:00,17:00 i.e 1 week data.I should get max and minimum difference.If column value is Off for all the 7 days then i should get result as off similarly Vacation.
If i have off for 2 columns out of 7 days then i should ignore that columns i should get maximum-minimum from remaining 5 values.
17:00,16:00,17:00,Off,Vac,17:00,17:00
For above example is should get 17:00 -16:00=1:00.
Please check the below stored procedure which i am getting error when the column value if Off or Vac.
ALTER procedure [dbo].[SAR_Sp_AddDelta](@empNo int,@startDate datetime,@endDate datetime)
as
select cast(datepart(hh,cast(max(MatchingOff) as datetime) - cast(min(MatchingOff) as datetime)) as varchar) +
'.' + cast(datepart(mi,cast(max(MatchingOff) as datetime) - cast(min(MatchingOff) as datetime)) as varchar)
from SAR_Timesheet where EmpNo=@empNo and atndate between @startDate and @endDate
October 12, 2010 at 6:00 am
not enough info to help so far I think, naresh;
does your MatchingOff table have 7 columns, or one column with all the values comma delimited?
you can use a CASE statement to replace "Off" or "Vac" with some value...but what? what do you mean by "ignore the value? substiotute zero, do'n t do some calculation?
you might want to consider redesigning so the values are all integers or datetime, vs the headaches you encounter with varchars and wird values you cannot convert.
can you provide soem CREATE TABLE statements, sample data and expected output?
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply