How to use CROSS APPLY in sql server

Problem: How to get evaluated rows output based on a table valued expressions.

Solution: APPLY, SQL Server 2005 or higher.

APPLY key word can be used to join a table valued function so it can return each row evaluated, the right table expression is processed every time for each row from the left table expression.

There are two types

1.       CROSS APPLY – Similar to INNER JOIN

2.       OUTER APPLY – Similar to OUTER JOIN

Example:

In this article I am going to use how we can get a logical use of CROSS APPLY, to do this I am creating a invoice table and populate some invoices with junk data and then retrieve invoices for each month first day.

Script to create table and populate the data.

CREATE TABLE [dbo].[Invoice]

(

                [Invoiceid] [int] NOT NULL,

                [itemid] [int] NULL,

                [unitprice] [numeric](18, 6) NULL,

                [quantity] [int] NULL,

                [lineitem] [int] NULL,

                [invoicedate] [datetime] NULL

)

GO

insert into dbo.Invoice

(

invoiceid,

itemid,

unitprice,

quantity,

lineitem,

invoicedate

)

select

            ABS(CHECKSUM(NewId())) % 1124120 as invoiceid

                                                ,    ABS(CHECKSUM(NewId())) % 1400 as itemid

       , (

             SELECT TOP (1) cast (c1 as int)

             FROM (

             VALUES ('123'), ('234'), ('345'), ('456'), ('678')

             ) AS T1(c1)

             ORDER BY ABS(CHECKSUM(newid()))% 10.00

       ) as  unitprice, ABS(CHECKSUM(NewId())) % 11, ROW_NUMBER() OVER (ORDER BY (Select 1 )) as lineitem, DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0) as invoicedate   GO   100000

Here you go I am creating dates using a master.dbo.spt_values to get an auto increment and starting the day from 10 years backwards. This can be a useful knife some times to generate dates to match the search criteria.

After I have generated 1stday of each month of every year, I am going to use this logic to apply with cross apply to get the match

SELECT  top 100 i.invoiceid,i.itemid,i.unitprice,i.quantity,i.lineitem,i.invoicedate

FROM dbo.Invoice i

CROSS APPLY (

                SELECT *

                FROM (                                                

                                                SELECT  top 10 invoiceid,itemid,unitprice,quantity,lineitem,invoicedate

FROM dbo.Invoice i

                where day(invoicedate)=1 --selects only first day of the month

                order by quantity*unitprice desc –orders by quantity*unitprice desc

                                ) a

                WHERE convert(VARCHAR, i.invoicedate, 112) = convert(VARCHAR, a.invoicedate, 112)

                ) b

                order by invoicedate

GO

OUTPUT: