build forecast

Prev: SELECTing a range from the results
Next: How do I Return Result of Case as Decimal

From: AHartman on 15 Jul 2008 21:09

---

I have a process that builds records to be loaded to my forecast table and
the records looks like this.

MM YY Customer Type fctlbs
07 2008 abc cu 100
08 2008 abc cu 200
08 2008 def cu 200
08 2008 def cu 200
08 2008 def cu 200
08 2008 def cu 200
08 2008 def cu 200

From: AHartman on 15 Jul 2008 21:26

---

Hit the button to quick..


I have a process that builds records to be loaded to my forecast table and
the records look like this.

MM YY Customer Type fctlbs
07 2008 abc cu 100
08 2008 abc cu 200
09 2008 def cu 200
10 2008 def cu 400
11 2008 def cu 200
12 2008 def cu 600
01 2009 def cu 550
02 2009 def cu 550
03 2009 def cu 550
04 2009 def cu 550
05 2009 def cu 650
06 2009 def cu 250
07 2009 def cu 350

When I load the forecast table I need month(07) and year 2008 to load to
CurrentMth and then have mm(08) for 2008 populate Future2
and so on to be build my record. The Month gets current and Year gets
current then Cust=abc and Type=Cu from above.

forecast table:
Month
Year
Cust
Type
CurrentMth
Future2
Future3
Future4
Future5
Future6
Future7
Future8
Future9
Future10
Future11
Future12


How can I make this happen?


Thanks.












"AHartman" <Hoosbruin(a)Kconline.com> wrote in message
news:trydnSMHmuD31-DVnZ2dnUVZ_iydnZ2d(a)kconline.com...
>I have a process that builds records to be loaded to my forecast table and
>the records looks like this.
>
> MM YY Customer Type fctlbs
> 07 2008 abc cu 100
> 08 2008 abc cu 200
> 08 2008 def cu 200
> 08 2008 def cu 200
> 08 2008 def cu 200
> 08 2008 def cu 200
> 08 2008 def cu 200
>
>
>
>

From: Plamen Ratchev on 15 Jul 2008 23:14

---

One way is to rank on year/month and then pivot based on the rank. Here is
how it may look (SQL Server 2005):

CREATE TABLE Forecast (
mm INT,
yy INT,
customer VARCHAR(10),
ftype CHAR(2),
fctlbs INT,
PRIMARY KEY (mm, yy));

INSERT INTO Forecast VALUES(7, 2008, 'abc', 'cu', 100);
INSERT INTO Forecast VALUES(8, 2008, 'abc', 'cu', 200);
INSERT INTO Forecast VALUES(9, 2008, 'def', 'cu', 200);
INSERT INTO Forecast VALUES(10, 2008, 'def', 'cu', 400);
INSERT INTO Forecast VALUES(11, 2008, 'def', 'cu', 200);
INSERT INTO Forecast VALUES(12, 2008, 'def', 'cu', 600);
INSERT INTO Forecast VALUES(01, 2009, 'def', 'cu', 550);
INSERT INTO Forecast VALUES(02, 2009, 'def', 'cu', 550);
INSERT INTO Forecast VALUES(03, 2009, 'def', 'cu', 550);
INSERT INTO Forecast VALUES(04, 2009, 'def', 'cu', 550);
INSERT INTO Forecast VALUES(05, 2009, 'def', 'cu', 650);
INSERT INTO Forecast VALUES(06, 2009, 'def', 'cu', 250);
INSERT INTO Forecast VALUES(07, 2009, 'def', 'cu', 350);

SELECT mm, yy, customer, ftype,
[1] AS CurrentMth,
[2] AS Future2,
[3] AS Future3,
[4] AS Future4,
[5] AS Future5,
[6] AS Future6,
[7] AS Future7,
[8] AS Future8,
[9] AS Future9,
[10] AS Future10,
[11] AS Future11,
[12] AS Future12
FROM
(SELECT mm, yy, customer, ftype, fctlbs,
RANK() OVER(ORDER BY yy, mm) AS rk
FROM Forecast
WHERE CAST(yy * 100 + mm AS CHAR(6)) >=
CONVERT(CHAR(6), CURRENT_TIMESTAMP, 112)) AS F
PIVOT
(MAX(fctlbs) FOR rk IN
([1], [2], [3], [4], [5],
[6], [7], [8], [9], [10],
[11], [12])) AS P
ORDER BY yy, mm, customer, ftype;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: Steve Kass on 15 Jul 2008 23:57

---

This is probably very similar to Plamen's solution. I changed 'abc' to
'def'. This
solution may not give the expected result if all 12 fctlbs values are not
present for each (Customer,Type) pair.

Steve Kass
Drew University
http://www.stevekass.com

create table T(
MM char(2),
YY char(4),
Customer varchar(20),
Type varchar(4),
fctlbs int
)
insert into T values ('07','2008','def','cu',100);
insert into T values ('08','2008','def','cu',200);
insert into T values ('09','2008','def','cu',200);
insert into T values ('10','2008','def','cu',400);
insert into T values ('11','2008','def','cu',200);
insert into T values ('12','2008','def','cu',600);
insert into T values ('01','2009','def','cu',550);
insert into T values ('02','2009','def','cu',550);
insert into T values ('03','2009','def','cu',550);
insert into T values ('04','2009','def','cu',550);
insert into T values ('05','2009','def','cu',650);
insert into T values ('06','2009','def','cu',250);
insert into T values ('07','2009','def','cu',350);

