金蝶k3库存账龄分析报表（带参数）

最新推荐文章于 2023-07-28 11:54:01 发布
最新推荐文章于 2023-07-28 11:54:01 发布
阅读量2.3k 收藏 1
点赞数
原文链接：https://blog.csdn.net/hzfw2008/article/details/78804709
版权 
单价取最近的结账账期加权平均单价；数量根据传入参数确定。

 

传入参数：

年份：如2017

账期：如11

 

SQL直接使用时执行存储过程：

 

execute [huwei_sp_stock_Age] 2017,07

 

K3调用查询分析语句：

 

 

  1. set nocount ON

  2. exec gd_sp_stock_Age @ParaYear@ ,@parePeriod@


 

 

存储过程代码如下：

 

 

  1. Set NoCount On

  2. SET ANSI_WARNINGS OFF

  3.  

  4. USE [AIS20140104204141]

  5. GO

  6.  

  7. /****** Object: StoredProcedure [dbo].[huwei_sp_stock_Age] Script Date: 12/15/2017 08:29:37 ******/

  8.  

  9. ALTER procedure [dbo].[gd_sp_stock_Age]

  10. @paraYear@ int,

  11. @parePeriod@ int

  12. as

  13.  

  14. declare @year int

  15. declare @period int

  16. declare @Fdate datetime

  17. declare @Fstartdate datetime

  18.  

  19. --declare @paraYear@ int

  20. --declare @parePeriod@ int

  21.  

  22. --set @paraYear@=(select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentYear')

  23. --set @parePeriod@=(select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentPeriod')

  24.  
  25.  

  26. if @parePeriod@=12

  27. begin

  28. set @year=@paraYear@+1

  29. set @period=1

  30. set @Fdate=convert(datetime,convert(nvarchar(20),@year)+'-01-01')-1

  31. set @Fstartdate=convert(datetime,convert(nvarchar(20),@paraYear@)+'-12-01')

  32. end

  33. else

  34. begin

  35. set @year=@paraYear@

  36. set @period=@parePeriod@+1

  37. set @Fdate=convert(datetime,convert(nvarchar(20),@year)+'-'+convert(nvarchar(20),@period)+'-01')-1

  38. set @Fstartdate=convert(datetime,convert(nvarchar(20),@Year)+'-'+convert(nvarchar(20),@parePeriod@)+'-01')

  39. end

  40.  
  41.  

  42. Create Table #Happen2(

  43. FItemID int Null,

  44. FStockID int Null,

  45. FBatchNo NVARCHAR(200),

  46. FQty decimal(28,10) Null,

  47. FCUUnitQty decimal(28,10) Null,

  48. FQty1 Decimal(28,10),

  49. FCUUnitQty1 Decimal(28,10),

  50. FAmount1 Decimal(28,10),

  51. FQty2 Decimal(28,10),

  52. FCUUnitQty2 Decimal(28,10),

  53. FAmount2 Decimal(28,10),

  54. FQty3 Decimal(28,10),

  55. FCUUnitQty3 Decimal(28,10),

  56. FAmount3 Decimal(28,10),

  57. FQty4 Decimal(28,10),

  58. FCUUnitQty4 Decimal(28,10),

  59. FAmount4 Decimal(28,10),

  60. FQty5 Decimal(28,10),

  61. FCUUnitQty5 Decimal(28,10),

  62. FAmount5 Decimal(28,10),

  63. FQty6 Decimal(28,10),

  64. FCUUnitQty6 Decimal(28,10),

  65. FAmount6 Decimal(28,10),

  66. FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10))

  67. Create Table #Happen(

  68. FItemID int Null,

  69. FStockID int Null,

  70. FBatchNo NVARCHAR(200),

  71. FQty decimal(28,10) Null,

  72. FCUUnitQty decimal(28,10) Null,

  73. FQty1 Decimal(28,10),

  74. FCUUnitQty1 Decimal(28,10),

  75. FAmount1 Decimal(28,10),

  76. FQty2 Decimal(28,10),

  77. FCUUnitQty2 Decimal(28,10),

  78. FAmount2 Decimal(28,10),

  79. FQty3 Decimal(28,10),

  80. FCUUnitQty3 Decimal(28,10),

  81. FAmount3 Decimal(28,10),

  82. FQty4 Decimal(28,10),

  83. FCUUnitQty4 Decimal(28,10),

  84. FAmount4 Decimal(28,10),

  85. FQty5 Decimal(28,10),

  86. FCUUnitQty5 Decimal(28,10),

  87. FAmount5 Decimal(28,10),

  88. FQty6 Decimal(28,10),

  89. FCUUnitQty6 Decimal(28,10),

  90. FAmount6 Decimal(28,10),

  91. FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10))

  92. Create Table #Happen1(

  93. FItemID int Null,

  94. FStockID int Null,

  95. FBatchNo NVARCHAR(200),

  96. FQty decimal(28,10) Null,

  97. FCUUnitQty decimal(28,10) Null,

  98. FQty1 Decimal(28,10),

  99. FCUUnitQty1 Decimal(28,10),

  100. FQty2 Decimal(28,10),

  101. FCUUnitQty2 Decimal(28,10),

  102. FQty3 Decimal(28,10),

  103. FCUUnitQty3 Decimal(28,10),

  104. FQty4 Decimal(28,10),

  105. FCUUnitQty4 Decimal(28,10),

  106. FQty5 Decimal(28,10),

  107. FCUUnitQty5 Decimal(28,10),

  108. FQty6 Decimal(28,10),

  109. FCUUnitQty6 Decimal(28,10),

  110. FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10) )

  111. Insert Into #Happen1

  112. Select t1.FItemID,t2.FItemID As FStockID,t6.FBatchNo,0,0,

  113. (Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-29,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,@Fdate),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty1,

  114. ((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-29,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,@Fdate),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient As FCUUnitQty1,

  115. (Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-59,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-30,@Fdate),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty2,

  116. ((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-59,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-30,@Fdate),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient As FCUUnitQty2,

  117. (Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-89,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-60,@Fdate),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty3,

  118. ((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-89,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-60,@Fdate),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient As FCUUnitQty3,

  119. (Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-179,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-90,@Fdate),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty4,

  120. ((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-179,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-90,@Fdate),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient As FCUUnitQty4,

  121. (Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-180,@Fdate),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty5,

  122. ((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-180,@Fdate),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient As FCUUnitQty5,

  123. (Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,@Fdate),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty6,

  124. ((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,@Fdate),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient As FCUUnitQty6,

  125. 1 ,ISNULL(((SELECT t12.FBegBal/t12.FBegQty FROM t_ICItem t11

  126. INNER JOIN (SELECT FItemID,SUM(FBegBal) AS FBegBal,SUM(FBegQty) AS FBegQty FROM ICBal t13

  127. Where t13.FItemID = t6.FItemID And t13.FYear = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentYear') And t13.FPeriod = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentPeriod')

  128. GROUP BY t13.FItemID) t12 ON t11.FItemID=t12.FItemID AND t12.FBegBal>0 AND t12.FBegQty>0) ),0),0,0

  129. From t_ICItem t1

  130. Join ICStockBill t5 On (t5.FStatus > 0 Or (t5.FUpStockWhenSave > 0 And t5.FCancellation <1 ))

  131. Join ICStockBillEntry t6 On t5.FInterID=t6.FInterID

  132. Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID

  133. Left Join t_Stock t2 On t2.FItemID = (case when t5.ftrantype=24 then t6.FSCStockID else t6.FDCStockID end)

  134. Where t1.FItemID = t6.FItemID

  135. And ((t5.FTrantype In (1,2,5,10,40) And t5.FRob =1) Or (t5.FTrantype In(21,24,28,29) And t5.FRob=-1))

  136. AND (NOT (t5.FTrantype In (1) AND t5.FPOMode = 36681 ))

  137. AND (NOT (t5.FTranType=1 and t6.FSourceInterID > 0 and EXISTS(SELECT 1 FROM ICHookRelations t8 where t6.FinterID=t8.fIBInterID and t8.FIBTag=4 )))

  138. And t2.FTypeID NOT IN (504)

  139.  
  140.  

  141. Insert Into #Happen1

  142. Select t1.FItemID,t2.FItemID As FStockID,t6.FBatchNo,0,0,

  143. (Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-29,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,@Fdate),101)) Then t6.FBegQty Else 0 End) As FQty1,

  144. ((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-29,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,@Fdate),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty1,

  145. (Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-59,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-30,@Fdate),101)) Then t6.FBegQty Else 0 End) As FQty2,

  146. ((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-59,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-30,@Fdate),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty2,

  147. (Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-89,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-60,@Fdate),101)) Then t6.FBegQty Else 0 End) As FQty3,

  148. ((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-89,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-60,@Fdate),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty3,

  149. (Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-179,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-90,@Fdate),101)) Then t6.FBegQty Else 0 End) As FQty4,

  150. ((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-179,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-90,@Fdate),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty4,

  151. (Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-180,@Fdate),101)) Then t6.FBegQty Else 0 End) As FQty5,

  152. ((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-180,@Fdate),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty5,

  153. (Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,@Fdate),101)) Then t6.FBegQty Else 0 End) As FQty6,

  154. ((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,@Fdate),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty6,

  155. 1 ,ISNULL(((SELECT t12.FBegBal/t12.FBegQty FROM t_ICItem t11

  156. INNER JOIN (SELECT FItemID,SUM(FBegBal) AS FBegBal,SUM(FBegQty) AS FBegQty FROM ICBal t13

  157. Where t13.FItemID = t6.FItemID And t13.FYear = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentYear') And t13.FPeriod = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentPeriod')

  158. GROUP BY t13.FItemID) t12 ON t11.FItemID=t12.FItemID AND t12.FBegBal>0 AND t12.FBegQty>0) ),0),0,0

  159.  

  160. From t_ICItem t1

  161. Join ICInvInitial t6 On t1.FItemID = t6.FItemID

  162. Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID

  163. Left Join t_Stock t2 On t2.FItemID = t6.FStockID

  164. Where 1=1 And t2.FTypeID NOT IN (504)

  165.  
  166.  

  167. CREATE TABLE #InventoryHanppen

  168. (FItemID INT NOT NULL,

  169. FStockID INT NULL,

  170. FBatchNo Varchar(255) NULL,

  171. FQty DECIMAL(28,10) NOT NULL,

  172. FAmount DECIMAL(28,10) NOT NULL )

  173. INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount)

  174. SELECT u1.FItemID,u1.FStockID,u1.FBatchNo,u1.FBegQty,u1.FBegBal

  175. FROM t_ICItem t1

  176. INNER JOIN ICInvBal u1 ON t1.FItemID=u1.FItemID

  177. LEFT JOIN t_Stock t2 ON t2.FItemID = u1.FStockID

  178. WHERE u1.FYear=@paraYear@ AND

  179. u1.FPeriod=@parePeriod@ And t2.FTypeID NOT IN (504)

  180.  

  181. INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount)

  182. SELECT u1.FItemID,t2.FItemID,u1.FBatchNo, CASE WHEN v1.FTranType IN (1,2,5,10,40,41,101,102) OR (v1.FTranType=100 AND v1.FBillTypeID=12542) THEN u1.FQty ELSE -1 * u1.FQty END,

  183. CASE WHEN v1.FTranType IN (1,2,5,10,40,101,102) OR (v1.FTranType=100 AND v1.FBillTypeID=12542) THEN u1.FAmount WHEN v1.FTranType=41 THEN u1.FAmtRef ELSE -1 * u1.FAmount END

  184. FROM t_ICItem t1

  185. INNER JOIN ICStockBillEntry u1 ON t1.FItemID=u1.FItemID

  186. INNER JOIN ICStockBill v1 ON u1.FInterID=v1.FInterID

  187. LEFT JOIN t_Stock t2 ON ((v1.FTrantype=24 AND u1.FSCStockID=t2.FItemID) OR (v1.FTranType IN (1,2,5,10,21,41,28,29,43,40,100,101,102) AND u1.FDCStockID=t2.FItemID ))

  188. WHERE v1.FDate>=@Fstartdate AND

  189. v1.FDate<=@Fdate And t2.FTypeID NOT IN (504)

  190.  

  191. AND v1.Ftrantype In (1,2,5,10,21,24,41,28,29,43,40,100,101,102) AND (NOT (v1.FTrantype In (1) AND isnull(v1.FPOMode,0) = 36681 ))

  192. AND (v1.FStatus > 0 Or (v1.FUpStockWhenSave > 0 And v1.FCancellation <1 ))

  193. INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount)

  194. SELECT u1.FItemID,t2.FItemID,u1.FBatchNo,-1 * u1.FQty ,-1 * u1.FAmount

  195. FROM t_ICItem t1

  196. INNER JOIN ICStockBillEntry u1 ON t1.FItemID=u1.FItemID

  197. INNER JOIN ICStockBill v1 ON u1.FInterID=v1.FInterID

  198. LEFT JOIN t_Stock t2 ON u1.FSCStockID=t2.FItemID

  199. WHERE v1.FDate>=@Fstartdate AND

  200. v1.FDate<=@Fdate And t2.FTypeID NOT IN (504)

  201.  

  202. AND v1.Ftrantype=41

  203. AND (v1.FStatus > 0 Or (v1.FUpStockWhenSave > 0 And v1.FCancellation <1 ))

  204. SELECT FItemID,FStockID,FBatchNo,SUM(FQty) AS FQty,SUM(FAmount) AS FAmount INTO #INVENTORY

  205. FROM #InventoryHanppen

  206. GROUP BY FItemID,FStockID,FBatchNo

  207. DROP TABLE #InventoryHanppen

  208. DELETE FROM #INVENTORY WHERE FQty<=0

  209. Insert Into #Happen1

  210. Select t1.FItemID,t2.FItemID As FStockID,t3.FBatchNo,(t3.FQTY) As FQTY,CAST(t3.FQTY AS DECIMAL(28,10))/t7.FCoefficient As FCUUnitQty,0,0,0,0,0,0,0,0,0,0,0,0,1 ,ISNULL(((SELECT t12.FBegBal/t12.FBegQty FROM t_ICItem t11

  211. INNER JOIN (SELECT FItemID,SUM(FBegBal) AS FBegBal,SUM(FBegQty) AS FBegQty FROM ICBal t13

  212. Where t13.FItemID = t3.FItemID And t13.FYear = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentYear') And t13.FPeriod = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentPeriod')

  213. GROUP BY t13.FItemID) t12 ON t11.FItemID=t12.FItemID AND t12.FBegBal>0 AND t12.FBegQty>0) ),0),0,0

  214.  

  215. From t_ICItem t1

  216. Join #INVENTORY t3 On t1.FItemID = t3.FItemID

  217. Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID

  218. Left Join t_Stock t2 On t2.FItemID = t3.FStockID

  219. Where 1=1

  220. And t2.FTypeID NOT IN (504)

  221.  
  222.  

  223. Insert Into #HAPPEN2

  224. Select t1.FITEMID,0,t1.FBatchNo,Sum(FQTY)As FQTY,Sum(FCUUnitQTY)As FCUUnitQTY,

  225. Sum(fqty1) As FQty1 ,Sum(fCUUnitqty1) As FCUUnitQty1 ,0,Sum(fqty2) As FQty2 ,Sum(fCUUnitqty2) As FCUUnitQty2 ,0,Sum(fqty3) As FQty3 ,Sum(fCUUnitqty3) As FCUUnitQty3 ,0,Sum(fqty4) As FQty4 ,Sum(fCUUnitqty4) As FCUUnitQty4 ,0,Sum(fqty5) As FQty5 ,Sum(fCUUnitqty5) As FCUUnitQty5 ,0,Sum(fqty6) As FQty6 ,Sum(fCUUnitqty6) As FCUUnitQty6 ,0,1,Min(FPrice),case Sum(FCUUnitQTY) when 0 then 0 else (Min(FPrice)*Sum(FQTY))/Sum(FCUUnitQTY) end,Min(FPrice)*Sum(FQTY)

  226. From #HAPPEN1 t1 INNER JOIN t_ICItem t2 ON t1.FItemID=t2.FItemID

  227. GROUP By t1.FITEMID,t1.FBatchNo Update #Happen2 Set FQty1= FQty,FQty2=0

  228. ,FQty3=0

  229. ,FQty4=0

  230. ,FQty5=0

  231. ,FQty6=0

  232. Where FQty-FQty1<0

  233. Update #Happen2 Set FQty2= FQty-FQty1,FQty3=0

  234. ,FQty4=0

  235. ,FQty5=0

  236. ,FQty6=0

  237. Where FQty-FQty1-FQty2<0

  238. Update #Happen2 Set FQty3= FQty-FQty1-FQty2,FQty4=0

  239. ,FQty5=0

  240. ,FQty6=0

  241. Where FQty-FQty1-FQty2-FQty3<0

  242. Update #Happen2 Set FQty4= FQty-FQty1-FQty2-FQty3,FQty5=0

  243. ,FQty6=0

  244. Where FQty-FQty1-FQty2-FQty3-FQty4<0

  245. Update #Happen2 Set FQty5= FQty-FQty1-FQty2-FQty3-FQty4,FQty6=0

  246. Where FQty-FQty1-FQty2-FQty3-FQty4-FQty5<0

  247. Update #Happen2 Set FQty6= FQty-FQty1-FQty2-FQty3-FQty4-FQty5

  248. Update #Happen2 Set FAmount1=FPrice*FQty1

  249. Update #Happen2 Set FAmount2=FPrice*FQty2

  250. Update #Happen2 Set FAmount3=FPrice*FQty3

  251. Update #Happen2 Set FAmount4=FPrice*FQty4

  252. Update #Happen2 Set FAmount5=FPrice*FQty5

  253. Update #Happen2 Set FAmount6=FPrice*FQty6

  254.  

  255. Update #Happen2 Set FCUUnitQty1= FCUUnitQty,FCUUnitQty2=0

  256. ,FCUUnitQty3=0

  257. ,FCUUnitQty4=0

  258. ,FCUUnitQty5=0

  259. ,FCUUnitQty6=0

  260. Where FCUUnitQty-FCUUnitQty1<0

  261. Update #Happen2 Set FCUUnitQty2= FCUUnitQty-FCUUnitQty1

  262. ,FCUUnitQty3=0

  263. ,FCUUnitQty4=0

  264. ,FCUUnitQty5=0

  265. ,FCUUnitQty6=0

  266. Where FCUUnitQty-FCUUnitQty1-FCUUnitQty2<0

  267. Update #Happen2 Set FCUUnitQty3= FCUUnitQty-FCUUnitQty1

  268. -FCUUnitQty2

  269. ,FCUUnitQty4=0

  270. ,FCUUnitQty5=0

  271. ,FCUUnitQty6=0

  272. Where FCUUnitQty-FCUUnitQty1-FCUUnitQty2-FCUUnitQty3<0

  273. Update #Happen2 Set FCUUnitQty4= FCUUnitQty-FCUUnitQty1

  274. -FCUUnitQty2

  275. -FCUUnitQty3

  276. ,FCUUnitQty5=0

  277. ,FCUUnitQty6=0

  278. Where FCUUnitQty-FCUUnitQty1-FCUUnitQty2-FCUUnitQty3-FCUUnitQty4<0

  279. Update #Happen2 Set FCUUnitQty5= FCUUnitQty-FCUUnitQty1

  280. -FCUUnitQty2

  281. -FCUUnitQty3

  282. -FCUUnitQty4

  283. ,FCUUnitQty6=0

  284. Where FCUUnitQty-FCUUnitQty1-FCUUnitQty2-FCUUnitQty3-FCUUnitQty4-FCUUnitQty5<0

  285. Update #Happen2 Set FCUUnitQty6= FCUUnitQty-FCUUnitQty1

  286. -FCUUnitQty2

  287. -FCUUnitQty3

  288. -FCUUnitQty4

  289. -FCUUnitQty5

  290.  

  291. Insert Into #HAPPEN

  292. Select t1.FITEMID,0,t1.FBatchNo,Sum(FQTY)As FQTY,Sum(FCUUnitQTY)As FCUUnitQTY,

  293. Sum(fqty1) As FQty1 ,Sum(fCUUnitqty1) As FCUUnitQty1 ,Sum(FAmount1) As FAmount1,Sum(fqty2) As FQty2 ,Sum(fCUUnitqty2) As FCUUnitQty2 ,Sum(FAmount2) As FAmount2,Sum(fqty3) As FQty3 ,Sum(fCUUnitqty3) As FCUUnitQty3 ,Sum(FAmount3) As FAmount3,Sum(fqty4) As FQty4 ,Sum(fCUUnitqty4) As FCUUnitQty4 ,Sum(FAmount4) As FAmount4,Sum(fqty5) As FQty5 ,Sum(fCUUnitqty5) As FCUUnitQty5 ,Sum(FAmount5) As FAmount5,Sum(fqty6) As FQty6 ,Sum(fCUUnitqty6) As FCUUnitQty6 ,Sum(FAmount6) As FAmount6,1,Min(FPrice),case Sum(FCUUnitQTY) when 0 then 0 else (Min(FPrice)*Sum(FQTY))/Sum(FCUUnitQTY) end,Min(FPrice)*Sum(FQTY)

  294. From #HAPPEN2 t1 INNER JOIN t_ICItem t2 ON t1.FItemID=t2.FItemID

  295. GROUP By t1.FITEMID,t1.FBatchNo

  296.  

  297. HAVING (SUM(FQTY)>=0)

  298. CREATE TABLE #DATA(

  299. FNumber Varchar(355) null,

  300. FShortNumber Varchar(355) null,

  301. FName Varchar(355) null,

  302. FModel Varchar(355) null,

  303. FUnitName Varchar(355) null,

  304. FCUUnitName Varchar(355) null,

  305. FQtyDecimal smallint null,

  306. FPriceDecimal smallint null,

  307. FQty Decimal(28,10) Null,

  308. FCUUnitQty Decimal(28,10) Null,

  309. FPrice Decimal(28,10) NULL,

  310. FCUPrice Decimal(28,10) Null,

  311. FAmount Decimal(28,10) Null,

  312. FQty1 Decimal(28,10),

  313. FCUUnitQty1 Decimal(28,10),

  314. FAmount1 Decimal(28,10),

  315. FQty2 Decimal(28,10),

  316. FCUUnitQty2 Decimal(28,10),

  317. FAmount2 Decimal(28,10),

  318. FQty3 Decimal(28,10),

  319. FCUUnitQty3 Decimal(28,10),

  320. FAmount3 Decimal(28,10),

  321. FQty4 Decimal(28,10),

  322. FCUUnitQty4 Decimal(28,10),

  323. FAmount4 Decimal(28,10),

  324. FQty5 Decimal(28,10),

  325. FCUUnitQty5 Decimal(28,10),

  326. FAmount5 Decimal(28,10),

  327. FQty6 Decimal(28,10),

  328. FCUUnitQty6 Decimal(28,10),

  329. FAmount6 Decimal(28,10),

  330. FSumSort smallint not null Default(0),

  331. Flevel0 Decimal(10,3),

  332. Flevel1 Decimal(10,3),

  333. Flevel2 Decimal(10,3),

  334. Flevel3 Decimal(10,3),

  335. Flevel4 Decimal(10,3),

  336. Flevel5 Decimal(10,3),

  337. Flevel6 Decimal(10,3),

  338.  

  339. FID int IDENTITY)

  340.  

  341. INSERT INTO #DATA (

  342. FNumber,FShortNumber,FName,FModel,FUnitName,FCUUnitName,

  343. FQtyDecimal,FPriceDecimal,FQty,FCUUnitQty,FPrice,FCUPrice,FAmount,FQty1,

  344. FCUUnitQty1,

  345. FAmount1,

  346. FQty2,

  347. FCUUnitQty2,

  348. FAmount2,

  349. FQty3,

  350. FCUUnitQty3,

  351. FAmount3,

  352. FQty4,

  353. FCUUnitQty4,

  354. FAmount4,

  355. FQty5,

  356. FCUUnitQty5,

  357. FAmount5,

  358. FQty6,

  359. FCUUnitQty6,

  360. FAmount6,

  361. FSumSort)

  362. SELECT CASE WHEN GROUPING(t1.FNumber)=1 THEN '合计'

  363. ELSE t1.FNumber END,

  364. '','','','','',MAX(t1.FQtyDecimal),MAX(t1.FPriceDecimal),Sum(FQty),Sum(FCUUnitQty), case Sum(FQty) when 0 then 0 else Sum(FAmount)/Sum(FQty) end,(CASE Sum(FCUUnitQty) WHEN 0 THEN 0 ELSE Sum(FAmount)/Sum(FCUUnitQty) END), sum(FAmount), SUM(FQty1),

  365. SUM(FCUUnitQty1),

  366. SUM(FAmount1),

  367. SUM(FQty2),

  368. SUM(FCUUnitQty2),

  369. SUM(FAmount2),

  370. SUM(FQty3),

  371. SUM(FCUUnitQty3),

  372. SUM(FAmount3),

  373. SUM(FQty4),

  374. SUM(FCUUnitQty4),

  375. SUM(FAmount4),

  376. SUM(FQty5),

  377. SUM(FCUUnitQty5),

  378. SUM(FAmount5),

  379. SUM(FQty6),

  380. SUM(FCUUnitQty6),

  381. SUM(FAmount6),

  382.  

  383. CASE WHEN GROUPING(t1.FNumber)=1 THEN 101

  384. ELSE 0 END FROM #Happen v2

  385. Inner Join t_ICItem t1 On v2.FItemID=t1.FItemID

  386. Left Join t_Stock t2 On v2.FStockID=t2.FItemID

  387. Where 1=1

  388.  
  389.  
  390.  
  391.  

  392. Group by t1.FNumber WITH ROLLUP Having Sum(FQty)>0

  393.  

  394. Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel, t1.FUnitName=t3.FName,t1.FCUUnitName=t4.FName ,t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal From #DATA t1,t_ICItem t2,t_MeasureUnit t3,t_MeasureUnit t4 Where t1.FNumber=t2.FNumber And t2.FUnitGroupID=t3.FUnitGroupID And t2.FStoreUnitID=t4.FMeasureUnitID And t3.FStandard=1

  395. --SELECT * FROM #DATA

  396. SELECT FNumber 物料编码,FName 物料名称,FUnitName 基本计量单位,FCUUnitName 库存单位,FQty 库存数量,FPrice 单价,FAmount 金额,

  397. Fqty1 '0-29天数量',fAmount1 '0-29天金额',Fqty2 '30-59天数量',fAmount2 '30-59天金额',Fqty3 '60-89天数量',fAmount3 '60-89天金额',Fqty4 '90-179天数量',fAmount4 '90-179天金额',Fqty5 '180-359天数量',fAmount5 '180-359天金额',Fqty6 '360天以上数量',fAmount6 '360天以上金额' FROM #DATA

  398.  

  399. DROP TABLE #DATA

  400.  

  401. Drop Table #Happen

  402. Drop Table #Happen1

  403. Drop Table #Happen2

  404. DROP TABLE #INVENTORY

  405. GO

 

 

