Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 12 months saleing forecast T-sql query

Author  Topic 

pradeepshraddha
Starting Member

8 Posts

Posted - 2006-12-08 : 01:50:20
I'm passing the date textbox. I have 3 tables.
period table-
Year
Period
PeriodName
periodid

Another table is Forecast-
SRecordID
Budget
PeriodID
CusREcordID

Last table is Customer
cusRecordid
cus_NAme


I've to write tsql query for 12 months rolling forecast. I'm passing the date from ui.

Please help me out here..

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-08 : 08:13:54
Forecast is easy. Which model are you interested in?
Linear? Exponential? Polynomic? Fourier?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-12-08 : 10:27:52
I'm partial to the Winter's model for cyclical anual forecasts.

STAR SCHEMAS ARE NOT DATA WAREHOUSES!
Go to Top of Page

pradeepshraddha
Starting Member

8 Posts

Posted - 2006-12-08 : 11:02:37
Any kind of solution is fine for me. I'm using ASP.NET datagrid to bind that solution with grid.
For exam - Nov 2006
cust name nov06 dec 06 jan07...oct07

if it's feb 07
result would be -
cust name feb07 to jan07

Thanks you...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-08 : 14:43:27
quote:
Originally posted by pradeepshraddha

Any kind of solution is fine for me.

if it's feb 07
result would be -
cust name feb07 to jan07
I would go for the RANDOM statistics model.
SELECT		c.cus_Name,
x.[Year],
x.[Month],
x.theData
FROM Customer c
CROSS JOIN (
SELECT DATENAME(month, DATEADD(month, Number, GETDATE())) [Month],
YEAR(DATEADD(month, Number, GETDATE())) [Year],
Number,
ABS(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT)) % 10000 theData
FROM master..spt_values
WHERE Name IS NULL
AND Number < 12
) x
ORDER BY c.cus_Name,
x.Number

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-12-11 : 07:28:03
Peter, you are wonderfully evil

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 07:41:28
Thank you!
At least I liked the RANDOM value generator...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

pradeepshraddha
Starting Member

8 Posts

Posted - 2006-12-11 : 11:31:22
Thanks peter for the solution.

I tried to convert your query, But I'm missing starting month.

Could you please help me out to write the T-SQL query? Here is table description -

Period-
PeriodYear Period PeriodName PeriodID
2006 1 Nov 11213e13-3131-13-1313-312
2006 2 Dec
2006 3 Jan
2006 4 Feb
.....
....
2006 12 Oct
2007 1 Nov
..
..
2007 12 Oct

FORECAST - There are columns in this table
RecordID FOR_CUS_RecordID PeriodID Budget FRO_PeriodID

CUSTOMER
CUS_REcordID CUS_NAME

I want to show this result in this way -
For Exam date is jan 2007
Cus_Name jan feb...dec

Could you please help me out? It's urgent.

Thanks..





Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 15:37:56
See this very informative article,
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

pradeepshraddha
Starting Member

8 Posts

Posted - 2006-12-11 : 16:43:08
Table Definition (DDLS)

CREATE TABLE [CustomerSalesForecast] (
[CSF_RecordID] [uniqueidentifier] NOT NULL ,
[CSF_CUS_RecordID] [uniqueidentifier] NOT NULL ,
[CSF_COP_RecordID] [uniqueidentifier] NOT NULL ,
[CSF_Budget] [money] NOT NULL CONSTRAINT [DF_CustomerSalesForecast_SLC_CSF_Budget] DEFAULT (0),
CONSTRAINT [PK__CustomerSalesForecast] PRIMARY KEY CLUSTERED
(
[CSF_RecordID]
) ON [PRIMARY] ,
CONSTRAINT [FK__CustomerSalesForecast_COM] FOREIGN KEY
(
[CSF_COP_RecordID]
) REFERENCES [COP] (
[COP_RecordID]
),
CONSTRAINT [FK__CustomerSalesForecast_Customer] FOREIGN KEY
(
[CSF_CUS_RecordID]
) REFERENCES [Customer] (
[CUS_RecordID]
)
) ON [PRIMARY]
GO

