A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


Cross apply and a quick date split function

02 June 2011 Don-Rickman SQL Server - T-SQL (0)
 
APPLY....
 
In my words: Finally, Parameterized Views, kinda... .
Pass current row values to your Table Valued View. ...
 
 
In technets words: The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.--Technet

 
 

  

---cross apply example. SQL2K5 +

---This example uses a table valued function to split a date into multiple fragments.

---I primarily use this for adhoc reports in which i'm going to include a pivot table.

 

 

--- create table valued function

 

create function dbo.date_diffuse(@datetime datetime) 

returns table as 

RETURN ( select  

 year(@datetime) [dt_year]       

 , month(@datetime) [dt_month]        

 , dateName(month,@datetime) [dt_month_name]       

 , datepart(week, @datetime) [dt_week]   

 , day(@datetime) [dt_day]         

 , datepart(hh,@datetime) dt_hour

 , convert(varchar(10), @datetime, 101) [dt]  

)

 

go

 

 

declare @random_data table (data varchar(128), created datetime)

 

insert into @random_data (data, created) values ('record 1', '2011-06-14 19:59:06.950')

insert into @random_data (data, created) values ('record 2', '2011-05-02 12:15:10.111')

insert into @random_data (data, created) values ('record 3', '2011-04-03 04:01:01.232')

insert into @random_data (data, created) values ('record 4', '2011-03-21 23:30:00.000')

 

select *

from @random_data rd

cross apply dbo.date_diffuse(rd.created)

 

 

 

     

 
 
 
 
Example: