外理多值查询,同时相关业务的复杂处理

原创 2007年09月15日 11:50:00

-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
/*
0:'2007-08-06 00:00:00'
1:b.ShopCode > '005' and a.StorageCode > '005.01' and a.PlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
2:ShopCode > '005' and StorageCode > '005.01' and PlaceCode > '2001' and BrandCode > '01.01' and ModelCode > '01.01.1110'
3:b.TheDate>'2007-08-06 00:00:00' and b.ShopCode > '005' and a.StorageCode > '005.01' and a.PlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
4:b.TheDate>'2007-08-06 00:00:00' and b.ShopCode > '005' and a.StorageCode > '005.01' and a.StorPlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
5:b.TheDate>'2007-08-06 00:00:00' and b.ShopInCode > '005' and a.RedeployInStorage > '005.01' and a.RedeployInPlace > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
6:b.TheDate>'2007-08-06 00:00:00' and c.ShopCode > '005' and b.StorageCode > '005.01' and a.PlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
7:b.TheDate>'2007-08-06 00:00:00' and b.ShopOutCode > '005' and a.RedeployOutStorage > '005.01' and a.RedeployOutPlace > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
8:b.TheDate>'2007-08-06 00:00:00' and c.ShopCode > '005' and a.StorageCode > '005.01' and a.PlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
9:(b.TheDate > @StartDate and b.TheDate <= '2007-08-06 00:00:00') and b.ShopCode > '005' and a.StorageCode > '005.01' and a.PlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
10:(b.TheDate > @StartDate and b.TheDate <= '2007-08-06 00:00:00') and b.ShopCode > '005' and a.StorageCode > '005.01' and a.StorPlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
11:(b.TheDate > @StartDate and b.TheDate <= '2007-08-06 00:00:00') and b.ShopInCode > '005' and a.RedeployInStorage > '005.01' and a.RedeployInPlace > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
12:(b.TheDate > @StartDate and b.TheDate <= '2007-08-06 00:00:00') and c.ShopCode > '005' and b.StorageCode > '005.01' and a.PlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
13:(b.TheDate > @StartDate and b.TheDate <= '2007-08-06 00:00:00') and b.ShopOutCode > '005' and a.RedeployOutStorage > '005.01' and a.RedeployOutPlace > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
14:(b.TheDate > @StartDate and b.TheDate <= '2007-08-06 00:00:00') and c.ShopCode > '005' and a.StorageCode > '005.01' and a.PlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
-------------------------
TheDate>'2007-08-06 00:00:00' and ShopCode > '005' and StorageCode > '005.01' and PlaceCode > '2001' and BrandCode > '01.01' and ModelCode > '01.01.1110'


b.TheDate;b.TheDate;b.TheDate;b.TheDate;b.TheDate;b.TheDate

b.ShopCode;b.ShopCode;b.ShopInCode;c.ShopCode;b.ShopOutCode;c.ShopCode

a.StorageCode;a.StorageCode;a.RedeployInStorage;b.StorageCode;a.RedeployOutStorage;a.StorageCode

a.PlaceCode;a.StorPlaceCode;a.RedeployInPlace;a.PlaceCode;a.RedeployOutPlace;a.PlaceCode

z.BrandCode;z.BrandCode;z.BrandCode;z.BrandCode;z.BrandCode;z.BrandCode

z.ModelCode;z.ModelCode;z.ModelCode;z.ModelCode;z.ModelCode;z.ModelCode
-------------------------------------------------------------
c.ShopCode,a.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode
b.ShopCode,a.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode 9
------------------------------------------------------------
select max(TheDate) from tbCourseStorStorage --?会不会出现2007-08-20 19:9:00  2007-08-20-10:00
*/
/*-------------------------------------------*/
|功能描述:门店营业日报表
|参数说明:
|  编写人:Kern
|编写时间:2007-08-19
|    备注:
|  修改人:szj 2007-08-24
|  增加  :仓库,库位,品牌,型号过滤,
/*-------------------------------------------*/

