Sunday, August 3, 2014

Intersect

The Intersect functionality is of the three Venn Collection functionality's (EXCEPT, INTERSECT, UNION).
Intersect will give all the members of SET1 which are also in SET2. By default it will remove any duplicates.
SELECT
    INTERSECT        (
            {
                 [Date].[Calendar Year].&[2010]
                ,[Date].[Calendar Year].&[2012]
                ,[Date].[Calendar Year].&[2013]
                ,[Date].[Calendar Year].&[2013]
            } //SET 1
            ,
            {
                  [Date].[Calendar Year].&[2012]
                 ,[Date].[Calendar Year].&[2013]
                 ,[Date].[Calendar Year].&[2013]
                 ,[Date].[Calendar Year].&[2014]
            } //SET 2
          ) ON COLUMNS,
        [Measures].[Sales Amount] on ROWS
FROM
[Adventure Works]
Will get you:
image
(Only [CY 2012] and [CY2013] are in both sets)
If we add the ALL flag duplicates will be visible:
SELECT
    INTERSECT
        (
            {
                 [Date].[Calendar Year].&[2010]
                ,[Date].[Calendar Year].&[2012]
                ,[Date].[Calendar Year].&[2013]
                ,[Date].[Calendar Year].&[2013]
            } //SET 1
            ,
            {
                  [Date].[Calendar Year].&[2012]
                 ,[Date].[Calendar Year].&[2013]
                 ,[Date].[Calendar Year].&[2013]
                 ,[Date].[Calendar Year].&[2014]
            } //SET 2
            , ALL 
        ) ON COLUMNS,
        [Measures].[Sales Amount] on ROWS
FROM
[Adventure Works]
image
([CY2013] is a duplicate, it doesn’t matter in which set it’s a duplicate.)
Have Fun And Till Next Time

No comments:

Post a Comment