SQLTeam.com | Weblogs | Forums

How to integrate target value in query


#1

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]


#2
select     t1.OrderMonth , t1.RecMonth, t1.QtyReceived, t1.WIP, t1.FinishedGood, t2.Target
from     #tblResult t1
    inner join
    (
        select    *, RecMonth = Mth + '-' + convert(varchar(4), Targetyear)
        from    #tblTarget
            unpivot
            (
                Target for Mth in 
                ([Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec])
            ) up
    ) t2    on    t1.RecMonth    = t2.RecMonth

#3

Thank you very much khtan..