declare @date datetime = '20180405';
With
CTEGoodsLogistic as
(
select gl.WarehouseId, gl.GoodsId, gl.QuantityMin
from GoodsLogistic gl with(NoLock)
where gl.GoodsId in (400318,400319)
and gl.WarehouseId in (34,35,37,132,143,144,157,167,168,173,174,175,176,188,190,198,208,209,219,228,229,230,266,269,270,272,273,274,276,279,291,292,293,294,295,296,297,298,299,300,301,302,303,304,306,308,309,310,311,312,315,317,321,322,323,324,325,331,332,337,338,339,341,342,343,344,347,348,349,350,351,352,354,358,359,361,362,363,364,366,368,369,370,373,375,376,379,380,382,386,387,389,391,392,393,394,397,399,402,403,404,405,408,412,413,414,415,416,417,418,427,428,432,433,434,435,437,438,439,440,442,444,445,448,453,454,455,456,457,458,459,460,462,463,464,465,466,467,468,472,474,475,476,477,478,479,480,481,482,484,485,486,487,488,489,491,495,496,497,498,499,503,507,508,509,511,512,515,518,519,522,525,527,528,529,531,532,534,547,548,549,550,552,554,555,556,557,558,560,561,563,564,566,567,570,571,572,573,574,575,580,581,582,584,585,586,587,589,591,592,594,595,596,598,599,605,607,609,610,613,616,618,622,624,626,627,629,635,636,637,638,639,647,655,657,659,661,662,664,667,668,675,677,678,681,687,689,691,693,700,710,712,714,716,718,720,722,724,726,728,730,732,734,736,738,740,750,760,770,775,777,780,790,804,805,808,814,815,816,818,819,820,821,823,824,833,834,840,841,847,850,852,854,856,858,859,860,861,862,863,864,868,871,872,874,878,882,886,890,891,892,894,895,896,902,910,911,913,915,918,919,922,923,927,929,933,934,935,946,948,949,950,952,955,956,958,959,962,963,964,966,977,978,983,984,986,999,6004,6009,6014,6015,6018,6023,6024,6025,6026,6027,6030,6031,6033,6037,6038,6039,6040,6041,6047,6048,6050,6052,6057,6060,6061,6062,6063,6064,6070,6071,6072,6074,6077,6078,6079,6086,6088,6090,6093,6095) --:Edit Склады
and gl.QuantityMin<>0
),
CTEPromotion as
(
select pwg.WarehouseId, pwg.GoodsId, pwg.RestQuantity
from PromotionWarehouseGoods pwg with(NoLock)
left join PromotionGroup p with(NoLock) on pwg.PromotionGroupId=p.PromotionGroupId
left join PromotionGoods pg with(NoLock) on pwg.PromotionGroupId=p.PromotionGroupId
where pwg.GoodsId in (400318,400319) --:Edit Товары
and pwg.WarehouseId in (34,35,37,132,143,144,157,167,168,173,174,175,176,188,190,198,208,209,219,228,229,230,266,269,270,272,273,274,276,279,291,292,293,294,295,296,297,298,299,300,301,302,303,304,306,308,309,310,311,312,315,317,321,322,323,324,325,331,332,337,338,339,341,342,343,344,347,348,349,350,351,352,354,358,359,361,362,363,364,366,368,369,370,373,375,376,379,380,382,386,387,389,391,392,393,394,397,399,402,403,404,405,408,412,413,414,415,416,417,418,427,428,432,433,434,435,437,438,439,440,442,444,445,448,453,454,455,456,457,458,459,460,462,463,464,465,466,467,468,472,474,475,476,477,478,479,480,481,482,484,485,486,487,488,489,491,495,496,497,498,499,503,507,508,509,511,512,515,518,519,522,525,527,528,529,531,532,534,547,548,549,550,552,554,555,556,557,558,560,561,563,564,566,567,570,571,572,573,574,575,580,581,582,584,585,586,587,589,591,592,594,595,596,598,599,605,607,609,610,613,616,618,622,624,626,627,629,635,636,637,638,639,647,655,657,659,661,662,664,667,668,675,677,678,681,687,689,691,693,700,710,712,714,716,718,720,722,724,726,728,730,732,734,736,738,740,750,760,770,775,777,780,790,804,805,808,814,815,816,818,819,820,821,823,824,833,834,840,841,847,850,852,854,856,858,859,860,861,862,863,864,868,871,872,874,878,882,886,890,891,892,894,895,896,902,910,911,913,915,918,919,922,923,927,929,933,934,935,946,948,949,950,952,955,956,958,959,962,963,964,966,977,978,983,984,986,999,6004,6009,6014,6015,6018,6023,6024,6025,6026,6027,6030,6031,6033,6037,6038,6039,6040,6041,6047,6048,6050,6052,6057,6060,6061,6062,6063,6064,6070,6071,6072,6074,6077,6078,6079,6086,6088,6090,6093,6095) --:Edit Склады
and p.PromotionGroupTimeBegin <= @date and p.PromotionGroupTimeEnd >= @date
and pg.PromotionGoodsTimeBegin<=@date and pg.PromotionGoodsTimeEnd>= @date
group by pwg.WarehouseId, pwg.GoodsId, pwg.RestQuantity
),
CTECredit as
(
select c.WarehouseId, c.GoodsId, SUM(c.Quantity) CreditQuantity
from CreditCurrentViewCompact c with(NoLock)
where c.Datex between dateadd(day,-7, @date) and @date
and c.GoodsId in (400318,400319) --:Edit Товары
and c.WarehouseId in (34,35,37,132,143,144,157,167,168,173,174,175,176,188,190,198,208,209,219,228,229,230,266,269,270,272,273,274,276,279,291,292,293,294,295,296,297,298,299,300,301,302,303,304,306,308,309,310,311,312,315,317,321,322,323,324,325,331,332,337,338,339,341,342,343,344,347,348,349,350,351,352,354,358,359,361,362,363,364,366,368,369,370,373,375,376,379,380,382,386,387,389,391,392,393,394,397,399,402,403,404,405,408,412,413,414,415,416,417,418,427,428,432,433,434,435,437,438,439,440,442,444,445,448,453,454,455,456,457,458,459,460,462,463,464,465,466,467,468,472,474,475,476,477,478,479,480,481,482,484,485,486,487,488,489,491,495,496,497,498,499,503,507,508,509,511,512,515,518,519,522,525,527,528,529,531,532,534,547,548,549,550,552,554,555,556,557,558,560,561,563,564,566,567,570,571,572,573,574,575,580,581,582,584,585,586,587,589,591,592,594,595,596,598,599,605,607,609,610,613,616,618,622,624,626,627,629,635,636,637,638,639,647,655,657,659,661,662,664,667,668,675,677,678,681,687,689,691,693,700,710,712,714,716,718,720,722,724,726,728,730,732,734,736,738,740,750,760,770,775,777,780,790,804,805,808,814,815,816,818,819,820,821,823,824,833,834,840,841,847,850,852,854,856,858,859,860,861,862,863,864,868,871,872,874,878,882,886,890,891,892,894,895,896,902,910,911,913,915,918,919,922,923,927,929,933,934,935,946,948,949,950,952,955,956,958,959,962,963,964,966,977,978,983,984,986,999,6004,6009,6014,6015,6018,6023,6024,6025,6026,6027,6030,6031,6033,6037,6038,6039,6040,6041,6047,6048,6050,6052,6057,6060,6061,6062,6063,6064,6070,6071,6072,6074,6077,6078,6079,6086,6088,6090,6093,6095) --:Edit Склады
and c.WhatBase in (11,21,22)
and c.Quantity<>0
group by c.WarehouseId, c.GoodsId
),
CTERest as
(
select r.WarehouseId, r.GoodsId, SUM(r.Quantity) Quantity
from RestView r with(NoLock)
where @date between r.Datex and r.DateEnd
and r.GoodsId in (400318,400319)
and r.Quantity<>0
and r.WarehouseId in (34,35,37,132,143,144,157,167,168,173,174,175,176,188,190,198,208,209,219,228,229,230,266,269,270,272,273,274,276,279,291,292,293,294,295,296,297,298,299,300,301,302,303,304,306,308,309,310,311,312,315,317,321,322,323,324,325,331,332,337,338,339,341,342,343,344,347,348,349,350,351,352,354,358,359,361,362,363,364,366,368,369,370,373,375,376,379,380,382,386,387,389,391,392,393,394,397,399,402,403,404,405,408,412,413,414,415,416,417,418,427,428,432,433,434,435,437,438,439,440,442,444,445,448,453,454,455,456,457,458,459,460,462,463,464,465,466,467,468,472,474,475,476,477,478,479,480,481,482,484,485,486,487,488,489,491,495,496,497,498,499,503,507,508,509,511,512,515,518,519,522,525,527,528,529,531,532,534,547,548,549,550,552,554,555,556,557,558,560,561,563,564,566,567,570,571,572,573,574,575,580,581,582,584,585,586,587,589,591,592,594,595,596,598,599,605,607,609,610,613,616,618,622,624,626,627,629,635,636,637,638,639,647,655,657,659,661,662,664,667,668,675,677,678,681,687,689,691,693,700,710,712,714,716,718,720,722,724,726,728,730,732,734,736,738,740,750,760,770,775,777,780,790,804,805,808,814,815,816,818,819,820,821,823,824,833,834,840,841,847,850,852,854,856,858,859,860,861,862,863,864,868,871,872,874,878,882,886,890,891,892,894,895,896,902,910,911,913,915,918,919,922,923,927,929,933,934,935,946,948,949,950,952,955,956,958,959,962,963,964,966,977,978,983,984,986,999,6004,6009,6014,6015,6018,6023,6024,6025,6026,6027,6030,6031,6033,6037,6038,6039,6040,6041,6047,6048,6050,6052,6057,6060,6061,6062,6063,6064,6070,6071,6072,6074,6077,6078,6079,6086,6088,6090,6093,6095) --:Edit Склады
group by r.WarehouseId, r.GoodsId
),
CTERestTo as
(
select r.WarehouseId, r.GoodsId, sum(r.Quantity) OrderQuantityTo
from OrderSaleGoodsView r with(NoLock)
where 1=1
and r.GoodsId in (400318,400319)
and r.Quantity<>0
and r.WarehouseId in (34,35,37,132,143,144,157,167,168,173,174,175,176,188,190,198,208,209,219,228,229,230,266,269,270,272,273,274,276,279,291,292,293,294,295,296,297,298,299,300,301,302,303,304,306,308,309,310,311,312,315,317,321,322,323,324,325,331,332,337,338,339,341,342,343,344,347,348,349,350,351,352,354,358,359,361,362,363,364,366,368,369,370,373,375,376,379,380,382,386,387,389,391,392,393,394,397,399,402,403,404,405,408,412,413,414,415,416,417,418,427,428,432,433,434,435,437,438,439,440,442,444,445,448,453,454,455,456,457,458,459,460,462,463,464,465,466,467,468,472,474,475,476,477,478,479,480,481,482,484,485,486,487,488,489,491,495,496,497,498,499,503,507,508,509,511,512,515,518,519,522,525,527,528,529,531,532,534,547,548,549,550,552,554,555,556,557,558,560,561,563,564,566,567,570,571,572,573,574,575,580,581,582,584,585,586,587,589,591,592,594,595,596,598,599,605,607,609,610,613,616,618,622,624,626,627,629,635,636,637,638,639,647,655,657,659,661,662,664,667,668,675,677,678,681,687,689,691,693,700,710,712,714,716,718,720,722,724,726,728,730,732,734,736,738,740,750,760,770,775,777,780,790,804,805,808,814,815,816,818,819,820,821,823,824,833,834,840,841,847,850,852,854,856,858,859,860,861,862,863,864,868,871,872,874,878,882,886,890,891,892,894,895,896,902,910,911,913,915,918,919,922,923,927,929,933,934,935,946,948,949,950,952,955,956,958,959,962,963,964,966,977,978,983,984,986,999,6004,6009,6014,6015,6018,6023,6024,6025,6026,6027,6030,6031,6033,6037,6038,6039,6040,6041,6047,6048,6050,6052,6057,6060,6061,6062,6063,6064,6070,6071,6072,6074,6077,6078,6079,6086,6088,6090,6093,6095) --:Edit Склады
group by r.WarehouseId, r.GoodsId
),
CTERestToWay as
(
select w.WarehouseIdParent WarehouseId, r.GoodsId, sum(r.Quantity) RestQuantityToWay
from Rest r with(NoLock), Warehouse w with(NoLock)
where r.GoodsId in (400318,400319)
and r.WarehouseId=w.WarehouseId
and w.WarehouseIdParent in (34,35,37,132,143,144,157,167,168,173,174,175,176,188,190,198,208,209,219,228,229,230,266,269,270,272,273,274,276,279,291,292,293,294,295,296,297,298,299,300,301,302,303,304,306,308,309,310,311,312,315,317,321,322,323,324,325,331,332,337,338,339,341,342,343,344,347,348,349,350,351,352,354,358,359,361,362,363,364,366,368,369,370,373,375,376,379,380,382,386,387,389,391,392,393,394,397,399,402,403,404,405,408,412,413,414,415,416,417,418,427,428,432,433,434,435,437,438,439,440,442,444,445,448,453,454,455,456,457,458,459,460,462,463,464,465,466,467,468,472,474,475,476,477,478,479,480,481,482,484,485,486,487,488,489,491,495,496,497,498,499,503,507,508,509,511,512,515,518,519,522,525,527,528,529,531,532,534,547,548,549,550,552,554,555,556,557,558,560,561,563,564,566,567,570,571,572,573,574,575,580,581,582,584,585,586,587,589,591,592,594,595,596,598,599,605,607,609,610,613,616,618,622,624,626,627,629,635,636,637,638,639,647,655,657,659,661,662,664,667,668,675,677,678,681,687,689,691,693,700,710,712,714,716,718,720,722,724,726,728,730,732,734,736,738,740,750,760,770,775,777,780,790,804,805,808,814,815,816,818,819,820,821,823,824,833,834,840,841,847,850,852,854,856,858,859,860,861,862,863,864,868,871,872,874,878,882,886,890,891,892,894,895,896,902,910,911,913,915,918,919,922,923,927,929,933,934,935,946,948,949,950,952,955,956,958,959,962,963,964,966,977,978,983,984,986,999,6004,6009,6014,6015,6018,6023,6024,6025,6026,6027,6030,6031,6033,6037,6038,6039,6040,6041,6047,6048,6050,6052,6057,6060,6061,6062,6063,6064,6070,6071,6072,6074,6077,6078,6079,6086,6088,6090,6093,6095) --:Edit Склады
and w.WarehouseTypeId in (301)
and @date between r.Datex and r.DateEnd
and r.Quantity<>0
group by w.WarehouseIdParent, r.GoodsId
),
CTERestFrom as
(
select r.WarehouseId, r.GoodsId, sum(r.Quantity) OrderQuantityFrom
from OrderSaleGoodsView r with(NoLock)
where r.GoodsId in (400318,400319)
and r.WarehouseId in (1500)
and r.Quantity<>0
group by r.WarehouseId, r.GoodsId
),
CTERestMain as
(
select r.GoodsId, SUM(r.Quantity) RestQuantityMain, SUM(OrderQuantityFrom) OrderQuantityFromMain
from RestView r with(NoLock)
left join CteRestFrom rf on rf.GoodsId=r.GoodsId and rf.WarehouseId=r.WarehouseId
where r.GoodsId in (400318,400319)
and r.WarehouseId in (1500)
and @date between r.Datex and r.DateEnd
and r.Quantity<>0
group by r.WarehouseId, r.GoodsId
),
CTEPlan as
(
select
x.WarehouseId, x.GoodsId, x.GoodsPlanType, x.GoodsPlanStatus,
sum(x.QuantityMin) over (partition by x.GoodsId) SumLogisticAll,
sum(max(x.RestQuantity)) over (partition by x.GoodsId) SumAMDAll,
count (x.WarehouseId) over (partition by x.GoodsId,x.GoodsPlanType, x.GoodsPlanStatus) CountWarehouse,
sum(x.QuantityMin) over (partition by x.GoodsId,x.GoodsPlanType, x.GoodsPlanStatus) SumLogistic,
sum(max(x.RestQuantity)) over (partition by x.GoodsId,x.GoodsPlanType, x.GoodsPlanStatus) SumAMD,
sum(x.CreditQuantity) over (partition by x.GoodsId,x.GoodsPlanType, x.GoodsPlanStatus) SumCredit,
sum(x.Quantity) over (partition by x.GoodsId,x.GoodsPlanType, x.GoodsPlanStatus) SumRestBegin,
sum(x.OrderQuantityTo) over (partition by x.GoodsId,x.GoodsPlanType, x.GoodsPlanStatus) SumRestQuantityTo,
sum(x.RestQuantityToWay) over (partition by x.GoodsId,x.GoodsPlanType, x.GoodsPlanStatus) SumRestQuantityToWay,
sum(x.Quantity) over (partition by x.GoodsId) SumRestBeginAll,
sum(x.OrderQuantityTo) over (partition by x.GoodsId) SumRestQuantityToAll,
sum(x.RestQuantityToWay) over (partition by x.GoodsId) SumRestQuantityToWayAll
from
(
select wf.WarehouseId, gpr.GoodsId, IsNull(glc.QuantityMin,0) QuantityMin, IsNull(pc.RestQuantity,0) RestQuantity, cc.CreditQuantity,
rc.Quantity, rtc.OrderQuantityTo, rtwc.RestQuantityToWay, gprg.GoodsGroup2Id, gprg.GoodsPlanType, gprg.GoodsPlanStatus, wfgg2.GoodsGroup2Id Wfgg2GoodsGroup2Id
from WarehouseFormat wf With(NoLock)
inner join GoodsPlanRunGroup gprg With(NoLock) on gprg.GoodsPlanType=wf.GoodsPlanType and gprg.GoodsPlanStatus=wf.GoodsPlanStatus
inner join GoodsPlanRun gpr With(NoLock) on gpr.GoodsPlanRunGroupId=gprg.GoodsPlanRunGroupId
inner join WarehouseFormatGoodsGroup2 wfgg2 With(NoLock) on wfgg2.WarehouseFormatId=wf.WarehouseFormatId
left join CTEGoodsLogistic glc With(NoLock) on gpr.GoodsId=glc.GoodsId and wf.WarehouseId=glc.WarehouseId
left join CTEPromotion pc With(NoLock) on gpr.GoodsId=pc.GoodsId and wf.WarehouseId=pc.WarehouseId
left join CTECredit cc With(NoLock) on gpr.GoodsId=cc.GoodsId and wf.WarehouseId=cc.WarehouseId
left join CTERest rc With(NoLock) on gpr.GoodsId=rc.GoodsId and wf.WarehouseId=rc.WarehouseId
left join CTERestTo rtc With(NoLock) on gpr.GoodsId=rtc.GoodsId and wf.WarehouseId=rtc.WarehouseId
left join CTERestToWay rtwc With(NoLock) on gpr.GoodsId=rtwc.GoodsId and wf.WarehouseId=rtwc.WarehouseId
left join CTERestMain rmc With(NoLock) on gpr.GoodsId=rmc.GoodsId
where 1=1
and wfgg2.GoodsGroup2Id=-1
and @date between gprg.DateStart and gprg.DateEnd
and gpr.GoodsId in (400318,400319)
and wf.WarehouseId in (281,312,314,328,334,356,372,388,401,450,452,456,476,479,486,523,539,577,710,714,726,730,736,740,806,818,826,874,884,985)
group by wf.WarehouseId, gpr.GoodsId, glc.QuantityMin, pc.RestQuantity, cc.CreditQuantity,
rc.Quantity, rtc.OrderQuantityTo, rtwc.RestQuantityToWay, gprg.GoodsGroup2Id, gprg.GoodsPlanType, gprg.GoodsPlanStatus, wfgg2.GoodsGroup2Id
union All
select
wf.WarehouseId, gpr.GoodsId, IsNull(glc.QuantityMin,0) QuantityMin, IsNull(pc.RestQuantity,0) RestQuantity, cc.CreditQuantity,
rc.Quantity, rtc.OrderQuantityTo, rtwc.RestQuantityToWay, gprg.GoodsGroup2Id, gprg.GoodsPlanType, gprg.GoodsPlanStatus, wfgg2.GoodsGroup2Id Wfgg2GoodsGroup2Id
from WarehouseFormat wf With(NoLock)
inner join GoodsPlanRunGroup gprg With(NoLock) on gprg.GoodsPlanType=wf.GoodsPlanType and gprg.GoodsPlanStatus=wf.GoodsPlanStatus
inner join GoodsPlanRun gpr With(NoLock) on gpr.GoodsPlanRunGroupId=gprg.GoodsPlanRunGroupId
inner join WarehouseFormatGoodsGroup2 wfgg2 With(NoLock) on wfgg2.WarehouseFormatId=wf.WarehouseFormatId
left join CTEGoodsLogistic glc With(NoLock) on gpr.GoodsId=glc.GoodsId and wf.WarehouseId=glc.WarehouseId
left join CTEPromotion pc With(NoLock) on gpr.GoodsId=pc.GoodsId and wf.WarehouseId=pc.WarehouseId
left join CTECredit cc With(NoLock) on gpr.GoodsId=cc.GoodsId and wf.WarehouseId=cc.WarehouseId
left join CTERest rc With(NoLock) on gpr.GoodsId=rc.GoodsId and wf.WarehouseId=rc.WarehouseId
left join CTERestTo rtc With(NoLock) on gpr.GoodsId=rtc.GoodsId and wf.WarehouseId=rtc.WarehouseId
left join CTERestToWay rtwc With(NoLock) on gpr.GoodsId=rtwc.GoodsId and wf.WarehouseId=rtwc.WarehouseId
left join CTERestMain rmc With(NoLock) on gpr.GoodsId=rmc.GoodsId
where 1=1
and gprg.GoodsGroup2Id=wfgg2.GoodsGroup2Id
and @date between gprg.DateStart and gprg.DateEnd
and gpr.GoodsId in (400318,400319)
and wf.WarehouseId in (34,35,37,132,143,144,157,167,168,173,174,175,176,188,190,198,208,209,219,228,229,230,266,269,270,272,273,274,276,279,291,292,293,294,295,296,297,298,299,300,301,302,303,304,306,308,309,310,311,312,315,317,321,322,323,324,325,331,332,337,338,339,341,342,343,344,347,348,349,350,351,352,354,358,359,361,362,363,364,366,368,369,370,373,375,376,379,380,382,386,387,389,391,392,393,394,397,399,402,403,404,405,408,412,413,414,415,416,417,418,427,428,432,433,434,435,437,438,439,440,442,444,445,448,453,454,455,456,457,458,459,460,462,463,464,465,466,467,468,472,474,475,476,477,478,479,480,481,482,484,485,486,487,488,489,491,495,496,497,498,499,503,507,508,509,511,512,515,518,519,522,525,527,528,529,531,532,534,547,548,549,550,552,554,555,556,557,558,560,561,563,564,566,567,570,571,572,573,574,575,580,581,582,584,585,586,587,589,591,592,594,595,596,598,599,605,607,609,610,613,616,618,622,624,626,627,629,635,636,637,638,639,647,655,657,659,661,662,664,667,668,675,677,678,681,687,689,691,693,700,710,712,714,716,718,720,722,724,726,728,730,732,734,736,738,740,750,760,770,775,777,780,790,804,805,808,814,815,816,818,819,820,821,823,824,833,834,840,841,847,850,852,854,856,858,859,860,861,862,863,864,868,871,872,874,878,882,886,890,891,892,894,895,896,902,910,911,913,915,918,919,922,923,927,929,933,934,935,946,948,949,950,952,955,956,958,959,962,963,964,966,977,978,983,984,986,999,6004,6009,6014,6015,6018,6023,6024,6025,6026,6027,6030,6031,6033,6037,6038,6039,6040,6041,6047,6048,6050,6052,6057,6060,6061,6062,6063,6064,6070,6071,6072,6074,6077,6078,6079,6086,6088,6090,6093,6095)
group by wf.WarehouseId, gpr.GoodsId, glc.QuantityMin, pc.RestQuantity,cc.CreditQuantity,
rc.Quantity, rtc.OrderQuantityTo, rtwc.RestQuantityToWay, gprg.GoodsGroup2Id, gprg.GoodsPlanType, gprg.GoodsPlanStatus, wfgg2.GoodsGroup2Id
) x
group by x.WarehouseId, x.GoodsId, x.GoodsPlanType, x.GoodsPlanStatus, x.QuantityMin, x.Quantity, x.OrderQuantityTo, x.RestQuantityToWay,x.RestQuantity, x.CreditQuantity
)
select g.GoodsId, g.GoodsName, wf.GoodsPlanType, wf.GoodsPlanStatus, cp.CountWarehouse, cp.SumLogistic, cp.SumAMD, IsNull(cp.SumCredit,0) SumCredit,
IsNull(cp.SumRestBegin,0) SumRestBegin, IsNull(cp.SumRestQuantityTo,0) SumRestQuantityTo, IsNull(cp.SumRestQuantityToWay,0) SumRestQuantityToWay,
(IsNull(cp.SumRestBegin,0)+IsNull(cp.SumRestQuantityTo,0)+IsNull(cp.SumRestQuantityToWay,0)) RestAll1,
(rmc.RestQuantityMain+rmc.OrderQuantityFromMain) RestMain,
(IsNull(cp.SumRestBeginAll,0)+IsNull(cp.SumRestQuantityToAll,0)+IsNull(cp.SumRestQuantityToWayAll,0)+IsNull(rmc.RestQuantityMain,0)+IsNull(rmc.OrderQuantityFromMain,0)) RestAll2,
cp.SumLogisticAll, cp.SumAMDAll
from WarehouseFormat wf With(NoLock), Goods g With(NoLock)
left join CTEPlan cp on g.GoodsId=cp.GoodsId
left join CTERestMain rmc on g.GoodsId=rmc.GoodsId
where 1=1
and cp.GoodsPlanType=wf.GoodsPlanType and cp.GoodsPlanStatus=wf.GoodsPlanStatus
and g.GoodsId in (400318,400319)
group by g.GoodsId, g.GoodsName, wf.GoodsPlanType, wf.GoodsPlanStatus, cp.CountWarehouse, cp.SumLogistic, cp.SumCredit, cp.SumAMD, cp.SumRestBegin, cp.SumRestQuantityTo,
cp.SumRestQuantityToWay, cp.SumLogisticAll, rmc.RestQuantityMain, rmc.OrderQuantityFromMain,cp.SumRestBeginAll,cp.SumRestQuantityToAll,cp.SumRestQuantityToWayAll,cp.SumAMDAll
order by g.GoodsId, wf.GoodsPlanType, wf.GoodsPlanStatus
|