declare @StartDate datetime
set @StartDate=isnull((select max(TheDate) from tbCourseStorStorage where TheDate<{0}), '1900-1-1')


 select aa.*
 into #aa
                from
 (
  --结存
  select b.ShopCode,a.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode, a.Number,0 StockInNumber,0 OtherInNumber, 0 InNumber, 0 OutNumber, 0 CheckNumber, 0 SaleNumber, 0 SaleMoney
  from tbCourseStorStorage a left outer join vDatumStorageShop b on a.StorageCode=b.TheCode
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where TheDate=@StartDate and b.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {1}
  
 ----期初
  union all

  --销售开单
  select b.ShopCode,a.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode,-a.Number, 0 StockInNumber,0 OtherInNumber,0, 0, 0, 0, 0
  from tbSelfSaleOutItem a left outer join tbSelfSaleOut b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {9}
  union all
 
  --联盟开单
  select b.ShopCode,a.StorageCode,a.StorPlaceCode,z.BrandCode,z.ModelCode,-a.Number,0 StockInNumber,0 OtherInNumber, 0, 0, 0, 0, 0
  from tbSelfLeagueSaleItem a left outer join tbSelfLeagueSale b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {10}
  union all
 
  --调拨入库
  select b.ShopInCode,a.RedeployInStorage,a.RedeployInPlace,z.BrandCode,z.ModelCode, a.Number, 0 StockInNumber,0 OtherInNumber,0, 0, 0, 0, 0
  from tbStorRedeployItem a
  left outer join tbStorRedeploy b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.UseState,0x0010)=1 and b.ShopInCode in {{ShopGroup}} and a.RedeployInStorage in {{StorageGroup}} {11}
  union all
  
  --配送入库
  select b.ShopInCode,a.RedeployInStorage,a.RedeployInPlace,z.BrandCode,z.ModelCode, a.Number,0 StockInNumber,0 OtherInNumber, 0, 0, 0, 0, 0
  from tbStorRedeployLeagueItem a
  left outer join tbStorRedeployLeague b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.UseState,0x0010)=1 and b.ShopInCode in {{ShopGroup}} and a.RedeployInStorage in {{StorageGroup}} {11}
  union all


  --外购入库单
  select c.ShopCode,a.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode, a.Number,0 StockInNumber,0 OtherInNumber, 0, 0, 0, 0, 0
  from tbStorStockInItem a
  left outer join tbStorStockIn b on a.BillNo=b.BillNo
  left outer join vDatumStorageShop c on a.StorageCode=c.TheCode
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.AssistantCode<>'ZGRKCSH' and c.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {14}
  union all

  --其它入库单
  select c.ShopCode,a.StorageCode, a.PlaceCode,z.BrandCode,z.ModelCode, a.Number,0 StockInNumber,0 OtherInNumber, 0, 0, 0, 0, 0
  from tbStorOtherInItem a
  left outer join tbStorOtherIn b on a.BillNo=b.BillNo
  left outer join vDatumStorageShop c on a.StorageCode=c.TheCode
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.AssistantCode<>'DKHX' and c.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {14}
  union all


 
  --盘点
  select c.ShopCode,b.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode, a.Number,0 StockInNumber,0 OtherInNumber, 0, 0, 0, 0, 0
  from tbStorCheckItem a
  left outer join tbStorCheck b on a.BillNo=b.BillNo
  left outer join vDatumStorageShop c on b.StorageCode=c.TheCode
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and c.ShopCode in {{ShopGroup}} and b.StorageCode in {{StorageGroup}} {12}
  union all
 
  --调拨出库
  select b.ShopOutCode,a.RedeployOutStorage,a.RedeployOutPlace,z.BrandCode,z.ModelCode, -a.Number,0 StockInNumber,0 OtherInNumber,0, 0, 0, 0, 0
  from tbStorRedeployItem a
  left outer join tbStorRedeploy b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.ShopOutCode in {{ShopGroup}} and a.RedeployOutStorage in {{StorageGroup}} {13}
  union all
  
  --配送出库
  select b.ShopOutCode,a.RedeployOutStorage,a.RedeployOutPlace,z.BrandCode,z.ModelCode, -a.Number,0 StockInNumber,0 OtherInNumber, 0, 0, 0, 0, 0
  from tbStorRedeployLeagueItem a
  left outer join tbStorRedeployLeague b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.ShopOutCode in {{ShopGroup}} and a.RedeployOutStorage in {{StorageGroup}} {13}
             )aa

 select aa.*
                into #bb
 from
 (

 --本日调入
 
  --调拨入库
  select b.ShopInCode ShopCode,a.RedeployInStorage StorageCode,a.RedeployInPlace Placecode,z.BrandCode,z.ModelCode, 0 Number,0 StockInNumber,0 OtherInNumber, a.Number InNumber, 0 OutNumber, 0 CheckNumber, 0 SaleNumber, 0 SaleMoney
  from tbStorRedeployItem a
  left outer join tbStorRedeploy b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.UseState,0x0010)=1 and b.ShopInCode in {{ShopGroup}} and a.RedeployInStorage in {{StorageGroup}} {5}
  union all
 
  --配送入库
  select b.ShopInCode,a.RedeployInStorage,a.RedeployInPlace,z.BrandCode,z.ModelCode, 0,0,0, a.Number, 0, 0, 0, 0
  from tbStorRedeployLeagueItem a
  left outer join tbStorRedeployLeague b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.UseState,0x0010)=1 and b.ShopInCode in {{ShopGroup}} and a.RedeployInStorage in {{StorageGroup}} {5}
  union all
        --2007-08-24 增加外购入库StockInNumber,其它入库数量OtherInNumber
  --外购入库单
  select c.ShopCode,a.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode,0, a.Number StockInNumber,0, 0, 0, 0, 0, 0
  from tbStorStockInItem a
  left outer join tbStorStockIn b on a.BillNo=b.BillNo
  left outer join vDatumStorageShop c on a.StorageCode=c.TheCode
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.AssistantCode<>'ZGRKCSH' and c.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {8}
  union all

  --其它入库单
  select c.ShopCode,a.StorageCode, a.PlaceCode,z.BrandCode,z.ModelCode,0,0,a.Number OtherInNumber, 0, 0, 0, 0, 0
  from tbStorOtherInItem a
  left outer join tbStorOtherIn b on a.BillNo=b.BillNo
  left outer join vDatumStorageShop c on a.StorageCode=c.TheCode
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.AssistantCode<>'DKHX' and c.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {8}
  union all
 
 --本日调出
 
  --调拨出库
  select b.ShopOutCode,a.RedeployOutStorage,a.RedeployOutPlace,z.BrandCode,z.ModelCode, 0,0,0, 0, a.Number, 0, 0, 0
  from tbStorRedeployItem a
  left outer join tbStorRedeploy b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.ShopOutCode in {{ShopGroup}} and a.RedeployOutStorage in {{StorageGroup}} {7}
  union all
  
  --配送出库
  select b.ShopOutCode,a.RedeployOutStorage,a.RedeployOutPlace,z.BrandCode,z.ModelCode, 0,0,0 , 0, a.Number, 0, 0, 0
  from tbStorRedeployLeagueItem a
  left outer join tbStorRedeployLeague b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.ShopOutCode in {{ShopGroup}} and a.RedeployOutStorage in {{StorageGroup}} {7}
  union all
 
 --本日盈亏数量
 
  --盘点
  select c.ShopCode,b.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode, 0,0,0, 0, 0, a.Number, 0, 0
  from tbStorCheckItem a
  left outer join tbStorCheck b on a.BillNo=b.BillNo
  left outer join vDatumStorageShop c on b.StorageCode=c.TheCode
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and c.ShopCode in {{ShopGroup}} and b.StorageCode in {{StorageGroup}} {6}
  union all
 
  --本日销售数量金额
  
  --销售开单
  select b.ShopCode,a.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode,0,0,0 , 0, 0, 0, a.Number, a.TotalMoney
  from tbSelfSaleOutItem a left outer join tbSelfSaleOut b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {3}
  union all
 
  --联盟开单
  select b.ShopCode,a.StorageCode,a.StorPlaceCode,z.BrandCode,z.ModelCode, 0,0,0, 0, 0, 0, a.Number, a.TotalMoney
  from tbSelfLeagueSaleItem a left outer join tbSelfLeagueSale b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {4}
 )aa

 

