Report viewer DateDiff() How to eliminate sunday Date
Dear Sir, I'd tried below code i got result ID Date Difference 1 27-07-2012 12:54:02 0:0:0 1 27-07-2012 12:54:16 0:0:14 2. 28-07-2012 12:54:02 0:0:0 2. 28-07-2012 12:54:04 0:0:2 3 20-09-2012 12:54:02 0:0:0 3 22-09-2012 12:56:02 20:02:0 <--But here i need 21-09-2012 working 10 hours also calculated 3 24-09-2012 12:54:02 7:58:0 <- and Here IF have Sunday means eliminate the Sunday Please Help me =IIF(RowNumber(Nothing)=1,"0:0:0", (IIF(Fields!ID.Value=Previous(Fields!ID.Value), (REPLACE( (IIF(INSTR(IIF(RowNumber(Nothing)=1,0, IIF(Format(CDate(Fields!Date.Value),"MM-dd-yyyy")=Format(Previous(Fields!Date.Value),"MM-dd-yyyy"), DateDiff("s",Previous(Fields!Date.Value),Fields!Date.Value) / 3600, (DateDiff("s",Format(CDate(Fields!Date.Value),"MM-dd-yyyy 08:00:00"), Format(CDate(Fields!Date.Value),"MM-dd-yyyy HH:mm:ss")) + (DateDiff("s",Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy HH:mm:ss"), Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy 18:00:00")) ))/3600)),"."), LEFT(IIF(RowNumber(Nothing)=1,0, IIF(Format(CDate(Fields!Date.Value),"MM-dd-yyyy")=Format(Previous(Fields!Date.Value),"MM-dd-yyyy"), DateDiff("s",Previous(Fields!Date.Value),Fields!Date.Value) / 3600, ( DateDiff("s",Format(CDate(Fields!Date.Value),"MM-dd-yyyy 08:00:00"), Format(CDate(Fields!Date.Value),"MM-dd-yyyy HH:mm:ss")) + (DateDiff("s",Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy HH:mm:ss"), Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy 18:00:00")) ))/3600)) ,INSTR(IIF(RowNumber(Nothing)=1,0, IIF(Format(CDate(Fields!Date.Value),"MM-dd-yyyy")=Format(Previous(Fields!Date.Value),"MM-dd-yyyy"), DateDiff("s",Previous(Fields!Date.Value),Fields!Date.Value) / 3600, ( DateDiff("s",Format(CDate(Fields!Date.Value),"MM-dd-yyyy 08:00:00"), Format(CDate(Fields!Date.Value),"MM-dd-yyyy HH:mm:ss")) + (DateDiff("s",Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy HH:mm:ss"), Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy 18:00:00")) ) )/3600)),".")), IIF(RowNumber(Nothing)=1,0, IIF(Format(CDate(Fields!Date.Value),"MM-dd-yyyy")=Format(Previous(Fields!Date.Value),"MM-dd-yyyy"), DateDiff("s",Previous(Fields!Date.Value),Fields!Date.Value) / 3600, ( DateDiff("s",Format(CDate(Fields!Date.Value),"MM-dd-yyyy 08:00:00"), Format(CDate(Fields!Date.Value),"MM-dd-yyyy HH:mm:ss")) + (DateDiff("s",Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy HH:mm:ss"), Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy 18:00:00")) ) )/3600))) MOD 3600),".","") & ":"& REPLACE((IIF(INSTR(IIF(RowNumber(Nothing)=1,0, IIF(Format(CDate(Fields!Date.Value),"MM-dd-yyyy")=Format(Previous(Fields!Date.Value),"MM-dd-yyyy"), DateDiff("s",Previous(Fields!Date.Value),Fields!Date.Value) / 60, ( DateDiff("s",Format(CDate(Fields!Date.Value),"MM-dd-yyyy 08:00:00"), Format(CDate(Fields!Date.Value),"MM-dd-yyyy HH:mm:ss")) + (DateDiff("s",Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy HH:mm:ss"), Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy 18:00:00")) ) )/60)),"."), LEFT(IIF(RowNumber(Nothing)=1,0, IIF(Format(CDate(Fields!Date.Value),"MM-dd-yyyy")=Format(Previous(Fields!Date.Value),"MM-dd-yyyy"), DateDiff("s",Previous(Fields!Date.Value),Fields!Date.Value) / 60, ( DateDiff("s",Format(CDate(Fields!Date.Value),"MM-dd-yyyy 08:00:00"), Format(CDate(Fields!Date.Value),"MM-dd-yyyy HH:mm:ss")) + (DateDiff("s",Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy HH:mm:ss"), Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy 18:00:00")) ) )/60)) ,INSTR(IIF(RowNumber(Nothing)=1,0, IIF(Format(CDate(Fields!Date.Value),"MM-dd-yyyy")=Format(Previous(Fields!Date.Value),"MM-dd-yyyy"), DateDiff("s",Previous(Fields!Date.Value),Fields!Date.Value) / 60, ( DateDiff("s",Format(CDate(Fields!Date.Value),"MM-dd-yyyy 08:00:00"), Format(CDate(Fields!Date.Value),"MM-dd-yyyy HH:mm:ss")) + (DateDiff("s",Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy HH:mm:ss"), Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy 18:00:00")) ) )/60)),".")), IIF(RowNumber(Nothing)=1,0, IIF(Format(CDate(Fields!Date.Value),"MM-dd-yyyy")=Format(Previous(Fields!Date.Value),"MM-dd-yyyy"), DateDiff("s",Previous(Fields!Date.Value),Fields!Date.Value) / 60, ( DateDiff("s",Format(CDate(Fields!Date.Value),"MM-dd-yyyy 08:00:00"), Format(CDate(Fields!Date.Value),"MM-dd-yyyy HH:mm:ss")) + (DateDiff("s",Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy HH:mm:ss"), Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy 18:00:00")) ) )/60))) MOD 60 ),".","") & ":" & IIF(RowNumber(Nothing)=1,0, IIF(Format(CDate(Fields!Date.Value),"MM-dd-yyyy")=Format(Previous(Fields!Date.Value),"MM-dd-yyyy"), DateDiff("s",Previous(Fields!Date.Value),Fields!Date.Value) , ( DateDiff("s",Format(CDate(Fields!Date.Value),"MM-dd-yyyy 08:00:00"), Format(CDate(Fields!Date.Value),"MM-dd-yyyy HH:mm:ss")) + (DateDiff("s",Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy HH:mm:ss"), Format(CDate(Previous(Fields!Date.Value)),"MM-dd-yyyy 18:00:00")) ) ))) MOD 60),"0:0:0"))) Thanks Emalai
September 25th, 2012 8:11am

Having so many conditions will have adverse effect on report processing time. Cant you do it in the ETL or at the Query Level ?
Free Windows Admin Tool Kit Click here and download it now
September 25th, 2012 8:30am

Please Give me Related Code
September 26th, 2012 1:47am

Can you please provide a description of the business rules/requirements for the difference column? Then we can help you figure out a better way to do this.
Free Windows Admin Tool Kit Click here and download it now
September 26th, 2012 2:18am

Dear Sir, 1.Business rules all Sundays Holiday 2.Working Hours 8AM to 18PM 3.need total time between two dates and exclude sunday
September 26th, 2012 2:44am

Thank you very much
Free Windows Admin Tool Kit Click here and download it now
September 28th, 2012 8:33am

I would recommend putting the time logic for this in a SQL User Defined Function. Pass your date values to this function in a sql query of the report, or in your ETL Here is function that should get your started: http://visakhm.blogspot.com/2010/03/calculating-business-hours.html
September 30th, 2012 11:23am

Dear Sir, any one please help me,I have urgent Thanks Emalai
Free Windows Admin Tool Kit Click here and download it now
October 1st, 2012 1:25am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics