asp.sql

SQL例子

SELECT COUNT(*) FROM BP_PC_EXCEL

SELECT COUNT(*) FROM BP_PC_EXCEL_BAK


SELECT * INTO BP_FINAL2007 FROM BP_FINAL2007_BAK WHERE 1=2


DELETE FROM BP_PC_EXCEL 

SELECT SUM(PC) FROM BP_PC_EXCEL

SELECT SUM(PC) FROM PC2007_BAK

SELECT SUM(PC) FROM PC2007

SELECT SUM(UC) FROM UC2007

SELECT COUNT(DISTINCT FC) FROM BP_PC_EXCEL_BAK

SELECT DISTINCT SHEET_NAME FROM BP_PC_EXCEL_BAK


SELECT 73495.430562485941


DUMP TRANSACTION BP WITH NO_LOG 

SELECT COUNT(*) FROM PC2007_BAK
SELECT COUNT(*) FROM BP_FINALSUM
SELECT COUNT(*) FROM BP_FINAL2007_BAK_UC




SELECT
  sum(BP_FINAL2007.UC),
  sum(BP_FINAL2007.PC),
  sum(BP_FINAL2007.GMA)/1000,
  sum(BP_FINAL2007.DEPTEXP_SALES)/1000,
  sum(BP_FINAL2007.COGS),
  sum(BP_FINAL2007.GROSS_REVENUE),
  sum(BP_FINAL2007.INVESTIGATION_FEE)/1000,
  sum(BP_FINAL2007.CONCENTRATE_REBATE),
  sum(BP_FINAL2007.NORMAL_DISCOUNT),
  sum(BP_FINAL2007.PRICE_OFF_DISC),
  sum(BP_FINAL2007.SALES_REBATE),
  sum(BP_FINAL2007.CR_UPSIZE),
  sum(BP_FINAL2007.XT)/1000,
  sum(BP_FINAL2007.LOGISTICS_HEAD)/1000,
  sum(BP_FINAL2007.LOGISTICS_OUTSOURCING),
  sum(BP_FINAL2007.LOGISTICS_FLEET),
  sum(BP_FINAL2007.CODE_DRINK)/1000,
  sum(BP_FINAL2007.LOGISTIC_WS),
  sum(BP_FINAL2007.OUTSOURCING_TRANSFER)/1000,
  sum(BP_FINAL2007.OUTSOURCING_SD)/1000,
  sum(BP_FINAL2007.FLEET_T)/1000,
  sum(BP_FINAL2007.FLEET_SD)/1000,
  sum(BP_FINAL2007.CONTRACT_DME)/1000,
  sum(BP_FINAL2007.BRAND_DME)/1000,
  sum(BP_FINAL2007.WAREHOUSE_INCITY)/1000,
  sum(BP_FINAL2007.BRAND_DME_ABQ)/1000,
  sum(BP_FINAL2007.MARKETING07)/1000,
  sum(BP_FINAL2007.DEPT_EXP_101)/1000,
  sum(BP_FINAL2007.WATER_HQ)/1000,
  sum(BP_FINAL2007.REV_ADM_CREDIT)/1000,
  sum(BP_FINAL2007.KAMSA_DEPTEXP)/1000,
  sum(BP_FINAL2007.WAREHOUSE_OUTCITY)/1000,
  sum(BP_FINAL2007.OUTSOURCING_SD_OUTCITY)/1000,
  sum(BP_FINAL2007.Finance_Inc)/1000,
  sum(BP_FINAL2007.Finance_exp)/1000,
  sum(BP_FINAL2007.Exchange_Inc)/1000,
  sum(BP_FINAL2007.Commision)/1000,
  sum(BP_FINAL2007.OIncome)/1000,
  sum(BP_FINAL2007.Other_Expense)/1000,
  sum(BP_FINAL2007.Additional_Income)/1000,
  sum(BP_FINAL2007.Write_off)/1000,
  sum(BP_FINAL2007.Income_Tax)/1000,
  sum(BP_FINAL2007.Expenditure)/1000,
  sum(BP_FINAL2007.Expat_Cost)/1000,
  sum(BP_FINAL2007.Audit_Fee)/1000,
  sum(BP_FINAL2007.Chivalry_Guarantee)/1000,
  substring(DW.dbo.Monthly445.Description,1,3),
  DW.dbo.PakCat_dim.Pack_Category,
  DW.dbo.Brand_dim.Brand,
  DW.dbo.PakSize_dim.Pack_Size,
  OUTLOC.OUTLOC,
  OUTLOC.OUTLET_LACATION,
  OUTLOC.CHANNEL,
  DW.dbo.CHANNEL_Dim.CHANNEL_DESC,
  DW.dbo.Cat_dim.category,
  ( sum(BP_FINAL2007.GROSS_REVENUE) ) - ( ( sum(BP_FINAL2007.CONCENTRATE_REBATE) ) + ( sum(BP_FINAL2007.PRICE_OFF_DISC) ) + ( sum(BP_FINAL2007.NORMAL_DISCOUNT) ) + ( sum(BP_FINAL2007.SALES_REBATE) ) ),
  ( sum(BP_FINAL2007.CONCENTRATE_REBATE) ) + ( sum(BP_FINAL2007.PRICE_OFF_DISC) ) + ( sum(BP_FINAL2007.NORMAL_DISCOUNT) ) + ( sum(BP_FINAL2007.SALES_REBATE) ),
  ( sum(BP_FINAL2007.COGS) ) - ( sum(BP_FINAL2007.CR_UPSIZE) ),
  ( sum(BP_FINAL2007.DEPT_EXP) ) + ( ( sum(BP_FINAL2007.INVESTIGATION_FEE)/1000 ) + ( sum(BP_FINAL2007.XT)/1000 ) ) + ( sum(BP_FINAL2007.SHARED_SALES_EXP) ) + ( CASE ( sum(BP_FINAL2007.CONTRACT_DME)/1000 )  WHEN NULL THEN 0 ELSE ( sum(BP_FINAL2007.CONTRACT_DME)/1000 )  END+CASE  ( sum(BP_FINAL2007.BRAND_DME)/1000 ) WHEN NULL  THEN 0  ELSE  ( sum(BP_FINAL2007.BRAND_DME)/1000 )  END+CASE ( sum(BP_FINAL2007.BRAND_DME_ABQ)/1000 )  WHEN 0 THEN 0  ELSE ( sum(BP_FINAL2007.BRAND_DME_ABQ)/1000 )  END ) + ( sum(BP_FINAL2007.GMA)/1000 ),
  DW.dbo.Area_Dim.AreaDescr,
  substring(DW.dbo.Monthly445.YM_DESC,1,4),
  ltrim(rtrim(OUTLOC.OUTLET_LACATION)),
  rtrim(ltrim(DW.dbo.CHANNEL_Dim.CHANNEL_DESC)),
  DW.dbo.PakCat_dim.Pack_Category,
  BP_FINAL2007."2007FC",
  ( ( sum(BP_FINAL2007.GROSS_REVENUE) ) - ( ( sum(BP_FINAL2007.CONCENTRATE_REBATE) ) + ( sum(BP_FINAL2007.PRICE_OFF_DISC) ) + ( sum(BP_FINAL2007.NORMAL_DISCOUNT) ) + ( sum(BP_FINAL2007.SALES_REBATE) ) ) ) - ( ( sum(BP_FINAL2007.COGS) ) - ( sum(BP_FINAL2007.CR_UPSIZE) ) )
