-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
/*
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
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
