Skip to Content
author's profile photo
Ramana Ramana

SAP HANA Exclude weekends while calculating diff between two dates.

Jul 26, 2018 at 07:40 PM | 440 Views | Last edit Jul 26, 2018 at 07:46 PM 2 rev.

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.

Thanks,

RAMANA.

SAP HANA | SAP HANA studio | SQL | hana development | hana modelling
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • author's profile photo
    Kuldeep Jain
    Jul 27, 2018 at 05:32 PM
    -1

    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.

    DO
BEGIN
	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 
		then
			sel1.WRK_HOUR_DIFF[v_i] = :sel1.WRK_HOUR_DIFF[v_i] 	+ 24 ;
			sel1.HOUR_DIFF[v_i] 	= :sel1.HOUR_DIFF[v_i] 		+ 24  ;			
		else
			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;


END;
    Add comment
    10|10000 characters needed characters exceeded