select aaa.ShopCode,bbb.TheName ShopName,aaa.StorageCode,eee.TheName StorageName,aaa.PlaceCode,fff.StorPlaceName PlaceName,
aaa.BrandCode,ccc.TheName BrandName,aaa.ModelCode,ddd.TheName ModelName,aaa.Number BeginNumber,aaa.StockInNumber,aaa.OtherInNumber,aaa.InNumber,aaa.OutNumber,aaa.CheckNumber,aaa.SaleNumber,
aaa.SaleMoney,aaa.EndNumber
from
(
 select aa.ShopCode,aa.StorageCode,aa.PlaceCode,aa.BrandCode,aa.ModelCode,sum(isnull(Number, 0)) as Number,sum(isnull(StockInNumber, 0)) as StockInNumber,sum(isnull(OtherInNumber, 0)) as OtherInNumber,sum(isnull(InNumber, 0)) as InNumber,
  sum(isnull(OutNumber, 0)) as OutNumber,sum(isnull(CheckNumber, 0)) as CheckNumber,sum(isnull(SaleNumber, 0)) as SaleNumber,
  sum(isnull(SaleMoney, 0)) as SaleMoney, 
               (sum(isnull(Number, 0)) +sum(isnull(StockInNumber,0))+sum(isnull(OtherInNumber,0))+ sum(isnull(InNumber, 0))+ - sum(isnull(OutNumber, 0)) + sum(isnull(CheckNumber, 0)) - sum(isnull(SaleNumber, 0))) EndNumber
 from
 (
          select #aa.* from #aa
          union all
          select #bb.* from #bb
 ) aa group by aa.ShopCode,aa.StorageCode,aa.PlaceCode,aa.BrandCode,aa.ModelCode
) aaa left outer join tbDatumShop bbb on aaa.ShopCode=bbb.TheCode
left outer join (select TheCode,TheName from tbDatumProductBrand) ccc on aaa.BrandCode=ccc.TheCode
left outer join (select TheCode,TheName from tbDatumProductModel) ddd on aaa.ModelCode=ddd.TheCode
left outer join (select TheCode,TheName from tbDatumStorage) eee on aaa.StorageCode=eee.TheCode
left outer join tbDatumStorageItem fff on aaa.StorageCode=fff.StorageCode and aaa.PlaceCode=fff.StorPlaceCode
Order by aaa.ShopCode,aaa.StorageCode,aaa.PlaceCode,aaa.BrandCode,aaa.ModelCode
--删除临时表
drop table #aa
drop table #bb 

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

[startrelatedarticles]

{relatedtitle}

{relateddes}
[endrelatedarticles] [startrelatedarticlesdownload]

{relatedtitle}

  • {relateposttime}
  • {relatesize}
  • 下载
[endrelatedarticlesdownload] [startrelatedarticlesad1]

{relatedtitle}

{relateddes}
[endrelatedarticlesad1] [startrelatedarticlesad2] [endrelatedarticlesad2]
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)