CREATE TABLE [COP] (
[COP_Year] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[COP_Period] [smallint] NOT NULL ,
[COP_PeriodName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[COP_PeriodBeginDate] [datetime] NOT NULL ,
[COP_PeriodEndDate] [datetime] NOT NULL ,
[COP_RecordID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_COP_RecordID] DEFAULT (newid()),
[COP_COM_RecordID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_COP_COM_RecordID] DEFAULT (newid()),
CONSTRAINT [PK_COP] PRIMARY KEY CLUSTERED
(
[COP_Year],
[COP_Period]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_COP_RecordID] UNIQUE NONCLUSTERED
(
[COP_RecordID]
) ON [PRIMARY] ,
CONSTRAINT [IX_COP_YearPeriod] UNIQUE NONCLUSTERED
(
[COP_Year],
[COP_Period]
) ON [PRIMARY] ,
CONSTRAINT [FK_COP_Year_COM_Year] FOREIGN KEY
(
[COP_Year]
) REFERENCES [COM] (
[COM_Year]
)
) ON [PRIMARY]
GO

CREATE TABLE [Customer] (
[CUS_RecordID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CUS_CustomerID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CUS_CusName] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

CONSTRAINT [PK_CUS_CustomerID] PRIMARY KEY CLUSTERED
(
[CUS_CustomerID]
) ON [PRIMARY] ,

) ON [PRIMARY]
GO

DO you want me to post the data sample too? Please let me know.

I'm looking forward to hearing from you!

Thanks ..
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 16:47:31
Yes, some sample data would be great.
Also your expected output based on the provided sample data would be nice.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

pradeepshraddha
Starting Member

8 Posts

Posted - 2006-12-11 : 16:47:42
Report would look like this -
date '11/6/2006'

Name Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct
Rr 230 350 324 34 3242 423 234 0 0 0 0 0
er 53 535 534 43 33 535 34 53 543 535 343 5353

These are budget numbers..
Go to Top of Page

pradeepshraddha
Starting Member

8 Posts

Posted - 2006-12-11 : 16:53:24
Want to see this report in Cross-tab..

Date - '11/5/2006'

Name Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct
rr 242 535 535 535 53 535 53 053 35 0 0 0
mm 443 42 43 0 0 353 0 0 87 34 45 43

These are the budget numbers.

Thanks you!!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 17:04:22
What does CUS and COP stand for?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 17:12:43
You need something like this
SELECT		cus.CUS_CusName,
MAX(CASE WHEN COP.COP_Period = 1 THEN csf.CSF_Budget ELSE 0 END) AS 'Period 1',
MAX(CASE WHEN COP.COP_Period = 2 THEN csf.CSF_Budget ELSE 0 END) AS 'Period 2',
MAX(CASE WHEN COP.COP_Period = 3 THEN csf.CSF_Budget ELSE 0 END) AS 'Period 3',
MAX(CASE WHEN COP.COP_Period = 4 THEN csf.CSF_Budget ELSE 0 END) AS 'Period 4',
MAX(CASE WHEN COP.COP_Period = 5 THEN csf.CSF_Budget ELSE 0 END) AS 'Period 5',
MAX(CASE WHEN COP.COP_Period = 6 THEN csf.CSF_Budget ELSE 0 END) AS 'Period 6',
MAX(CASE WHEN COP.COP_Period = 7 THEN csf.CSF_Budget ELSE 0 END) AS 'Period 7',
MAX(CASE WHEN COP.COP_Period = 8 THEN csf.CSF_Budget ELSE 0 END) AS 'Period 8',
MAX(CASE WHEN COP.COP_Period = 9 THEN csf.CSF_Budget ELSE 0 END) AS 'Period 9',
MAX(CASE WHEN COP.COP_Period = 10 THEN csf.CSF_Budget ELSE 0 END) AS 'Period 10',
MAX(CASE WHEN COP.COP_Period = 11 THEN csf.CSF_Budget ELSE 0 END) AS 'Period 11',
MAX(CASE WHEN COP.COP_Period = 12 THEN csf.CSF_Budget ELSE 0 END) AS 'Period 12'
FROM CustomerSalesForecast csf
INNER JOIN COP ON COP.COP_RecordID = csf.CSF_COP_RecordID
INNER JOIN Customer AS cus ON cus.CUS_RECORDID = csf.CSF_CUS_RecordID
GROUP BY cus.CUS_CusName
ORDER BY cus.CUS_CusName


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

pradeepshraddha
Starting Member

8 Posts

Posted - 2006-12-12 : 11:11:42
COP is a period table and cus stands for Customer.

I had the same query too. but I want to generate horizontal column dynamically.
For Exam -
date - '1/1/2007'
Jan feb mar .... dec

if date is '6/7/2007'
jun jul oct.....may

Please let me know if you have any idea how to do it.

Thanks,
Go to Top of Page
   

- Advertisement -