T-SQL (SS2K8) :: Query That Can Provide 6 Month Forecast?
Dec 17, 2014
Background is that I am recreating charts in SSRS that were being created in Excel. The source data is residing in a SQL Server database. I'm having a hard time coming up with a SQL query to provide a 6 month forecast. I can get my data into a pivot (within a stored procedure) in the following format:
ID varchar (contains alphanumeric values,not unique) Territory (combined with ID unique) Total_Used int can be null Date_ date (date of the import of the data) ID Territory Total_Used Date_ ACASC CAL071287 2014-06-01 ACASC CAL071287 2014-08-01 ACASC CAL071288 2014-09-01
[Code] .....
Now the problem,per month I need the most recent value so I'm expecting
select min(dbo.FS_ItemInventory.Bin) from (SELECT MIN(dbo.FS_LotTrace.LotNumber),dbo.FS_ItemInventory.Bin FROM dbo.FS_LotTrace RIGHT OUTER JOIN dbo.FS_MOHeader AS h INNER JOIN dbo.FS_MOLine AS l ON l.MOHeaderKey = h.MOHeaderKey INNER JOIN dbo.FS_Item AS i ON i.ItemKey = l.ItemKey INNER JOIN dbo.FS_MOLineData ON l.MOLineKey = dbo.FS_MOLineData.MOLineKey LEFT OUTER JOIN dbo.FS_DemandSupply AS ds ON l.MOLineKey = ds.TopLevelDemandSupplyKey LEFT OUTER JOIN dbo.FS_ItemInventory RIGHT OUTER JOIN dbo.FS_Item AS di ON dbo.FS_ItemInventory.ItemKey = di.ItemKey on ds.DemandItemKey = di.ItemKey on dbo.FS_LotTrace.LotTraceKey = dbo.FS_ItemInventory.LotTraceKey where (dbo.FS_ItemInventory.InventoryCategory!='H' or dbo.FS_ItemInventory.InventoryCategory is null) group by di.ItemNumber,dbo.FS_ItemInventory.Bin)
select min(dbo.FS_ItemInventory.Bin) from (SELECT MIN(dbo.FS_LotTrace.LotNumber),dbo.FS_ItemInventory.Bin FROM dbo.FS_LotTrace RIGHT OUTER JOIN dbo.FS_MOHeader AS h INNER JOIN dbo.FS_MOLine AS l ON l.MOHeaderKey = h.MOHeaderKey INNER JOIN dbo.FS_Item AS i ON i.ItemKey = l.ItemKey INNER JOIN dbo.FS_MOLineData ON l.MOLineKey = dbo.FS_MOLineData.MOLineKey LEFT OUTER JOIN dbo.FS_DemandSupply AS ds ON l.MOLineKey = ds.TopLevelDemandSupplyKey LEFT OUTER JOIN dbo.FS_ItemInventory RIGHT OUTER JOIN dbo.FS_Item AS di ON dbo.FS_ItemInventory.ItemKey = di.ItemKey on ds.DemandItemKey = di.ItemKey on dbo.FS_LotTrace.LotTraceKey = dbo.FS_ItemInventory.LotTraceKey where (dbo.FS_ItemInventory.InventoryCategory!='H' or dbo.FS_ItemInventory.InventoryCategory is null) group by di.ItemNumber,dbo.FS_ItemInventory.Bin)
I have a Table with Financial Data in it and for Certain Accounts (the Key Field is actindx Column)there is no data for Certain Months.
I need a query to fill in the data for each Calendar_Month. This should look at the actindx column and Calendar_Month column if there is no data for a specific Month for that actindx I want it to copy all columns for that actindx and insert into a new row, but just puts Zero Dollars for ActivityDebit, ActivityCredit, and NetAmount.
I have created a CalendarMonth_Lookup Table. I assume there is a way to outer Join the two and insert rows or use an "IF" ,"THEN" type of Statement and just manually add the Calendar Month data.
I am including all the code to make the tables and Insert Data into them.
i have a monthly production script that requires two forms of the prior month's date.'yyyymm' or '201411' and 'yyyy-mm-dd' or '2014-11-01'. The second date must have a day value of '1'. I have to use these date values in my script a half dozen times. Ive tried to declare two variables to accomodate but i can not get them correct.
I m creating P&L(profit and Loss ) Reports of accounts its consists of 2 levels
in level2:
my procedure display the output of (Actuals in lakhs)
RESPONSIBILITY DEPT CATEGORY CURRENT YTD ACTUALS SALES Sales Net Sales 444.65 Sales Sales LESS TRD 22.55 SALES NET RETURNS NET RETURNS 422.10 (net sales - TRD) Finance LESS ED LESS ED 40 Sales Totals Sales 382.10(RETURNS - ED)
(only calculation for above dept only remaining dept values display sum of relvenat accounts ,and if i click the category relvent account codes shown here)
Materials .... ... .. production ..... ............ ........
i made a procedure for above
create procedure Pl_level2
@fmdate datetime, @todate datetime, @category varchar(200) as begin create table #temp1
[code]....
like i m inserted so many accounts in temp tables .
IF (@category IS NULL or @catagory=' ' ) begin select responsibility, dept, category, round(Max(Actuals)/100000,1,2) as Actuals from #temp
[code]....
here i can display only current YTD only how to display previous year (13-14) YTD in level1
but if my month is November and two months is added, the year does not change, it stays the same. how do I make the year change when two months are added toward the end of the year.
i would like to see the 2014-06 matched results (3rd query), if the same ssn and acctno is exist in 2012-06 and 2013-06 and 2014-06 then eliminate from results, otherwise show it
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06' select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06' select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06'
i have written the below query but it shows only matched across three queries, but i want to display / delete from 2014-06 records if the ssn and acctno is exist in 2012-06 and 2013-06
select c.* from ( (select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06' ) a join (select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06' ) b on a.SSN = b.SSN and a.acctno = b.acctno join (select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06' ) C on a.SSN = c.SSN and a.acctno = c.acctno join )
how to return the 3 month rolling average count per username? This means, that if jan = 4, feb = 5, mar = 5, then 3 month rolling average will be 7 in April. And if apr = 6, the May rolling average will be 8.
Columns are four:
username, current_tenure, move_in_date, and count.
DDL (create script generated by SSMS from sample table I created, which is why the move_in_date is in hex form. When run it's converted to date. Total size of table 22 rows, 4 columns.)
CREATE TABLE [dbo].[countHistory]( [username] [varchar](50) NULL, [current_tenure] [int] NULL, [move_in_date] [smalldatetime] NULL, [Cnt_Lead_id] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up ArtNo March <=March 1210 20,500 50,900 1220 21,200 64,000 1230 15,400 40,300 ... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
I am new to Data mining in SQL Server. I am using SQL 2005 to create a forecast model for Product sales. I two fact tables that I am using. One has all the Orders historically with the line item details. The other table is a time dimension table which has the value of each of the time values referenced in the Orders. So the time dimesion has multiple values for one day as it goes down to the hour the order was placed.
Can I create a forecast using the time series algorithm in the Data mining module. When I tried to use this algorithm, I got an error that the time is not synchronized with starting series "Unknown" and I should try to set the missing_value_Substitution parameter to previous.
Can some one explain to me how this will resolve my issue. I read in one of the articles on Data mining that in order to use the time series algorithm , I need to have unique set of values for the time. Can some one help me with this
Using the TimeSeries algorithm, how do I forecast more than one time period ahead? I read in you book on page 182 that PredictTimeSeries function can take a parameter for the number of time periods you want to predict. Fore example, SELECT PredictTimeSeries(Bread,5) tells the algorithm to predict the next 5 time periods. Can you tell me how to change that parameter using the graphic interfaces?
I have few more clarification regarding time series. Firstly In my model the month level product sales value represented across 1st day of every month. So that the key time column is of datetime datatype containing a sequence of dates representing the 1st day of every month of the year. Eg: 2006-01-01, 2006-02-01¦¦¦. etc. all in (yy-mm-dd) format But when I make prediction for next five months, though it makes monthly predictions the date part for the months are random whereas I expect the date part to be 1st of every month. What is the reason for this and how can I overcome it. Secondly Predicted sales values for some time period are negative though I do not have any negative value in the training data. What is the reason for this and how can I rectify it?
Thirdly In one of your earlier posts you had said that the time series algorithm does not have any built in time intelligence but uses the key time column as a time sequence stamp. So If have to make predictions for a particular time period where the time slice for each time period is 25 days or 50 days etc, then I understand that the input data used to train the model should be in the same time sequence. Or Can I specify the span of the time period according to which the prediction needs to be made? Basically how can I use the same time series model to make monthly, yearly, quarterly, daily or predictions or for custom time period like I have mentioned above.
Attached is a file that generates a sample dataset from which I want to forecast a value for column 'Parcel Count 2014' from tomorrow (30/10/2014) till the end of December (31/12/2014). The forecast should be based on values from column 'Parcel Count 2013' for the same Month.
I am trying to SUM a column of ActivityDebit with current Calendar_Month to a Column of Trial_Balance_Debit from Last Calendar_Month. I am providing Temp Table code as well as fake data.
===== IF OBJECT_ID('TempDB..#MyTrialBalance','U') IS NOT NULL DROP TABLE #MyTrialBalance CREATE TABLE #MyTrialBalance ( [Trial_Balance_ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL, [FISCALYEAR] [smallint] NULL,
[Code] ....
Here is my Query I am trying but not working. I cant figure out how to doo the dateadd for correct column.
SELECT A.Trial_Balance_ID,A.ACTIVITYDEBIT --SUM(A.ACTIVITYDEBIT + B.Last_Trail_Balance_Debit) AS New_TB FROM (SELECT [Trial_Balance_ID], [Calendar_Month],[ACTIVITYDEBIT] FROM Mytrialbalance WHERE actindx='48397' AND ACTIVITYDEBIT='820439.78000' )A INNER JOIN (SELECT [Trial_Balance_ID],DATEADD(MM, -1,Calendar_Month)AS Last_Month FROM Mytrialbalance) B ON B.Trial_Balance_ID=A.Trial_Balance_ID
I have built a time series model to forecast sales value
I have data from jan 2004 to jan 2006 and the sales value is at a day level in my database. But I am aggregating it to month level in the DSV of the mining model.
I am required to make only historical predictions using the above model starting form jan 2004 to jan 2006 for every month.
I have set Historical_Model_Count and Historical_Model_Gap parameter values to 24 and 10 respectively, and trying to predict for the past few months (PredictTImeseries(SalesValue,-1,1))
But its throwing me the following error
Error(Data Mining): A time series prediction was requested with a start time further in the past than the internal models of the mining model, Sales Forecast, specified in the HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process
In fact it throws the above error irrespective of what the Historical_Model_Count and Historical_Model_Gap parameter values are
I am not able to figure our why this problem is happening?
What should the parameter values for the above scenario?
It would also be helpful if I can get an explanation on how these two parameters affect the historical predictions. I kind of understand that these two parameters are important for historical predictions but dont know why or how.
I have create a simple tabular rolling forecast report (with cumulative totals) from today (October) thru the next 12 months that looks like this. It smartly works no matter when the report is generated, by starting with this CurrentMonth and moving forward by using 1,2,3,4,etc. in the dateadd: =MonthName(datepart("m",dateadd("m",1,Now())))
The report sample (formatting lost in dropping it in here):
Code Block
Close Pct October November December January February
25.% $0 $0 $26,625 $0 $0
50.% $237,500 $110,000 $262,500 $0 $0
75.% $56,250 $0 $891,075 $0 $0
90.% $1,051,830 $0 $0 $0 $0
Monthly Total $1,345,580 $110,000 $1,180,200 $0 $0
Cumulative Total $1,345,580 $1,455,580 $2,635,780 $2,635,780 $2,635,780
It is working fine....there doesn't seem to be anything wrong with it (all numbers total correctly, etc.), but it is very unelegant.....and I know there must be a better way.
In the righthand most month (which would be September 2008) column, I have a formula that produces the amount (the Monthly Total amount is the same):
I am new to SSAS and i want to try to build a "Sales" model. I will have some "Usage" data for some timespanns, but I am not quite sure how to tackle this. Is there somewhere a "Howto" for this?
Edit: There are several locations, and for each location a forecast is needed. And the Icing would be If I would be able to tell where my supplies must go 1st to achieve the best sales...
The potential Client wants to use Oracle but I would like to show them that SQL Server is the better tool for this ;)
Is there any way to copy my Data of 2015 to the Planning/Forecasting Value of 2016?
My question is based on that we use INFOR ION BI right now and there we can just add an Button in our reports wich physically copies the value from one year to the next year based on some other rules in the cube.
Now I need to make this example work with SSAS and Excel PivotTables but I cant figure out how.
I have absolutely no clue where and how to accomplish it. Do I use Calculations, do I use Actions, do i make it in the Dataview, Cube or directly in Excel?
Looking advice on how to query for just a single day of the month. Example is for billing sent on the same day of each month, need to get all accounts that started on that day of the month no matter which month. If a account started January 7th, and it is July 7th, I would like it to list all accounts that started on the 7th day of the month. The database has a start date field in short date format.
Is my query below the most efficient way to query the first day of each month?
SELECT base_rate, doc_pt, chgdate FROM producthistory WHERE chgdate IN ( SELECT MIN(chgdate) FROM ProductHistory WHERE prod_id =1199812 and base_rate > 0 GROUP BY MONTH(chgdate), YEAR(chgdate) ) AND prod_id = 1199812 ORDER BY chgdate DESC
Hi alli want result as month name from my queryi tried with the following query but it give result as month numberlike (8)select (month(getdate())) as expri want result as month name (Augest)..give me the proper query...from Sachin
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month], FROM RECalendar WHERE (EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101))) AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
