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

    −171

    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
    CREATE TABLE #Calibration(
              CertificateNumber VARCHAR(50), CalibrationID VARCHAR(50),Dateoffissue VARCHAR(50),IssuedBy VARCHAR(50),DateofExpiry VARCHAR(50),
              TransducerSerial VARCHAR(50),Supplier VARCHAR(50),Model VARCHAR(50),Transducer VARCHAR(50),[Description] VARCHAR(50),DAQ_ID VARCHAR(50),InstrumentTypeId VARCHAR(50),Unit_ID VARCHAR(50),
              Calibrated_Unit_ID VARCHAR(50),InstrumentID VARCHAR(50),[1] float,[2] float,[3] float,[4] float,[5] float
              )
              
INSERT INTO #Calibration (CertificateNumber,CalibrationID,Dateoffissue,IssuedBy,
                                                DateofExpiry,TransducerSerial,Supplier, Model, InstrumentTypeId,  Calibrated_unit_id,InstrumentID, [1], [2],[3],[4],[5])
SELECT
       C.CertificateNumber,
       C.CalibrationID,
       C.Dateoffissue,
       C.IssuedBy,
       C.DateofExpiry,
       C.TransducerSerial,
       C.Supplier,
       C.Model,
       C.InstrumentTypeId,
       C.calibrated_unit_id,
       C.InstrumentID,
       I.[1],
       I.[2],
       I.[3],
       I.[4],
       I.[5]

FROM

(SELECT  dbo.WH_Calibration.DateOffIssue, dbo.WH_Calibration.CertificateNumber, dbo.WH_Calibration.IssuedBy, dbo.WH_Calibration.DateOfExpiry, 
                                  dbo.WH_Calibration.TransducerSerial, dbo.WH_Calibration.Supplier, dbo.WH_Calibration.Model,  
                                  dbo.WH_Calibration.InstrumentID, dbo.WH_Calibration.Calibrated_Unit_ID, dbo.WH_Calibration.Calibrated_Site_ID, 
                    dbo.WH_Calibration.InstrumentID as InstrumentTypeID,dbo.WH_calibration.calibrationid -- dbo.WH_Calibration.InstrumentID FOR instrumentTypeID
                                  FROM    dbo.WH_Calibration 
)

   C
   INNER JOIN (
      SELECT *
      FROM
         (SELECT Calibrationid, Reading, TestID FROM dbo.WH_Calibrationdata) I
         PIVOT (Max(Reading) FOR TestID IN ([1],[2],[3],[4],[5])) P
   ) I ON C.CalibrationId = I.CalibrationID
   
   Where --(InstrumentTypeID <> 7) AND
       (CertificateNumber like '' + @CertNo  + '')
       and (TransducerSerial like '' + @SerNo + '')
       and (DateOffIssue>= @StartDate) and  (DateOffIssue <= @EndDate)
       --if @UnitID <> 0
       --     begin
       and Calibrated_unit_ID IN (select [value] from dbo.fn_split(@UnitID,','))
       --     end
      
     order by  InstrumentID,DateOffissue desc

    Читаемость кода зашкаливает!

    Запостил: Domkrat, 03 Марта 2015

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

    • ava someone 05.03.2015 07:07 # +1 

      CREATE TABLE #Calibration(


      Гаррус, перелогиньтесь.
      Ответить

    • ava kegdan 05.03.2015 07:34 # +1

      >>[1] float,[2] float,[3] float,[4] float,[5] float
      Сдаюсь, сдаюсь, нет больше фантазии!
      PS
INNER JOIN
А тож по слову JOIN не понятно, что он INNER 
                                                                                      Ваш
                                                                                      Человек Kegdan
      Ответить

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