- 001
- 002
- 003
- 004
- 005
- 006
- 007
- 008
- 009
- 010
- 011
- 012
- 013
- 014
- 015
- 016
- 017
- 018
- 019
- 020
- 021
- 022
- 023
- 024
- 025
- 026
- 027
- 028
- 029
- 030
- 031
- 032
- 033
- 034
- 035
- 036
- 037
- 038
- 039
- 040
- 041
- 042
- 043
- 044
- 045
- 046
- 047
- 048
- 049
- 050
- 051
- 052
- 053
- 054
- 055
- 056
- 057
- 058
- 059
- 060
- 061
- 062
- 063
- 064
- 065
- 066
- 067
- 068
- 069
- 070
- 071
- 072
- 073
- 074
- 075
- 076
- 077
- 078
- 079
- 080
- 081
- 082
- 083
- 084
- 085
- 086
- 087
- 088
- 089
- 090
- 091
- 092
- 093
- 094
- 095
- 096
- 097
- 098
- 099
- 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,
guest 14.07.2009 17:15 # +8
жаль запрос зтот весь не уместился.......
когда мне предстоит внести изменения в зтот запрос, то пол дня может уйти на разборку..
KiTE 12.01.2010 06:24 # 0
Топик показывает отличный пример говноверстки.
guest 14.07.2009 18:47 # +4
от такой маленький запросик.. продолжение....
*365)+[timelossafterbefore]+[timelossafter],[workfrom]) AS Usedto,
(SELECT SUM (StateHoliDay)
FROM StateHoliChas
WHERE StateHoliChas.WORKER = WORKER_LIST.ID
) AS StateHoliDayTable,
IIF
(
StateHoliDayTable IS NULL,0,StateHoliDayTable
) AS StateHoliDay
FROM Worker_list
LEFT JOIN VacationList
ON [Worker_list].[Id]=[VacationList].[Worker]
GROUP BY [Worker_list].[Id] ,
[Worker_list].[Name_worker] ,
[Worker_list].[Name] ,
[Worker_list].[Firstname] ,
[Worker_list].[Middlenamme] ,
[Worker_list].[Табельний номер],
[Worker_list].[workfrom] ,
[Worker_list].[startstockdate] ,
[Worker_list].[startaccrualday],
[Worker_list].[startusedday] ,
[Worker_list].[VacationDay] ,
[Worker_list.Post_adress] ,
[Worker_list.Ident_fiscal];
guest 18.07.2009 11:34 # 0
.... жесть.... я однажды что-то подобное сначала на M$$QL, потом посмотрел на это дело и нахер переписал с нуля используя временные таблицы. Но это жесть, господа =)
guest 21.07.2009 12:48 # 0
это писал человек?
guest 06.08.2009 18:10 # +1
оторвать!!! )))
Holden 11.08.2009 10:15 # 0
guest 14.08.2009 12:37 # 0
guest 31.08.2009 09:28 # 0
MINDFUCK!