- 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 t1.PosTag, t1.KlTOt, t1.NameKipID, t1.TypMarkaName, t1.Lim, t1.EdIzm,
t1.OD_NUMMNF, t1.PeriodP, t1.PeriodK,
D3, D5,
case
--поверка
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.DataOchP)) =1 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd3_1p)) =1 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd3_2p)) =1 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd3_3p)) =1 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd3_4p)) =1 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd3_5p)) =1 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd6_1p)) =1 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd6_2p)) =1 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.ddp)) =1 then 'Пм'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.DataOchP)) =1 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd3_1p)) =1 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd3_2p)) =1 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd3_3p)) =1 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd3_4p)) =1 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd3_5p)) =1 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd6_1p)) =1 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd6_2p)) =1 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.ddp)) =1 then 'П'
--калибровка
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.DataOchK)) =1 then 'Км'
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.dd3_1k)) =1 then 'Км'
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.dd3_2k)) =1 then 'Км'
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.dd3_3k)) =1 then 'Км'
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.dd3_4k)) =1 then 'Км'
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.dd3_5k)) =1 then 'Км'
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.dd6_1k)) =1 then 'Км'
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.dd6_2k)) =1 then 'Км'
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.ddk)) =1 then 'Км'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.DataOchK)) =1 then 'К'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.dd3_1k)) =1 then 'К'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.dd3_2k)) =1 then 'К'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.dd3_3k)) =1 then 'К'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.dd3_4k)) =1 then 'К'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.dd3_5k)) =1 then 'К'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.dd6_1k)) =1 then 'К'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.dd6_2k)) =1 then 'К'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.ddk)) =1 then 'К' end a1,
case
--поверка
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.DataOchP)) =2 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd3_1p)) =2 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd3_2p)) =2 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd3_3p)) =2 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd3_4p)) =2 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd3_5p)) =2 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd6_1p)) =2 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd6_2p)) =2 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.ddp)) =2 then 'Пм'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.DataOchP)) =2 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd3_1p)) =2 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd3_2p)) =2 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd3_3p)) =2 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd3_4p)) =2 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd3_5p)) =2 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd6_1p)) =2 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd6_2p)) =2 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.ddp)) =2 then 'П'
--калибровка
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.DataOchK)) =2 then 'Км'
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.dd3_1k)) =2 then 'Км'
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.dd3_2k)) =2 then 'Км'
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.dd3_3k)) =2 then 'Км'
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.dd3_4k)) =2 then 'Км'
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.dd3_5k)) =2 then 'Км'
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.dd6_1k)) =2 then 'Км'
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.dd6_2k)) =2 then 'Км'
when Convert(varchar,t1.ArealK) = '1' and Convert(varchar,MONTH(t1.ddk)) =2 then 'Км'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.DataOchK)) =2 then 'К'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.dd3_1k)) =2 then 'К'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.dd3_2k)) =2 then 'К'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.dd3_3k)) =2 then 'К'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.dd3_4k)) =2 then 'К'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.dd3_5k)) =2 then 'К'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.dd6_1k)) =2 then 'К'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.dd6_2k)) =2 then 'К'
when Convert(varchar,t1.ArealK) <> '1' and Convert(varchar,MONTH(t1.ddk)) =2 then 'К' end a2,
case
--поверка
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.DataOchP)) =3 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd3_1p)) =3 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd3_2p)) =3 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd3_3p)) =3 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd3_4p)) =3 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd3_5p)) =3 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd6_1p)) =3 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.dd6_2p)) =3 then 'Пм'
when Convert(varchar,t1.ArealP) = '1' and Convert(varchar,MONTH(t1.ddp)) =3 then 'Пм'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.DataOchP)) =3 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd3_1p)) =3 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd3_2p)) =3 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd3_3p)) =3 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd3_4p)) =3 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd3_5p)) =3 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd6_1p)) =3 then 'П'
when Convert(varchar,t1.ArealP) <> '1' and Convert(varchar,MONTH(t1.dd6_2p)) =3 then 'П'
Взято со знаменитого топика на sql.ru: http://sql.ru/forum/actualthread.aspx?tid=591607
Писано ручками некоего безумца. Тому, кто занял его место впоследствии, было дано задание оптимизировать это под угрозой увольнения.
P.S. Функционал сайта, как выяснилось, не держит больше 100 строк говнокода, поэтому с полным текстом лучше ознакомиться по данной выше ссылке.