Hi, I have this result from my multiple cte's wich i would like to integrate or added the target value per month. This query is pulled using model and year to date transaction. Any idea is very much appreciated. Thanks everyone.
Below is a DDL for target table #tblTarget. I give only one model for tblTarget as reference.
[code]table #tblTarget
(model varchar(5), Targetyear int, Jan int, Feb int, Mar int, Apr int, May int, Jun int, Jul int, Aug int, Sep int, Oct int, Nov int, Dec int)
insert into #tblTarget(model, Targetyear, Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)values('IP5', 2015,68,68, 68, 67, 67, 67, 67, 67, 67, 67, 67,67)
create table #tblResult
(OrderMonth nvarchar(2), RecMonth nvarchar(10), QtyReceived int, WIP int, FinishedGood numeric(12,2))
Insert into #tblResult(OrderMonth, RecMonth, QtyReceived, WIP, FinishedGood) values('01','Jan-2015',5077,65,59.30)
Insert into #tblResult(OrderMonth, RecMonth, QtyReceived, WIP, FinishedGood) values('02','Feb-2015',2915,8,63.10)
Insert into #tblResult(OrderMonth, RecMonth, QtyReceived, WIP, FinishedGood) values('03','Mar-2015',3384,1,66.30)
Insert into #tblResult(OrderMonth, RecMonth, QtyReceived, WIP, FinishedGood) values('04','Apr-2015',2850,35,67.50)
Insert into #tblResult(OrderMonth, RecMonth, QtyReceived, WIP, FinishedGood) values('05','May-2015',2751,45,41.90)
Insert into #tblResult(OrderMonth, RecMonth, QtyReceived, WIP, FinishedGood) values('06','Jun-2015',2507,20,10.10)
Insert into #tblResult(OrderMonth, RecMonth, QtyReceived, WIP, FinishedGood) values('07','Jul-2015',2426,29,58.20)
Insert into #tblResult(OrderMonth, RecMonth, QtyReceived, WIP, FinishedGood) values('08','Aug-2015',808,16,44.20)
Insert into #tblResult(OrderMonth, RecMonth, QtyReceived, WIP, FinishedGood) values('09','Sep-2015',2101,4,51.90)
Insert into #tblResult(OrderMonth, RecMonth, QtyReceived, WIP, FinishedGood) values('10','Oct-2015',1427,15,49.60)
Insert into #tblResult(OrderMonth, RecMonth, QtyReceived, WIP, FinishedGood) values('11','Nov-2015',1204,228,38.30)
Insert into #tblResult(OrderMonth, RecMonth, QtyReceived, WIP, FinishedGood) values('12','Dec-2015',850,46,54.80)
Insert into #tblResult(OrderMonth, RecMonth, QtyReceived, WIP, FinishedGood) values('24','YTD',28300,512,52.70)
Expected Result:
Order Month==Rec Month==QtyReceived===WIP====FG%=====MonthlyTarget
01----------Jan-2015-------5077-------65----59.30------68
02----------Feb-2015-------2915--------8 ----63.10-----68
03----------Mar-2015-------3384--------1 ----66.30-----68
04----------Apr-2015-------2850--------35----67.50-----67
05----------May-2015-------2751--------45----41.90-----67
06----------Jun-2015-------2507--------20----10.10-----67
07----------Jul-2015-------2426--------29----58.20-----67
08----------Aug-2015-------808 --------16----44.20-----67
09----------Sep-2015-------2101--------4 ----51.90-----67
10----------Oct-2015-------1427--------15----49.60-----67
11----------Nov-2015-------1204--------228---38.30-----67
12----------Dec-2015-------850 ---------46----54.80-----67
24-------------YTD ------28300--------512----52.70-----
select t1.OrderMonth , t1.RecMonth, t1.QtyReceived, t1.WIP, t1.FinishedGood
from #tblResult t1[/code]