Friends! I have a query and the result.when i select then i get the result. But i want to see the that result in the one row.

Now the query result is like that

Agreement_no Total_Time   Officer_name         Sp/Tt/by_Of    Sp/Tt/for l- pros
999Li74         0:18:19   'Mr.Jason'               0:02:00      0.40 sec
999Li74         0:18:19   'Mr.Jason'               0:02:00      0.25 sec
999Li74         0:18:19   'Mr.Jason'               0:02:00      0.40 sec
999Li74         0:18:19   'Mr.Jason'               0:02:00      0.15 sec
999Li74         0:18:19  'Mr.Firdovsi'             0:16:19      0.42 sec
999Li74         0:18:19  'Mr.Firdovsi'             0:16:19      0.20 sec
999Li74         0:18:19  'Mr.Firdovsi'             0:16:19      1.2 sec
999Li74         0:18:19  'Mr.Firdovsi'             0:16:19      11.1 sec
999Li74         0:18:19  'Mr.Firdovsi'             0:16:19      3.1 sec
999Li74         0:18:19  'Mr.Firdovsi'             0:16:19      0.13 sec

I query this result from 12 JOINED table.

First column is loan approved loan agreement_no. Second Total_Time column is result of the start_time and end_time using [DATEDIFF] and [OVER PARTITION BY] agreement_no. Third column is officers name who gave loan and approve loan.

4th column(Sp/Tt/by_Of - spending total time by Officer ) is the result of the start_time and end_time using [DATEDIFF] and [OVER PARTITION BY] Officer_Name.

5th column (Sp/Tt/for l-pros - Spending total time for loan prosses) is result of the start_time and end_time using [DATEDIFF].

When i query approved loans by day then i see many result because of the this prosses.

I want the query result shown like below

Agreement_no Total_Time Officer_name Sp/Tt/by_Of Officer_name2 Sp/Tt/by_Of
999Li74      0:18:19    Mr.Jason     0:02:00     Mr.Firdovsi   0:16:19

Note:5th column is not important.

If there are other loan then i want that also shown like below

Agreement_no Total_Time Officer_name Sp/Tt/by_Of Officer_name2 Sp/Tt/by_Of
999Li74      0:18:19    Mr.Jason     0:02:00     Mr.Firdovsi   0:16:19
1000Li01     0:27:30    Mr.Jon       0:12:15     Mr.Felaket    0:15:15

2 Answers

0
PaulvdElst On

I would go about solving this using temp-tables. The first table, #DataIn, should contain the result of your query, like you have shown in your table.

    SELECT [Agreement_no]
      ,[Total_Time]
      ,[Officer_name]
      ,[Sp/Tt/by_Of]
      ,CAST(REPLACE([Sp Tt for], ' sec', '') AS decimal(4,2)) AS [Sp/Tt/for l- pros]
    INTO #test1
    FROM #DataIn;

To get your data in #DataIn you should alter your query using:

    SELECT … INTO #DataIn …

In my query REPLACE is to remove ' sec' from [Sp/Tt/for l- pros] and the CAST is to convert the values to decimal values. Your data (in #Test1) now looks like this:

Agreement_no Total_Time   Officer_name         Sp/Tt/by_Of    Sp/Tt/for l- pros
999Li74         0:18:19   'Mr.Jason'               0:02:00      0.40
999Li74         0:18:19   'Mr.Jason'               0:02:00      0.25
999Li74         0:18:19   'Mr.Jason'               0:02:00      0.40
999Li74         0:18:19   'Mr.Jason'               0:02:00      0.15
999Li74         0:18:19  'Mr.Firdovsi'             0:16:19      0.42
999Li74         0:18:19  'Mr.Firdovsi'             0:16:19      0.20
999Li74         0:18:19  'Mr.Firdovsi'             0:16:19      1.2
999Li74         0:18:19  'Mr.Firdovsi'              0:16:19      11.1
999Li74         0:18:19  'Mr.Firdovsi'             0:16:19      3.1
999Li74         0:18:19  'Mr.Firdovsi'             0:16:19      0.13

Now you can SUM column [Sp/Tt/for l- pros] per Officer_name:

SELECT [Agreement_no]
      ,[Total_Time]
      ,[Officer_name]
      ,[Sp/Tt/by_Of]
      ,CONVERT(varchar, DATEADD(ms, SUM ([Sp/Tt/for l- pros]) * 1000, 0), 114) AS [Sp/Tt/for l- pros]
FROM #test1
GROUP BY [Officer_name], [Agreement_no], [Total_Time], [Sp/Tt/by_Of];

DATEADD in this query is used to display the resulting value as time. To do that, I'll calculate the time in miliseconds (multiplying the values in seconds by 1000). The result of this query:

Agreement_no        Total_Time  Officer_name    Sp/Tt/by_Of Sp/Tt/for l- pros
999Li74             0:18:19     'Mr.Firdovsi'    0:16:19        00:00:16:150
999Li74             0:18:19     'Mr.Jason'       0:02:00        00:00:01:200

Regards, Paul

-1
Community On

Paul. But i just want to see the result like below.

Agreement_no Total_Time Officer_name Sp/Tt/by_Of Officer_name2 Sp/Tt/by_Of

999Li74 0:18:19 Mr.Jason 0:02:00 Mr.Firdovsi 0:16:19