How to select MAX(TimeStamp) Value from DB?

Question

Hi buddies,

I want to fetch the maximum value of the Employee Hrs saved in the SQL server DB. Suppose I have saved the values in DB as-

___________

EmployeeHrs

----------------

88:24:28

93:39:09

99:59:50

103:20:49

105:20:22

If I use MAX(EmployeeHrs), it shows the result as 99:59:50. It couldnt recognise the values above 99:59:59. What may be the reason and how to get the correct value?

Any help is appreciated.

Thanks a lot.

Cheers,

Vi

Answer 1

You would need to convert it to str in the order by clause. Something like this might work.

declare @table table(str varchar(20))
insert into @table (str) values('88:24:28')
insert into @table (str) values('93:39:09')
insert into @table (str) values('99:59:50')
insert into @table (str) values('103:20:49')
insert into @table (str) values('105:20:22')
select str,convert(int, left(str, charindex(':',str)-1))*60*60 +convert(int, substring(str, charindex(':',str)+1,2))*60+convert(int,right(str,2))
From @table 
order by convert(int, left(str, charindex(':',str)-1))*60*60 +convert(int, substring(str, charindex(':',str)+1,2))*60+convert(int,right(str,2))
desc

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

Hey Hilary,

Thanks for your valuable Reply.  I need to display this in RDLC report using MAX() Aggregate function. Please explain me step wise. I have saved the data in the same format in my SQL server 2005 as a varchar datatype.

Regards,

vi

Answer 3

Hi Vi,

However, you should convert the TimeStamps string as the second values to fetch the maximum value. In this scenario, the Max() aggregate function can't be used. You can write the custom code to get the maximum value of the employee hrs, which shoule be something like this;


Dim MaxValue as Integer
Dim MaxTimeStamp as string   

Function GetSeconds(ByVal OneTimeStamp As String)

        Dim TSeconds as Integer
        Dim TimeStamps As String()   
        TimeStamps = Split(OneTimeStamp, ":")

        TSeconds = CInt(TimeStamps(0)) * 60 * 60 + CInt(TimeStamps(1) * 60) + CInt(TimeStamps(2))
         IF TSeconds >= MaxValue
             MaxValue=TSeconds
             MaxTimeStamp = OneTimeStamp    
          End If      

  End Function

  Function GetMaxTimeStamp() as String

             Return MaxTimeStamp

  End Function

1. On the report layout page, click the Report menu and select Report Properties, it will pop up the Report Properties window, click the Code tab and enter the above code into this code box, click the OK button to close this Report Properties window.

2. Insert a additional column to the right of the EmployeeHrs column, and use this expression in the detail cell, =Code.GetSeconds(Fields!EmployeeHrs.Value) and set this column Visiblity hidden property to true.

3. You can use the expression =Code.GetMaxTimeStamp() to fetch the maximum value of employeehrs in the table footer or header.

Please let me know if you have more question,

thanks,
jerry

Answer 4

Hi Jerry,

Hmm.. Seems to be quite interesting. I have never used Code part of Reports. I will definitely try your suggestion. 

Thanks for support.

Regards,

Vidyadhar

Answer 5

Hi jerry, I have tried your suggestion, but when I put the code in the code tab of Report Properties, it is showing some kind of error on compilation. Actually I have converted you VB code to C# and used. The error is in first line of code.

Answer 6

Hi Vidyadhar,

VB is the only one .net lanaguage supported in the Code tab of Report Properties. please use my code to test, don't convert it to C#.

thanks,
Jerry

Answer 7

Hi Jerry,

I have tried using the VB Code of yours. But when I type Code. in Expression of the respective textBox, the methods which you have implemented in that code i.e., neither GetSeconds(xx) nor GetMaxTimeStamp() gets showed through IDE. I have however typed these forcefully, but when I have run the application, the textBox in which I have put the expression "=Code.GetMaxTimeStamp() " shows "#Error". whats the problem with this?

thanks,

Vidyadhar

Answer 8

Hi,

Did you implement the step 2?  could you tell me all what you did?

thanks,
Jerry

Answer 9

Yes Jerry, I followed all your steps, still its giving '#Error' in the cell where I want to display the actual thing. Is there any alternative for this problem? Y do u think I am getting this error?

Answer 10

Hi Jerry,

For your clarity, I will try to put the table which I am Using in my report. It can be viewed as follows-

--------------------------------------------------------------------------------------

| Employee Name           | Worked Hrs                       |                                           |

--------------------------------------------------------------------------------------

