SHARE
TWEET

Untitled

a guest Jul 17th, 2017 48 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DECLARE @SchoolYear varchar(4) = '2016'
  2. DECLARE @Submission int = 3;
  3.  
  4. SELECT @SchoolYear as SchoolYear,
  5.     @Submission AS Submission,
  6.     '220905' AS DistrictID,
  7.     Row_number() OVER (partition by A.incnum, A.locid ORDER BY A.dispnum DESC) as seq,
  8.     A.permnum,
  9.     B.txuniqueid,
  10.     '220905' + A.disschnum AS CampusIDofEnrollment,
  11.     stuff(A.incnum, 1, 0, replicate('0', 6 - len(A.incnum))) AS DisciplinaryIncidentNumber,
  12.     **CASE
  13.         WHEN ROW_NUMBER() OVER (PARTITION BY A.incnum, A.locid ORDER BY A.dispnum DESC) = 2    
  14.         AND COUNT(A.PERMNUM) > 2
  15.         AND COUNT(STUFF(A.incnum, 1, 0, REPLICATE('0', 6 - LEN(A.incnum)))) > 2
  16.         AND COUNT(CONVERT(VARCHAR(10), A.dateofdisciplinaryaction, 120) > 2
  17.         THEN
  18.  
  19.         --WHEN A.desccode LIKE 'XSTA-[0-9][0-9]%' THEN Substring(A.desccode, 6, 2) ELSE Substring(A.desccode, 1, 2)
  20.     END AS DisciplinaryActionReasonCode,**
  21.     CONVERT(VARCHAR(10), A.dateofdisciplinaryaction, 120) AS DateofDisciplinaryAction
  22. FROM [dbo].[XXdiscipline] A
  23. INNER JOIN [Fwisddw].[dbo].[dwstudents] B
  24. ON A.permnum = B.permnum
  25. WHERE (A.desccode LIKE '[0-9][0-9]%' OR A.desccode LIKE 'XSTA%')
  26. AND (A.dispcode LIKE '[0-9][0-9]%' OR A.dispcode LIKE 'XSTA-[0-9][0-9]%')
  27. AND A.PERMNUM = 1042003
  28. ORDER BY permnum asc, DisciplinaryIncidentNumber asc,  a.DISPNUM asc
RAW Paste Data
Top