April 2, 2012 at 8:13 pm
Hi All,
I have to create report based on Linear regression formula. Linear Regression formula create Store Procedure but Not giving me exact result Below is Store Procedure which I created.:
USE [Vision]
GO
/****** Object: StoredProcedure [dbo].[uspLinearRegression] Script Date: 04/02/2012 21:47:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspLinearRegression]
AS
BEGIN
DECLARE @n bigint
SET @n = (SELECT COUNT(ItemID)
FROM Common.t_Visit_Monthly)
SELECT ItemID,YEAR,TotalVisits,COUNT(*) AS n
INTO #lr
FROM Common.t_Visit_Monthly
GROUP BY ItemID,YEAR,TotalVisits
SELECT
((@n * sum([YEAR]*TotalVisits)) - (sum([Year])*sum(TotalVisits)))/
((@n * sum(Power([Year],2)))-Power(Sum([Year]),2)) AS Slope,
avg(TotalVisits) - ((@n * sum([Year]*TotalVisits)) - (sum([Year])*sum(TotalVisits)))/
((@n * sum(Power([Year],2)))-Power(Sum([Year]),2)) * avg([Year]) as Intercept,
ItemID
INTO #lr1
FROM #lr
GROUP BY ItemID
SELECT VM.ItemID,
VM.TotalVisits,VM.Year,
#lr1.Slope,#lr1.Intercept,
(Intercept+(Slope*VM.[Year])) AS Y
FROM #lr1
INNER JOIN Common.t_Visit_Monthly VM ON VM.ItemID = #lr1.ItemID
DROP TABLE #lr
DROP TABLE #lr1
END
GO
-----------------------------------------------------------------------------
Below is my result which I am getting:
ItemIDTotalVisitsYearSlopeInterceptY
127201002727
226201002626
362010066
415201001515
510020100100100
611201001111
711120100111111
870201007070
959201005959
1087201008787
1131201003131
1224201002424
1352010055
----------------------------------------------------------------------------
Linear Regression Formula Which I used for this store procedure:-
/* y = mx + b
m = (nSxy - SxSy) / (nSxx - SxSx)
b = Ay - (Ax * m)
N.B. S = Sum, A = Avg */
@n = Count(*) FROM [dbo].
Select
((@n * Sum(X*Y)) - (Sum(X) * Sum(Y)))/
((@n * Sum(X*X)) - (Sum(X) * Sum(X))) AS M,
Avg(Y) - Avg(X) *
((@n * Sum(X*Y)) - (Sum(X) * Sum(Y)))/
((@n * Sum(X*X)) - (Sum(X) * Sum(X))) AS B,
FROM [dbo].
--------------------------------------------------------------------------
Based on this I had Create report:- See The Attachment Report
TrendReport :- which I am getting Chart report based on Store Procedure.
But I want Graph Like Attched another document name called 'NeedThisGraph'
----------------------------------------------------
I really appriciate If someone would like to help me out with this report
Thanks You.
April 3, 2012 at 9:58 am
I really appreciate to taking time to look this.
Please can anyone help me out?
April 3, 2012 at 10:38 am
Please provide DDL for the responsible tables and sample data with the DML to populate it. Please reference the article in my signature below.
To get a jump on this, though, my assumption is that the data in the [YEAR] column is something like 2010 or 2011... Why would you use that in your formula?
Jared
CE - Microsoft
April 3, 2012 at 2:53 pm
/* Sample Test Table */
CREATE TABLE [Common].[t_Visit_Monthly](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[Year] [bigint] NULL,
[Month] [int] NULL,
[FirstOfMonth] [date] NULL,
[VisitTypeID] [int] NULL,
[DoctorID] [int] NULL,
[FacilityID] [int] NULL,
[TotalVisits] [bigint] NULL
)
/*Insert Sample Data*/
INSERT INTO [Common].[t_Visit_Monthly]
([Year]
,[Month]
,[FirstOfMonth]
,[VisitTypeID]
,[DoctorID]
,[FacilityID]
,[TotalVisits]
)
VALUES
(2010,1,'2010-01-01',1,4,1,27)
(2010,1,'2010-01-01',1,6,1,26)
(2010,1,'2010-01-01',1,8,1,6)
(2010,2,'2010-02-01',1,77,5,5)
(2010,2,'2010-02-01',1,12,7,8)
(2010,2,'2010-02-01',1,49,7,20)
(2010,2,'2010-02-01',1,61,7,27)
(2011,1,'2011-01-01',1,1,1,112)
(2011,1,'2011-01-01',1,4,1,21)
(2011,1,'2011-01-01',1,1,1,112)
(2011,2,'2011-02-01',2,54,11,32)
(2011,3,'2011-03-01',3,16,5,10)
(2011,3,'2011-03-01',3,29,5,9)
(2012,1,'2011-01-01',1,1,1,131)
(2012,1,'2011-01-01',1,7,1,91)
(2012,2,'2011-02-01',2,149,14,15)
(2012,2,'2011-02-01',2,6,15,105)
(2012,1,'2011-01-01',1,1,1,131)
(2012,3,'2011-03-01',3,1,1,20 )
/*Below Created Store Procedure Which I made some changes Previous one*/
CREATE PROCEDURE [dbo].[uspLinearRegression_2]
AS
BEGIN
SELECT YEAR,Month,SUM(TotalVisits)AS TotalVisits,COUNT(*) AS n
INTO #lr
FROM Common.t_Visit_Monthly
--WHERE YEAR In (2010,2011)
GROUP BY YEAR,Month
--SELECT * FROM #lr Where Month In (1,2)
ORDER BY Month
DECLARE @YEAR INT
SELECT L1.Year,L1.Month,(L1.TotalVisits) AS TotalVisits,
CASE WHEN ((L1.n * sum(Power(L1.Month,2)))-Power(Sum(L1.Month),2)) = 0
THEN 0 ELSE
((L1.n * sum(L1.Month*L1.TotalVisits)) - (sum(L1.Month)*sum(L1.TotalVisits)))/
((L1.n * sum(Power(L1.Month,2)))-Power(Sum(L1.Month),2))
END AS Slope,--This is M for regression equation(Y= MX+B)
CASE WHEN ((L1.n * sum(Power(L1.Month,2)))-Power(Sum(L1.Month),2)) * avg(L1.Month) =0
THEN 0 ELSE
avg(L1.TotalVisits) - ((L1.n * sum(L1.Month*L1.TotalVisits)) - (sum(L1.Month)*sum(L1.TotalVisits)))/
((L1.n * sum(Power(L1.Month,2)))-Power(Sum(L1.Month),2)) * avg(L1.Month)
END AS Intercept--This is B for regression equation(Y= MX+B)
INTO #lr1
FROM #lr L1
INNER JOIN #lr L2 ON L1.Month =L2.Month and L1.Year=L2.Year and L1.TotalVisits=L2.TotalVisits
--WHERE L1.Year In (2010,2011)
GROUP BY L1.Month,L1.TotalVisits,L1.n,L1.Year
SELECT
SUM(VM.TotalVisits) AS TotalVisits,VM.Year,VM.Month,
#lr1.Slope,#lr1.Intercept,
--Intercept+Slope AS Y,
(Intercept+(Slope*VM.[Month])) AS Y1--This is Y= B+MX
FROM #lr1
INNER JOIN Common.t_Visit_Monthly VM ON VM.Month = #lr1.Month AND VM.Year = #lr1.Year
GROUP BY VM.Month,VM.Year,#lr1.Intercept,#lr1.Slope
DROP TABLE #lr
DROP TABLE #lr1
END
GO
-------------------------------------------------------------------------------------------------------------------------------
I used Year and Month for X-axis and For Y-axis Used TotalVistis.
April 3, 2012 at 3:09 pm
Ok, so now can you tell me what the problem is? Which values are not correct?
Jared
CE - Microsoft
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply