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 2008 Forums
 Transact-SQL (2008)
 Predicting based on a INSTALL_DATE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nietzky
Yak Posting Veteran

72 Posts

Posted - 02/04/2013 :  17:28:46  Show Profile  Reply with Quote
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.

Edited by - nietzky on 02/04/2013 17:34:49

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 02/04/2013 :  19:03:46  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 02/04/2013 :  23:06:32  Show Profile  Reply with Quote
[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

72 Posts

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

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 02/05/2013 :  14:58:44  Show Profile  Reply with Quote
You are very welcome - glad to help.
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.06 seconds. Powered By: Snitz Forums 2000