SHARE
TWEET

Untitled

a guest Jun 19th, 2017 43 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
Want to get better at MySQL?
Learn to code MySQL in 2017
Pastebin PRO Summer Special!
Get 40% OFF on Pastebin PRO accounts!
Top