效果图：

宝贝啊！
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
k3坏账处理之账龄分析法
07-20
k3坏账处理之账龄分析法
金蝶K3库存账龄分析表（优化）
mamengna的博客
12-28 583
Set NoCount On SET ANSI_WARNINGS OFF Create Table #Happen2( FItemID int Null, FStockID int Null, FBatchNo NVARCHAR(200), FQty decimal(28,10) Null, FCUUnitQty decimal(28,10) Null, FQty1 Decimal(28,10), ...
金蝶K3 SQL报表系列-库存账龄分析
mamengna的博客
12-27 1757
1、创建存储过程：sql代码如下： create procedure [dbo].[rk_sp_stock_Age] @paraYear int, @parePeriod int as declare @year int declare @period int declare @Fdate datetime --declare @paraYear int --declare @parePe...
金蝶云星空库存账龄分析表(SQL)
01-16
金蝶云星空库存账龄分析表(SQL)
金蝶K3 V14.2合并报表系统.pdf
07-04
本手册包含的内容可以帮助您进行有效的合并报表系统使用，主要包括 以下内容： 1 系统总述 2 操作建议 3 详细的操作流程 4 系统的功能和特点 5 系统设置 在手册的前言部分还向您提供了获得其它帮助信息的途径及编写...
金蝶K3报表平台源代码主界面
最新发布
07-05
金蝶K3报表平台源代码主界面
金蝶K3 WISE报表系统用户手册
04-29
金蝶K3 WISE报表系统是一款由金蝶软件(中国)有限公司开发的专业企业报表管理工具。该系统旨在帮助企业高效编制和管理各类报表，提供详细的操作流程、系统功能和特点介绍，以及必要的系统设置指南。本手册面向具有...
金蝶K3 V12.1BOS万能报表工具用户手册.pdf
09-21
金蝶K3 V12.1BOS万能报表工具用户手册.pdf
用友U8自定义库龄分析报表
03-29
用友U8自带的库龄分析报表中，若物料进行了调拨，则库龄又进行了重新计算，该自定义库龄分析报表，体现出物料的现存量，最后的采购、委外、其他入库时间。
[SBO](转)SQL_查询语句(库龄分析报表)
陈和樟(Sunny) – SAP B1 专栏
09-24 3739
SELECT T0.ItemCode, T0.BatchNum, T0.ItemName, T0.Quantity, T0.CreateDate <br />,'0-30'=case when T0.CreateDate >= getdate()-30 then T0.Quantity else 0 end<br />,'31-60'=case when T0.CreateDate >= getdate()-60 and T0.CreateDate < getdate()<br />-30 then T0.
oracle 库存账龄分析,库存账龄分析表
weixin_36179469的博客
04-06 378
在本地按照我了解的方法，对库存账龄分析报表进行了扩展，达成了添加基础资料属性的效果。完整步骤如下(楼主缺少步骤一、步骤二)：步骤一：重新开发库存账龄分析报表的插件要点：1. 需从报表原插件类派生自定义报表插件，这样就可以重用功能，仅仅修改取数服务，减少二开插件工作量；2. 重载BuilderReportSqlAndTempTable事件，在原报表临时表的基础上，关联基础资料表格，输出基础资料属性；...
库存账龄报表开发总结——Sqlserver LAG()的使用
郎才女喵的博客
12-20 1976
库存账龄报表开发总结——Sqlserver LAG()的使用 开发需求 根据出入库流水明细，查询对应产品库存状态：包含库龄、库存量及其金额等。 注意:出库按照先入先出的规则进行。 例如: 物品 数量 日期 A 10 2020-01-01 A 20 2020-02-01 A 30 2020-03-03 A -5 2020-04-10 A -35 2020-05-15 A 300 2020-06-18 A -60 2020-11-10 A 90 2020-11-3
SQL Server 中 SET NOCOUNT 的作用和影响
励志做最业余的专业博主，控件产品可以私我~
07-28 884
在本文中，我们探讨了 SET NOCOUNT 在 SQL Server 中的作用和重要性~
通过SET NOCOUNT ON 命令使触发器具有更好的性能
qinghecool的专栏
09-27 3024
 通过SET NOCOUNT ON 命令使触发器具有更好的性能  触发器使得表和跨数据库之间数据的自动同步行为变得简单。但是，那些一眼看上去不是很明显的东西却会对它们的性能造成显著的影响。这里我将描述一个命令，它可用来减少一些不可预料的问题产生的影响 。　　以反复被触发的INSERT触发器为例，尤其是当使用INSERT INTO语句用来进行大量插入操作时。在语句执行的过程中
关于SET NOCOUNT ON
weixin_34221332的博客
08-13 102
对于SQL而言通常会在存储过程、触发器、函数，开头SET NOCOUNT ON，然后末尾再来个SET NOCOUNT OFF，SQL联机帮助中说： 当 SET NOCOUNT 为 ON 时，将不给客户端发送存储过程中的每个语句的 DONE_IN_PROC 信息。当使用 Microsoft® SQL Server™ 提供的实用工具执行查询时，在 Transact-SQL 语句（如 SELECT、I...
SQLServer 存储过程 SET NOCOUNT { ON | OFF } 的使用。
热门推荐
09-12 2万+
使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息 语法：SET NOCOUNT { ON | OFF } 默认情况下为 OFF 当 SET NOCOUNT 为 ON 时，不返回计数（表示受Transact-SQL 语句影响的行数）。 当 SET NOCOUNT 为 OFF 时，返回计数。 即使当 SET NOCOUNT 为 ON 时，也更新 @@ROWCOUNT

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值