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-YearPeriodPeriodNameperiodidAnother table is Forecast- SRecordIDBudgetPeriodIDCusREcordIDLast table is CustomercusRecordidcus_NAmeI'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 LarssonHelsingborg, Sweden |
|
|
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! |
|
|
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 2006cust name nov06 dec 06 jan07...oct07if it's feb 07result would be -cust name feb07 to jan07Thanks you... |
|
|
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 07result would be -cust name feb07 to jan07
I would go for the RANDOM statistics model.SELECT c.cus_Name, x.[Year], x.[Month], x.theDataFROM Customer cCROSS 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 ) xORDER BY c.cus_Name, x.Number Peter LarssonHelsingborg, Sweden |
|
|
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.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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-3122006 2 Dec 2006 3 Jan2006 4 Feb.........2006 12 Oct2007 1 Nov....2007 12 OctFORECAST - There are columns in this tableRecordID FOR_CUS_RecordID PeriodID Budget FRO_PeriodIDCUSTOMERCUS_REcordID CUS_NAME I want to show this result in this way -For Exam date is jan 2007 Cus_Name jan feb...decCould you please help me out? It's urgent.Thanks.. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
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]GOCREATE 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]GOCREATE 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]GODO you want me to post the data sample too? Please let me know. I'm looking forward to hearing from you!Thanks .. |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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 0er 53 535 534 43 33 535 34 53 543 535 343 5353These are budget numbers.. |
|
|
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 Octrr 242 535 535 535 53 535 53 053 35 0 0 0mm 443 42 43 0 0 353 0 0 87 34 45 43These are the budget numbers.Thanks you!! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-11 : 17:04:22
|
What does CUS and COP stand for?Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-11 : 17:12:43
|
You need something like thisSELECT 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 csfINNER JOIN COP ON COP.COP_RecordID = csf.CSF_COP_RecordIDINNER JOIN Customer AS cus ON cus.CUS_RECORDID = csf.CSF_CUS_RecordIDGROUP BY cus.CUS_CusNameORDER BY cus.CUS_CusName Peter LarssonHelsingborg, Sweden |
|
|
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 .... decif date is '6/7/2007'jun jul oct.....mayPlease let me know if you have any idea how to do it.Thanks, |
|
|
|