with Numbered as (
select
MM,
YY,
Customer,
Type,
datediff(month,getdate(),cast(YY+MM+'01' as datetime))+1 as MMno,
fctlbs
from T
), Twelve as (
select 1 as Ahead union all
select 2 as Ahead union all
select 3 as Ahead union all
select 4 as Ahead union all
select 5 as Ahead union all
select 6 as Ahead union all
select 7 as Ahead union all
select 8 as Ahead union all
select 9 as Ahead union all
select 10 as Ahead union all
select 11 as Ahead union all
select 12 as Ahead
), Named as (
select
MM,
YY,
Customer,
Type,
MMno,
case
when Ahead = 1 then 'CurrentMth'
else 'Future'+RTRIM(Ahead) end as MMname,
fctlbs
from Twelve left outer join Numbered
on Ahead = MMno
)

select
RIGHT(100+MONTH(GETDATE()),2) AS MM,
YEAR(GETDATE()) AS YY,
Customer,
Type,
[CurrentMth],
[Future2],
[Future3],
[Future4],
[Future5],
[Future6],
[Future7],
[Future8],
[Future9],
[Future10],
[Future11],
[Future12]
from (
SELECT Customer, Type, fctlbs, MMname
FROM Named
) as P PIVOT ( MAX(fctlbs) FOR
MMname IN (
[CurrentMth],
[Future2],
[Future3],
[Future4],
[Future5],
[Future6],
[Future7],
[Future8],
[Future9],
[Future10],
[Future11],
[Future12])
) AS Pvt
ORDER BY Customer, Type
go

drop table T


AHartman wrote:

> Hit the button to quick..
>
>
> I have a process that builds records to be loaded to my forecast table
> and
> the records look like this.
>
> MM YY Customer Type fctlbs
> 07 2008 abc cu 100
> 08 2008 abc cu 200
> 09 2008 def cu 200
> 10 2008 def cu 400
> 11 2008 def cu 200
> 12 2008 def cu 600
> 01 2009 def cu 550
> 02 2009 def cu 550
> 03 2009 def cu 550
> 04 2009 def cu 550
> 05 2009 def cu 650
> 06 2009 def cu 250
> 07 2009 def cu 350
>
> When I load the forecast table I need month(07) and year 2008 to load
> to CurrentMth and then have mm(08) for 2008 populate Future2
> and so on to be build my record. The Month gets current and Year gets
> current then Cust=abc and Type=Cu from above.
>
> forecast table:
> Month
> Year
> Cust
> Type
> CurrentMth
> Future2
> Future3
> Future4
> Future5
> Future6
> Future7
> Future8
> Future9
> Future10
> Future11
> Future12
>
>
> How can I make this happen?
>
>
> Thanks.
>
>
>
>
>
>
>
>
>
>
>
>
> "AHartman" <Hoosbruin(a)Kconline.com> wrote in message
> news:trydnSMHmuD31-DVnZ2dnUVZ_iydnZ2d(a)kconline.com...
>
>> I have a process that builds records to be loaded to my forecast
>> table and the records looks like this.
>>
>> MM YY Customer Type fctlbs
>> 07 2008 abc cu 100
>> 08 2008 abc cu 200
>> 08 2008 def cu 200
>> 08 2008 def cu 200
>> 08 2008 def cu 200
>> 08 2008 def cu 200
>> 08 2008 def cu 200
>>
>>
>>
>>
>

From: Steve Malley on 15 Jul 2008 23:58

---

Can I suggest what I think is more of a KISS approach?
-----------------------------------------------------------------------------------------------------------
declare @ctr int
, @futuredate datetime

select @ctr=0;

while @ctr < 12
Begin

select @ctr=(a)ctr+1

select @futuredate=dateadd(m,@ctr,getdate())

print cast(@ctr as varchar(5)) + '|' + cast(@futuredate as
varchar(20))


End
-----------------------------------------------------------------------------------------

You can add as many months as you want. You can peel off the parts
using datepart().



On Jul 15, 7:26 pm, "AHartman" <Hoosbr...(a)Kconline.com> wrote:
> Hit the button to quick..
>
> I have a process that builds records to be loaded to my forecast table and
> the records look like this.
>
> MM YY   Customer Type fctlbs
> 07    2008 abc          cu    100
> 08    2008 abc          cu    200
> 09    2008 def           cu    200
> 10    2008 def           cu    400
> 11    2008 def           cu    200
> 12    2008 def           cu    600
> 01    2009 def           cu    550
> 02    2009 def           cu    550
> 03    2009 def           cu    550
> 04    2009 def           cu    550
> 05    2009 def           cu    650
> 06    2009 def           cu    250
> 07    2009 def           cu    350
>
> When I load the forecast table I need month(07) and year 2008 to load to
> CurrentMth and then have mm(08) for 2008 populate Future2
> and so on to be build my record. The Month gets current and Year gets
> current then Cust=abc and Type=Cu from above.
>
>  forecast table:
> Month
> Year
> Cust
> Type
> CurrentMth
> Future2
> Future3
> Future4
> Future5
> Future6
> Future7
> Future8
> Future9
> Future10
> Future11
> Future12
>
> How can I make this happen?
>
> Thanks.
>
> "AHartman" <Hoosbr...(a)Kconline.com> wrote in message
>
> news:trydnSMHmuD31-DVnZ2dnUVZ_iydnZ2d(a)kconline.com...
>
>
>
> >I have a process that builds records to be loaded to my forecast table and
> >the records looks like this.
>
> > MM YY   Customer Type fctlbs
> > 07    2008 abc          cu    100
> > 08    2008 abc          cu    200
> > 08    2008 def           cu    200
> > 08    2008 def           cu    200
> > 08    2008 def           cu    200
> > 08    2008 def           cu    200
> > 08    2008 def           cu    200- Hide quoted text -
>
> - Show quoted text -

 |  Next  |  Last
Pages: 1 2 3
Prev: SELECTing a range from the results
Next: How do I Return Result of Case as Decimal