daily pastebin goal
77%
SHARE
TWEET

Untitled

a guest Jun 19th, 2017 45 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DECLARE                  
  2.  @YEAR         INT,
  3.  @DEPID        INT,
  4.  @FIRMID       INT              
  5.  
  6. SET        @YEAR   = 2010;           
  7. SET        @DEPID  = NULL;
  8. SET        @FIRMID = 38838;                                                                      
  9.  
  10. SELECT
  11.       'тривалого (понад 10 років)' AS keyword,
  12.        Year(a2.DatOpen) YearOpen, Year(a2.DatClose) YearClose,
  13.       (SELECT COUNT(m3.analit)) as countable,
  14.       (SELECT SUM(CAST(isNull(m3.stroka2t3, 0) as int)) from dbo.MBAnValR3 m3 where m3.analit = a.analit) AS MB,
  15.        s.NameAn OurFirm,            
  16.       (CASE when a3.YesNo  = 1 THEN 1 ELSE 0 end) AS TransientDoc,
  17.       (CASE when a3.YesNo2 = 1 THEN 1 ELSE 0 end) AS EPCDoc
  18.  
  19. from
  20.            dbo.MBAnalit a
  21. LEFT  JOIN dbo.MBAnalitSpr         s  ON a.OurFirm          = s.Analit
  22. LEFT  JOIN dbo.MBAnalitSpr         s3 ON a.Podr             = s3.Analit
  23. LEFT  JOIN dbo.MBAnalit            a2 ON LTRIM(a.Stroka)            = LTRIM(a2.Recv)
  24. LEFT  JOIN dbo.MBAnalit            a3 ON a2.FileStoragePeriod           = a3.Analit
  25. LEFT  JOIN dbo.MBAnValR3           m3 ON a.Analit           = m3.Analit
  26.      
  27. WHERE
  28.         a.Vid = 3476
  29.     AND a.XRecStat = '+'
  30.     AND IsNull(a.OurFirm, @FIRMID) = @FIRMID
  31.     AND isNull(a3.IntNumber ,0) >= 10
  32.     AND (year(a2.DatOpen) <= @YEAR and @YEAR >= year(a2.DatClose))        
  33.     AND (case when @DEPID is not NULL then @DEPID else s3.Analit end) = s3.Analit
  34.    
  35. GROUP BY
  36.       a2.DatOpen, a2.DatClose, a3.YesNo, a3.YesNo2, s.NameAn, m3.stroka2t3, m3.analit, a.analit
RAW Paste Data
Top