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)   
 ----we need create function with cte because need select multiple rows
     with insert_cte (StartTime , EndTime ,StartPrice ,EndPrice ,  ChangePrice )
     as   (
  StartTime=CAST(v.Date_ as char(12)),
  EndTime=CAST(a.Date_ as char(12)),
  ChangePrice=SUBSTRING('- +',SIGN(a.Sales-v.Sales)+2,1) + CAST(ABS(a.Sales -v.Sales)As varchar)
   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_,
   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
--- 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


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