SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 12 months saleing forecast T-sql query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pradeepshraddha
Starting Member

8 Posts

Posted - 12/08/2006 :  01:50:20  Show Profile  Reply with Quote
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

Sweden
30249 Posts

Posted - 12/08/2006 :  08:13:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Forecast is easy. Which model are you interested in?
Linear? Exponential? Polynomic? Fourier?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 12/08/2006 :  10:27:52  Show Profile  Reply with Quote
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 - 12/08/2006 :  11:02:37  Show Profile  Reply with Quote
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

Sweden
30249 Posts

Posted - 12/08/2006 :  14:43:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 12/08/2006 14:49:37
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 12/11/2006 :  07:28:03  Show Profile  Reply with Quote
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

Sweden
30249 Posts

Posted - 12/11/2006 :  07:41:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Thank you!
At least I liked the RANDOM value generator...


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 12/11/2006 07:42:48
Go to Top of Page

pradeepshraddha
Starting Member

8 Posts

Posted - 12/11/2006 :  11:31:22  Show Profile  Reply with Quote
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

Sweden
30249 Posts

Posted - 12/11/2006 :  15:37:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 12/11/2006 :  16:43:08  Show Profile  Reply with Quote
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

Sweden
30249 Posts

Posted - 12/11/2006 :  16:47:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 12/11/2006 :  16:47:42  Show Profile  Reply with Quote
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 - 12/11/2006 :  16:53:24  Show Profile  Reply with Quote
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

Sweden
30249 Posts

Posted - 12/11/2006 :  17:04:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What does CUS and COP stand for?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30249 Posts

Posted - 12/11/2006 :  17:12:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 12/12/2006 :  11:11:42  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000