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 2008 Forums
 Transact-SQL (2008)
 Predicting based on a INSTALL_DATE

Author  Topic 

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-02-04 : 17:28:46
I have a table that looks like this:

AGE_RANGE AGE_DAYS INV_NO BUS STATUS INSTALL_DATE lastRefresh
60+ Months 2980.899387 213439 RAGD Installed Dec 8 2004 11:00AM 1/31/2013
60+ Months 1930.899387 341225 RAGD Installed Oct 24 2007 11:00AM 1/31/2013
60+ Months 1849.899387 364182 RAGD Installed Jan 13 2008 11:00AM 1/31/2013
60+ Months 1952.899387 358937 RAGD Installed Oct 2 2007 11:00AM 1/31/2013
37-59 Months 1407.899387 419510 RAGD Installed Mar 30 2009 11:00AM 1/31/2013
37-59 Months 1147.899387 445755 RAGD Installed Dec 15 2009 12:00AM 1/31/2013
37-59 Months 1699.899387 148275 RAGD Installed Jun 11 2008 1:00PM 1/31/2013

0 - 12 Months 238.8993866 877675 RAGD Installed Jun 11 2012 1:00PM 1/31/2013
13 - 29 Months 780.8993866 671054 RAGD Installed Dec 17 2010 12:00AM 1/31/2013
13 - 29 Months 885.8993866 594754 RAGD Installed Sep 3 2010 12:00AM 1/31/2013

the AGE_RANGE criteria comes from oracle:
CASE
WHEN TRUNC(MONTHS_BETWEEN(sysdate,BEGIN)) <13 THEN ''0 - 12 Months'' WHEN (TRUNC(MONTHS_BETWEEN(sysdate,BEGIN))<=29 AND TRUNC(MONTHS_BETWEEN(sysdate,BEGIN))>12) THEN ''13 - 29 Months'' -- WHEN (TRUNC(MONTHS_BETWEEN(sysdate,BEGIN))<=36 AND TRUNC(MONTHS_BETWEEN(sysdate,BEGIN))>29) THEN ''30 - 36 Months'' -- WHEN (TRUNC(MONTHS_BETWEEN(sysdate,BEGIN))<=59 AND TRUNC(MONTHS_BETWEEN(sysdate,BEGIN))>36) THEN ''37 - 59 Months'' -- WHEN TRUNC(MONTHS_BETWEEN(sysdate,BEGIN)) >=60 THEN ''60+ Months''
ELSE ''NA''



I would like to predict/forecast which devices will age (fall into diffrent age bracket)by doing COUNT on INV_NO per month going all the way to December 2014. So for example if today I have 4 devices in "60+ Months" age bracket knowing the installed date how many devices will turn into a diffrent age_bracket by the END of each month. Example: Feb 28th 2013, mar 31 2013, apr 30 2013, may 31 2013 and so on till Dec 2014. I have some ideas with temp table and creating manually columns like this but it is all on paper. Can one of you help me out? Thx. Appreciate it.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-04 : 19:03:46
You should create a calendar table that has the beginning of each month in the range of interest - sort of like shown in the first query below. Once you have that, join your table to that table like shown in the second query. I am only showing the general scheme of how to approach it; you will need to tailor it to your precise needs. If you need help with that, post some consumable code(i.e., code that someone can copy and paste to run on their computer, like the first query that I posted below)
-- Query 1 Create a monthly calendar and populate it.
CREATE TABLE #calendar(Dt DATETIME);
WITH cte AS
(
SELECT CAST('20000101' AS DATETIME) AS Dt
UNION ALL
SELECT DATEADD (mm, 1, Dt)
FROM cte
WHERE Dt < '20151231'
)
INSERT INTO #Calendar

SELECT *
FROM cte
OPTION (MAXRECURSION 0)

-- Query 2 join your table to the calendar table like shown below
SELECT
INV_NO,
c.Dt,
CASE
WHEN DATEDIFF(mm,INSTALL_DATE,dt) BETWEEN 0 AND 12 THEN '0 - 12 Months'
WHEN DATEDIFF(mm,INSTALL_DATE,dt) BETWEEN 13 AND 29 THEN '13 - 29 Months'
WHEN DATEDIFF(mm,INSTALL_DATE,dt) BETWEEN 30 AND 36 THEN '30 - 36 Months'
-- other ranges here
ELSE 'N/A'
END
FROM
#Calendar c
INNER JOIN YourActualTable i ON i.Install_Date <= c.Dt;

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 23:06:32
[ithe AGE_RANGE criteria comes from oracle[/i]

how are you bringing it to sql server db? have you set up a linked server connection?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-02-05 : 14:44:54
Thank you James K. - this is exactly what I was looking for.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 14:58:44
You are very welcome - glad to help.
Go to Top of Page
   

- Advertisement -