| =Fields!EmpName.Value|=MAX(Fields!EmpProjHrs.Value|=Code.GetSeconds(                 |

|                                |                                        |   Fields!EmpProjHrs.Value)        |

--------------------------------------------------------------------------------------

|            Total Hrs:       |=Code.GetMaxTimeStamp()      |                                         |

--------------------------------------------------------------------------------------

AS you mentioned, I have made the last column as hidden.

I am getting following errors-

1) In the cell where I am calling the function - Code.GetMaxTimeStamp(), I am getting blank.

2) In the cell where I am calling the function - COde.GetSeconds(x), I am getting #Error.

Answer 11

Hi,

The report shoule be something like this;

The Table Header----------|      EmployeeName            |             Worked Hrs           |      additional column, should be set to hidden|

The Table row details------|=Fields!EmpName.Value     |=Fields!EmpProjHrs.Value    |=Code.GetSeconds(Fields!EmpProjHrs.Value)  |

The Table Footer-----------|           Max project hours:  |=Code.GetMaxTimeStamp() |                                                                   |


Note: Tested in SSRS 2005.

thanks,
Jerry

Answer 12

Hey Jerry, Thank you for the reply.

The scenario is something different. As U mentioned above, I cannot remove the MAX from Worked Hrs Column in details cell.

------------------------------------------------------------------
EmployeeName                 Worked Hrs               [Hidden Column]     ------> Header
------------------------------------------------------------------
Jagadeesh                         99:12:26                     
Akash                               98:58:27                                           -------> Details  
Vevek                               85:27:28                #Error
------------------------------------------------------------------
 Total Hrs:                               -                                               -------> Footer
------------------------------------------------------------------

Although the maximum Project Hrs for Jagadeesh and Akash have reached more than 100, its showing below 100 values and another thing is, when I make the hidden column back to visible, I can see the #Error as shown above. And the Total Hrs is still shown as blank which is in the Worked Hrs COlumn, footer row. For Understanding I have put '-'

Answer 13

Hi,

Based on your first post, you just want the maximum value from a list of timestamp. So, i provide a custom code to do that and think that is what you are looking for. Later on, you post several questions which is confused. In order to understand your purpose fully, please you post the dataset structure and what you want the report look like.

Hope the detailed purpose.

thanks,
Jerry

Answer 14

Okay, Let me be more clear this time.

I repeat, I am trying to generate report using Visual studio's Microsoft Report Viewer tool.

Here is how the data is been stored in my database-

-------------------------------------------------------------------------------
EmpNam  Date    ProjectID  LoginTime  LogoutTim WorkedHrs ProjHrs  EmpProjHrs
-------------------------------------------------------------------------------
     .         .             .             .            .             .            .             .
     .         .             .             .            .             .            .             .
Sneha  02/11/09  FL0031  09:36:12A 12:40:20P 03:04:08  86:14:24  41:14:04
Vivek   03/11/09  FL0031  09:45:10A 01:50:20P 04:05:10  90:19:34   25:12:14   
Sneha  04/11/09  FL0031  09:30:05A 02:40:20P 05:10:15  95:29:49  46:24:19
Akash   04/11/09  FL0031  09:40:05A 02:40:10P 05:00:05 100:29:54 14:15:14
     .             .                 .                .               .                    .               .             .
     .             .                 .                .               .                    .               .             .

I need the report look similar as-

ProjectID : FL0031

---------------------------------------
EmployeeName              EmpProjHrs    ----> (Header)
---------------------------------------
Sneha                               120:13:25                    
Vivek                                  50:05:10        ----> (Details) 
Akash                                 85:27:20       
---------------------------------------
 Total Project Hrs:          255:45:55       ----> (Footer)
---------------------------------------

So it continues in the same manner. As you can see the report, I have taken a project wise report for the project FL0031, and if we see the database for the same project, 3 employees have worked on it. If the records goes on updating, finally each employee may contribute more than 100 hrs(Ex: Sneha has worked 120 hrs). and the total hrs of the project may reach more than 100 (Ex: above report, it reaches around 255 hrs).

The problem what I am facing is-
1) The EmpProjHrs(Employee Project Hrs) when reaches above 100, it gives the value less than 100.
2) similarly, the total project hrs also cannot show if hrs are more than 100.

I have used =MAX(EmpProjHrs) to get Maximum of EmpProjHrs and =MAX(ProjHrs) to get maximum of Project Hrs(Which gives total of all the employees indirectly).

Hope now M clear.

 I thank you for your patience.

Regards,
Vidyadhar

Answer 15

Vidyadhar,

Can you tell us how do you got 120:13:25 hrs for Sneha. I'm try to understand how you derive the numbers from the sample data you gave. I don't think 41:14:04 + 46:24:19 will give you 120+hrs. Thanks.

