c78d1927722df3187e89f8f1ae31cd0830a26d34.svn-base 7.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153
  1. select * from BemsMonitoringPoint where ValueType = 0
  2. select * from BemsMonitoringPoint where FacilityCode = 1390
  3. select TOP 1 * from BemsMonitoringPointHistory15min where PropertyId = 10 and FacilityCode = 3396 order by CreatedDateTime desc
  4. select * from BemsMonitoringPointHistory15min where PropertyId = 10 and FacilityCode = 3396 order by CreatedDateTime desc
  5. select MAX(CreatedDateTime), * from BemsMonitoringPointHistory15min where PropertyId = 10 and FacilityCode = 3396
  6. select MAX(CreatedDateTime) from BemsMonitoringPointHistory15min where PropertyId = 10 and FacilityCode = 3396
  7. select * from BemsMonitoringPointHistory15min where PropertyId = 10 and FacilityCode = 3396 and select in (MAX(CreatedDateTime) = CreatedDateTime)
  8. select * from BemsMonitoringPointHistory15min where PropertyId = 10 and FacilityCode = 3396 and CreatedDateTime = (select MAX(CreatedDateTime)
  9. from BemsMonitoringPointHistory15min where PropertyId = 10 and FacilityCode = 3396)
  10. select * from BemsControlPointHistory where CreateDateTime = (select MAX(CreateDateTime)
  11. from BemsControlPointHistory )
  12. select * from BemsMonitoringPointHistory15min where CreatedDateTime = '2015-08-17 15:00'
  13. select * from BemsMonitoringPointHistoryDaily where FacilityCode = 1390
  14. select * from CmFacilityTempSet
  15. select * from CmFacility where FacilityTypeId = 1
  16. select * from FmsBudgetCodeClass
  17. select *, LEN(BudgetSeq) from FmsBudgetCodeClass
  18. select BudgetClassId from FmsBudgetCodeClass
  19. select * from FmsBudgetCodeClass where Depth = 0
  20. select * from FmsBudgetDetailExecution
  21. select * from FmsBudgetDetailExecution where BudgetClassId in (select BudgetClassId from FmsBudgetCodeClass where Depth = 0)
  22. select * from FmsBudgetDetailExecution where BudgetClassId in (select BudgetClassId from FmsBudgetCodeClass where LEN(BudgetSeq) = 6)
  23. select * from FmsBudgetDetailExecution where BudgetClassId in (select BudgetClassId from FmsBudgetCodeClass where SUBSTRING(BudgetSeq,1,2) = '01')
  24. select * from FmsBudgetDetailExecution where BudgetClassId in (select BudgetClassId from FmsBudgetCodeClass where LEN(BudgetSeq) = 6 and
  25. SUBSTRING(BudgetSeq,1,2) = '05')
  26. select year, Month, SUM(MonthlyExecution) from FmsBudgetDetailExecution
  27. where BudgetClassId in (select BudgetClassId from FmsBudgetCodeClass where LEN(BudgetSeq) = 6 and
  28. SUBSTRING(BudgetSeq,1,2) = '01')
  29. group by year, Month
  30. select year, Month, SUM(MonthlyExecution) from FmsBudgetDetailExecution
  31. where BudgetClassId in (select BudgetClassId from FmsBudgetCodeClass where LEN(BudgetSeq) = 6 and
  32. SUBSTRING(BudgetSeq,1,2) = '02')
  33. group by year, Month
  34. select year, Month, SUM(MonthlyExecution) from FmsBudgetDetailExecution
  35. where BudgetClassId in (select BudgetClassId from FmsBudgetCodeClass where LEN(BudgetSeq) = 6 and
  36. SUBSTRING(BudgetSeq,1,2) = '03')
  37. group by year, Month
  38. select SUBSTRING(BudgetSeq,1,2) from FmsBudgetCodeClass
  39. select BudgetSeq from FmsBudgetCodeClass
  40. select * from FmsBudgetDetailExecution where Year >= 1000 and Month > 0 and Year < 2016 or (Year = 2016 and Month < 5)
  41. select * from CmDepartment
  42. select * from FmsInvestmentCost
  43. select SUM(InsvestmentCost) from FmsInvestmentCost
  44. select * from FmsWorkRequest where WorkProgressId = 6
  45. select * from FmsWorkResult where IsConfirmed = 1
  46. select * from FmsWorkRequest where WorkProgressId = 3 or WorkProgressId = 4 or WorkProgressId = 5 or (WorkProgressId = 6 and
  47. WorkRequestId in (select WorkRequestId from FmsWorkResult where IsConfirmed = 1 and ConfirmedDate > '2015 09 01'))
  48. select * from FmsWorkResult
  49. select * from FmsWorkRequest where (WorkProgressId = 6 and
  50. WorkRequestId in (select WorkRequestId from FmsWorkResult where IsConfirmed = 1 and ConfirmedDate > '2015 09 01'))
  51. select * from FmsWorkOrder where BusinessFieldId = 1
  52. select * from FmsWorkResult where WorkRequestId in (select WorkRequestId from FmsWorkRequest where
  53. WorkProgressId = 4 ) and WorkerUserId = 'im'
  54. select * from FmsWorkOrder where WorkRequestId in (select WorkRequestId from FmsWorkRequest where
  55. WorkProgressId = 4 ) and BusinessFieldId = 2
  56. order by WorkRequestId
  57. select * from FmsWorkOrder where WorkRequestId in (select WorkRequestId from FmsWorkRequest where
  58. WorkProgressId = 3 or WorkProgressId = 4 ) and BusinessFieldId = 2
  59. order by WorkRequestId
  60. select * from FmsWorkOrder where WorkRequestId in (select WorkRequestId from FmsWorkRequest where
  61. WorkProgressId = 3 ) and BusinessFieldId = 2
  62. order by WorkRequestId
  63. select * from FmsWorkOrder where WorkRequestId in (select WorkRequestId from FmsWorkRequest where
  64. WorkProgressId = 4 ) and BusinessFieldId = 2
  65. order by WorkRequestId
  66. select * from FmsWorkOrder O where O.WorkRequestId in (select A.WorkRequestId from FmsWorkRequest A, FmsWorkResult B where
  67. A.WorkProgressId = 3 or A.WorkProgressId = 4 and B.WorkerUserId = 'im')
  68. and O.BusinessFieldId = 2
  69. order by O.WorkRequestId
  70. select * from FmsWorkOrder where WorkRequestId in (select WorkRequestId from FmsWorkRequest where WorkProgressId= 3)
  71. and BusinessFieldId = 5
  72. order by WorkRequestId
  73. select * from FmsWorkOrder where WorkRequestId in (select WorkRequestId from FmsWorkRequest where WorkProgressId = 4)
  74. and BusinessFieldId = 5
  75. order by WorkRequestId
  76. select * from FmsWorkOrder where BusinessFieldId = 5 or BusinessFieldId = 1
  77. select * from FmsMaterialStored group by MaterialId
  78. select * from FmsMaterial where MaterialId = 3042
  79. select * from FmsMaterialStored where MaterialId = 3042 and StoredDate = (select MAX(StoredDate) from FmsMaterialStored where MaterialId = 3042)
  80. select * from FmsMaterialStored where MaterialId = 3042 and StoredDate = (select MAX(StoredDate) from FmsMaterialStored where MaterialId = 3042)
  81. select * from FmsMaterialRelease where MaterialId = 3068
  82. //3077 ¼³ºñ100 3076°øÅë
  83. select * from FmsMaterialRelease where MaterialId = 3076
  84. select * from FmsMaterialStored where MaterialId = 3076
  85. select SUM(RemainStoredCount) from FmsMaterialStored where MaterialId = 3077 group by WarehouseId
  86. select SUM(RemainStoredCount) from FmsMaterialStored where WarehouseId = 1
  87. select MaterialId, SUM(RemainStoredCount) from FmsMaterialStored where WarehouseId = 1 group by MaterialId
  88. select WarehouseId, MaterialId, SUM(RemainStoredCount) from FmsMaterialStored group by WarehouseId, MaterialId
  89. select * from FmsMaterialRelease where MaterialId = 3068
  90. select * from BemsMonitoringPointHistory15min where CreatedDateTime = '2015 10 05 14:30:00' and PropertyId = 17
  91. //update BemsMonitoringPointHistory15min set CurrentValue = 100 where CreatedDateTime = '2015 10 05 14:30:00' and PropertyId = 17
  92. select * from BemsMonitoringPointHistory15min where CreatedDateTime = '2015 10 05 14:30:00' and PropertyId = 17
  93. select * from BemsMonitoringPointHistoryDaily where FacilityCode = 4863 and PropertyId = 2 and CreatedDateTime = '2016 03 18'
  94. select * from BemsMonitoringPointHistoryDaily where FacilityCode = 4863 and PropertyId = 1 and CreatedDateTime = '2016 03 18'
  95. // 2016 04 19
  96. select * from BemsMonitoringPoint where ServiceTypeId = 100
  97. select * from BemsMonitoringPointHistoryHourly where PropertyId = 3 and CreatedDateTime > '2016 04 18' and FacilityCode = 4499
  98. select * from CmFacility where FacilityCode = 4499
  99. select ApplyDate from BemsNoticePriceDetail
  100. select Max (ApplyDate), * from BemsNoticePriceDetail where ApplyDate <= '2016 04 01'
  101. select * from BemsNoticePriceDetail where ApplyDate <= '2014 04 01'
  102. select * from BemsNoticePriceDetail where ApplyDate = (select Max (ApplyDate) from BemsNoticePriceDetail where ApplyDate <= '2016 04 01')
  103. and FuelTypeId = 1 and ContractType = 1
  104. select * from BemsNoticePriceDetail where ApplyDate = (select Max (ApplyDate) from BemsNoticePriceDetail where ApplyDate <= '2016 04 01')
  105. and FuelTypeId = 1 and ContractType = 1
  106. select AVG(P07) from BemsNoticePriceDetail where ApplyDate = (select Max (ApplyDate) from BemsNoticePriceDetail where ApplyDate <= '2016 04 01')
  107. and FuelTypeId = 1 and ContractType = 1