Your first problem is caused by having an INNER JOIN but not including an ON clause e.g.
b ON a.radnik = b.radnik
I've used
b
as the alias for the last sub-query.
You are then going to get an error
Column 'a.vrijemeodjave' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
You have to have both
vrijemeodjave
and
vrijemeprijave
in an aggregate function to get this to work. I ususally use
MAX
. Note, you should not be converting the dates to strings at this point either - leave that to the calling code or UI layer.
I'm guessing that you are trying to calculate hours worked and overtime over a monthly period but your sql is so messy it is difficult to say what you should do next.
Here is a technique for making it easier to read (and therefore easier to find out where things are going wrong)
To start, I set up some test data (in a table variable) like this:
declare @prijava_radnika table (IdPrijava int identity(1,1), radnik int, vrijemeodjave datetime, vrijemeprijave datetime)
insert into @prijava_radnika (radnik, vrijemeodjave, vrijemeprijave) values
(1001, '2018 MAY 03 08:00:00', '2018 MAY 03 17:00:00'),
(1001, '2018 MAY 04 08:00:00', '2018 MAY 04 17:00:00'),
(1002, '2018 MAY 04 09:00:00', '2018 MAY 04 17:00:00'),
(1003, '2018 MAY 04 10:00:00', '2018 MAY 04 17:00:00'),
(1004, '2018 MAY 04 10:00:00', '2018 MAY 04 17:00:00'),
(1004, NULL, '2018 MAY 04 17:00:00'),
(1004, '2018 MAY 04 10:00:00', NULL)
Next I looked at all those
DATEDIFF(SECOND, 01, 3) / 3600)
They're making the code difficult to read, and also making it less efficient. They're also only returning whole hours, my employees want to be credited for working those extra half-hours too :-) So I "abstracted" those calculations out into a sub-query ... or in my case a "Common Table Expression (CTE)" like this:
;with getHours as
(
SELECT IdPrijava, radnik, vrijemeodjave, vrijemeprijave, DATEDIFF(SECOND, vrijemeodjave, vrijemeprijave) / 3600.0 as hrsWorked,
wkday = CASE WHEN DATEPART(WEEKDAY, vrijemeprijave) in (1,7) THEN 0 ELSE 1 END
FROM @prijava_radnika
WHERE vrijemeodjave IS NOT NULL AND vrijemeprijave IS NOT NULL
)
select * from getHours
Some points to note here:
1. I can now refer to
hrsWorked
everywhere instead of
DATEDIFF(SECOND, 01, 3) / 3600)
2. I'm going to get the hrsWorked back decimal because I used 3600
.0
3. I've included a trick -
wkday
that I will use to set the
hrsWorked
to 0 if the date is over a weekend ... see the next query.
4. That extra semi-colon (;) at the start of the CTE is important as I have all my table value initialisation code above this query in my script.
Here are my results so far:
1 1001 2018-05-03 08:00:00.000 2018-05-03 17:00:00.000 9.000000 1
2 1001 2018-05-04 08:00:00.000 2018-05-04 17:00:00.000 9.000000 1
3 1002 2018-05-04 09:00:00.000 2018-05-04 17:00:00.000 8.000000 1
4 1003 2018-05-04 10:00:00.000 2018-05-04 17:00:00.000 7.000000 1
5 1004 2018-05-04 10:00:00.000 2018-05-04 17:00:00.000 7.000000 1
Now I want to be able to calculate normal working hours and overtime, taking into account weekends, so I added another CTE
calcOverTime
;with getHours as
(
SELECT IdPrijava, radnik, vrijemeodjave, vrijemeprijave, DATEDIFF(SECOND, vrijemeodjave, vrijemeprijave) / 3600.0 as hrsWorked,
wkday = CASE WHEN DATEPART(WEEKDAY, vrijemeprijave) in (1,7) THEN 0 ELSE 1 END
FROM @prijava_radnika
WHERE vrijemeodjave IS NOT NULL AND vrijemeprijave IS NOT NULL
)
, calcOverTime as
(
SELECT IdPrijava, radnik, vrijemeodjave, vrijemeprijave, (wkday * hrsWorked) as actualhours,
OverTime = CASE WHEN (wkday * hrsWorked) > 8 THEN hrsWorked - 8 ELSE 0 END,
NormalHours = CASE WHEN (wkday * hrsWorked) > 8 THEN 8 ELSE (wkday * hrsWorked) END
FROM getHours
)
select * from calcOverTime
Points to note:
1. Look at how I'm calculating hrsWorked now ... if the date is a weekend then wkday will be 0 and any number multiplied by 0 becomes 0.
Now my results so far look like this (I've edited some decimal places out to keep the output tidy)
1 1001 2018-05-03 08:00:00 2018-05-03 17:00:00 9.000 1.000 8.000
2 1001 2018-05-04 08:00:00 2018-05-04 17:00:00 9.000 1.000 8.000
3 1002 2018-05-04 09:00:00 2018-05-04 17:00:00 8.000 0.000 8.000
4 1003 2018-05-04 10:00:00 2018-05-04 17:00:00 7.000 0.000 7.000
5 1004 2018-05-04 10:00:00 2018-05-04 17:00:00 7.000 0.000 7.000
Finally I can sum those details by worker over each month with the following query
SELECT radnik, month(vrijemeodjave) as [Month], SUM(NormalHours) as NormalHours, SUM(OverTime) as OverTime
FROM calcOverTime
group by radnik, month(vrijemeodjave)
ORDER BY radnik, month(vrijemeodjave)
I'm not sure why you were trying to use rownum and rownum - 1 BUT that is a way of getting values for "previous" rows. I don't think you need it here because you have the start and end times on the same row. If you do end up needing to look at previous (or next) rows then consider using LAG (or LEAD) if you have a more recent version of SQL
Edit - so the full code is:
;with getHours as ( SELECT IdPrijava, radnik, vrijemeodjave, vrijemeprijave, DATEDIFF(SECOND, vrijemeodjave, vrijemeprijave) / 3600.0 as hrsWorked, wkday = CASE WHEN DATEPART(WEEKDAY, vrijemeprijave) in (1,7) THEN 0 ELSE 1 END -- This is a trick FROM @prijava_radnika WHERE vrijemeodjave IS NOT NULL AND vrijemeprijave IS NOT NULL ) , calcOverTime as ( SELECT IdPrijava, radnik, vrijemeodjave, vrijemeprijave, (wkday * hrsWorked) as actualhours, OverTime = CASE WHEN (wkday * hrsWorked) > 8 THEN hrsWorked - 8 ELSE 0 END, NormalHours = CASE WHEN (wkday * hrsWorked) > 8 THEN 8 ELSE (wkday * hrsWorked) END FROM getHours )
SELECT radnik, month(vrijemeodjave) as [Month], SUM(NormalHours) as NormalHours, SUM(OverTime) as OverTime FROM calcOverTime group by radnik, month(vrijemeodjave) ORDER BY radnik, month(vrijemeodjave)