---

Chicagoan ...

Answer 16

Isham,

The database which I have drawn in previous reply indicates that the data is not full. The dotted rows indicates that there is data before and after the filled rows too. So there are few more working hrs for Sneha may be before or after the filled rows. So its not just abt 41:14:04+46:24:19 but it is .... + 41:14:04 + 46:24:19 + ... = 120+ hrs.

Hope I am clear now.

Answer 17

Hi,

what's the datatype of EmpProjHrs in the underlying database? This reason i ask this question is why its values can't reach more than 100.

thanks,
Jerry

Answer 18

Hi Jerry,

The datatype of WorkedHrs,EmpProjHrs and ProjHrs are nVarChar type. I can store the values more than 100 in the database. But I am not able to display them in the reports. As I said, even if the database values are more than 100, the reports shows the MAX as less than 100 since it is a nVarChar. As I understand, we can use MAX() function only for integer or float values only. Since my columns are in varchar type, the MAX() function not working properly.

But I cannot use Integer or float values in DB because I want to display the colon (:) in between the hrs mins and seconds. which is not possible in int or float types.

Thanks
Vidyadhar

Answer 19

Hi Vidyadhar,

Based on your scenario, i try build a similar report, the detailed steps as follows;(I do it in VS 2008, but mostly same with VS 2005)
1. Here is my sample data:
EmpNm  PjID      EmpProJHrs
Sneha    FL0031    56:39:05
Vivek     FL0031    89:25:48
Sneha    FL0031    83:26:45
Akash    FL0031   150:23:54
Destiny  FL0031    23:56:41
Joan      FL0032    59:78:56
Tim       FL0032    12:34:89
Jacky    FL0032   152:489:200
Joan      FL0032     10:23:36

2. Drag a list control from the ToolBox into the Report layout, then in this List, i insert a Textbox and Table which has 2 columns.

3.Enable the List Grouping. If using SQL server 2005,you can click the List and locate the Grouping property in the right-hand Properties Windows and click its "..." button, it will pop up the List "Grouping and Sorting Properties" window, in its general tab, set the group on expression to =Fields!PjID.value.

4. In the Textbox, enter this expression: =Fieds!PjID.value. And drag the =Fields!EmpNm and =Fields!EmpHrs into the Table's detail cells and enter "Total Project Hrs:" in the Table footer's first cell.

5. Add a group for the Table, and its group-on expression is =Fields!EmpNm.value. In this Group Header's first cell, enter the expression =Fields!EmpNm.value, in this Group Header's second cell, enter the expression: =SUM(Cint(Split(Fields!EmpProjHrs.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!EmpProjHrs.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!EmpProjHrs.Value,":").GetValue(2))).ToString  to get the sum of EmpProjHrs in the sub group.  Delete this Group Footer and Set the Table detail cell row Hidden to True.(means to hide the table detail row and delete its footer) This will only show the Group header rows.

6. In the Table Footer's second cell("Total Project Hrs:" in the first cell), enter the expression: =SUM(Cint(Split(Fields!EmpProjHrs.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!EmpProjHrs.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!EmpProjHrs.Value,":").GetValue(2))).ToString to get the sum of EmpProjHrs in the sub Table splitted by List (Note: even thought this expression is the same with that in the group header's second cell, they do different calculations because they are in a different Scope. the former is in the Group scope and the later is in the Table scope).

After these steps, the report will looks like this(this graph is from my test result):



So far, i did not see any where need to calculate the max vlaue of the employee project hours.

Please use the function Split in your calculations for this secinaro.

Thanks,
Jerry

Answer 20

Hey this is a great stuff Jerry.

I thank you for your detailed description.

I will take example of Sneha here. According to your sample data and report, you have calculated the SUM of Sneha's Working hr. But what I want is the maximum hrs that she has spent on particular project.

For example, if we consider above scenario, the EmpProjHrs value for Sneha should be 83:26:45 rather than 139:65:50 . That is because, the maximum value from your sample data for Sneha will be  83:26:45 (Among 83:26:45 and 56:39:05). That was the reason why I was using MAX() function. but it is not working if we consider that Sneha works for more than 100+ hrs i.e. in your sample data, If sneha's values are - 56:39:05 , 83:26:45 and 101:23:24 then MAX(Fields!EmpProjHrs.Value) for sneha will display 83:26:45 rather than displaying 101:23:24). This is my problem.

One more thing, the mins and seconds cannot be more than '59'. If mins/Secs reach more than 59, then respectice hrs/Mins should be increased by 1.

