Hi,

  I have 2 table (Employee T0 & Employee Absense T1), T0 & T1 use Employee Code to connect.

In T0, I have EmployeeCode, Location Code, Shift Code & Hour Rate....,

T1 has EmployeeCode, Date, Quantity ..., Does anybody can show me how to write a query to get summary of T1.[Quantity] * T0.[Hour Rate] as AbsHR group by T0.[Location], T0.[Shift] ?

so the result has [Location], [Shift], [Date], [AbsHR] 4 column.

What have you done so far?

You'd have to group by Date as well unless you plan on getting max/min date?

• Proposed as answer by Naomi NModerator 4 hours 24 minutes ago

Hi JamesLiang,

Based on my understanding, if it is a one-to-many relationship between t0 and t1, you can refer to below sample.

DECLARE @T0 TABLE(EmployeeCode INT, [Location Code] VARCHAR(99), [Shift Code] VARCHAR(99), [Hour Rate] VARCHAR(99))
DECLARE @T1 TABLE(EmployeeCode INT, [Date] DATE, Quantity INT)

SELECT [Location Code] AS Location, 
	   [Shift Code] AS [Shift], 
	   [Date],
	   T1.[Quantity]*T0.[Hour Rate] AS AbsHR 
FROM @T0 T0 JOIN @T1 T1 ON T0.EmployeeCode=T1.EmployeeCode

--If there's same dates for employeeCode in T1
SELECT [Location Code] AS Location, 
	   [Shift Code] AS [Shift], 
	   [Date],
	   SUM(T1.[Quantity]*T0.[Hour Rate]) AS AbsHR 
FROM @T0 T0 JOIN @T1 T1 ON T0.EmployeeCode=T1.EmployeeCode
GROUP BY [Location Code],[Shift Code],[Date]

If you have any question, feel free to let me