SQL Server Table data Trend Analysis

  • Article

Source:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0eeb274a-0894-4eb4-8af2-58514dc9651c/trend-query-in-sql-server-2012?forum=transactsql#c49b6d5e-6c65-4896-b7ed-95e9d5455264

Sample Scenario/ Problem Statement 

Important to know:for this quering there are various methods 

For example,  we can call Phyton or R scripts with sql Sql server external scripts.

But this article shows the power of SQL Scripts, because with table valued functions and success cross joins we can have a very simple result.

Sample Code 

1) Create table and add some data 

    

create table  select_trend_table  (Areas char(10),Date_ smalldatetime,Sales int)
 
insert select_trend_table(Areas,Date_,Sales)values('Boston','19990901',28)
insert select_trend_table(Areas,Date_,Sales)values('New York','19991001',25)
insert select_trend_table(Areas,Date_,Sales)values('Boston','19991101',13)
insert select_trend_table(Areas,Date_,Sales)values('Boston','19991201',15)
insert select_trend_table(Areas,Date_,Sales)values('New York','20000101',35)
insert select_trend_table(Areas,Date_,Sales)values('Chicago','20000201',38)
insert select_trend_table(Areas,Date_,Sales)values('Chicago','20000301',16)
insert select_trend_table(Areas,Date_,Sales)values('New York','20000301',16)

2) Create table valued function 

   

    
CREATE   FUNCTION  dbo.GetTrend_Analyse(@Areas char(10))  
       RETURNS @ReturnData TABLE  
(  
  Areas char(20),
  StartTime nvarchar(max),
  EndTime nvarchar(max),
  StartPrice int,
  EndPrice int,
 ChangePrice varchar(20)    
)   
AS  
 
BEGIN 
 ----we need create function with cte because need select multiple rows
     with insert_cte (StartTime , EndTime ,StartPrice ,EndPrice ,  ChangePrice ) 
     as   (
  SELECT  
  StartTime=CAST(v.Date_ as  char(12)),
  EndTime=CAST(a.Date_ as  char(12)),
  StartPrice=v.Sales,
  EndPrice=a.Sales,
  ChangePrice=SUBSTRING('- +',SIGN(a.Sales-v.Sales)+2,1) + CAST(ABS(a.Sales -v.Sales)As varchar)
 FROM
 (
   SELECT Date_,
   Sales ,
   ranking=(select count(distinct Date_)
   from select_trend_table u
   where u.Date_<=l.Date_ and u.Areas=@Areas 
   )
   from select_trend_table l where l.Areas=@Areas ) v left outer join
   (
   select Date_,
   Sales,
   ranking=(select count(distinct Date_) from select_trend_table u
   where u.Date_ <= l.Date_ and u.Areas=@Areas  )
   from select_trend_table l where l.Areas=@Areas) a
   on (a.ranking=v.ranking+1 )
   where a.Date_ is not null  
    )   
---and inserting massive data from cte to return table data
        INSERT @ReturnData  
 
        SELECT @Areas ,StartTime , EndTime ,StartPrice ,EndPrice ,  ChangePrice  from  insert_cte
         
    RETURN;  
END;  
GO  
 
--- and result succes
 
   
 
  select * from dbo.GetTrend_Analyse('New York')
   
 
 
Areas                                         StartTime     EndTime        StartPrice    EndPrice    ChangePrice
New York                окт  1 1999     янв  1 2000     25          35           +10
New York                янв  1 2000     мар  1 2000     35          16           -19

    

Back to top

See Also

<other Wiki articles on this topic>

Back to top

References

You can see also cross join and table valued parameter articles from msdn documentation.