MY_SQLCode

一、SPC查询 根据日期查询

      应用到了随机函数

     NEWID（）可以随机生成一个列值实现随机抓取记录

  CONVERT(varchar(100),列名, 23) AS TestDate  转换为日期格式 2013-14-14

  CONVERT(varchar(100),列名, 24) AS TestTime  转换为时间格式 19：24:25

   

     //SPC管制图 ExfoData

//SPC 管制图_ExfoData

SELECT TOP (5) Wave, Il_A, Refl_A, Il_B, Refl_B, TestDate, Time, Random
FROM (SELECT (CASE WHEN TWaveLength.Wvl_Id = 1 THEN '1310nm' WHEN TWaveLength.Wvl_Id
               = 2 THEN '1550nm' WHEN TWaveLength.Wvl_Id = 3 THEN '850nm' WHEN TWaveLength.Wvl_Id
               = 4 THEN '1300nm' END) AS Wave, TFiberMeasurement.Fim_ILEndA AS Il_A, 
              TFiberMeasurement.Fim_ReflectanceEndA AS Refl_A, 
              TFiberMeasurement.Fim_ILEndB AS Il_B, 
              TFiberMeasurement.Fim_ReflectanceEndB AS Refl_B, CONVERT(varchar(100), 
              TDUTMeasurement.Dum_MeasurementDate, 23) AS TestDate, 
              CONVERT(varchar(100), TDUTMeasurement.Dum_MeasurementDate, 24) 
              AS Time, NEWID() AS Random
        FROM TTestTemplate INNER JOIN
              TDUTMeasurement ON 
              TTestTemplate.Tst_Id = TDUTMeasurement.Dum_Tst_Id LEFT OUTER JOIN
              TFiber INNER JOIN
              TFiberMeasurement ON TFiber.Fib_Id = TFiberMeasurement.Fim_Fib_Id ON 
              TDUTMeasurement.Dum_Id = TFiberMeasurement.Fim_Dum_Id LEFT OUTER JOIN
              TWaveLength ON TWaveLength.Wvl_Id = TFiberMeasurement.Fim_Wvl_Id) 
      AS derivedtbl_1
WHERE (TestDate > '2014-01-06') AND (TestDate < '2014-03-06')
ORDER BY Random

  //SPC管制图 3D_Data

 

//SPC管制图 3D_Data

SELECT TOP (100) (CASE LEFT(Type, 3) WHEN 'APC' THEN 'APC' ELSE 'PC' END) AS Type, 
      Result, Curvature, Spherical, Apex_Offset, Tilt_Angle, CONVERT(varchar(100), 
      Test_Date, 23) AS Test_Date, Test_Time, NEWID() AS Random
FROM User_3D_Test_Good
WHERE (Test_Date = '2013-12-05') AND ((CASE LEFT(Type, 3) 
      WHEN 'APC' THEN 'APC' ELSE 'PC' END) = 'PC')
ORDER BY Random