技巧集锦023：YKX取数

最新推荐文章于 2022-01-25 09:51:37 发布

NO66781

最新推荐文章于 2022-01-25 09:51:37 发布

阅读量507

点赞数 1

分类专栏：时空智友

本文链接：https://blog.csdn.net/NO66781/article/details/103182729

版权

本文介绍了在SALEOUTMD、SALEOUTDT、SALENODEMT和SALENODEDT表中增加KK_YKX字段，用于销售出库和销退开票的汇总及明细查询，提供了相应的SQL查询示例，帮助理解YKX取数的方法。

摘要由CSDN通过智能技术生成

SALEOUTMD增加KK_YKX字段：

IF NOT EXISTS(SELECT * FROM syscolumns WHERE object_name(id) = 'SaleOutMt' AND name = 'KK_YKX')
	ALTER TABLE SaleOutMt ADD KK_YKX char(1) NULL DEFAULT 'N';

DELETE FROM TbStru WHERE upper(TbName) = 'SALEOUTMT' AND upper(FdName) = 'KK_YKX';
INSERT INTO TbStru (TbName, SortNum, FdName, FdDesc, Is_Null, DefaultValue, Explains, IsDevFld) 
	VALUES ('SaleOutMt', 53, 'KK_YKX', '药快销同步', 'Y', 'N', '药快销同步标识', 'N');
IF NOT EXISTS(SELECT * FROM FldList WHERE FdName = 'KK_YKX') 
INSERT INTO FldList (FdName, FdDesc, FdType, FdSize, FdDec, DefaultValue) 
	VALUES ('KK_YKX', '药快销同步', 'char', 1, 0, 'N');

销售出库汇总取数SQL：

SELECT 'Y' AS is_ykx,'SKKM' AS enterpriseID,'GZWL' AS orgID,a.BillCode AS missionCode,CONVERT(CHAR(19),GETDATE(),120) AS K_CREATETIME
      ,'N' AS automatic,'' AS travelTaskNumber,CONVERT(CHAR(19),GETDATE(),120) AS travelTaskTime,'' AS memberID,'X'+a.SaleManId AS salesmanID
      ,LTRIM(RTRIM(c.Dates))+' ' +LTRIM(RTRIM(c.OnTime)) AS orderTime,c.BillCode AS salesOrderCode
      ,LTRIM(RTRIM(a.Dates))+' ' +LTRIM(RTRIM(a.OnTime)) AS missionTime,'X'+a.ClientId AS addressCode,'X'+a.ClientId AS unitCode
      ,CONVERT(INT,SUBSTRING(d.zcqmc,5,2)) AS whole,CONVERT(INT,SUBSTRING(d.zcqmc,CHARINDEX('总箱数:',d.zcqmc)+4,LEN(d.zcqmc)-CHARINDEX('总箱数:',d.zcqmc))) AS scattered
      ,0 AS bag,a.TaxAmount AS orderAmount,'SKKM' AS cargoOwnerID,'世康科贸' AS cargoOwnerName
      ,a.kk_islc AS isRefrigeratedGoods
      ,CASE WHEN a.Delivery =2 THEN 'N' ELSE 'Y' END AS isPickUp
      ,CASE WHEN a.Delivery = 2 THEN 1 WHEN a.Delivery = 0 THEN 3 ELSE 1 END AS shippingMethods
      ,'' AS logisticsCompany,a.Remark AS remark
      ,CONVERT(CHAR(19),GETDATE(),120) AS lastmodifytime,d.totalRows,CONV