碰到一个现存量和库存台账不一致的问题，所以跟踪了下后台收发存汇总表的脚本，用来对比具体有多少个存货存在差异，如下：
1 --暂未考虑精度问题，例子默认两位精度，如果是其他精度需要修改对应类型转换的精度，要不然会导致和现存量不一致，也可以从设置表取出精度设置信息，作为参数传入，更加通用 2 DECLARE @dsdate DATE,@dbdate DATE,@dedate DATE,@iyear INT,@iperiod INT 3 SET @dsdate=‘2014-01-01‘--模块启用日期,如果有帐套卸出还是以最初的启用日期 4 SET @dbdate=‘2015-01-01‘--查询开始日期 5 SET @dedate=‘2015-01-31‘--查询结束日期 6 SET @iyear=2014--帐套启用年度 7 SET @iperiod=12--帐套启用月份 8 IF OBJECT_ID(‘tempdb..#a‘) IS NOT NULL 9 BEGIN 10 DROP TABLE #a 11 END 12 IF OBJECT_ID(‘tempdb..#b‘) IS NOT NULL 13 BEGIN 14 DROP TABLE #b 15 END 16 IF OBJECT_ID(‘tempdb..#c‘) IS NOT NULL 17 BEGIN 18 DROP TABLE #c 19 END 20 IF OBJECT_ID(‘tempdb..#d‘) IS NOT NULL 21 BEGIN 22 DROP TABLE #d 23 END 24 IF OBJECT_ID(‘tempdb..#rdrecord01‘) IS NOT NULL 25 BEGIN 26 DROP TABLE #rdrecord01 27 END 28 IF OBJECT_ID(‘tempdb..#rdrecord08‘) IS NOT NULL 29 BEGIN 30 DROP TABLE #rdrecord08 31 END 32 IF OBJECT_ID(‘tempdb..#rdrecord09‘) IS NOT NULL 33 BEGIN 34 DROP TABLE #rdrecord09 35 END 36 IF OBJECT_ID(‘tempdb..#rdrecord10‘) IS NOT NULL 37 BEGIN 38 DROP TABLE #rdrecord10 39 END 40 IF OBJECT_ID(‘tempdb..#rdrecord32‘) IS NOT NULL 41 BEGIN 42 DROP TABLE #rdrecord32 43 END 44 IF OBJECT_ID(‘tempdb..#rdrecord34‘) IS NOT NULL 45 BEGIN 46 DROP TABLE #rdrecord34 47 END 48 IF OBJECT_ID(‘tempdb..#rdrecord11‘) IS NOT NULL 49 BEGIN 50 DROP TABLE #rdrecord11 51 END 52 select id into #rdrecord01 from rdrecord01 RdRecord where 1=1 and 53 ((RdRecord.dDate < @dsdate And IsNull(RdRecord.bIsSTQc,0) = 1) 54 Or (RdRecord.dDate >= @dsdate 55 And IsNull(RdRecord.bPUFirst,0) = 0 56 And IsNull(RdRecord.bIAFirst,0) = 0 57 And IsNull(RdRecord.bOMFirst,0) = 0 58 And (not (RdRecord.cBusType = N‘假退料‘and RdRecord.cVouchType = 11)) 59 ) 60 ) AND dDate <= @dedate AND dDate >= @dbdate 61 62 select id into #rdrecord08 from rdrecord08 RdRecord where 1=1 and ((RdRecord.dDate < @dsdate And IsNull(RdRecord.bIsSTQc,0) = 1) 63 Or (RdRecord.dDate >= @dsdate 64 And IsNull(RdRecord.bPUFirst,0) = 0 65 And IsNull(RdRecord.bIAFirst,0) = 0 66 And IsNull(RdRecord.bOMFirst,0) = 0 67 And (not (RdRecord.cBusType = N‘假退料‘and RdRecord.cVouchType = 11)) 68 ) 69 ) AND dDate <= @dedate AND dDate >= @dbdate 70 71 select id into #rdrecord09 from rdrecord09 RdRecord where 1=1 and ((RdRecord.dDate < @dsdate And IsNull(RdRecord.bIsSTQc,0) = 1) 72 Or (RdRecord.dDate >= @dsdate 73 And IsNull(RdRecord.bPUFirst,0) = 0 74 And IsNull(RdRecord.bIAFirst,0) = 0 75 And IsNull(RdRecord.bOMFirst,0) = 0 76 And (not (RdRecord.cBusType = N‘假退料‘and RdRecord.cVouchType = 11)) 77 ) 78 ) AND dDate <= @dedate AND dDate >= @dbdate 79 80 select id into #rdrecord10 from rdrecord10 RdRecord where 1=1 and ((RdRecord.dDate < @dsdate And IsNull(RdRecord.bIsSTQc,0) = 1) 81 Or (RdRecord.dDate >= @dsdate 82 And IsNull(RdRecord.bPUFirst,0) = 0 83 And IsNull(RdRecord.bIAFirst,0) = 0 84 And IsNull(RdRecord.bOMFirst,0) = 0 85 And (not (RdRecord.cBusType = N‘假退料‘and RdRecord.cVouchType = 11)) 86 ) 87 ) AND dDate <= @dedate AND dDate >= @dbdate 88 89 select id into #rdrecord11 from rdrecord11 RdRecord where 1=1 and ((RdRecord.dDate < @dsdate And IsNull(RdRecord.bIsSTQc,0) = 1) 90 Or (RdRecord.dDate >= @dsdate 91 And IsNull(RdRecord.bPUFirst,0) = 0 92 And IsNull(RdRecord.bIAFirst,0) = 0 93 And IsNull(RdRecord.bOMFirst,0) = 0 94 And (not (RdRecord.cBusType = N‘假退料‘and RdRecord.cVouchType = 11)) 95 ) 96 ) AND dDate <= @dedate AND dDate >= @dbdate 97 98 select id into #rdrecord32 from rdrecord32 RdRecord where 1=1 and ((RdRecord.dDate < @dsdate And IsNull(RdRecord.bIsSTQc,0) = 1) 99 Or (RdRecord.dDate >= @dsdate 100 And IsNull(RdRecord.bPUFirst,0) = 0 101 And IsNull(RdRecord.bIAFirst,0) = 0 102 And IsNull(RdRecord.bOMFirst,0) = 0 103 And (not (RdRecord.cBusType = N‘假退料‘and RdRecord.cVouchType = 11)) 104 ) 105 ) AND dDate <= @dedate AND dDate >= @dbdate 106 107 select id into #rdrecord34 from rdrecord34 RdRecord where 1=1 and ((RdRecord.dDate < @dsdate And IsNull(RdRecord.bIsSTQc,0) = 1) 108 Or (RdRecord.dDate >= @dsdate 109 And IsNull(RdRecord.bPUFirst,0) = 0 110 And IsNull(RdRecord.bIAFirst,0) = 0 111 And IsNull(RdRecord.bOMFirst,0) = 0 112 And (not (RdRecord.cBusType = N‘假退料‘and RdRecord.cVouchType = 11)) 113 ) 114 ) AND dDate <= @dedate AND dDate >= @dbdate 115 116 Select rdrecord.cwhcode,warehouse.cWhName,inventory.cInvCCode ,cInvCName ,rdrecords.cinvcode,IsNull(cItem_class,N‘‘) AS cItem_class , 117 IsNull(cItemCName,N‘‘) AS cItemCName ,IsNull(cItemCode,N‘‘) AS cItemCode ,IsNull(cName,N‘‘) As cName,RdRecord.bRdFlag, 118 isnull(RdRecord.cDefine3,N‘‘) as cDefine3,isnull(RdRecord.cDefine1,N‘‘) as cDefine1,isnull(RdRecord.cDefine11,N‘‘) as cDefine11,isnull(cFree9,N‘‘) as cFree9,isnull(cFree10,N‘‘) as cFree10, 119 Left(RdRecord.cRdCode,3) As cRdCode, 120 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 121 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ELSE 0 END) * convert(decimal(38,8),iquantity) as iquantity, 122 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 123 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iNum, 124 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 0 else 125 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END END) * convert(decimal(38,8),iquantity) as iQCJCSL, 126 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 0 else 127 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iQCJCJS, 128 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 129 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE 0 END ELSE 0 END) * convert(decimal(38,8),iquantity) as iInQuantity, 130 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 131 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE 0 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iinNum, 132 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 133 CASE WHEN RdRecord.bRdFlag <>0 THEN 0 ELSE 1 END ELSE 0 END) * convert(decimal(38,8),iquantity) as ioutQuantity, 134 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 135 CASE WHEN RdRecord.bRdFlag <>0 THEN 0 ELSE 1 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as ioutNum, 136 ( CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ) * convert(decimal(38,8),iquantity) as iQMJCSL, 137 ( CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iQMJCJS, 138 convert(decimal (38,6),IsNull(Inventory.iInvSPrice,0)) as iUnitCost, CU_F.cComUnitName as cInvA_Unit , (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 1 else 0 end) as bBQFS , 139 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then case when brdflag=0 then 1 else 0 end else 0 end) as bBQCK , 140 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then case when brdflag=1 then 1 else 0 end else 0 end) as bBQRK , 141 isnull(Left(RdRecord.cRdCode,3) ,N‘‘)+ convert(nvarchar(1),brdflag) as newrdflag 142 INTO #a 143 from 144 rdrecord01 rdrecord left join rdrecords01 rdrecords on rdrecord.id=rdrecords.id 145 left join inventory on rdrecords.cinvcode=inventory.cinvcode 146 left join warehouse on RdRecord.cWhCode = Warehouse.cWhCode 147 left JOIN InventoryClass On Inventory.cInvCCode = InventoryClass.cInvCCode 148 left join department on rdrecord.cdepcode =department.cdepcode 149 left join ComputationUnit CU_F on Inventory.cSTComUnitCode = CU_F.cComUnitCode 150 where 1=0 151 152 insert into #a Select rdrecord.cwhcode,warehouse.cWhName,inventory.cInvCCode ,cInvCName ,rdrecords.cinvcode,IsNull(cItem_class,N‘‘) AS cItem_class , 153 IsNull(cItemCName,N‘‘) AS cItemCName ,IsNull(cItemCode,N‘‘) AS cItemCode ,IsNull(cName,N‘‘) As cName,RdRecord.bRdFlag, 154 isnull(RdRecord.cDefine3,N‘‘) as cDefine3,isnull(RdRecord.cDefine1,N‘‘) as cDefine1,isnull(RdRecord.cDefine11,N‘‘) as cDefine11,isnull(cFree9,N‘‘) as cFree9,isnull(cFree10,N‘‘) as cFree10, 155 Left(RdRecord.cRdCode,3) As cRdCode, 156 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 157 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ELSE 0 END) * convert(decimal(38,8),iquantity) as iquantity, 158 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 159 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iNum, 160 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 0 else 161 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END END) * convert(decimal(38,8),iquantity) as iQCJCSL, 162 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 0 else 163 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iQCJCJS, 164 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 165 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE 0 END ELSE 0 END) * convert(decimal(38,8),iquantity) as iInQuantity, 166 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 167 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE 0 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iinNum, 168 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 169 CASE WHEN RdRecord.bRdFlag <>0 THEN 0 ELSE 1 END ELSE 0 END) * convert(decimal(38,8),iquantity) as ioutQuantity, 170 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 171 CASE WHEN RdRecord.bRdFlag <>0 THEN 0 ELSE 1 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as ioutNum, 172 ( CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ) * convert(decimal(38,8),iquantity) as iQMJCSL, 173 ( CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iQMJCJS, 174 convert(decimal (38,6),IsNull(Inventory.iInvSPrice,0)) as iUnitCost, CU_F.cComUnitName as cInvA_Unit , (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 1 else 0 end) as bBQFS , 175 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then case when brdflag=0 then 1 else 0 end else 0 end) as bBQCK , 176 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then case when brdflag=1 then 1 else 0 end else 0 end) as bBQRK , 177 isnull(Left(RdRecord.cRdCode,3) ,N‘‘)+ convert(nvarchar(1),brdflag) as newrdflag 178 from 179 rdrecord01 rdrecord left join rdrecords01 rdrecords on rdrecord.id=rdrecords.id 180 left join inventory on rdrecords.cinvcode=inventory.cinvcode 181 left join warehouse on RdRecord.cWhCode = Warehouse.cWhCode 182 left JOIN InventoryClass On Inventory.cInvCCode = InventoryClass.cInvCCode 183 left join department on rdrecord.cdepcode =department.cdepcode 184 left join ComputationUnit CU_F on Inventory.cSTComUnitCode = CU_F.cComUnitCode 185 where 186 rdrecord.id in (select id from #rdrecord01 ) 187 188 insert into #a Select rdrecord.cwhcode,warehouse.cWhName,inventory.cInvCCode ,cInvCName ,rdrecords.cinvcode,IsNull(cItem_class,N‘‘) AS cItem_class , 189 IsNull(cItemCName,N‘‘) AS cItemCName ,IsNull(cItemCode,N‘‘) AS cItemCode ,IsNull(cName,N‘‘) As cName,RdRecord.bRdFlag, 190 isnull(RdRecord.cDefine3,N‘‘) as cDefine3,isnull(RdRecord.cDefine1,N‘‘) as cDefine1,isnull(RdRecord.cDefine11,N‘‘) as cDefine11,isnull(cFree9,N‘‘) as cFree9,isnull(cFree10,N‘‘) as cFree10, 191 Left(RdRecord.cRdCode,3) As cRdCode, 192 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 193 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ELSE 0 END) * convert(decimal(38,8),iquantity) as iquantity, 194 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 195 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iNum, 196 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 0 else 197 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END END) * convert(decimal(38,8),iquantity) as iQCJCSL, 198 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 0 else 199 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iQCJCJS, 200 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 201 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE 0 END ELSE 0 END) * convert(decimal(38,8),iquantity) as iInQuantity, 202 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 203 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE 0 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iinNum, 204 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 205 CASE WHEN RdRecord.bRdFlag <>0 THEN 0 ELSE 1 END ELSE 0 END) * convert(decimal(38,8),iquantity) as ioutQuantity, 206 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 207 CASE WHEN RdRecord.bRdFlag <>0 THEN 0 ELSE 1 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as ioutNum, 208 ( CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ) * convert(decimal(38,8),iquantity) as iQMJCSL, 209 ( CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iQMJCJS, 210 convert(decimal (38,6),IsNull(Inventory.iInvSPrice,0)) as iUnitCost, CU_F.cComUnitName as cInvA_Unit , (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 1 else 0 end) as bBQFS , 211 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then case when brdflag=0 then 1 else 0 end else 0 end) as bBQCK , 212 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then case when brdflag=1 then 1 else 0 end else 0 end) as bBQRK , 213 isnull(Left(RdRecord.cRdCode,3) ,N‘‘)+ convert(nvarchar(1),brdflag) as newrdflag 214 from 215 rdrecord08 rdrecord left join rdrecords08 rdrecords on rdrecord.id=rdrecords.id 216 left join inventory on rdrecords.cinvcode=inventory.cinvcode 217 left join warehouse on RdRecord.cWhCode = Warehouse.cWhCode 218 left JOIN InventoryClass On Inventory.cInvCCode = InventoryClass.cInvCCode 219 left join department on rdrecord.cdepcode =department.cdepcode 220 left join ComputationUnit CU_F on Inventory.cSTComUnitCode = CU_F.cComUnitCode 221 where 222 rdrecord.id in (select id from #rdrecord08 ) 223 224 insert into #a Select rdrecord.cwhcode,warehouse.cWhName,inventory.cInvCCode ,cInvCName ,rdrecords.cinvcode,IsNull(cItem_class,N‘‘) AS cItem_class , 225 IsNull(cItemCName,N‘‘) AS cItemCName ,IsNull(cItemCode,N‘‘) AS cItemCode ,IsNull(cName,N‘‘) As cName,RdRecord.bRdFlag, 226 isnull(RdRecord.cDefine3,N‘‘) as cDefine3,isnull(RdRecord.cDefine1,N‘‘) as cDefine1,isnull(RdRecord.cDefine11,N‘‘) as cDefine11,isnull(cFree9,N‘‘) as cFree9,isnull(cFree10,N‘‘) as cFree10, 227 Left(RdRecord.cRdCode,3) As cRdCode, 228 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 229 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ELSE 0 END) * convert(decimal(38,8),iquantity) as iquantity, 230 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 231 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iNum, 232 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 0 else 233 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END END) * convert(decimal(38,8),iquantity) as iQCJCSL, 234 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 0 else 235 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iQCJCJS, 236 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 237 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE 0 END ELSE 0 END) * convert(decimal(38,8),iquantity) as iInQuantity, 238 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 239 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE 0 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iinNum, 240 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 241 CASE WHEN RdRecord.bRdFlag <>0 THEN 0 ELSE 1 END ELSE 0 END) * convert(decimal(38,8),iquantity) as ioutQuantity, 242 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 243 CASE WHEN RdRecord.bRdFlag <>0 THEN 0 ELSE 1 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as ioutNum, 244 ( CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ) * convert(decimal(38,8),iquantity) as iQMJCSL, 245 ( CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iQMJCJS, 246 convert(decimal (38,6),IsNull(Inventory.iInvSPrice,0)) as iUnitCost, CU_F.cComUnitName as cInvA_Unit , (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 1 else 0 end) as bBQFS , 247 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then case when brdflag=0 then 1 else 0 end else 0 end) as bBQCK , 248 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then case when brdflag=1 then 1 else 0 end else 0 end) as bBQRK , 249 isnull(Left(RdRecord.cRdCode,3) ,N‘‘)+ convert(nvarchar(1),brdflag) as newrdflag 250 from 251 rdrecord09 rdrecord left join rdrecords09 rdrecords on rdrecord.id=rdrecords.id 252 left join inventory on rdrecords.cinvcode=inventory.cinvcode 253 left join warehouse on RdRecord.cWhCode = Warehouse.cWhCode 254 255 left JOIN InventoryClass On Inventory.cInvCCode = InventoryClass.cInvCCode 256 left join department on rdrecord.cdepcode =department.cdepcode 257 left join ComputationUnit CU_F on Inventory.cSTComUnitCode = CU_F.cComUnitCode 258 where 259 rdrecord.id in (select id from #rdrecord09 ) 260 261 insert into #a Select rdrecord.cwhcode,warehouse.cWhName,inventory.cInvCCode ,cInvCName ,rdrecords.cinvcode,IsNull(cItem_class,N‘‘) AS cItem_class , 262 IsNull(cItemCName,N‘‘) AS cItemCName ,IsNull(cItemCode,N‘‘) AS cItemCode ,IsNull(cName,N‘‘) As cName,RdRecord.bRdFlag, 263 isnull(RdRecord.cDefine3,N‘‘) as cDefine3,isnull(RdRecord.cDefine1,N‘‘) as cDefine1,isnull(RdRecord.cDefine11,N‘‘) as cDefine11,isnull(cFree9,N‘‘) as cFree9,isnull(cFree10,N‘‘) as cFree10, 264 Left(RdRecord.cRdCode,3) As cRdCode, 265 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 266 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ELSE 0 END) * convert(decimal(38,8),iquantity) as iquantity, 267 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 268 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iNum, 269 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 0 else 270 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END END) * convert(decimal(38,8),iquantity) as iQCJCSL, 271 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 0 else 272 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iQCJCJS, 273 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 274 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE 0 END ELSE 0 END) * convert(decimal(38,8),iquantity) as iInQuantity, 275 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 276 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE 0 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iinNum, 277 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 278 CASE WHEN RdRecord.bRdFlag <>0 THEN 0 ELSE 1 END ELSE 0 END) * convert(decimal(38,8),iquantity) as ioutQuantity, 279 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 280 CASE WHEN RdRecord.bRdFlag <>0 THEN 0 ELSE 1 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as ioutNum, 281 ( CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ) * convert(decimal(38,8),iquantity) as iQMJCSL, 282 ( CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iQMJCJS, 283 convert(decimal (38,6),IsNull(Inventory.iInvSPrice,0)) as iUnitCost, CU_F.cComUnitName as cInvA_Unit , (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 1 else 0 end) as bBQFS , 284 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then case when brdflag=0 then 1 else 0 end else 0 end) as bBQCK , 285 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then case when brdflag=1 then 1 else 0 end else 0 end) as bBQRK , 286 isnull(Left(RdRecord.cRdCode,3) ,N‘‘)+ convert(nvarchar(1),brdflag) as newrdflag 287 from 288 rdrecord10 rdrecord left join rdrecords10 rdrecords on rdrecord.id=rdrecords.id 289 left join inventory on rdrecords.cinvcode=inventory.cinvcode 290 left join warehouse on RdRecord.cWhCode = Warehouse.cWhCode 291 292 left JOIN InventoryClass On Inventory.cInvCCode = InventoryClass.cInvCCode 293 left join department on rdrecord.cdepcode =department.cdepcode 294 left join ComputationUnit CU_F on Inventory.cSTComUnitCode = CU_F.cComUnitCode 295 where 296 rdrecord.id in (select id from #rdrecord10 ) 297 298 insert into #a Select rdrecord.cwhcode,warehouse.cWhName,inventory.cInvCCode ,cInvCName ,rdrecords.cinvcode,IsNull(cItem_class,N‘‘) AS cItem_class , 299 IsNull(cItemCName,N‘‘) AS cItemCName ,IsNull(cItemCode,N‘‘) AS cItemCode ,IsNull(cName,N‘‘) As cName,RdRecord.bRdFlag, 300 isnull(RdRecord.cDefine3,N‘‘) as cDefine3,isnull(RdRecord.cDefine1,N‘‘) as cDefine1,isnull(RdRecord.cDefine11,N‘‘) as cDefine11,isnull(cFree9,N‘‘) as cFree9,isnull(cFree10,N‘‘) as cFree10, 301 Left(RdRecord.cRdCode,3) As cRdCode, 302 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 303 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ELSE 0 END) * convert(decimal(38,8),iquantity) as iquantity, 304 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 305 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iNum, 306 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 0 else 307 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END END) * convert(decimal(38,8),iquantity) as iQCJCSL, 308 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 0 else 309 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iQCJCJS, 310 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 311 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE 0 END ELSE 0 END) * convert(decimal(38,8),iquantity) as iInQuantity, 312 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 313 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE 0 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iinNum, 314 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 315 CASE WHEN RdRecord.bRdFlag <>0 THEN 0 ELSE 1 END ELSE 0 END) * convert(decimal(38,8),iquantity) as ioutQuantity, 316 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 317 CASE WHEN RdRecord.bRdFlag <>0 THEN 0 ELSE 1 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as ioutNum, 318 ( CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ) * convert(decimal(38,8),iquantity) as iQMJCSL, 319 ( CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iQMJCJS, 320 convert(decimal (38,6),IsNull(Inventory.iInvSPrice,0)) as iUnitCost, CU_F.cComUnitName as cInvA_Unit , (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 1 else 0 end) as bBQFS , 321 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then case when brdflag=0 then 1 else 0 end else 0 end) as bBQCK , 322 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then case when brdflag=1 then 1 else 0 end else 0 end) as bBQRK , 323 isnull(Left(RdRecord.cRdCode,3) ,N‘‘)+ convert(nvarchar(1),brdflag) as newrdflag 324 from 325 rdrecord11 rdrecord left join rdrecords11 rdrecords on rdrecord.id=rdrecords.id 326 left join inventory on rdrecords.cinvcode=inventory.cinvcode 327 left join warehouse on RdRecord.cWhCode = Warehouse.cWhCode 328 329 left JOIN InventoryClass On Inventory.cInvCCode = InventoryClass.cInvCCode 330 left join department on rdrecord.cdepcode =department.cdepcode 331 left join ComputationUnit CU_F on Inventory.cSTComUnitCode = CU_F.cComUnitCode 332 where 333 rdrecord.id in (select id from #rdrecord11 ) 334 335 insert into #a Select rdrecord.cwhcode,warehouse.cWhName,inventory.cInvCCode ,cInvCName ,rdrecords.cinvcode,IsNull(cItem_class,N‘‘) AS cItem_class , 336 IsNull(cItemCName,N‘‘) AS cItemCName ,IsNull(cItemCode,N‘‘) AS cItemCode ,IsNull(cName,N‘‘) As cName,RdRecord.bRdFlag, 337 isnull(RdRecord.cDefine3,N‘‘) as cDefine3,isnull(RdRecord.cDefine1,N‘‘) as cDefine1,isnull(RdRecord.cDefine11,N‘‘) as cDefine11,isnull(cFree9,N‘‘) as cFree9,isnull(cFree10,N‘‘) as cFree10, 338 Left(RdRecord.cRdCode,3) As cRdCode, 339 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 340 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ELSE 0 END) * convert(decimal(38,8),iquantity) as iquantity, 341 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 342 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iNum, 343 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 0 else 344 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END END) * convert(decimal(38,8),iquantity) as iQCJCSL, 345 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 0 else 346 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iQCJCJS, 347 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 348 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE 0 END ELSE 0 END) * convert(decimal(38,8),iquantity) as iInQuantity, 349 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 350 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE 0 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iinNum, 351 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 352 CASE WHEN RdRecord.bRdFlag <>0 THEN 0 ELSE 1 END ELSE 0 END) * convert(decimal(38,8),iquantity) as ioutQuantity, 353 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 354 CASE WHEN RdRecord.bRdFlag <>0 THEN 0 ELSE 1 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as ioutNum, 355 ( CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ) * convert(decimal(38,8),iquantity) as iQMJCSL, 356 ( CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iQMJCJS, 357 convert(decimal (38,6),IsNull(Inventory.iInvSPrice,0)) as iUnitCost, CU_F.cComUnitName as cInvA_Unit , (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 1 else 0 end) as bBQFS , 358 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then case when brdflag=0 then 1 else 0 end else 0 end) as bBQCK , 359 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then case when brdflag=1 then 1 else 0 end else 0 end) as bBQRK , 360 isnull(Left(RdRecord.cRdCode,3) ,N‘‘)+ convert(nvarchar(1),brdflag) as newrdflag 361 from 362 rdrecord32 rdrecord left join rdrecords32 rdrecords on rdrecord.id=rdrecords.id 363 left join inventory on rdrecords.cinvcode=inventory.cinvcode 364 left join warehouse on RdRecord.cWhCode = Warehouse.cWhCode 365 366 left JOIN InventoryClass On Inventory.cInvCCode = InventoryClass.cInvCCode 367 left join department on rdrecord.cdepcode =department.cdepcode 368 left join ComputationUnit CU_F on Inventory.cSTComUnitCode = CU_F.cComUnitCode 369 where 370 rdrecord.id in (select id from #rdrecord32 ) 371 372 insert into #a Select rdrecord.cwhcode,warehouse.cWhName,inventory.cInvCCode ,cInvCName ,rdrecords.cinvcode,IsNull(cItem_class,N‘‘) AS cItem_class , 373 IsNull(cItemCName,N‘‘) AS cItemCName ,IsNull(cItemCode,N‘‘) AS cItemCode ,IsNull(cName,N‘‘) As cName,RdRecord.bRdFlag, 374 isnull(RdRecord.cDefine3,N‘‘) as cDefine3,isnull(RdRecord.cDefine1,N‘‘) as cDefine1,isnull(RdRecord.cDefine11,N‘‘) as cDefine11,isnull(cFree9,N‘‘) as cFree9,isnull(cFree10,N‘‘) as cFree10, 375 Left(RdRecord.cRdCode,3) As cRdCode, 376 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 377 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ELSE 0 END) * convert(decimal(38,8),iquantity) as iquantity, 378 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 379 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iNum, 380 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 0 else 381 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END END) * convert(decimal(38,8),iquantity) as iQCJCSL, 382 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 0 else 383 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iQCJCJS, 384 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 385 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE 0 END ELSE 0 END) * convert(decimal(38,8),iquantity) as iInQuantity, 386 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 387 CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE 0 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iinNum, 388 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 389 CASE WHEN RdRecord.bRdFlag <>0 THEN 0 ELSE 1 END ELSE 0 END) * convert(decimal(38,8),iquantity) as ioutQuantity, 390 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 391 CASE WHEN RdRecord.bRdFlag <>0 THEN 0 ELSE 1 END ELSE 0 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as ioutNum, 392 ( CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ) * convert(decimal(38,8),iquantity) as iQMJCSL, 393 ( CASE WHEN RdRecord.bRdFlag <>0 THEN 1 ELSE -1 END ) * (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else rdrecords.iNum end ) as iQMJCJS, 394 convert(decimal (38,6),IsNull(Inventory.iInvSPrice,0)) as iUnitCost, CU_F.cComUnitName as cInvA_Unit , (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then 1 else 0 end) as bBQFS , 395 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then case when brdflag=0 then 1 else 0 end else 0 end) as bBQCK , 396 (case when ( dDate>=@dbdate and isnull(bisstqc,0)=0) then case when brdflag=1 then 1 else 0 end else 0 end) as bBQRK , 397 isnull(Left(RdRecord.cRdCode,3) ,N‘‘)+ convert(nvarchar(1),brdflag) as newrdflag 398 from 399 rdrecord34 rdrecord left join rdrecords34 rdrecords on rdrecord.id=rdrecords.id 400 left join inventory on rdrecords.cinvcode=inventory.cinvcode 401 left join warehouse on RdRecord.cWhCode = Warehouse.cWhCode 402 403 left JOIN InventoryClass On Inventory.cInvCCode = InventoryClass.cInvCCode 404 left join department on rdrecord.cdepcode =department.cdepcode 405 left join ComputationUnit CU_F on Inventory.cSTComUnitCode = CU_F.cComUnitCode 406 where 407 rdrecord.id in (select id from #rdrecord34 ) 408 409 insert into #a Select warehouse.cwhcode,warehouse.cWhName,inventory.cInvCCode ,cInvCName ,a.cinvcode,IsNull(cItem_class,N‘‘) AS cItem_class , 410 IsNull(cItemCName,N‘‘) AS cItemCName ,IsNull(cItemCode,N‘‘) AS cItemCode ,IsNull(cName,N‘‘) As cName,1 as bRdFlag, 411 isnull(a.cdefine3,N‘‘) as cdefine3,isnull(a.cdefine1,N‘‘) as cdefine1,isnull(a.cdefine11,N‘‘) as cdefine11,isnull(cFree9,N‘‘) as cFree9,isnull(cFree10,N‘‘) as cFree10, 412 Left(a.cRdCode,3) As cRdCode, 413 0 as iquantity, 414 0 as iNum, 415 convert(decimal(38,8),iquantity) as iQCJCSL, 416 (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else a.iNum end ) as iQCJCJS, 417 0 as iInQuantity, 418 0 as iinNum, 419 0 as ioutQuantity, 420 0 as ioutNum, 421 convert(decimal(38,8),iquantity) as iQMJCSL, 422 (case Inventory.iGroupType when 0 then Null when 1 then iquantity/Cu_f.iChangRate else a.iNum end ) as iQMJCJS, 423 convert(decimal (38,6),IsNull(Inventory.iInvSPrice,0)) as iUnitCost, CU_F.cComUnitName as cInvA_Unit , 0 as bBQFS , 424 0 as bBQCK , 425 0 as bBQRK , 426 isnull(Left(cRdCode,3) ,N‘‘)+ convert(nvarchar(1),1) as newrdflag 427 from 428 ST_MonthAccounts a left join inventory on a.cinvcode=inventory.cinvcode 429 left join warehouse on a.cWhCode = Warehouse.cWhCode 430 431 left JOIN InventoryClass On Inventory.cInvCCode = InventoryClass.cInvCCode 432 left join department on a.cdepcode =department.cdepcode left join ComputationUnit CU_F on Inventory.cSTComUnitCode = CU_F.cComUnitCode 433 where iYear = @iyear and iMonth = @iperiod 434 435 Select cWhCode as 仓库编码, cWhName as 仓库名称, a.cInvCode as 存货编码, cInvName as 存货名称,cInvAddCode AS 存货代码, cInvStd as 规格型号, cAddress AS 产地, ComputationUnit.CComUnitName AS 计量单位,cInvA_Unit AS 库存单位, a.cInvCCode ,cInvCName AS 存货分类名称 , cItem_class AS 项目大类编码,cItemCName AS 项目大类名称 ,cItemCode AS 项目编码 ,cName AS 项目名称 , 436 cFree9,cFree10,cInvDefine1,cInvDefine2,cDefine3,cDefine1,cDefine11, 437 iQCJCSL AS 期初结存数量 ,iQCJCJS AS 期初结存件数, convert(decimal(36,2), iQCJCSL * iUnitCost) AS 期初结存金额, 438 iInQuantity as 总计_入库数量,iInNum as 总计_入库件数 , convert(decimal(36,2),iInQuantity*iUnitCost) As 总计_入库金额 , 439 iOutQuantity as 总计_出库数量,iOutNum as 总计_出库件数 , convert(decimal(36,2),iOutQuantity*iUnitCost) As 总计_出库金额 , 440 iQMJCSL AS 期末结存数量,iQMJCJS AS 期末结存件数, convert(decimal(36,2),iQMJCSL*iUnitCost) AS 期末结存金额 , i.igrouptype, ComputationUnit.ichangrate as iinvexchrate, bBQFS,bBQRK,bBQCK,Null as cDefine6,Null as cDefine9,Null as cDefine12,Null as cDefine14,Null as cDefine22,Null as cDefine25,Null as cDefine28,Null as cDefine31,Null as cDefine34,Null as cDefine37,Null as cDefine2,Null as cDefine4,Null as cDefine5,Null as cDefine7,Null as cDefine8,Null as cDefine10,Null as cInvDefine3,Null as cFree1,Null as cFree2,Null as cFree3,Null as cDefine23,Null as cDefine24,Null as cDefine26,Null as cDefine27,Null as cFree4,Null as cFree5,Null as cFree6,Null as cFree7,Null as cFree8,Null as cDefine13,Null as cDefine15,Null as cDefine16,Null as cDefine29,Null as cDefine30,Null as cDefine32,Null as cDefine33,Null as cDefine35,Null as cDefine36,Null as cInvDefine4,Null as cInvDefine5,Null as cInvDefine6,Null as cInvDefine7,Null as cInvDefine8,Null as cInvDefine9,Null as cInvDefine10,Null as cInvDefine11,Null as cInvDefine12,Null as cInvDefine13,Null as cInvDefine14,Null as cInvDefine15,Null as cInvDefine16 441 INTO #b 442 FROM #a A left join inventory i on a.cinvcode=i.cinvcode left join ComputationUnit ON i.cComUnitCode = ComputationUnit.cComUnitCode 443 444 --SELECT * FROM #b WHERE 存货编码=‘01019002063‘ 445 update a set a.期末结存件数=0.00 from #b a left join inventory i with (nolock) on a.存货编码 = i.cinvcode 446 where i.igrouptype =1 and convert(decimal(38,2),a.期末结存数量) = 0 447 update a set a.期初结存件数=0.00 from #b a left join inventory i with (nolock) on a.存货编码 = i.cinvcode 448 where i.igrouptype =1 and convert(decimal(38,2),a.期初结存数量) = 0 449 450 SELECT [仓库编码],[仓库名称] as [仓库名称],[存货编码],[存货代码] as [存货代码],[存货名称] as [存货名称],[规格型号] as [规格型号],[计量单位] as [计量单位],[库存单位] as [库存单位],[产地] as [产地],[cInvCCode] as [cInvCCode],[存货分类名称] as [存货分类名称],[项目大类编码] as [项目大类编码],[项目大类名称] as [项目大类名称],[项目编码] as [项目编码],[项目名称] as [项目名称],round([期初结存数量],2) as [期初结存数量],round([期初结存件数],2) as [期初结存件数],round([期初结存金额],2) as [期初结存金额],round([总计_入库数量],2) as [总计_入库数量],round([总计_入库件数],2) as [总计_入库件数],round([总计_入库金额],2) as [总计_入库金额],round([总计_出库数量],2) as [总计_出库数量],round([总计_出库件数],2) as [总计_出库件数],round([总计_出库金额],2) as [总计_出库金额],round([期末结存数量],2) as [期末结存数量],round([期末结存件数],2) as [期末结存件数],round([期末结存金额],2) as [期末结存金额],round([bBQFS],2) as [bBQFS],round([bBQCK],2) as [bBQCK],round([bBQRK],2) as [bBQRK],round([igrouptype],2) as [igrouptype],round([iinvexchrate],2) as [iinvexchrate],[cFree9],[cFree10],[cInvDefine1],[cInvDefine2],[cDefine3],[cDefine1],[cDefine11],[cDefine6],[cDefine9],[cDefine12],[cDefine14],[cDefine22],[cDefine25],[cDefine28],[cDefine31],[cDefine34],[cDefine37],[cDefine2],[cDefine4],[cDefine5],[cDefine7],[cDefine8],[cDefine10],[cInvDefine3],[cFree1],[cFree2],[cFree3],[cDefine23],[cDefine24],[cDefine26],[cDefine27],[cFree4],[cFree5],[cFree6],[cFree7],[cFree8],[cDefine13],[cDefine15],[cDefine16],[cDefine29],[cDefine30],[cDefine32],[cDefine33],[cDefine35],[cDefine36],[cInvDefine4],[cInvDefine5],[cInvDefine6],[cInvDefine7],[cInvDefine8],[cInvDefine9],[cInvDefine10],[cInvDefine11],[cInvDefine12],[cInvDefine13],[cInvDefine14],[cInvDefine15],[cInvDefine16] 451 INTO #c 452 FROM #b 453 454 SELECT [仓库编码],max([仓库名称]) as [仓库名称],[存货编码],max([存货代码]) as [存货代码],max([存货名称]) as [存货名称],max([规格型号]) as [规格型号],max([计量单位]) as [计量单位],max([库存单位]) as [库存单位],max([产地]) as [产地],max([cInvCCode]) as [cInvCCode],max([存货分类名称]) as [存货分类名称],max([项目大类编码]) as [项目大类编码],max([项目大类名称]) as [项目大类名称],max([项目编码]) as [项目编码],max([项目名称]) as [项目名称],sum(round([期初结存数量],2)) as [期初结存数量],sum(round([期初结存件数],2)) as [期初结存件数],sum(round([期初结存金额],2)) as [期初结存金额],sum(round([总计_入库数量],2)) as [总计_入库数量],sum(round([总计_入库件数],2)) as [总计_入库件数],sum(round([总计_入库金额],2)) as [总计_入库金额],sum(round([总计_出库数量],2)) as [总计_出库数量],sum(round([总计_出库件数],2)) as [总计_出库件数],sum(round([总计_出库金额],2)) as [总计_出库金额],sum(round([期末结存数量],2)) as [期末结存数量],sum(round([期末结存件数],2)) as [期末结存件数],sum(round([期末结存金额],2)) as [期末结存金额],max(round([bBQFS],2)) as [bBQFS],max(round([bBQCK],2)) as [bBQCK],max(round([bBQRK],2)) as [bBQRK],min([igrouptype]) as [igrouptype],min([iinvexchrate]) as [iinvexchrate] 455 INTO #d 456 FROM #c GROUP BY [仓库编码], [存货编码] 457 458 SELECT * FROM #d 459 WHERE (1 = 1) and (convert(decimal(38,6), IsNull(期初结存数量,0))<>0 or convert(decimal(38,6), IsNull(期末结存数量,0))<>0 or convert(decimal(38,6), IsNull(总计_入库数量,0))<>0 or convert(decimal(38,6), IsNull(总计_出库数量,0))<>0 or convert(decimal(38,6), IsNull(期初结存件数,0))<>0 or convert(decimal(38,6), IsNull(期末结存件数,0))<>0 or convert(decimal(38,6), IsNull(总计_入库件数,0))<>0 or convert(decimal(38,6), IsNull(总计_出库件数,0))<>0 ) 460 AND 存货编码=‘01019002063‘ 461 ORDER BY [仓库编码], [存货编码]