Thank you very much
Vi

Answer 21

Hi,

From your last clarification, there is one workaround to do it, but which need to rebuild the Dataset query string. I create this report step by step as follows.
1. Here is my data sample;

 EmpNam     ProjectID      EmpProjHrs

  Sneha            FL0031            56:39:05
   Vivek            FL0031            89:25:48
  Sneha            FL0031            83:26:45
   Akash           FL0031           150:23:54
   Destiny         FL0031             23:56:41
    Joan            FL0032             59:78:56
    Tim              FL0032             12:34:42
   Jacky            FL0032            152:48:20
    Joan             FL0032              10:23:36
  Destiny          FL0031              50:53:32

2. The dataset SQL string like this;

WITH CTE1(EmpNam,EmpProjHrsSeconds) 
AS(
SELECT EmpNam,MAX(CAST(LEFT(EmpProjHrs,CHARINDEX(':',EmpProjHrs)-1) AS INT)*60*60 +
                                    CAST(SUBSTRING(EmpProjHrs,CHARINDEX(':',EmpProjHrs)+1,2) AS INT)*60 + 
                                    CAST(right(EmpProjHrs,2) AS INT)
                                    )
FROM Tbl_EmpProjHrs
GROUP BY EmpNam
     ),
       CTE2(EmpNam,ProjectID,EmpProjHrs)
AS(
      SELECT T1.EmpNam,T1.ProjectID,T1.EmpProjHrs 
      FROM Tbl_EmpProjHrs T1 INNER JOIN CTE1 T2 
                                             ON     CAST(LEFT(T1.EmpProjHrs,CHARINDEX(':',T1.EmpProjHrs)-1) AS INT)*60*60 +
                                                       CAST(SUBSTRING(T1.EmpProjHrs,CHARINDEX(':',T1.EmpProjHrs)+1,2) AS INT)*60 + 
                                                       CAST(RIGHT(T1.EmpProjHrs,2) AS INT) = T2.EmpProjHrsSeconds 
                                                        AND 
                                                        T1.EmpNam = T2.EmpNam
      )
SELECT * FROM CTE2

3. On the Report layout,drag a List control from the toolbox and set its Grouping expression =Fields!ProjectID.value.

4. In this List control, insert a Textbox to put the projectid =Fields!ProjectID.value and insert a Table to put the =Fields!EmpNam.value and =Fields!EmpProjHrs.value.

5. In the Table Footer's second cell, enter this expression  =(SUM(Cint(Split(Fields!EmpProjHrs.Value,":").GetValue(0))) + (SUM(Cint(Split(Fields!EmpProjHrs.Value,":").GetValue(1))) + Sum(Cint(split(Fields!EmpProjHrs.Value,":").GetValue(2)))\60)\60 ).ToString + ":" + ((SUM(Cint(Split(Fields!EmpProjHrs.Value,":").GetValue(1))) + Sum(Cint(split(Fields!EmpProjHrs.Value,":").GetValue(2)))\60) Mod 60).ToString + ":" + (Sum(Cint(split(Fields!EmpProjHrs.Value,":").GetValue(2))) Mod 60).ToString  to get the total of all the "max" employee project hours per employee.

6. The report grahp will look like this;


Hope this help,

thanks,
Jerry

Answer 22

Hi Jerry,

thank you for your tremendous patience.

Can U explain me in detail about the SQL query U have placed above and where exactly have U done this? Have U done this in dataset designer?

Answer 23

Hi,

Yes, the entire SQL string in my post need to be putted into the dataset designer.

In my sample SQL code, i use CTE(Common Table Expression, see this article http://msdn.microsoft.com/en-us/library/ms190766.aspx for details on how to use it) to be self-referencing and referenced multiple times in the same query, for example, CTE1 and CTE2.  In them, the CTE1 is the tempory table containing the max value of empProjHrsSeconds per emplyee name, and in the CTE2 i use the CTE1 inner join the initial table Tbl_EmpProjHrs to get the other info of emplyees with max empProjHrs values, by now, i can get the all infos of emplyee with max project hours, which are shown on the report.

On more thing, about the expression CAST(LEFT(T1.EmpProjHrs,CHARINDEX(':',T1.EmpProjHrs)-1) AS INT)*60*60 +CAST(SUBSTRING(T1.EmpProjHrs,CHARINDEX(':',T1.EmpProjHrs)+1,2) AS INT)*60 + CAST(RIGHT(T1.EmpProjHrs,2) AS INT), please check the SQL built -in function, Chartindex,Cast,Substring and Right syntaxs out in MSDN book.

Please let me know if you have more question.

thanks,
Jerry