1. 1
2. 2
3. 3
4. 4
5. 5
6. 6
7. 7
8. 8
9. 9
10. 10
11. 11
12. 12
13. 13
14. 14
15. 15
16. 16
17. 17
18. 18
19. 19
20. 20
21. 21
22. 22
23. 23
24. 24
25. 25
26. 26
27. 27
28. 28
29. 29
30. 30
31. 31
32. 32
33. 33
34. 34
35. 35
36. 36
37. 37
38. 38
39. 39
40. 40
41. 41
42. 42
43. 43
44. 44
45. 45
46. 46
47. 47
48. 48
49. 49
50. 50
51. 51
52. 52
53. 53
54. 54
55. 55
56. 56
57. 57
58. 58
59. 59
60. 60
61. 61
62. 62
63. 63
64. 64
65. 65
66. 66
67. 67
68. 68
69. 69
70. 70
71. 71
72. 72
73. 73
74. 74
75. 75
76. 76
77. 77
78. 78
79. 79
80. 80
81. 81
82. 82
83. 83
84. 84
85. 85
86. 86
87. 87
88. 88
89. 89
90. 90
91. 91
92. 92
93. 93
94. 94
95. 95
96. 96
97. 97
98. 98
99. 99
100. 100

SELECT   [Worker_list].[Id]                       ,
         [Worker_list.Post_adress]                ,
         [Worker_list.Ident_fiscal]               ,
         [Worker_list.VacationDay]                ,
         [Worker_list].[Name_worker]              ,
         [Worker_list].[Name]                     ,
         [Worker_list].[Firstname]                ,
         [Worker_list].[Middlenamme]              ,
         [Worker_list].[Табельний номер]          ,
         [Worker_list].[workfrom] AS workfromtable,
         (SELECT MIN([time_list].[begin_date])
         FROM    [time_list]
         WHERE   [time_list].[worker]=[worker_list].[id]
         ) AS accountfrom,
         IIF
         (
                  ISNULL([workfromtable]),[accountfrom],[workfromtable]
         )                              AS workfrom           ,
         [Worker_list].[startstockdate] AS startstockdatetable,
         IIF
         (
                  ISNULL([startstockdatetable]),[accountfrom],[startstockdatetable]
         )                               AS startstockdate      ,
         [Worker_list].[startaccrualday] AS startaccrualdaytable,
         [Worker_list].[startusedday]    AS startuseddaytable   ,
         IIF
         (
                  ISNULL([startaccrualdaytable]),0,[startaccrualdaytable]
         ) AS Startaccrualday,
         IIF
         (
                  ISNULL([startuseddaytable]),0,[startuseddaytable]
         ) AS startusedday,
         (SELECT MIN([calendar_all].[Дата])
         FROM    calendar_all
         )                                                                       AS Todayis          ,
         SUM(DATEDIFF("d",[Chas]![Date Begin],DATEADD("d",1,[Chas]![Date end]))) AS UsedDayAfterTable,
         IIF
         (
                  ISNULL([useddayaftertable]),0,[useddayaftertable]
         ) AS UsedDayAfter,
         (SELECT SUM (DATEDIFF("d",Chas.[Date Begin], DATEADD("d",1,Chas.[Date End])))
         FROM    Worker_list AS Worker_list_1
                 INNER JOIN (Reason_list
                         INNER JOIN Chas
                         ON      Reason_list.Id = Chas.Reason)
                 ON      Worker_list_1.Id       = Chas.Worker
         WHERE   Reason_list.List               =0
             AND Worker_list.Id                 =Worker_List_1.Id
             AND chas.[date end]               <=
                 (SELECT MIN([calendar_all].[Дата])
                 FROM    calendar_all
                 )
         ) AS timelossafterbeforetable,
         (SELECT SUM (DATEDIFF("d",Chas.[Date Begin], DATEADD("d",1,
                 (SELECT MIN([calendar_all].[Дата])
                 FROM    calendar_all
                 ))))
         FROM    Worker_list AS Worker_list_1
                 INNER JOIN (Reason_list
                         INNER JOIN Chas
                         ON      Reason_list.Id = Chas.Reason)
                 ON      Worker_list_1.Id       = Chas.Worker
         WHERE   Reason_list.List               =0
             AND Worker_list.Id                 =Worker_List_1.Id
             AND chas.[date begin]              <
                 (SELECT MIN([calendar_all].[Дата])
                 FROM    calendar_all
                 )
             AND chas.[date end] >
                 (SELECT MIN([calendar_all].[Дата])
                 FROM    calendar_all
                 )
         ) AS timelossaftertable,
         IIF
         (
                  ISNULL([timelossafterbeforetable]),0,[timelossafterbeforetable]
         ) AS timelossafterbefore,
         IIF
         (
                  ISNULL([timelossaftertable]),0,[timelossaftertable]
         )                                                                              AS timelossafter,
         DATEDIFF("d",[startstockdate],[todayis])-[timelossafterbefore]-[timelossafter] AS workoffafter ,
         INT([workoffafter]                      /365*
         IIF
         (
                  ISNULL([Worker_list].[VacationDay]),GetVacationDayCount(),[Worker_list].[VacationDay]
         )
         )                                                   AS accrualdayafter,
         DATEDIFF("d",[workfrom],[todayis])                  AS workoffall     ,
         [Startaccrualday]        -[startusedday]+[accrualdayafter] AS accrualdayall  ,
         [accrualdayafter]        +[startaccrualday]                AS accrualday     ,
         [startusedday]           +[useddayafter]-StateHoliDay      AS usedday        ,
         [accrualday]             -[usedday]                        AS unusedday      ,
         DATEADD("d",INT([usedday]/
         IIF
         (
                  ISNULL([Worker_list].[VacationDay]),GetVacationDayCount(),[Worker_list].[VacationDay]
         )
         *365)+[timelossafterbefore]+[timelossafter],[workfrom]) AS Usedto,