Labels

Apache Hadoop (3) ASP.NET (2) AWS S3 (2) Batch Script (3) BigQuery (21) BlobStorage (1) C# (3) Cloudera (1) Command (2) Data Model (3) Data Science (1) Django (1) Docker (1) ETL (7) Google Cloud (5) GPG (2) Hadoop (2) Hive (3) Luigi (1) MDX (21) Mongo (3) MYSQL (3) Pandas (1) Pentaho Data Integration (5) PentahoAdmin (13) Polybase (1) Postgres (1) PPS 2007 (2) Python (13) R Program (1) Redshift (3) SQL 2016 (2) SQL Error Fix (18) SQL Performance (1) SQL2012 (7) SQOOP (1) SSAS (20) SSH (1) SSIS (42) SSRS (17) T-SQL (75) Talend (3) Vagrant (1) Virtual Machine (2) WinSCP (1)

Thursday, February 17, 2011

Cross Apply and Outer Apply in SQL

DECLARE @Year INT
SET @Year = 2008

SELECT  ISNULL(ROUND(a.SalesAmount,0),0) AS CurrentSales
                     , ISNULL(ROUND(b.SalesAmount,0),0) As PreviousSales
                     ,a.MONTH As 'Month'
            FROM
                      (SELECT    SUM(Fs.SalesAmt) AS SalesAmount
                          ,DD.MonthName AS 'Month'
                           ,DD.MonthNumber
                              FROM    FactSales FS
                              JOIN     DimDate DD on DD.DateKey = FS.DateKey
                              WHERE    DD.Year = @Year
                              GROUP BY DD.MonthName
                           ,DD.MonthNumber
                          ) AS a
             OUTER APPLY
             --CROSS APPLY
                         (SELECT    Sum(Fs.SalesAmt) AS SalesAmount
                                      ,DD.MONTHNAME AS 'Month'
                                      ,DD.MonthNumber
                        FROM    FactSales FS
                        JOIN     DimDate DD ON DD.DateKey = FS.DateKey
                        WHERE     DD.Year = @Year - 1
                                    and   DD.MonthNumber = a.MonthNumber
                        GROUP BY DD.MONTHNAME
                           ,DD.MonthNumber
                         ) AS b

Consider ther is no data for year 2007, then on replacing ‘Outer Apply’ with ‘Cross Apply’ no data will be displayed. But using Outer Apply will display data for the year 2008 (current Sales) and 0 for 2007 (Previous Sales)

No comments:

Post a Comment