0
SELECT DISTINCT 
   F_Emp_code,CONVERT(varchar, t.F_Log_dtPunched, 103) AS F_Tbl_dtpunched,

  Loc.F_Loc_code,COUNT(TF.F_Ter_LocCode)'Worked'
  FROM 
   (select *, row_number() over (partition by F_Emp_code order by F_Emp_code) as rn from T_Mst_Employee )  A 
   LEFT JOIN T_Mst_Company co ON co.F_Com_code = A.F_Emp_Company_ID     
   RIGHT JOIN T_Tra_Loginfo t ON t.F_Log_Emp_Code = F_Emp_code  
   JOIN   dbo.T_Mst_terminalinfo TF ON TF.F_Ter_nTerminalID=t.F_Log_TerminalID
   LEFT JOIN T_Mst_Location Loc ON Loc.F_Loc_code = TF.F_Ter_LocCode
   WHERE A.F_Emp_Status = 'Active'   
   AND F_Emp_code='100229'
   and  t.F_Log_dtPunched between '2017-01-01' and '2017-01-08' 
   group by co.F_Com_Logo, A.f_emp_firstname,A.F_Emp_MiddleName,
   A.F_Emp_LastName,F_Emp_code,
   F_Log_dtPunched,co.F_Com_Desc,Loc.F_Loc_code,Loc.F_Loc_Desc,TF.F_Ter_nTerminalID



My result is like this :
empid     date      loccode  worktime
100229  01/01/2017  05138   1
100229  02/01/2017  05138   1
100229  03/01/2017  05138   1
100229  05/01/2017  09409   1
100229  06/01/2017  05138   1
100229  07/01/2017  01305   1
100229  07/01/2017  05138   1

if any employee worked same date with differnt location then i want to show result in worktime= 1/count of different location

example if employee worked 3 location in same day ,,so i need to calculate worktime 1/3 in working time column

user3262364
  • 369
  • 3
  • 9
  • 23
  • please provide sample data and DDL in consumable format, https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Mazhar Jan 10 '18 at 11:04

1 Answers1

0

You are looking for COUNT(DISTINCT column) OVER (...):

worktime * 1.0 / count(distinct loccode) over (partition by emp_id, date)

The multiplication with 1.0 is necessary in order to avoid integer division.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Use of DISTINCT is not allowed with the OVER clause. – user3262364 Jan 10 '18 at 11:19
  • i given your code code after this line : COUNT(TF.F_Ter_LocCode)'Worked' – user3262364 Jan 10 '18 at 11:20
  • @user3262364: `COUNT(TF.F_Ter_LocCode)` is different from `COUNT (DISTINCT TF.F_Ter_LocCode)`, so this is not a solution. I didn't know SQL Server lacks `COUNT DISTINCT OVER`. Annoying. Here is an SO entry where this problem is solved with a trick: https://stackoverflow.com/questions/11202878/partition-function-count-over-possible-using-distinct – Thorsten Kettner Jan 10 '18 at 11:29