Showing results for 
Search instead for 
Did you mean: 

SAP HANA Exclude weekends while calculating diff between two dates.

0 Kudos

Hi All,

I have requirement to find out the Number of hour between two dates, but we have to exclude the weekends (Saturday & Sunday).

Example : DATE1 is 19-July and DATE2 is July-26 - Actual diff is - 168 Hours.

Required Output is (168 - 48 Hours (Saturday & Sunday) - 120 Hours.

This I want to achieve using Graphical or Script Cal view, please help me to solve the issue.



Accepted Solutions (0)

Answers (1)

Answers (1)

0 Kudos

Hi Ramana,

I have taken your scenario as a challenge for me and tried some SQL coding. It's not perfect even bad way. I am trying my hands with SQL codes.

It might give you some idea.

	declare v_i integer;
	declare v_count integer;
sel1 = select 
			"0CALDAY" as "CALDAY",
 	 		"Yesterday" as "YESTERSDAY",
	 		 sum("DATE_DIFF") AS "DATE_DIFF" ,	 		 
	 		 sum((select 0 from dummy)) as "HOUR_DIFF",
	 		 sum((select 0 from dummy)) as "WRK_HOUR_DIFF"
	 		 from  "_SYS_BIC"."ZTABLE"
--	 		 where "0CALDAY" >= '20180701'
			 GROUP BY "0CALDAY", "Yesterday";

select count(*) into v_count from :sel1;

for v_i in 1..v_count do
	WHILE to_date(:sel1.CALDAY[v_i]) < to_date(:sel1.YESTERSDAY[v_i]) do 
		if( weekday(to_date(:sel1.CALDAY[v_i])) ) < 5 
			sel1.WRK_HOUR_DIFF[v_i] = :sel1.WRK_HOUR_DIFF[v_i] 	+ 24 ;
			sel1.HOUR_DIFF[v_i] 	= :sel1.HOUR_DIFF[v_i] 		+ 24  ;			
			sel1.HOUR_DIFF[v_i] 	= :sel1.HOUR_DIFF[v_i] 		+ 24  ;			
		end if;
		sel1.CALDAY[v_i] = ADD_DAYS(to_date(:sel1.CALDAY[v_i]), 1) ;
	end while ;	
end for;

select * from :sel1;


0 Kudos

Thank you so much Kuldeep for your valuable post. Let me try with this.

Active Contributor

I'm don't think either the question nor the answer are well done.

As there's a lot more to say about the problem and the solution approaches, I put that into my blog post: Finding answers on workdays.