MS SQL How to SUM DATIDIFF result for 4 ID user?

Question

MS SQL How to SUM DATIDIFF result for 4 ID user?

Answer 1

Hi @Сергей Ходор
Try this query:

SELECT P.ID,P.name,P.SurName  
      ,DATEDIFF(day,date_of_employment,GETDATE())AS Employment  
      ,Business_trip  
	  ,Sick  
	  ,Vacation  
FROM Personal_Card P   
LEFT JOIN (SELECT UserID,SUM(DATEDIFF(day,First_day_business_trip,Last_day_business_trip))AS Business_trip FROM Business_trip GROUP BY UserID) B   
       ON P.ID=B.UserID  
LEFT JOIN (SELECT UserID,SUM(DATEDIFF(day,First_day_Sick,Last_day_Sick))AS Sick FROM Sick_leave GROUP BY UserID) S   
	   ON P.ID=S.UserID  
LEFT JOIN (SELECT UserID,SUM(DATEDIFF(day,First_day_Vacation,Last_day_Vacation))AS Vacation FROM Vacation GROUP BY UserID) V   
	   ON P.ID=V.UserID  

Note:

1. Please post your code by clicking this button: . Since we cannot copy your query code from an image.
2. As Erland suggested, for this type of question, you help yourself a lot if you post CREATE TABLE statements for your tables, preferably simplified to focus on the actual problem. Together with INSERT statements with sample data and then the desired result given the sample. So that we’ll get a right direction and make some test. Even more so, if you also give a brief description of why you want that particular result.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

How GROUP by result for "Дни болезни" column?

Answer 3

For this type of question it is a very good idea to post the CREATE TABLE statement for your table(s) and INSERT data with sample data, and the expected result given the sample together with an explanation why you want that result.

This makes it easy to copy and paste into a query window to develop a tested solution. But we cannot copy from images.

Now as it stands, we can only resort to guesses. And my guess is that you should remove all these datediff from the GROUP BY clause, and instead wrap all datediff calls in SUM:

   SUM(DATEDIFF(day, first_business_day, last_business_day)) AS Komandirovka.  

But I may be completely off.