Montly report SQL from existing tables by worker

Question

0.00/5 (No votes)

See more:

Need to repair this code to monthly report
Radnik is worker
Vrijemeprijave is datetime come to work,
Vrijemeodjave is work end

What I have tried:

SELECT  [radnik] as Radnik,SUM(cast((CASE WHEN DATEPART(WEEKDAY, vrijemeprijave) in (1, 7) then 0 else (CASE WHEN(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) > 8 THEN 8 ELSE(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) END) end)as float))   AS radni_sati," 
                     + "SUM(cast((CASE WHEN DATEPART(WEEKDAY, vrijemeodjave) in (1, 7) then(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) else(case when(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) > 8  then((DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) - 8) ELSE NULL END) end)as float))   AS 'Prekovremeni'"
                     + "from[prijava_radnika]"
                     + "group by[radnik]";

Posted 3-May-18 0:38am

Goran Bibic

Updated 3-May-18 23:32pm

Add a Solution

Comments

Jörgen Andersson 3-May-18 6:57am

And what's wrong?

CHill60 3-May-18 7:32am

Give us the table schemas, some sample data and some expected results, or at least explain what the results are supposed to show. It's incredibly difficult to unravel those case statements (which I suspect are not required)

CHill60 3-May-18 7:53am

Some other points:
1) why use DATEDIFF(SECOND, Vrijemeprijave, Vrijemeodjave) / 3600 instead of DATEDIFF(MINUTE, Vrijemeprijave, Vrijemeodjave) ?
2) why cast the SUM as float - it seems pointless
3) why remove 8 minutes from the amount of work done for Prekovremeni?
4) do you relly mean NULL in that part instead of 0? Be consistent

Goran Bibic 3-May-18 16:18pm

Post down my code...create some repaired code for example...i need help...thank you
1) That is ok for me...some recomantidiot?
2)sum...what to use?
3) not 8 minutes, 8 hours...work 13 hours...8 is normal, 13-8= 5 it is overtime
4)Yes...null...i not need 0...Or I can use 0 becose of sum hours

I need help for best solution

CHill60 4-May-18 4:04am

1 - It seems overkill, especially as the calculation will only result in whole hours anyway. If you want to record the partial hours you will need to use / 3600.0
2 - Don't cast at all
3 - My apologies, I misread the code.
4 - As you are feeding the value into a SUM and not a COUNT then you would be better to use 0 instead of NULL.
Now that I know what the actual problem is I will go and have a look at that

Goran Bibic 3-May-18 16:01pm

Like this...
error is Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'group'.

SELECT a.radnik, isnull(convert(varchar(20), a.vrijemeodjave, 113), '') as vrijemeodjave, convert(varchar(20), b.vrijemeprijave, 113) as vrijemeprijave,
--CASE WHEN DATEPART(WEEKDAY, b.vrijemeprijave) in (1,7) then 0 else (CASE WHEN (DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8 THEN 8 ELSE(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) END) end AS radni_sati,
--CASE WHEN DATEPART(WEEKDAY, a.vrijemeodjave) in (1,7) then (DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) else(case when(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8 then ((DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600)-8) ELSE NULL END) end AS prekovremeni
SUM(cast((CASE WHEN DATEPART(WEEKDAY, 01) in (1, 7) then 0 else (CASE WHEN(DATEDIFF(SECOND, 01, 3) / 3600) > 8 THEN 8 ELSE(DATEDIFF(SECOND, 01, 3) / 3600) END) end)as float)) AS ukupno_radni_sati,
SUM(cast((CASE WHEN DATEPART(WEEKDAY, 3) in (1, 7) then(DATEDIFF(SECOND, 01, 3) / 3600) else(case when(DATEDIFF(SECOND, 01, 3) / 3600) > 8 then((DATEDIFF(SECOND, 01, 3) / 3600) - 8) ELSE NULL END) end)as float)) AS 'Ukupno rekovremeni'
FROM(SELECT radnik, vrijemeodjave, ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) as RowNum
FROM dbo.prijava_radnika) a INNER JOIN(SELECT radnik, vrijemeprijave, (ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) - 1) as RowNumMinusOne FROM dbo.prijava_radnika)
group by a.radnik

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2018-05-03T23:33:00

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:

SQL

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:

SQL

;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
 )
 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

SQL

;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 * 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

SQL

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)