FROM
  BP_FINAL2007,
  DW.dbo.Monthly445,
  DW.dbo.PakCat_dim,
  DW.dbo.Brand_dim,
  DW.dbo.PakSize_dim,
  OUTLOC,
  DW.dbo.CHANNEL_Dim,
  DW.dbo.Cat_dim,
  DW.dbo.Area_Dim
WHERE
  ( DW.dbo.Monthly445.Month=BP_FINAL2007.MONTH  )
  AND  ( OUTLOC.OUTLOC=BP_FINAL2007.OUTLOC  )
  AND  ( DW.dbo.CHANNEL_Dim.CHANNEL_ID=BP_FINAL2007.CHANNEL  )
  AND  ( DW.dbo.PakSize_dim.PakSize_Id=BP_FINAL2007.paksize_id  )
  AND  ( DW.dbo.Brand_dim.Brand_Id=BP_FINAL2007.brand_id  )
  AND  ( DW.dbo.PakCat_dim.PakCat_Id=BP_FINAL2007.pakcat_id  )
  AND  ( BP_FINAL2007.cat_id=DW.dbo.Cat_dim.Cat_Id  )
  AND  ( BP_FINAL2007.AREAID=DW.dbo.Area_Dim.AreaId  )
 GROUP BY
  substring(DW.dbo.Monthly445.Description,1,3), 
  DW.dbo.PakCat_dim.Pack_Category, 
  DW.dbo.Brand_dim.Brand, 
  DW.dbo.PakSize_dim.Pack_Size, 
  OUTLOC.OUTLOC, 
  OUTLOC.OUTLET_LACATION, 
  OUTLOC.CHANNEL, 
  DW.dbo.CHANNEL_Dim.CHANNEL_DESC, 
  DW.dbo.Cat_dim.category, 
  DW.dbo.Area_Dim.AreaDescr, 
  substring(DW.dbo.Monthly445.YM_DESC,1,4), 
  ltrim(rtrim(OUTLOC.OUTLET_LACATION)), 
  rtrim(ltrim(DW.dbo.CHANNEL_Dim.CHANNEL_DESC)), 
  DW.dbo.PakCat_dim.Pack_Category, 
  BP_FINAL2007."2007FC"