1. SQL / Говнокод #27976

    0

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    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
    ttt as (
                        select o.tovarid, o.edizmerid, o.cena, 
                               o.vos, o.cena*o.vos as sum_vos,  
                               coalesce(p.sum_kl,0.0)  as _prih_kl, coalesce( p.sum_prih,0.0) as  sum_prih, 
                               coalesce( p.sum_prih_docs,0.0) as  sum_prih_docs,
                               coalesce(r.sum_kl,0.0) as _rash_kl, coalesce(r.sum_rash,0.0)  as  sum_rash ,
                               coalesce(r.sum_rash_docs,0.0)  as  sum_rash_docs ,
                               coalesce(rd.sum_kl,0.0) as _rash_doc_kl, coalesce(rd.sum_rash_doc,0.0) as sum_rash_doc, 
                               coalesce(rd.sum_rash_doc_docs, 0.0) as sum_rash_doc_docs ,
                     --          coalesce(rs.sum_kl,0.0) as _rash_storno_kl, coalesce(rs.sum_rash,0.0) as sum_rash_storno ,
                               o.ostid, o.ss, o.sb 
                        from ost_ o
                        left join prih p on (o.tovarid = p.tovarid 
                                         and o.edizmerid=p.edizmerid
                                         and o.ss = p.ss
                                         and o.sb = p.sb)   
                        left join rash r on (o.tovarid =r.tovarid 
                                         and o.edizmerid=r.edizmerid
                                         and o.ss = r.ss
                                         and o.sb = r.sb) 
                        left join rash_doc rd on (o.tovarid =rd.tovarid 
                                              and o.edizmerid=rd.edizmerid
                                              and o.ss = rd.ss_
                                              and o.sb = rd.sb_) 
                       -- left join rash_old_072018 ro on (o.tovarid =ro.tovarid 
                                          --    and o.edizmerid=ro.edizmerid) 
                  -- 08.2018     left join rash_storno rs on (o.tovarid =rs.tovarid    and o.edizmerid=rs.edizmerid) 
    --                    where   o.vos+p.sum_kl>0  
                      ),
                    tt as (    
                      select *, ttt.vos + ttt._prih_kl - ttt._rash_kl as ost_fact ,
    --                    case when  (ttt.vos+ttt._prih_kl<>0 )  then
    --                        ( ttt.sum_vos+ttt.sum_prih -ttt.sum_rash_storno)/(ttt.vos+ttt._prih_kl-ttt._rash_storno_kl)
    --                      else ttt.cena end   as new_cen from ttt
              --          case when  (ttt.vos+ttt._prih_kl+ttt._rash_storno_kl-ttt._rash_kl-ttt._rash_doc_kl<>0 )  then
              --              ( ttt.sum_vos+ttt.sum_prih +ttt.sum_rash_storno-ttt.sum_rash-ttt.sum_rash_doc)/(ttt.vos+ttt._prih_kl-ttt._rash_storno_kl-ttt._rash_kl-ttt._rash_doc_kl)
                             case when (ttt.vos+ttt._prih_kl-ttt._rash_kl-ttt._rash_doc_kl<>0 ) then
                               ( ttt.sum_vos+ttt.sum_prih -ttt.sum_rash-ttt.sum_rash_doc)/(ttt.vos+ttt._prih_kl-ttt._rash_kl-ttt._rash_doc_kl)
                             else ttt.cena end as new_cen 
                      from ttt
                    )        
          select tt.tovarid,
                 tt.cena,
                 tt.edizmerid,
                 t.kt,
                 t.nt,
                 tt.vos,
                 round(sum_vos,2), 
                 u.name_u,
                 tt._prih_kl as _prih_kl, 
          --       round( tt.sum_prih,2)  as sum_prih ,
                  tt.sum_prih_docs  as sum_prih ,
                 tt._rash_kl as _rash_kl, 
           --      round( tt.sum_rash,2)  as sum_rash ,
                 tt.sum_rash_docs  as sum_rash ,
                 tt.ost_fact,
    --             round(tt.ost_fact* tt.new_cen,5),
                 round(tt.ost_fact* tt.new_cen,2),
                 t.priz,
                 round(tt.new_cen,5),
                 tt.ostid,
                 tt._rash_doc_kl,
    --             tt.sum_rash_doc,
                 tt.sum_rash_doc_docs,
                 t.dk_2010,
                 coalesce( tt.ss, '0'),
                 coalesce( tt.sb, '0')
              from tt
              left join material.tovar t on (t.tovarid=tt.tovarid)
              left join nsi.units u  on (u.id=tt.edizmerid);

    Называется найди разницу между ttt, tt и t

    Запостил: Baiumka, 31 Января 2022

    Комментарии (2) RSS

    Добавить комментарий